OpenPyXL 教程 · ZetCode 中文系列教程

# OpenPyXL 教程 > 原文： [http://zetcode.com/python/openpyxl/](http://zetcode.com/python/openpyxl/) 在本教程中，我們展示如何使用 OpenPyXL 庫在 Python 中使用 Excel 文件。 ## OpenPyXL OpenPyXL 是用于讀取和寫入 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 庫。 ## Excel xlsx 在本教程中，我們使用 xlsx 文件。 xlsx 是 Microsoft Excel 使用的開放 XML 電子表格文件格式的文件擴展名。 xlsm 文件支持宏。 xlsx 是專有的二進制格式，而 xlsx 是基于 Office Open XML 格式的。 ```py $ sudo pip3 install openpyxl ``` 我們使用`pip3`工具安裝 OpenPyXL。 ## OpenPyXL 創建新文件在第一個示例中，我們使用 OpenPyXL 創建一個新的 xlsx 文件。 `write_xlsx.py` ```py #!/usr/bin/env python from openpyxl import Workbook import time book = Workbook() sheet = book.active sheet['A1'] = 56 sheet['A2'] = 43 now = time.strftime("%x") sheet['A3'] = now book.save("sample.xlsx") ``` 在示例中，我們創建一個新的 xlsx 文件。我們將數據寫入三個單元格。 ```py from openpyxl import Workbook ``` 從 OpenPyXL 模塊，我們導入`Workbook`類。工作簿是文檔所有其他部分的容器。 ```py book = Workbook() ``` 我們創建一個新的工作簿。始終使用至少一個工作表創建一個工作簿。 ```py sheet = book.active ``` 我們獲得對活動工作表的引用。 ```py sheet['A1'] = 56 sheet['A2'] = 43 ``` 我們將數值數據寫入單元格`A1`和`A2`。 ```py now = time.strftime("%x") sheet['A3'] = now ``` 我們將當前日期寫入單元格`A3`。 ```py book.save("sample.xlsx") ``` 我們使用`save()`方法將內容寫入`sample.xlsx`文件。 ![New file](https://img.kancloud.cn/96/4d/964d16594d79c7107587cc45f7b4290d_419x181.jpg) 圖：新文件 ## OpenPyXL 寫入單元格寫入單元格有兩種基本方法：使用工作表的鍵（例如`A1`或`D3`），或通過`cell()`方法使用行和列表示法。 `write2cell.py` ```py #!/usr/bin/env python from openpyxl import Workbook book = Workbook() sheet = book.active sheet['A1'] = 1 sheet.cell(row=2, column=2).value = 2 book.save('write2cell.xlsx') ``` 在示例中，我們將兩個值寫入兩個單元格。 ```py sheet['A1'] = 1 ``` 在這里，我們將數值分配給`A1`單元。 ```py sheet.cell(row=2, column=2).value = 2 ``` 在這一行中，我們用行和列表示法寫入單元格`B2`。 ## OpenPyXL 附加值使用`append()`方法，我們可以在當前工作表的底部附加一組值。 `appending_values.py` ```py #!/usr/bin/env python from openpyxl import Workbook book = Workbook() sheet = book.active rows = ( (88, 46, 57), (89, 38, 12), (23, 59, 78), (56, 21, 98), (24, 18, 43), (34, 15, 67) ) for row in rows: sheet.append(row) book.save('appending.xlsx') ``` 在示例中，我們將三列數據附加到當前工作表中。 ```py rows = ( (88, 46, 57), (89, 38, 12), (23, 59, 78), (56, 21, 98), (24, 18, 43), (34, 15, 67) ) ``` 數據存儲在元組的元組中。 ```py for row in rows: sheet.append(row) ``` 我們逐行瀏覽容器，并使用`append()`方法插入數據行。 ## OpenPyXL 讀取單元格在下面的示例中，我們從`sample.xlsx`文件中讀取先前寫入的數據。 `read_cells.py` ```py #!/usr/bin/env python import openpyxl book = openpyxl.load_workbook('sample.xlsx') sheet = book.active a1 = sheet['A1'] a2 = sheet['A2'] a3 = sheet.cell(row=3, column=1) print(a1.value) print(a2.value) print(a3.value) ``` 該示例加載一個現有的 xlsx 文件并讀取三個單元格。 ```py book = openpyxl.load_workbook('sample.xlsx') ``` 使用`load_workbook()`方法打開文件。 ```py a1 = sheet['A1'] a2 = sheet['A2'] a3 = sheet.cell(row=3, column=1) ``` 我們讀取`A1`，`A2`和`A3`單元的內容。在第三行中，我們使用`cell()`方法獲取`A3`單元格的值。 ```py $ ./read_cells.py 56 43 10/26/16 ``` 這是示例的輸出。 ## OpenPyXL 讀取多個單元格我們有以下數據表： ![Items](https://img.kancloud.cn/12/54/12546b0405a6dbdb15a5a4e88d2aa7c1_291x134.jpg) 圖：項目我們使用范圍運算符讀取數據。 `read_cells2.py` ```py #!/usr/bin/env python import openpyxl book = openpyxl.load_workbook('items.xlsx') sheet = book.active cells = sheet['A1': 'B6'] for c1, c2 in cells: print("{0:8} {1:8}".format(c1.value, c2.value)) ``` 在示例中，我們使用范圍運算從兩列讀取數據。 ```py cells = sheet['A1': 'B6'] ``` 在這一行中，我們從單元格`A1-B6`中讀取數據。 ```py for c1, c2 in cells: print("{0:8} {1:8}".format(c1.value, c2.value)) ``` `format()`函數用于在控制臺上整潔地輸出數據。 ```py $ ./read_cells2.py Items Quantity coins 23 chairs 3 pencils 5 bottles 8 books 30 ``` 這是程序的輸出。 ## OpenPyXL 按行迭代 `iter_rows()`方法將工作表中的單元格返回為行。 `iterating_by_rows.py` ```py #!/usr/bin/env python from openpyxl import Workbook book = Workbook() sheet = book.active rows = ( (88, 46, 57), (89, 38, 12), (23, 59, 78), (56, 21, 98), (24, 18, 43), (34, 15, 67) ) for row in rows: sheet.append(row) for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3): for cell in row: print(cell.value, end=" ") print() book.save('iterbyrows.xlsx') ``` 該示例逐行遍歷數據。 ```py for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3): ``` 我們提供了迭代的邊界。 ```py $ ./iterating_by_rows.py 88 46 57 89 38 12 23 59 78 56 21 98 24 18 43 34 15 67 ``` 這是示例的輸出。 ## OpenPyXL 按列迭代 `iter_cols()`方法將工作表中的單元格作為列返回。 `iterating_by_columns.py` ```py #!/usr/bin/env python from openpyxl import Workbook book = Workbook() sheet = book.active rows = ( (88, 46, 57), (89, 38, 12), (23, 59, 78), (56, 21, 98), (24, 18, 43), (34, 15, 67) ) for row in rows: sheet.append(row) for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3): for cell in row: print(cell.value, end=" ") print() book.save('iterbycols.xlsx') ``` 該示例逐列遍歷數據。 ```py $ ./iterating_by_columns.py 88 89 23 56 24 34 46 38 59 21 18 15 57 12 78 98 43 67 ``` 這是示例的輸出。 ## 統計對于下一個示例，我們需要創建一個包含數字的 xlsx 文件。例如，我們使用`RANDBETWEEN()`函數在 10 列中創建了 25 行數字。 `mystats.py` ```py #!/usr/bin/env python import openpyxl import statistics as stats book = openpyxl.load_workbook('numbers.xlsx', data_only=True) sheet = book.active rows = sheet.rows values = [] for row in rows: for cell in row: values.append(cell.value) print("Number of values: {0}".format(len(values))) print("Sum of values: {0}".format(sum(values))) print("Minimum value: {0}".format(min(values))) print("Maximum value: {0}".format(max(values))) print("Mean: {0}".format(stats.mean(values))) print("Median: {0}".format(stats.median(values))) print("Standard deviation: {0}".format(stats.stdev(values))) print("Variance: {0}".format(stats.variance(values))) ``` 在示例中，我們從工作表中讀取所有值并計算一些基本統計信息。 ```py import statistics as stats ``` 導入`statistics`模塊以提供一些統計函數，例如中值和方差。 ```py book = openpyxl.load_workbook('numbers.xlsx', data_only=True) ``` 使用`data_only`選項，我們從單元格而不是公式中獲取值。 ```py rows = sheet.rows ``` 我們得到所有不為空的單元格行。 ```py for row in rows: for cell in row: values.append(cell.value) ``` 在兩個`for`循環中，我們從單元格中形成一個整數值列表。 ```py print("Number of values: {0}".format(len(values))) print("Sum of values: {0}".format(sum(values))) print("Minimum value: {0}".format(min(values))) print("Maximum value: {0}".format(max(values))) print("Mean: {0}".format(stats.mean(values))) print("Median: {0}".format(stats.median(values))) print("Standard deviation: {0}".format(stats.stdev(values))) print("Variance: {0}".format(stats.variance(values))) ``` 我們計算并打印有關值的數學統計信息。一些函數是內置的，其他函數是通過`statistics`模塊導入的。 ```py $ ./mystats.py Number of values: 312 Sum of values: 15877 Minimum value: 0 Maximum value: 100 Mean: 50.88782051282051 Median: 54.0 Standard deviation: 28.459203819700967 Variance: 809.9262820512821 ``` 這是一個示例輸出。 ## OpenPyXL 過濾器&排序數據圖紙具有`auto_filter`屬性，該屬性允許設置過濾條件和排序條件。請注意，OpenPyXL 設置了條件，但是我們必須在電子表格應用中應用它們。 `filter_sort.py` ```py #!/usr/bin/env python from openpyxl import Workbook wb = Workbook() sheet = wb.active data = [ ['Item', 'Colour'], ['pen', 'brown'], ['book', 'black'], ['plate', 'white'], ['chair', 'brown'], ['coin', 'gold'], ['bed', 'brown'], ['notebook', 'white'], ] for r in data: sheet.append(r) sheet.auto_filter.ref = 'A1:B8' sheet.auto_filter.add_filter_column(1, ['brown', 'white']) sheet.auto_filter.add_sort_condition('B2:B8') wb.save('filtered.xlsx') ``` 在示例中，我們創建一個包含項目及其顏色的工作表。我們設置一個過濾器和一個排序條件。 ## OpenPyXL 維度為了獲得那些實際包含數據的單元格，我們可以使用維度。 `dimensions.py` ```py #!/usr/bin/env python from openpyxl import Workbook book = Workbook() sheet = book.active sheet['A3'] = 39 sheet['B3'] = 19 rows = [ (88, 46), (89, 38), (23, 59), (56, 21), (24, 18), (34, 15) ] for row in rows: sheet.append(row) print(sheet.dimensions) print("Minimum row: {0}".format(sheet.min_row)) print("Maximum row: {0}".format(sheet.max_row)) print("Minimum column: {0}".format(sheet.min_column)) print("Maximum column: {0}".format(sheet.max_column)) for c1, c2 in sheet[sheet.dimensions]: print(c1.value, c2.value) book.save('dimensions.xlsx') ``` 該示例計算兩列數據的維數。 ```py sheet['A3'] = 39 sheet['B3'] = 19 rows = [ (88, 46), (89, 38), (23, 59), (56, 21), (24, 18), (34, 15) ] for row in rows: sheet.append(row) ``` 我們將數據添加到工作表。請注意，我們從第三行開始添加。 ```py print(sheet.dimensions) ``` `dimensions`屬性返回非空單元格區域的左上角和右下角單元格。 ```py print("Minimum row: {0}".format(sheet.min_row)) print("Maximum row: {0}".format(sheet.max_row)) ``` 使用`min_row`和`max_row`屬性，我們可以獲得包含數據的最小和最大行。 ```py print("Minimum column: {0}".format(sheet.min_column)) print("Maximum column: {0}".format(sheet.max_column)) ``` 通過`min_column`和`max_column`屬性，我們獲得了包含數據的最小和最大列。 ```py for c1, c2 in sheet[sheet.dimensions]: print(c1.value, c2.value) ``` 我們遍歷數據并將其打印到控制臺。 ```py $ ./dimensions.py A3:B9 Minimum row: 3 Maximum row: 9 Minimum column: 1 Maximum column: 2 39 19 88 46 89 38 23 59 56 21 24 18 34 15 ``` 這是示例的輸出。 ## 工作表每個工作簿可以有多個工作表。 ![Sheets](https://img.kancloud.cn/f7/7d/f77d84658b4da44acef338b3e5078140_265x41.jpg) 圖：床單讓我們有一張包含這三張紙的工作簿。 `sheets.py` ```py #!/usr/bin/env python import openpyxl book = openpyxl.load_workbook('sheets.xlsx') print(book.get_sheet_names()) active_sheet = book.active print(type(active_sheet)) sheet = book.get_sheet_by_name("March") print(sheet.title) ``` 該程序可用于 Excel 工作表。 ```py print(book.get_sheet_names()) ``` `get_sheet_names()`方法返回工作簿中可用工作表的名稱。 ```py active_sheet = book.active print(type(active_sheet)) ``` 我們獲取活動表并將其類型打印到終端。 ```py sheet = book.get_sheet_by_name("March") ``` 我們使用`get_sheet_by_name()`方法獲得對工作表的引用。 ```py print(sheet.title) ``` 檢索到的工作表的標題將打印到終端。 ```py $ ./sheets.py ['January', 'February', 'March'] <class 'openpyxl.worksheet.worksheet.Worksheet'> March ``` 這是程序的輸出。 `sheets2.py` ```py #!/usr/bin/env python import openpyxl book = openpyxl.load_workbook('sheets.xlsx') book.create_sheet("April") print(book.sheetnames) sheet1 = book.get_sheet_by_name("January") book.remove_sheet(sheet1) print(book.sheetnames) book.create_sheet("January", 0) print(book.sheetnames) book.save('sheets2.xlsx') ``` 在此示例中，我們創建一個新工作表。 ```py book.create_sheet("April") ``` 使用`create_sheet()`方法創建一個新圖紙。 ```py print(book.sheetnames) ``` 圖紙名稱也可以使用`sheetnames`屬性顯示。 ```py book.remove_sheet(sheet1) ``` 可以使用`remove_sheet()`方法將紙張取出。 ```py book.create_sheet("January", 0) ``` 可以在指定位置創建一個新圖紙。在我們的例子中，我們在索引為 0 的位置創建一個新工作表。 ```py $ ./sheets2.py ['January', 'February', 'March', 'April'] ['February', 'March', 'April'] ['January', 'February', 'March', 'April'] ``` 這是程序的輸出。可以更改工作表的背景顏色。 `sheets3.py` ```py #!/usr/bin/env python import openpyxl book = openpyxl.load_workbook('sheets.xlsx') sheet = book.get_sheet_by_name("March") sheet.sheet_properties.tabColor = "0072BA" book.save('sheets3.xlsx') ``` 該示例修改了標題為`"March"`的工作表的背景顏色。 ```py sheet.sheet_properties.tabColor = "0072BA" ``` 我們將`tabColor`屬性更改為新顏色。 ![Background colour of a worksheet](https://img.kancloud.cn/ad/6e/ad6efc671798008227de5109e0302b72_254x40.jpg) 圖：工作表的背景色第三工作表的背景色已更改為某種藍色。 ## 合并單元格單元格可以使用`merge_cells()`方法合并，而可以不使用`unmerge_cells()`方法合并。當我們合并單元格時，除了左上角的所有單元格都將從工作??表中刪除。 `merging_cells.py` ```py #!/usr/bin/env python from openpyxl import Workbook from openpyxl.styles import Alignment book = Workbook() sheet = book.active sheet.merge_cells('A1:B2') cell = sheet.cell(row=1, column=1) cell.value = 'Sunny day' cell.alignment = Alignment(horizontal='center', vertical='center') book.save('merging.xlsx') ``` 在該示例中，我們合并了四個單元格：`A1`，`B1`，`A2`和`B2`。最后一個單元格中的文本居中。 ```py from openpyxl.styles import Alignment ``` 為了使文本在最后一個單元格中居中，我們使用了`openpyxl.styles`模塊中的`Alignment`類。 ```py sheet.merge_cells('A1:B2') ``` 我們用`merge_cells()`方法合并四個單元格。 ```py cell = sheet.cell(row=1, column=1) ``` 我們得到了最后一個單元格。 ```py cell.value = 'Sunny day' cell.alignment = Alignment(horizontal='center', vertical='center') ``` 我們將文本設置為合并的單元格并更新其對齊方式。 ![Merged cells](https://img.kancloud.cn/f7/cb/f7cbcbbe0de6eced3a48b7fe955ab216_265x81.jpg) 圖：合并的單元格 ## OpenPyXL 凍結窗格凍結窗格時，在滾動到工作表的另一個區域時，我們會保持工作表的某個區域可見。 `freezing.py` ```py #!/usr/bin/env python from openpyxl import Workbook from openpyxl.styles import Alignment book = Workbook() sheet = book.active sheet.freeze_panes = 'B2' book.save('freezing.xlsx') ``` 該示例通過單元格`B2`凍結窗格。 ```py sheet.freeze_panes = 'B2' ``` 要凍結窗格，我們使用`freeze_panes`屬性。 ## OpenPyXL 公式下一個示例顯示如何使用公式。 OpenPyXL 不進行計算；它將公式寫入單元格。 `formulas.py` ```py #!/usr/bin/env python from openpyxl import Workbook book = Workbook() sheet = book.active rows = ( (34, 26), (88, 36), (24, 29), (15, 22), (56, 13), (76, 18) ) for row in rows: sheet.append(row) cell = sheet.cell(row=7, column=2) cell.value = "=SUM(A1:B6)" cell.font = cell.font.copy(bold=True) book.save('formulas.xlsx') ``` 在示例中，我們使用`SUM()`函數計算所有值的總和，并以粗體顯示輸出樣式。 ```py rows = ( (34, 26), (88, 36), (24, 29), (15, 22), (56, 13), (76, 18) ) for row in rows: sheet.append(row) ``` 我們創建兩列數據。 ```py cell = sheet.cell(row=7, column=2) ``` 我們得到顯示計算結果的單元格。 ```py cell.value = "=SUM(A1:B6)" ``` 我們將一個公式寫入單元格。 ```py cell.font = cell.font.copy(bold=True) ``` 我們更改字體樣式。 ![Calculating the sum of values](https://img.kancloud.cn/5c/60/5c601690e7def3f7cc764d6432fc9c65_189x162.jpg) 圖：計算值之和 ## OpenPyXL 圖像在下面的示例中，我們顯示了如何將圖像插入到工作表中。 `write_image.py` ```py #!/usr/bin/env python from openpyxl import Workbook from openpyxl.drawing.image import Image book = Workbook() sheet = book.active img = Image("icesid.png") sheet['A1'] = 'This is Sid' sheet.add_image(img, 'B2') book.save("sheet_image.xlsx") ``` 在示例中，我們將圖像寫到一張紙上。 ```py from openpyxl.drawing.image import Image ``` 我們使用`openpyxl.drawing.image`模塊中的`Image`類。 ```py img = Image("icesid.png") ``` 創建一個新的`Image`類。 `icesid.png`圖像位于當前工作目錄中。 ```py sheet.add_image(img, 'B2') ``` 我們使用`add_image()`方法添加新圖像。 ## OpenPyXL 圖表 OpenPyXL 庫支持創建各種圖表，包括條形圖，折線圖，面積圖，氣泡圖，散點圖和餅圖。根據文檔，OpenPyXL 僅支持在工作表中創建圖表。現有工作簿中的圖表將丟失。 `create_bar_chart.py` ```py #!/usr/bin/env python from openpyxl import Workbook from openpyxl.chart import ( Reference, Series, BarChart ) book = Workbook() sheet = book.active rows = [ ("USA", 46), ("China", 38), ("UK", 29), ("Russia", 22), ("South Korea", 13), ("Germany", 11) ] for row in rows: sheet.append(row) data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6) categs = Reference(sheet, min_col=1, min_row=1, max_row=6) chart = BarChart() chart.add_data(data=data) chart.set_categories(categs) chart.legend = None chart.y_axis.majorGridlines = None chart.varyColors = True chart.title = "Olympic Gold medals in London" sheet.add_chart(chart, "A8") book.save("bar_chart.xlsx") ``` 在此示例中，我們創建了一個條形圖，以顯示 2012 年倫敦每個國家/地區的奧運金牌數量。 ```py from openpyxl.chart import ( Reference, Series, BarChart ) ``` `openpyxl.chart`模塊具有使用圖表的工具。 ```py book = Workbook() sheet = book.active ``` 創建一個新的工作簿。 ```py rows = [ ("USA", 46), ("China", 38), ("UK", 29), ("Russia", 22), ("South Korea", 13), ("Germany", 11) ] for row in rows: sheet.append(row) ``` 我們創建一些數據并將其添加到活動工作表的單元格中。 ```py data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6) ``` 對于`Reference`類，我們引用表中代表數據的行。在我們的案例中，這些是奧運金牌的數量。 ```py categs = Reference(sheet, min_col=1, min_row=1, max_row=6) ``` 我們創建一個類別軸。類別軸是將數據視為一系列非數字文本標簽的軸。在我們的案例中，我們有代表國家名稱的文本標簽。 ```py chart = BarChart() chart.add_data(data=data) chart.set_categories(categs) ``` 我們創建一個條形圖并為其設置數據和類別。 ```py chart.legend = None chart.y_axis.majorGridlines = None ``` 使用`legend`和`majorGridlines`屬性，可以關閉圖例和主要網格線。 ```py chart.varyColors = True ``` 將`varyColors`設置為`True`，每個條形都有不同的顏色。 ```py chart.title = "Olympic Gold medals in London" ``` 為圖表設置標題。 ```py sheet.add_chart(chart, "A8") ``` 使用`add_chart()`方法將創建的圖表添加到工作表中。 ![Bar chart](https://img.kancloud.cn/90/75/90754c9cb630d67de01306dd7752aec0_597x441.jpg) 圖：條形圖在本教程中，我們使用了 OpenPyXL 庫。我們已經從 Excel 文件中讀取數據，并將數據寫入 Excel 文件中。您可能也對以下相關教程感興趣： [Python 教程](/lang/python/)， [Python CSV 教程](/python/csv/)， [Python SimpleJson 教程](/python/simplejson/)和 [Python 列表推導](/articles/pythonlistcomprehensions/) 。列出[所有 Python 教程](/all/#python)。