openpyxl 操作 Excel#
openpyxl 用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。
安装：
pip install openpyxl
新建工作簿#
无须在文件系统中创建文件即可开始使用 openpyxl。只要导入 Workbook 类就可以开始工作了：
from openpyxl import Workbook
wb = Workbook()
一个工作簿（workbook）至少有一个工作表（worksheet）. 你可以通过 active 来获取这个属性：
ws = wb.active
你可以使用 create_sheet() 方法来创建新的工作表：
在结尾插入(默认)
ws1 = wb.create_sheet("Mysheet")
在开始位置插入：
ws2 = wb.create_sheet("Mysheet", 0)
插入倒数第二个位置
ws3 = wb.create_sheet("Mysheet", -1)
工作表在创建时会自动生成名字，以 (Sheet, Sheet1, Sheet2, …) 来进行命名。你也可以通过 Worksheet.title 属性来修改命名：
ws.title = "New Title"
默认情况下，包含该标题的选项卡的背景颜色为白色。你也可以使用 RRGGBB 颜色来改变 Worksheet.sheet_properties.tabColor 属性：
ws.sheet_properties.tabColor = "1072BA"
给工作表命名后，就可以将其作为工作簿的键：
ws3 = wb["New Title"]
使用 Workbook.sheetname 属性查看工作簿中所有工作表的名称：
print(wb.sheetnames)
['Mysheet1', 'New Title', 'Mysheet2', 'Mysheet']
遍历工作表：
for sheet in wb:
print(sheet.title)
Mysheet1
New Title
Mysheet2
Mysheet
你可以在工作簿中创建工作表的副本：
source = wb.active
target = wb.copy_worksheet(source)
备注
只有单元格（包含值、样式、超链接和注释）以及确定的工作表属性（包含尺寸、格式和属性）会被复制。 其余的工作表/工作簿属性都不会被复制，例如：文件、图表。
你也 不能 跨工作簿复制工作表。工作簿以 read-only 或 write_only 模式打开时也无法复制。
操作数据#
访问单元格#
接下来可以开始修改单元格内容了。可以直接通过工作表的键来访问单元格：
c = ws['A4']
这将返回 A4 位置的单元格，如果它还不存在，则创建。可以直接赋值：
ws['A4'] = 4
还有 Worksheet.cell() 方法。
这提供了使用行和列表示法访问单元格的方法：
d = ws.cell(row=4, column=2, value=10)
备注
当工作薄在内存中被创建之后并没有此单元格，单元格只有在被第一次访问(access)的时候才会创建。
警告
由于这个特性，滚动单元格而不是直接访问它们将在内存中创建它们，即使您没有为它们分配值。
比如
for x in range(1,101):
for y in range(1,101):
ws.cell(row=x, column=y)
将在内存中创建 100x100 单元格。
访问大量单元格#
可以使用切片来访问一系列单元格：
cell_range = ws['A1':'C2']
一系列的行和列也可以通过类似的方法获取:
colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]
也可以使用 Worksheet.iter_rows 方法：
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
<Cell 'New Title'.A1>
<Cell 'New Title'.B1>
<Cell 'New Title'.C1>
<Cell 'New Title'.A2>
<Cell 'New Title'.B2>
<Cell 'New Title'.C2>
同样 Worksheet.iter_cols 方法会返回列：
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell)
<Cell 'New Title'.A1>
<Cell 'New Title'.A2>
<Cell 'New Title'.B1>
<Cell 'New Title'.B2>
<Cell 'New Title'.C1>
<Cell 'New Title'.C2>
如果需要遍历文件中的所有行和列，可以使用 Worksheet.rows 属性：
ws = wb.active
ws['C9'] = 'hello world'
tuple(ws.rows)
((<Cell 'Mysheet1'.A1>, <Cell 'Mysheet1'.B1>, <Cell 'Mysheet1'.C1>),
(<Cell 'Mysheet1'.A2>, <Cell 'Mysheet1'.B2>, <Cell 'Mysheet1'.C2>),
(<Cell 'Mysheet1'.A3>, <Cell 'Mysheet1'.B3>, <Cell 'Mysheet1'.C3>),
(<Cell 'Mysheet1'.A4>, <Cell 'Mysheet1'.B4>, <Cell 'Mysheet1'.C4>),
(<Cell 'Mysheet1'.A5>, <Cell 'Mysheet1'.B5>, <Cell 'Mysheet1'.C5>),
(<Cell 'Mysheet1'.A6>, <Cell 'Mysheet1'.B6>, <Cell 'Mysheet1'.C6>),
(<Cell 'Mysheet1'.A7>, <Cell 'Mysheet1'.B7>, <Cell 'Mysheet1'.C7>),
(<Cell 'Mysheet1'.A8>, <Cell 'Mysheet1'.B8>, <Cell 'Mysheet1'.C8>),
(<Cell 'Mysheet1'.A9>, <Cell 'Mysheet1'.B9>, <Cell 'Mysheet1'.C9>))
或者 Worksheet.columns 属性：
tuple(ws.columns)
((<Cell 'Mysheet1'.A1>,
<Cell 'Mysheet1'.A2>,
<Cell 'Mysheet1'.A3>,
<Cell 'Mysheet1'.A4>,
<Cell 'Mysheet1'.A5>,
<Cell 'Mysheet1'.A6>,
<Cell 'Mysheet1'.A7>,
<Cell 'Mysheet1'.A8>,
<Cell 'Mysheet1'.A9>),
(<Cell 'Mysheet1'.B1>,
<Cell 'Mysheet1'.B2>,
<Cell 'Mysheet1'.B3>,
<Cell 'Mysheet1'.B4>,
<Cell 'Mysheet1'.B5>,
<Cell 'Mysheet1'.B6>,
<Cell 'Mysheet1'.B7>,
<Cell 'Mysheet1'.B8>,
<Cell 'Mysheet1'.B9>),
(<Cell 'Mysheet1'.C1>,
<Cell 'Mysheet1'.C2>,
<Cell 'Mysheet1'.C3>,
<Cell 'Mysheet1'.C4>,
<Cell 'Mysheet1'.C5>,
<Cell 'Mysheet1'.C6>,
<Cell 'Mysheet1'.C7>,
<Cell 'Mysheet1'.C8>,
<Cell 'Mysheet1'.C9>))
访问值#
如果你只想要工作薄的值，你可以使用 Worksheet.values 属性。这会遍历工作簿中所有的行但只返回单元格值：
for row in ws.values:
for value in row:
print(value, end=" ")
None None None None None None None None None None None None None None None None None None None None None None None None None None hello world
Worksheet.iter_rows 和 Worksheet.iter_cols 可以用 values_only 参数来返回单元格值：
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
print(row)
(None, None, None)
(None, None, None)
数据存储#
一旦有了 Cell, 可以为其分配值：
c.value = 'hello, world'
print(c.value)
hello, world
d.value = 3.14
print(d.value)
3.14
保存至文件#
保存工作簿最简单和安全的方法就是使用 Workbook.save 方法：
wb = Workbook()
wb.save('../build/balances.xlsx')
---------------------------------------------------------------------------
FileNotFoundError Traceback (most recent call last)
Cell In[25], line 2
1 wb = Workbook()
----> 2 wb.save('../build/balances.xlsx')
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/workbook/workbook.py:386, in Workbook.save(self, filename)
384 if self.write_only and not self.worksheets:
385 self.create_sheet()
--> 386 save_workbook(self, filename)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/writer/excel.py:291, in save_workbook(workbook, filename)
279 def save_workbook(workbook, filename):
280 """Save the given workbook on the filesystem under the name filename.
281
282 :param workbook: the workbook to save
(...)
289
290 """
--> 291 archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
292 workbook.properties.modified = datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)
293 writer = ExcelWriter(workbook, archive)
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/zipfile/__init__.py:1331, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)
1329 while True:
1330 try:
-> 1331 self.fp = io.open(file, filemode)
1332 except OSError:
1333 if filemode in modeDict:
FileNotFoundError: [Errno 2] No such file or directory: '../build/balances.xlsx'
警告
该操作将覆盖现有文件而不发出警告。
可以指定属性 template=True 将工作簿保存为模板：
from openpyxl import load_workbook
wb = load_workbook('../build/balances.xlsx')
wb.template = True
wb.save('../build/document_template.xltx')
---------------------------------------------------------------------------
FileNotFoundError Traceback (most recent call last)
Cell In[26], line 3
1 from openpyxl import load_workbook
----> 3 wb = load_workbook('../build/balances.xlsx')
4 wb.template = True
5 wb.save('../build/document_template.xltx')
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/reader/excel.py:346, in load_workbook(filename, read_only, keep_vba, data_only, keep_links, rich_text)
316 def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA,
317 data_only=False, keep_links=True, rich_text=False):
318 """Open the given filename and return the workbook
319
320 :param filename: the path to open or a file-like object
(...)
344
345 """
--> 346 reader = ExcelReader(filename, read_only, keep_vba,
347 data_only, keep_links, rich_text)
348 reader.read()
349 return reader.wb
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/reader/excel.py:123, in ExcelReader.__init__(self, fn, read_only, keep_vba, data_only, keep_links, rich_text)
121 def __init__(self, fn, read_only=False, keep_vba=KEEP_VBA,
122 data_only=False, keep_links=True, rich_text=False):
--> 123 self.archive = _validate_archive(fn)
124 self.valid_files = self.archive.namelist()
125 self.read_only = read_only
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/reader/excel.py:95, in _validate_archive(filename)
88 msg = ('openpyxl does not support %s file format, '
89 'please check you can open '
90 'it with Excel first. '
91 'Supported formats are: %s') % (file_format,
92 ','.join(SUPPORTED_FORMATS))
93 raise InvalidFileException(msg)
---> 95 archive = ZipFile(filename, 'r')
96 return archive
File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/zipfile/__init__.py:1331, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)
1329 while True:
1330 try:
-> 1331 self.fp = io.open(file, filemode)
1332 except OSError:
1333 if filemode in modeDict:
FileNotFoundError: [Errno 2] No such file or directory: '../build/balances.xlsx'
备注
可以使用 openpyxl.load_workbook() 打开已存在的工作簿。
保存成流#
如果想把文件保存成流。例如当使用 Pyramid, Flask 或 Django 等 web 应用程序时，可以提供 NamedTemporaryFile：
from tempfile import NamedTemporaryFile
from openpyxl import Workbook
wb = Workbook()
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
stream = tmp.read()