原文： 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 格式的。

  1. $ sudo pip3 install openpyxl

我们使用pip3工具安装 OpenPyXL。

OpenPyXL 创建新文件

在第一个示例中，我们使用 OpenPyXL 创建一个新的 xlsx 文件。

write_xlsx.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  4. import time
  6. book = Workbook()
  7. sheet = book.active
  9. sheet['A1'] = 56
  10. sheet['A2'] = 43
  12. now = time.strftime("%x")
  13. sheet['A3'] = now
  15. book.save("sample.xlsx")

在示例中，我们创建一个新的 xlsx 文件。 我们将数据写入三个单元格。

  1. from openpyxl import Workbook

从 OpenPyXL 模块，我们导入Workbook类。 工作簿是文档所有其他部分的容器。

  1. book = Workbook()

我们创建一个新的工作簿。 始终使用至少一个工作表创建一个工作簿。

  1. sheet = book.active

我们获得对活动工作表的引用。

  1. sheet['A1'] = 56
  2. sheet['A2'] = 43

我们将数值数据写入单元格A1和A2。

  1. now = time.strftime("%x")
  2. sheet['A3'] = now

我们将当前日期写入单元格A3。

  1. book.save("sample.xlsx")

我们使用save()方法将内容写入sample.xlsx文件。

OpenPyXL 教程 - 图1

图：新文件

OpenPyXL 写入单元格

写入单元格有两种基本方法：使用工作表的键（例如A1或D3），或通过cell()方法使用行和列表示法。

write2cell.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  5. book = Workbook()
  6. sheet = book.active
  8. sheet['A1'] = 1
  9. sheet.cell(row=2, column=2).value = 2
  11. book.save('write2cell.xlsx')

在示例中，我们将两个值写入两个单元格。

  1. sheet['A1'] = 1

在这里，我们将数值分配给A1单元。

  1. sheet.cell(row=2, column=2).value = 2

在这一行中，我们用行和列表示法写入单元格B2。

OpenPyXL 附加值

使用append()方法，我们可以在当前工作表的底部附加一组值。

appending_values.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  5. book = Workbook()
  6. sheet = book.active
  8. rows = (
  9.     (88, 46, 57),
  10.     (89, 38, 12),
  11.     (23, 59, 78),
  12.     (56, 21, 98),
  13.     (24, 18, 43),
  14.     (34, 15, 67)
  15. )
  17. for row in rows:
  18.     sheet.append(row)
  20. book.save('appending.xlsx')

在示例中，我们将三列数据附加到当前工作表中。

  1. rows = (
  2.     (88, 46, 57),
  3.     (89, 38, 12),
  4.     (23, 59, 78),
  5.     (56, 21, 98),
  6.     (24, 18, 43),
  7.     (34, 15, 67)
  8. )

数据存储在元组的元组中。

  1. for row in rows:
  2.     sheet.append(row)

我们逐行浏览容器，并使用append()方法插入数据行。

OpenPyXL 读取单元格

在下面的示例中，我们从sample.xlsx文件中读取先前写入的数据。

read_cells.py

  1. #!/usr/bin/env python
  3. import openpyxl
  5. book = openpyxl.load_workbook('sample.xlsx')
  7. sheet = book.active
  9. a1 = sheet['A1']
  10. a2 = sheet['A2']
  11. a3 = sheet.cell(row=3, column=1)
  13. print(a1.value)
  14. print(a2.value) 
  15. print(a3.value)

该示例加载一个现有的 xlsx 文件并读取三个单元格。

  1. book = openpyxl.load_workbook('sample.xlsx')

使用load_workbook()方法打开文件。

  1. a1 = sheet['A1']
  2. a2 = sheet['A2']
  3. a3 = sheet.cell(row=3, column=1)

我们读取A1，A2和A3单元的内容。 在第三行中，我们使用cell()方法获取A3单元格的值。

  1. $ ./read_cells.py 
  2. 56
  3. 43
  4. 10/26/16

这是示例的输出。

OpenPyXL 读取多个单元格

我们有以下数据表：

OpenPyXL 教程 - 图2

图：项目

我们使用范围运算符读取数据。

read_cells2.py

  1. #!/usr/bin/env python
  3. import openpyxl
  5. book = openpyxl.load_workbook('items.xlsx')
  7. sheet = book.active
  9. cells = sheet['A1': 'B6']
  11. for c1, c2 in cells:
  12.     print("{0:8} {1:8}".format(c1.value, c2.value))

在示例中，我们使用范围运算从两列读取数据。

  1. cells = sheet['A1': 'B6']

在这一行中，我们从单元格A1-B6中读取数据。

  1. for c1, c2 in cells:
  2.     print("{0:8} {1:8}".format(c1.value, c2.value))

format()函数用于在控制台上整洁地输出数据。

  1. $ ./read_cells2.py 
  2. Items    Quantity
  3. coins          23
  4. chairs          3
  5. pencils         5
  6. bottles         8
  7. books          30

这是程序的输出。

OpenPyXL 按行迭代

iter_rows()方法将工作表中的单元格返回为行。

iterating_by_rows.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  5. book = Workbook()
  6. sheet = book.active
  8. rows = (
  9.     (88, 46, 57),
  10.     (89, 38, 12),
  11.     (23, 59, 78),
  12.     (56, 21, 98),
  13.     (24, 18, 43),
  14.     (34, 15, 67)
  15. )
  17. for row in rows:
  18.     sheet.append(row)
  20. for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):
  21.     for cell in row:
  22.         print(cell.value, end=" ")
  23.     print()    
  25. book.save('iterbyrows.xlsx')

该示例逐行遍历数据。

  1. for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):

我们提供了迭代的边界。

  1. $ ./iterating_by_rows.py 
  2. 88 46 57 
  3. 89 38 12 
  4. 23 59 78 
  5. 56 21 98 
  6. 24 18 43 
  7. 34 15 67

这是示例的输出。

OpenPyXL 按列迭代

iter_cols()方法将工作表中的单元格作为列返回。

iterating_by_columns.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  5. book = Workbook()
  6. sheet = book.active
  8. rows = (
  9.     (88, 46, 57),
  10.     (89, 38, 12),
  11.     (23, 59, 78),
  12.     (56, 21, 98),
  13.     (24, 18, 43),
  14.     (34, 15, 67)
  15. )
  17. for row in rows:
  18.     sheet.append(row)
  20. for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3):
  21.     for cell in row:
  22.         print(cell.value, end=" ")
  23.     print()    
  25. book.save('iterbycols.xlsx')

该示例逐列遍历数据。

  1. $ ./iterating_by_columns.py 
  2. 88 89 23 56 24 34 
  3. 46 38 59 21 18 15 
  4. 57 12 78 98 43 67

这是示例的输出。

统计

对于下一个示例，我们需要创建一个包含数字的 xlsx 文件。 例如，我们使用RANDBETWEEN()函数在 10 列中创建了 25 行数字。

mystats.py

  1. #!/usr/bin/env python
  3. import openpyxl
  4. import statistics as stats
  6. book = openpyxl.load_workbook('numbers.xlsx', data_only=True)
  8. sheet = book.active
  10. rows = sheet.rows
  12. values = []
  14. for row in rows:
  15.     for cell in row:
  16.         values.append(cell.value)
  18. print("Number of values: {0}".format(len(values)))
  19. print("Sum of values: {0}".format(sum(values)))
  20. print("Minimum value: {0}".format(min(values)))
  21. print("Maximum value: {0}".format(max(values)))
  22. print("Mean: {0}".format(stats.mean(values)))
  23. print("Median: {0}".format(stats.median(values)))
  24. print("Standard deviation: {0}".format(stats.stdev(values)))
  25. print("Variance: {0}".format(stats.variance(values)))

在示例中，我们从工作表中读取所有值并计算一些基本统计信息。

  1. import statistics as stats

导入statistics模块以提供一些统计函数，例如中值和方差。

  1. book = openpyxl.load_workbook('numbers.xlsx', data_only=True)

使用data_only选项，我们从单元格而不是公式中获取值。

  1. rows = sheet.rows

我们得到所有不为空的单元格行。

  1. for row in rows:
  2.     for cell in row:
  3.         values.append(cell.value)

在两个for循环中，我们从单元格中形成一个整数值列表。

  1. print("Number of values: {0}".format(len(values)))
  2. print("Sum of values: {0}".format(sum(values)))
  3. print("Minimum value: {0}".format(min(values)))
  4. print("Maximum value: {0}".format(max(values)))
  5. print("Mean: {0}".format(stats.mean(values)))
  6. print("Median: {0}".format(stats.median(values)))
  7. print("Standard deviation: {0}".format(stats.stdev(values)))
  8. print("Variance: {0}".format(stats.variance(values)))

我们计算并打印有关值的数学统计信息。 一些函数是内置的，其他函数是通过statistics模块导入的。

  1. $ ./mystats.py 
  2. Number of values: 312
  3. Sum of values: 15877
  4. Minimum value: 0
  5. Maximum value: 100
  6. Mean: 50.88782051282051
  7. Median: 54.0
  8. Standard deviation: 28.459203819700967
  9. Variance: 809.9262820512821

这是一个示例输出。

OpenPyXL 过滤器&排序数据

图纸具有auto_filter属性，该属性允许设置过滤条件和排序条件。

请注意，OpenPyXL 设置了条件，但是我们必须在电子表格应用中应用它们。

filter_sort.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  5. wb = Workbook()
  6. sheet = wb.active
  8. data = [
  9.     ['Item', 'Colour'],
  10.     ['pen', 'brown'],
  11.     ['book', 'black'],
  12.     ['plate', 'white'],
  13.     ['chair', 'brown'],
  14.     ['coin', 'gold'],
  15.     ['bed', 'brown'],
  16.     ['notebook', 'white'],
  17. ]
  19. for r in data:
  20.     sheet.append(r)
  22. sheet.auto_filter.ref = 'A1:B8'
  23. sheet.auto_filter.add_filter_column(1, ['brown', 'white'])
  24. sheet.auto_filter.add_sort_condition('B2:B8')
  26. wb.save('filtered.xlsx')

在示例中，我们创建一个包含项目及其颜色的工作表。 我们设置一个过滤器和一个排序条件。

OpenPyXL 维度

为了获得那些实际包含数据的单元格，我们可以使用维度。

dimensions.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  5. book = Workbook()
  6. sheet = book.active
  8. sheet['A3'] = 39
  9. sheet['B3'] = 19
  11. rows = [
  12.     (88, 46),
  13.     (89, 38),
  14.     (23, 59),
  15.     (56, 21),
  16.     (24, 18),
  17.     (34, 15)
  18. ]
  20. for row in rows:
  21.     sheet.append(row)
  23. print(sheet.dimensions)
  24. print("Minimum row: {0}".format(sheet.min_row))
  25. print("Maximum row: {0}".format(sheet.max_row))
  26. print("Minimum column: {0}".format(sheet.min_column))
  27. print("Maximum column: {0}".format(sheet.max_column))
  29. for c1, c2 in sheet[sheet.dimensions]:
  30.     print(c1.value, c2.value)
  32. book.save('dimensions.xlsx')

该示例计算两列数据的维数。

  1. sheet['A3'] = 39
  2. sheet['B3'] = 19
  4. rows = [
  5.     (88, 46),
  6.     (89, 38),
  7.     (23, 59),
  8.     (56, 21),
  9.     (24, 18),
  10.     (34, 15)
  11. ]
  13. for row in rows:
  14.     sheet.append(row)

我们将数据添加到工作表。 请注意，我们从第三行开始添加。

  1. print(sheet.dimensions)

dimensions属性返回非空单元格区域的左上角和右下角单元格。

  1. print("Minimum row: {0}".format(sheet.min_row))
  2. print("Maximum row: {0}".format(sheet.max_row))

使用min_row和max_row属性，我们可以获得包含数据的最小和最大行。

  1. print("Minimum column: {0}".format(sheet.min_column))
  2. print("Maximum column: {0}".format(sheet.max_column))

通过min_column和max_column属性，我们获得了包含数据的最小和最大列。

  1. for c1, c2 in sheet[sheet.dimensions]:
  2.     print(c1.value, c2.value)

我们遍历数据并将其打印到控制台。

  1. $ ./dimensions.py 
  2. A3:B9
  3. Minimum row: 3
  4. Maximum row: 9
  5. Minimum column: 1
  6. Maximum column: 2
  7. 39 19
  8. 88 46
  9. 89 38
  10. 23 59
  11. 56 21
  12. 24 18
  13. 34 15

这是示例的输出。

工作表

每个工作簿可以有多个工作表。

OpenPyXL 教程 - 图3

图：床单

让我们有一张包含这三张纸的工作簿。

sheets.py

  1. #!/usr/bin/env python
  3. import openpyxl
  5. book = openpyxl.load_workbook('sheets.xlsx')
  7. print(book.get_sheet_names())
  9. active_sheet = book.active
  10. print(type(active_sheet))
  12. sheet = book.get_sheet_by_name("March")
  13. print(sheet.title)

该程序可用于 Excel 工作表。

  1. print(book.get_sheet_names())

get_sheet_names()方法返回工作簿中可用工作表的名称。

  1. active_sheet = book.active
  2. print(type(active_sheet))

我们获取活动表并将其类型打印到终端。

  1. sheet = book.get_sheet_by_name("March")

我们使用get_sheet_by_name()方法获得对工作表的引用。

  1. print(sheet.title)

检索到的工作表的标题将打印到终端。

  1. $ ./sheets.py 
  2. ['January', 'February', 'March']
  3. <class 'openpyxl.worksheet.worksheet.Worksheet'>
  4. March

这是程序的输出。

sheets2.py

  1. #!/usr/bin/env python
  3. import openpyxl
  5. book = openpyxl.load_workbook('sheets.xlsx')
  7. book.create_sheet("April")
  9. print(book.sheetnames)
  11. sheet1 = book.get_sheet_by_name("January")
  12. book.remove_sheet(sheet1)
  14. print(book.sheetnames)
  16. book.create_sheet("January", 0)
  17. print(book.sheetnames)
  19. book.save('sheets2.xlsx')

在此示例中，我们创建一个新工作表。

  1. book.create_sheet("April")

使用create_sheet()方法创建一个新图纸。

  1. print(book.sheetnames)

图纸名称也可以使用sheetnames属性显示。

  1. book.remove_sheet(sheet1)

可以使用remove_sheet()方法将纸张取出。

  1. book.create_sheet("January", 0)

可以在指定位置创建一个新图纸。 在我们的例子中，我们在索引为 0 的位置创建一个新工作表。

  1. $ ./sheets2.py 
  2. ['January', 'February', 'March', 'April']
  3. ['February', 'March', 'April']
  4. ['January', 'February', 'March', 'April']

这是程序的输出。

可以更改工作表的背景颜色。

sheets3.py

  1. #!/usr/bin/env python
  3. import openpyxl
  5. book = openpyxl.load_workbook('sheets.xlsx')
  7. sheet = book.get_sheet_by_name("March")
  8. sheet.sheet_properties.tabColor = "0072BA"
  10. book.save('sheets3.xlsx')

该示例修改了标题为"March"的工作表的背景颜色。

  1. sheet.sheet_properties.tabColor = "0072BA"

我们将tabColor属性更改为新颜色。

OpenPyXL 教程 - 图4

图：工作表的背景色

第三工作表的背景色已更改为某种蓝色。

合并单元格

单元格可以使用merge_cells()方法合并，而可以不使用unmerge_cells()方法合并。 当我们合并单元格时，除了左上角的所有单元格都将从工作表中删除。

merging_cells.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  4. from openpyxl.styles import Alignment
  6. book = Workbook()
  7. sheet = book.active
  9. sheet.merge_cells('A1:B2')
  11. cell = sheet.cell(row=1, column=1)
  12. cell.value = 'Sunny day'
  13. cell.alignment = Alignment(horizontal='center', vertical='center')
  15. book.save('merging.xlsx')

在该示例中，我们合并了四个单元格：A1，B1，A2和B2。 最后一个单元格中的文本居中。

  1. from openpyxl.styles import Alignment

为了使文本在最后一个单元格中居中，我们使用了openpyxl.styles模块中的Alignment类。

  1. sheet.merge_cells('A1:B2')

我们用merge_cells()方法合并四个单元格。

  1. cell = sheet.cell(row=1, column=1)

我们得到了最后一个单元格。

  1. cell.value = 'Sunny day'
  2. cell.alignment = Alignment(horizontal='center', vertical='center')

我们将文本设置为合并的单元格并更新其对齐方式。

OpenPyXL 教程 - 图5

图：合并的单元格

OpenPyXL 冻结窗格

冻结窗格时，在滚动到工作表的另一个区域时，我们会保持工作表的某个区域可见。

freezing.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  4. from openpyxl.styles import Alignment
  6. book = Workbook()
  7. sheet = book.active
  9. sheet.freeze_panes = 'B2'
  11. book.save('freezing.xlsx')

该示例通过单元格B2冻结窗格。

  1. sheet.freeze_panes = 'B2'

要冻结窗格，我们使用freeze_panes属性。

OpenPyXL 公式

下一个示例显示如何使用公式。 OpenPyXL 不进行计算； 它将公式写入单元格。

formulas.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  5. book = Workbook()
  6. sheet = book.active
  8. rows = (
  9.     (34, 26),
  10.     (88, 36),
  11.     (24, 29),
  12.     (15, 22),
  13.     (56, 13),
  14.     (76, 18)
  15. )
  17. for row in rows:
  18.     sheet.append(row)
  20. cell = sheet.cell(row=7, column=2)
  21. cell.value = "=SUM(A1:B6)"
  22. cell.font = cell.font.copy(bold=True)
  24. book.save('formulas.xlsx')

在示例中，我们使用SUM()函数计算所有值的总和，并以粗体显示输出样式。

  1. rows = (
  2.     (34, 26),
  3.     (88, 36),
  4.     (24, 29),
  5.     (15, 22),
  6.     (56, 13),
  7.     (76, 18)
  8. )
  10. for row in rows:
  11.     sheet.append(row)

我们创建两列数据。

  1. cell = sheet.cell(row=7, column=2)

我们得到显示计算结果的单元格。

  1. cell.value = "=SUM(A1:B6)"

我们将一个公式写入单元格。

  1. cell.font = cell.font.copy(bold=True)

我们更改字体样式。

OpenPyXL 教程 - 图6

图：计算值之和

OpenPyXL 图像

在下面的示例中，我们显示了如何将图像插入到工作表中。

write_image.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  4. from openpyxl.drawing.image import Image
  6. book = Workbook()
  7. sheet = book.active
  9. img = Image("icesid.png")
  10. sheet['A1'] = 'This is Sid'
  12. sheet.add_image(img, 'B2')
  14. book.save("sheet_image.xlsx")

在示例中，我们将图像写到一张纸上。

  1. from openpyxl.drawing.image import Image

我们使用openpyxl.drawing.image模块中的Image类。

  1. img = Image("icesid.png")

创建一个新的Image类。 icesid.png图像位于当前工作目录中。

  1. sheet.add_image(img, 'B2')

我们使用add_image()方法添加新图像。

OpenPyXL 图表

OpenPyXL 库支持创建各种图表，包括条形图，折线图，面积图，气泡图，散点图和饼图。

根据文档，OpenPyXL 仅支持在工作表中创建图表。 现有工作簿中的图表将丢失。

create_bar_chart.py

  1. #!/usr/bin/env python
  3. from openpyxl import Workbook
  4. from openpyxl.chart import (
  5.     Reference,
  6.     Series,
  7.     BarChart
  8. )
  10. book = Workbook()
  11. sheet = book.active
  13. rows = [
  14.     ("USA", 46),
  15.     ("China", 38),
  16.     ("UK", 29),
  17.     ("Russia", 22),
  18.     ("South Korea", 13),
  19.     ("Germany", 11)
  20. ]
  22. for row in rows:
  23.     sheet.append(row)
  25. data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)
  26. categs = Reference(sheet, min_col=1, min_row=1, max_row=6)
  28. chart = BarChart()
  29. chart.add_data(data=data)
  30. chart.set_categories(categs)
  32. chart.legend = None
  33. chart.y_axis.majorGridlines = None
  34. chart.varyColors = True
  35. chart.title = "Olympic Gold medals in London"
  37. sheet.add_chart(chart, "A8")    
  39. book.save("bar_chart.xlsx")

在此示例中，我们创建了一个条形图，以显示 2012 年伦敦每个国家/地区的奥运金牌数量。

  1. from openpyxl.chart import (
  2.     Reference,
  3.     Series,
  4.     BarChart
  5. )

openpyxl.chart模块具有使用图表的工具。

  1. book = Workbook()
  2. sheet = book.active

创建一个新的工作簿。

  1. rows = [
  2.     ("USA", 46),
  3.     ("China", 38),
  4.     ("UK", 29),
  5.     ("Russia", 22),
  6.     ("South Korea", 13),
  7.     ("Germany", 11)
  8. ]
  10. for row in rows:
  11.     sheet.append(row)

我们创建一些数据并将其添加到活动工作表的单元格中。

  1. data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)

对于Reference类，我们引用表中代表数据的行。 在我们的案例中，这些是奥运金牌的数量。

  1. categs = Reference(sheet, min_col=1, min_row=1, max_row=6)

我们创建一个类别轴。 类别轴是将数据视为一系列非数字文本标签的轴。 在我们的案例中，我们有代表国家名称的文本标签。

  1. chart = BarChart()
  2. chart.add_data(data=data)
  3. chart.set_categories(categs)

我们创建一个条形图并为其设置数据和类别。

  1. chart.legend = None
  2. chart.y_axis.majorGridlines = None

使用legend和majorGridlines属性，可以关闭图例和主要网格线。

  1. chart.varyColors = True

将varyColors设置为True，每个条形都有不同的颜色。

  1. chart.title = "Olympic Gold medals in London"

为图表设置标题。

  1. sheet.add_chart(chart, "A8")

使用add_chart()方法将创建的图表添加到工作表中。

OpenPyXL 教程 - 图7

图：条形图

在本教程中，我们使用了 OpenPyXL 库。 我们已经从 Excel 文件中读取数据，并将数据写入 Excel 文件中。

您可能也对以下相关教程感兴趣： Python 教程， Python CSV 教程， Python SimpleJson 教程和 Python 列表推导 。

列出所有 Python 教程。