Python openpyxl
最后修改于 2024 年 1 月 29 日
本文介绍如何使用 openpyxl 库在 Python 中处理 Excel 文件。
Openpyxl
openpyxl 是一个用于读取和写入 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。
Excel xlsx
本文档我们处理 xlsx 文件。xlsx 是 Microsoft Excel 使用的开放 XML 电子表格文件格式的文件扩展名。xlsm 文件支持宏。xls 格式是专有的二进制格式，而 xlsx 基于 Office Open XML 格式。
$ pip install openpyxl
我们使用 pip 工具安装 openpyxl。
Openpyxl 创建新文件
在第一个示例中，我们使用 openpyxl 创建一个新的 xlsx 文件。
#!/usr/bin/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 文件。我们将数据写入三个单元格。
from openpyxl import Workbook
我们从 openpyxl 模块导入 Workbook 类。工作簿是文档所有其他部分的容器。
book = Workbook()
创建了一个新的工作簿。工作簿始终至少包含一个工作表。
sheet = book.active
我们使用 active 属性获取对活动工作表的引用。
sheet['A1'] = 56 sheet['A2'] = 43
我们将数值数据写入单元格 A1 和 A2。
now = time.strftime("%x")
sheet['A3'] = now
我们将当前日期写入单元格 A3。
book.save("sample.xlsx")
我们使用 save 方法将内容写入 sample.xlsx 文件。
Openpyxl 写入单元格
有两种基本方法可以写入单元格：使用工作表键，如 A1 或 D3，或使用 cell 方法的行和列表示法。
#!/usr/bin/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')
在此示例中，我们将两个值写入两个单元格。
sheet['A1'] = 1
在这里，我们将数值分配给 A1 单元格。
sheet.cell(row=2, column=2).value = 2
在此行中，我们使用行和列表示法写入单元格 B2。
Openpyxl 追加值
使用 append 方法，我们可以将一组值追加到当前工作表的底部。
#!/usr/bin/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')
在此示例中，我们将三列数据追加到当前工作表中。
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)
我们逐行遍历容器，并使用 append 方法插入数据行。
Openpyxl 读取单元格
在下面的示例中，我们从 sample.xlsx 文件读取之前写入的数据。
#!/usr/bin/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 文件并读取三个单元格。
book = openpyxl.load_workbook('sample.xlsx')
使用 load_workbook 方法打开文件。
a1 = sheet['A1'] a2 = sheet['A2'] a3 = sheet.cell(row=3, column=1)
我们读取 A1、A2 和 A3 单元格的内容。在第三行，我们使用 cell 方法获取 A3 单元格的值。
$ ./read_cells.py 56 43 10/26/16
Openpyxl 读取多个单元格
我们有以下数据表
我们使用范围运算符读取数据。
#!/usr/bin/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))
在此示例中，我们使用范围操作从两列读取数据。
cells = sheet['A1': 'B6']
在此行中，我们从单元格 A1 - B6 读取数据。
for c1, c2 in cells:
print("{0:8} {1:8}".format(c1.value, c2.value))
format 函数用于在控制台上整洁地输出数据。
$ ./read_cells2.py Items Quantity coins 23 chairs 3 pencils 5 bottles 8 books 30
Openpyxl 按行迭代
iter_rows 函数将工作表中的单元格作为行返回。
#!/usr/bin/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')
该示例逐行迭代数据。
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=3):
我们为迭代提供边界。
$ ./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 函数将工作表中的单元格作为列返回。
#!/usr/bin/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')
该示例逐列迭代数据。
$ ./iterating_by_columns.py 88 89 23 56 24 34 46 38 59 21 18 15 57 12 78 98 43 67
统计
对于下一个示例，我们需要创建一个包含数字的 xlsx 文件。例如，我们使用 RANDBETWEEN 函数创建了 25 行 10 列的数字。
#!/usr/bin/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)))
在此示例中，我们读取工作表中的所有值并计算一些基本统计数据。
import statistics as stats
导入了 statistics 模块以提供一些统计函数，如中位数和方差。
book = openpyxl.load_workbook('numbers.xlsx', data_only=True)
使用 data_only 选项，我们获取单元格中的值，而不是公式。
rows = sheet.rows
我们获取所有非空单元格的所有行。
for row in rows:
for cell in row:
values.append(cell.value)
在两个 for 循环中，我们从单元格中形成一个整数列表。
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 模块一起导入。
$ ./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 设置条件，但我们必须在电子表格应用程序中应用它们。
#!/usr/bin/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 尺寸
为了获取实际包含数据的单元格，我们可以使用尺寸。
#!/usr/bin/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')
该示例计算两列数据的尺寸。
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)
dimensions 属性返回非空单元格区域的左上角和右下角单元格。
print("Minimum row: {0}".format(sheet.min_row))
print("Maximum row: {0}".format(sheet.max_row))
使用 min_row 和 max_row 属性，我们获得包含数据的最小和最大行。
print("Minimum column: {0}".format(sheet.min_column))
print("Maximum column: {0}".format(sheet.max_column))
使用 min_column 和 max_column 属性，我们获得包含数据的最小和最大列。
for c1, c2 in sheet[sheet.dimensions]:
print(c1.value, c2.value)
我们遍历数据并将其打印到控制台。
$ ./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
工作表
每个工作簿可以有多个工作表。
让我们有一个包含这三个工作簿的工作簿。
#!/usr/bin/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 工作表。
print(book.get_sheet_names())
get_sheet_names 方法返回工作簿中可用工作表的名称。
active_sheet = book.active print(type(active_sheet))
我们获取活动工作表并将其类型打印到终端。
sheet = book.get_sheet_by_name("March")
我们使用 get_sheet_by_name 方法获取工作表的引用。
print(sheet.title)
检索到的工作表的标题将打印到终端。
$ ./sheets.py ['January', 'February', 'March'] <class 'openpyxl.worksheet.worksheet.Worksheet'> March
#!/usr/bin/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')
在此示例中，我们创建了一个新工作表。
book.create_sheet("April")
使用 create_sheet 方法创建了一个新工作表。
print(book.sheetnames)
也可以使用 sheetnames 属性显示工作表名称。
book.remove_sheet(sheet1)
可以使用 remove_sheet 方法删除工作表。
book.create_sheet("January", 0)
可以在指定位置创建新工作表；在我们的例子中，我们在位置索引 0 处创建了一个新工作表。
$ ./sheets2.py ['January', 'February', 'March', 'April'] ['February', 'March', 'April'] ['January', 'February', 'March', 'April']
可以更改工作表的背景颜色。
#!/usr/bin/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”的工作表的背景颜色。
sheet.sheet_properties.tabColor = "0072BA"
我们将 tabColor 属性更改为新颜色。
第三张工作表的背景颜色已更改为某种蓝色。
合并单元格
可以使用 merge_cells 方法合并单元格，并使用 unmerge_cells 方法取消合并单元格。当我们合并单元格时，除了左上角的单元格外，所有单元格都将从工作表中删除。
#!/usr/bin/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。最终单元格中的文本已居中。
from openpyxl.styles import Alignment
为了在最终单元格中居中文本，我们使用了 openpyxl.styles 模块中的 Alignment 类。
sheet.merge_cells('A1:B2')
我们使用 merge_cells 方法合并了四个单元格。
cell = sheet.cell(row=1, column=1)
我们获取最终单元格。
cell.value = 'Sunny day' cell.alignment = Alignment(horizontal='center', vertical='center')
我们向合并的单元格设置文本并更新其对齐方式。
Openpyxl 冻结窗格
当我们冻结窗格时，我们在滚动到工作表的另一区域时保持工作表的一个区域可见。
#!/usr/bin/python
from openpyxl import Workbook
from openpyxl.styles import Alignment
book = Workbook()
sheet = book.active
sheet.freeze_panes = 'B2'
book.save('freezing.xlsx')
该示例按单元格 B2 冻结窗格。
sheet.freeze_panes = 'B2'
要冻结窗格，我们使用 freeze_panes 属性。
Openpyxl 公式
下一个示例演示如何使用公式。openpyxl 不执行计算；它将公式写入单元格。
#!/usr/bin/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 函数计算所有值的总和，并将输出样式设置为粗体。
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)
我们更改字体样式。
Openpyxl 图像
在下面的示例中，我们展示了如何将图像插入到工作表中。
#!/usr/bin/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")
在此示例中，我们将图像写入工作表。
from openpyxl.drawing.image import Image
我们使用 openpyxl.drawing.image 模块中的 Image 类进行处理。
img = Image("icesid.png")
创建了一个新的 Image 类。icesid.png 图像位于当前工作目录中。
sheet.add_image(img, 'B2')
我们使用 add_image 方法添加新图像。
Openpyxl 图表
openpyxl 库支持创建各种图表，包括条形图、折线图、面积图、气泡图、散点图和饼图。
根据文档，openpyxl 仅支持在工作表内创建图表。现有工作簿中的图表将丢失。
#!/usr/bin/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 年伦敦奥运会各国的金牌数量。
from openpyxl.chart import (
Reference,
Series,
BarChart
)
openpyxl.chart 模块提供了处理图表的工具。
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)
使用 Reference 类，我们引用工作表中代表数据的行。在我们的例子中，这些是奥运金牌的数量。
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
使用 legend 和 majorGridlines 属性，我们关闭了图例和主要网格线。
chart.varyColors = True
将 varyColors 设置为 True，每根条形图都有不同的颜色。
chart.title = "Olympic Gold medals in London"
为图表设置了标题。
sheet.add_chart(chart, "A8")
使用 add_chart 方法将创建的图表添加到工作表中。
来源
本文档中我们使用了 openpyxl 库。我们从 Excel 文件读取了数据，并将数据写入了 Excel 文件。
作者
列出所有 Python 教程。