openpyxl 操作 Excel#
openpyxl 用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。
安装：
pip install openpyxl
新建工作簿#
无须在文件系统中创建文件即可开始使用 openpyxl。只要导入 Workbook 类就可以开始工作了：
from openpyxl import Workbook
wb = Workbook()
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[1], line 1
----> 1 from openpyxl import Workbook
3 wb = Workbook()
ModuleNotFoundError: No module named 'openpyxl'
一个工作簿（workbook）至少有一个工作表（worksheet）. 你可以通过 active 来获取这个属性：
ws = wb.active
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[2], line 1
----> 1 ws = wb.active
NameError: name 'wb' is not defined
你可以使用 create_sheet() 方法来创建新的工作表：
在结尾插入(默认)
ws1 = wb.create_sheet("Mysheet")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[3], line 1
----> 1 ws1 = wb.create_sheet("Mysheet")
NameError: name 'wb' is not defined
在开始位置插入：
ws2 = wb.create_sheet("Mysheet", 0)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[4], line 1
----> 1 ws2 = wb.create_sheet("Mysheet", 0)
NameError: name 'wb' is not defined
插入倒数第二个位置
ws3 = wb.create_sheet("Mysheet", -1)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[5], line 1
----> 1 ws3 = wb.create_sheet("Mysheet", -1)
NameError: name 'wb' is not defined
工作表在创建时会自动生成名字，以 (Sheet, Sheet1, Sheet2, …) 来进行命名。你也可以通过 Worksheet.title 属性来修改命名：
ws.title = "New Title"
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[6], line 1
----> 1 ws.title = "New Title"
NameError: name 'ws' is not defined
默认情况下，包含该标题的选项卡的背景颜色为白色。你也可以使用 RRGGBB 颜色来改变 Worksheet.sheet_properties.tabColor 属性：
ws.sheet_properties.tabColor = "1072BA"
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[7], line 1
----> 1 ws.sheet_properties.tabColor = "1072BA"
NameError: name 'ws' is not defined
给工作表命名后，就可以将其作为工作簿的键：
ws3 = wb["New Title"]
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[8], line 1
----> 1 ws3 = wb["New Title"]
NameError: name 'wb' is not defined
使用 Workbook.sheetname 属性查看工作簿中所有工作表的名称：
print(wb.sheetnames)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[9], line 1
----> 1 print(wb.sheetnames)
NameError: name 'wb' is not defined
遍历工作表：
for sheet in wb:
print(sheet.title)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[10], line 1
----> 1 for sheet in wb:
2 print(sheet.title)
NameError: name 'wb' is not defined
你可以在工作簿中创建工作表的副本：
source = wb.active
target = wb.copy_worksheet(source)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[11], line 1
----> 1 source = wb.active
2 target = wb.copy_worksheet(source)
NameError: name 'wb' is not defined
备注
只有单元格（包含值、样式、超链接和注释）以及确定的工作表属性（包含尺寸、格式和属性）会被复制。 其余的工作表/工作簿属性都不会被复制，例如：文件、图表。
你也 不能 跨工作簿复制工作表。工作簿以 read-only 或 write_only 模式打开时也无法复制。
操作数据#
访问单元格#
接下来可以开始修改单元格内容了。可以直接通过工作表的键来访问单元格：
c = ws['A4']
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[12], line 1
----> 1 c = ws['A4']
NameError: name 'ws' is not defined
这将返回 A4 位置的单元格，如果它还不存在，则创建。可以直接赋值：
ws['A4'] = 4
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[13], line 1
----> 1 ws['A4'] = 4
NameError: name 'ws' is not defined
还有 Worksheet.cell() 方法。
这提供了使用行和列表示法访问单元格的方法：
d = ws.cell(row=4, column=2, value=10)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[14], line 1
----> 1 d = ws.cell(row=4, column=2, value=10)
NameError: name 'ws' is not defined
备注
当工作薄在内存中被创建之后并没有此单元格，单元格只有在被第一次访问(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']
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[15], line 1
----> 1 cell_range = ws['A1':'C2']
NameError: name 'ws' is not defined
一系列的行和列也可以通过类似的方法获取:
colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[16], line 1
----> 1 colC = ws['C']
2 col_range = ws['C:D']
3 row10 = ws[10]
NameError: name 'ws' is not defined
也可以使用 Worksheet.iter_rows 方法：
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[17], line 1
----> 1 for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
2 for cell in row:
3 print(cell)
NameError: name 'ws' is not defined
同样 Worksheet.iter_cols 方法会返回列：
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[18], line 1
----> 1 for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
2 for cell in col:
3 print(cell)
NameError: name 'ws' is not defined
如果需要遍历文件中的所有行和列，可以使用 Worksheet.rows 属性：
ws = wb.active
ws['C9'] = 'hello world'
tuple(ws.rows)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[19], line 1
----> 1 ws = wb.active
2 ws['C9'] = 'hello world'
3 tuple(ws.rows)
NameError: name 'wb' is not defined
或者 Worksheet.columns 属性：
tuple(ws.columns)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[20], line 1
----> 1 tuple(ws.columns)
NameError: name 'ws' is not defined
访问值#
如果你只想要工作薄的值，你可以使用 Worksheet.values 属性。这会遍历工作簿中所有的行但只返回单元格值：
for row in ws.values:
for value in row:
print(value, end=" ")
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[21], line 1
----> 1 for row in ws.values:
2 for value in row:
3 print(value, end=" ")
NameError: name 'ws' is not defined
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)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[22], line 1
----> 1 for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
2 print(row)
NameError: name 'ws' is not defined
数据存储#
一旦有了 Cell, 可以为其分配值：
c.value = 'hello, world'
print(c.value)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[23], line 1
----> 1 c.value = 'hello, world'
2 print(c.value)
NameError: name 'c' is not defined
d.value = 3.14
print(d.value)
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[24], line 1
----> 1 d.value = 3.14
2 print(d.value)
NameError: name 'd' is not defined
保存至文件#
保存工作簿最简单和安全的方法就是使用 Workbook.save 方法：
wb = Workbook()
wb.save('../build/balances.xlsx')
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
Cell In[25], line 1
----> 1 wb = Workbook()
2 wb.save('../build/balances.xlsx')
NameError: name 'Workbook' is not defined
警告
该操作将覆盖现有文件而不发出警告。
可以指定属性 template=True 将工作簿保存为模板：
from openpyxl import load_workbook
wb = load_workbook('../build/balances.xlsx')
wb.template = True
wb.save('../build/document_template.xltx')
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[26], line 1
----> 1 from openpyxl import load_workbook
3 wb = load_workbook('../build/balances.xlsx')
4 wb.template = True
ModuleNotFoundError: No module named 'openpyxl'
备注
可以使用 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()
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
Cell In[27], line 2
1 from tempfile import NamedTemporaryFile
----> 2 from openpyxl import Workbook
3 wb = Workbook()
4 with NamedTemporaryFile() as tmp:
ModuleNotFoundError: No module named 'openpyxl'