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
2
>>> from openpyxl import Workbook
>>> wb = Workbook()

至此一个至少有1个工作表的工作本已经被创建了。你可以通过Workbook.active属性来获取到工作表。

1
>>> ws = wb.active

注意：
默认工作表序号是被设置成0的。除非你修改了这个值，否则用这个方法获取到的都是第一张表。

可以通过 Workbook.create_sheet() 方法来创建新的工作表

1
2
3
4
5
>>> ws1 = wb.create_sheet("Mysheet") # 默认在后面插入一张新表
# 或者
>>> ws2 = wb.create_sheet("Mysheet", 0) # 在第一位插入新表
# 或者
>>> ws3 = wb.create_sheet("Mysheet", -1) # 在倒数第二的位置插入表

工作表被创建的时候会自动命名，名字是以数字序列来排的（例如Sheet, Sheet1, Sheet2, …）。
可以使用 Worksheet.title 来修改工作表的名字。

1
ws.title = "New Title"

默认情况下，工作表名字的标题颜色是白色的。
赋予RGB值 Worksheet.sheet_properties.tabColor 来修改颜色。

1
ws.sheet_properties.tabColor = "1072BA"

当你命名了工作表后，也可以通过名字来获取到这张表。

1
>>> ws3 = wb["New Title"]

查看工作本中所有表格的名字，使用 Workbook.sheetname 属性。

1
2
>>> print(wb.sheetnames)
['Sheet2', 'New Title', 'Sheet1']

循环打印名字

1
2
>>> for sheet in wb:
...     print(sheet.title)

在同一个工作本（Excel文件）中，可以使用 Workbook.copy_worksheet() 复制表格。

1
2
>>> source = wb.active
>>> target = wb.copy_worksheet(source)

注意
只有单元格（包括数值，风格，超链接和注释）和确定的工作表属性（包括尺寸，格式和属性）可以被复制。
工作表其他的内容是不能复制的，比如图片，图表等。

也可以在不同的Excel文件中复制表格。如果Excel文件是只读或只写的，就不能复制表格。

处理数据

单元格

现在我们知道来如何获取到工作表。我们可以开始修改单元格数据了。
用单元格的坐标可以获取到单元格。

1
>>> c = ws['A4']

返回的是A4单元格。如果之前A4不存在，则会创建A4单元格。

给单元格传入数据，直接赋值

1
>>> ws['A4'] = 4

我们也可以用 Worksheet.cell() 方法，通过行列序号的方式来拿到单元格。

1
>>> d = ws.cell(row=4, column=2, value=10)

注意
如果工作表是在内存中创建的，它是不包含单元格的。第一次获取单元格时会先创建单元格。

警告：因为这个特性，就算是没给单元格赋值，遍历的时候会直接在内存中创建它们。

例如

1
2
3
>>> for x in range(1,101):
...        for y in range(1,101):
...            ws.cell(row=x, column=y)

会在内存中创建 100x100 个没有值的单元格。

处理多个单元格

获取一个范围内的多个单元格。

1
>>> cell_range = ws['A1':'C2']

获取行或列的单元格

1
2
3
4
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]

也可以用 Worksheet.iter_rows() 方法

1
2
3
4
5
6
7
8
9
>>> for 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
9
>>> for 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.rows

1
2
3
4
5
6
7
8
9
10
11
12
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> 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
20
>>> tuple(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
2
3
for row in ws.values:
   for value in row:
     print(value)

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
>>> c.value = 'hello, world'
>>> print(c.value)
'hello, world'

>>> d.value = 3.14
>>> print(d.value)
3.14

保存文件

最简单和最安全的保存文件的方法就是使用 Workbook.save()。

1
2
wb = Workbook()
wb.save('balances.xlsx')

注意，这个方法会在没有警告的情况下覆盖已有的文件。

文件扩展名并没有被限制为xlsx或xlsm。

存储成为流（stream）

如果要把文件存成流，例如web应用Pyramid, Flask 或者Django，提供一个NamedTemporaryFile()即可

1
2
3
4
5
6
7
>>> from tempfile import NamedTemporaryFile
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> with NamedTemporaryFile() as tmp:
        wb.save(tmp.name)
        tmp.seek(0)
        stream = tmp.read()

把工作本保存成为模板，需要指定属性 template=True

1
2
3
>>> wb = load_workbook('document.xlsx')
>>> wb.template = True
>>> wb.save('document_template.xltx')

设template为False，即保存为文档。

1
2
3
>>> wb = load_workbook('document_template.xltx')
>>> wb.template = False
>>> wb.save('document.xlsx', as_template=False)

注意：在保存文档的时候应该注意文件扩展名，比如.xltx和.xlsx。以防后面用其他软件打开的时候遇到麻烦。

接下来的这些是错误操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
>>> wb = load_workbook('document.xlsx')
>>> # 需要存储成*.xlsx文件
>>> wb.save('new_document.xlsm')
>>> # MS Excel 将无法打开这个文件
>>>
>>> # 或者
>>>
>>> # 需要指定 keep_vba=True
>>> wb = load_workbook('document.xlsm')
>>> wb.save('new_document.xlsm')
>>> # MS Excel 将无法打开这个文件
>>>
>>> # 或者
>>>
>>> wb = load_workbook('document.xltm', keep_vba=True)
>>> # 如果我们需要一个模板文件，我们必须指定扩展名为xltm
>>> wb.save('new_document.xlsm')
>>> # 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']