Python openpyxl 处理Excel使用指南
这是一份openpyxl的使用指南。
翻译自官网 https://openpyxl.readthedocs.io/en/stable/tutorial.html
本文链接 https://rustfisher.com/2019/11/03/Python/Python-openpyxl_tutorial/
创建工作本
workbook指的就是Excel文件。使用openpyxl并不需要你在电脑上自己创建文件。直接引入Workbook类就可以开始工作。
1 | from openpyxl import Workbook |
至此一个至少有1个工作表的工作本已经被创建了。你可以通过Workbook.active属性来获取到工作表。1
ws = wb.active
注意:
默认工作表序号是被设置成0的。除非你修改了这个值,否则用这个方法获取到的都是第一张表。
可以通过 Workbook.create_sheet() 方法来创建新的工作表
1 | "Mysheet") # 默认在后面插入一张新表 ws1 = wb.create_sheet( |
工作表被创建的时候会自动命名,名字是以数字序列来排的(例如Sheet, Sheet1, Sheet2, …)。
可以使用 Worksheet.title 来修改工作表的名字。
1 | ws.title = "New Title" |
默认情况下,工作表名字的标题颜色是白色的。
赋予RGB值 Worksheet.sheet_properties.tabColor 来修改颜色。
1 | ws.sheet_properties.tabColor = "1072BA" |
当你命名了工作表后,也可以通过名字来获取到这张表。
1 | "New Title"] ws3 = wb[ |
查看工作本中所有表格的名字,使用 Workbook.sheetname 属性。
1 | print(wb.sheetnames) |
循环打印名字1
2for sheet in wb:
print(sheet.title)
在同一个工作本(Excel文件)中,可以使用 Workbook.copy_worksheet() 复制表格。1
2 source = wb.active
target = wb.copy_worksheet(source)
注意
只有单元格(包括数值,风格,超链接和注释)和确定的工作表属性(包括尺寸,格式和属性)可以被复制。
工作表其他的内容是不能复制的,比如图片,图表等。
也可以在不同的Excel文件中复制表格。如果Excel文件是只读或只写的,就不能复制表格。
处理数据
单元格
现在我们知道来如何获取到工作表。我们可以开始修改单元格数据了。
用单元格的坐标可以获取到单元格。1
'A4'] c = ws[
返回的是A4单元格。如果之前A4不存在,则会创建A4单元格。
给单元格传入数据,直接赋值1
'A4'] = 4 ws[
我们也可以用 Worksheet.cell() 方法,通过行列序号的方式来拿到单元格。1
4, column=2, value=10) d = ws.cell(row=
注意
如果工作表是在内存中创建的,它是不包含单元格的。第一次获取单元格时会先创建单元格。
警告:因为这个特性,就算是没给单元格赋值,遍历的时候会直接在内存中创建它们。
例如1
2
3for x in range(1,101):
for y in range(1,101):
ws.cell(row=x, column=y)
会在内存中创建 100x100 个没有值的单元格。
处理多个单元格
获取一个范围内的多个单元格。1
'A1':'C2'] cell_range = ws[
获取行或列的单元格1
2
3
4'C'] colC = ws[
'C:D'] col_range = ws[
10] row10 = ws[
5:10] row_range = ws[
也可以用 Worksheet.iter_rows() 方法1
2
3
4
5
6
7
8
9for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
类似的 Worksheet.iter_cols() 也会返回列单元格1
2
3
4
5
6
7
8
9for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.A2>
<Cell Sheet1.B1>
<Cell Sheet1.B2>
<Cell Sheet1.C1>
<Cell Sheet1.C2>
注意:性能原因 Worksheet.iter_cols() 方法在只读模式下不能使用。
如果你需要遍历文件中的所有行列,可以使用Worksheet.rows1
2
3
4
5
6
7
8
9
10
11
12 ws = wb.active
'C9'] = 'hello world' ws[
tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
或者是Worksheet.columns属性1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))
注意:基于性能方面的考虑,Worksheet.columns在只读模式中不能使用。
读取数据
如果只是要读取表中的数据,可以使用 Worksheet.values。它会返回表中所有行的单元格的值。
1 | for row in ws.values: |
Worksheet.iter_rows() 和 Worksheet.iter_cols() 都可以获取到单元格的值。1
2
3
4
5>>> 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)
数据存储
当我们有了单元格后,可以赋值1
2
3
4
5
6
7'hello, world' c.value =
print(c.value)
'hello, world'
3.14 d.value =
print(d.value)
3.14
保存文件
最简单和最安全的保存文件的方法就是使用 Workbook.save()。
1 | wb = Workbook() |
注意,这个方法会在没有警告的情况下覆盖已有的文件。
文件扩展名并没有被限制为xlsx或xlsm。
存储成为流(stream)
如果要把文件存成流,例如web应用Pyramid, Flask 或者Django,提供一个NamedTemporaryFile()即可1
2
3
4
5
6
7from tempfile import NamedTemporaryFile
from openpyxl import Workbook
wb = Workbook()
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
stream = tmp.read()
把工作本保存成为模板,需要指定属性 template=True1
2
3'document.xlsx') wb = load_workbook(
True wb.template =
'document_template.xltx') wb.save(
设template为False,即保存为文档。1
2
3'document_template.xltx') wb = load_workbook(
False wb.template =
'document.xlsx', as_template=False) wb.save(
注意:在保存文档的时候应该注意文件扩展名,比如.xltx和.xlsx。以防后面用其他软件打开的时候遇到麻烦。
接下来的这些是错误操作1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18'document.xlsx') wb = load_workbook(
# 需要存储成*.xlsx文件
'new_document.xlsm') wb.save(
# MS Excel 将无法打开这个文件
>>>
# 或者
>>>
# 需要指定 keep_vba=True
'document.xlsm') wb = load_workbook(
'new_document.xlsm') wb.save(
# MS Excel 将无法打开这个文件
>>>
# 或者
>>>
'document.xltm', keep_vba=True) wb = load_workbook(
# 如果我们需要一个模板文件,我们必须指定扩展名为xltm
'new_document.xlsm') wb.save(
# MS Excel 将无法打开这个文件
读取文件
和写文件类似,使用openpyxl.load_workbook()方法去打开一个已有文件。1
2
3
4>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print wb2.sheetnames
['Sheet2', 'New Title', 'Sheet1']