openpyxl - 操作Excel文件

操作Excel文件

新建工作簿

新建工作簿不需要再系统中创建新文件，在内存中操作即可。

from openpyxl import Workbook

# 工作簿实例化
wb = Workbook()

工作簿创建后，工作簿至少有一个工作表，可以用openpyxl.workbook.Workbook.active()方法激活并获取。

# 激活获取工作表，此方法调用 _active_sheet_index方法，默认索引为0，即第一个工作表
ws = wb.active

也可以用openpyxl.workbook.Workbook.create_sheet()方法创建工作表。

# 默认在最后添加工作表
ws1 = wb.create_sheet()

# 在指定位置添加工作表，位置0，即第一个
ws2 = wb.create_sheet(0)

工作表会按照创建顺序自动命名：(Sheet, Sheet1, Sheet2, …)。也可手动命名：

ws.title = "New Title"

工作表标签颜色可以设置RGB颜色，默认为白色。效果如图所示。

ws.sheet_properties.tabColor = "1072BA"

可以通过工作表名称获取工作表，以下两种方式效果相同。

ws3 = wb["New Title"]
ws4 = wb.get_sheet_by_name("New Title")

可以通过openpyxl.workbook.Workbook.get_sheet_names()获取工作簿所有工作表的名称，返回值为list类型。

>>> print(wb.get_sheet_names())
['Sheet2', 'New Title', 'Sheet1']

处理数据

获取一个单元格

获取一个工作表后，可以直接利用坐标获取单元格，若单元格不存在，则被创建。

c = ws['A4']

可以对单元格的值直接赋值。

ws['A4'] = 4

也可以通过以下两种方式获取单元格，效果相同。

c = ws.cell('A4')

# 指定行和列获取，从1开始，不从0开始。
d = ws.cell(row = 4, column = 1)

注意：每次实例化工作表时，工作表为空，不包含单元格，只有获取单元格，即单元格被实例化之后，单元格才被创建。这样也减小了内存的占用。所以当以任何方式实例化单元格、获取单元格时，单元格都会被创建。例如下面的遍历会创建100*100的单元格。

>>> for i in range(1,101):
...     for j in range(1,101):
...         ws.cell(row = i, column = j)

获取多个单元格

利用切片可以获取指定区域的单元格。

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

也可以使用openpyxl.worksheet.Worksheet.iter_rows()实现：

>>> tuple(ws.iter_rows('A1:C2'))
((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>),
 (<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>))

>>> for row in ws.iter_rows('A1:C2'):
...     for cell in row:
...         print cell
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>

如果需要迭代工作表中所有行和列可以使用属性：openpyxl.worksheet.Worksheet.rows()和openpyxl.worksheet.Worksheet.columns()， 返回值类型为tuple。

>>> ws = wb.create_sheet() # 实例化工作表，此时工作表为空 
>>> ws.rows()
()
>>> ws.columns()
()
>>> ws['C9'] = 'hello world' # 实例化C9单元格，此时A1到C9单元格都会被创建
>>> 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>))

>>> 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>))

写数据

获取单元格后，可对其写数据：

>>> c.value = 'hello, world'
>>> print(c.value)
'hello, world'

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

Excel还会保留python的格式和类型。

>>> wb = Workbook(guess_types=True)
>>> c.value = '12%'
>>> print(c.value)
0.12

>>> import datetime
>>> d.value = datetime.datetime.now()
>>> print d.value
datetime.datetime(2010, 9, 10, 22, 25, 18)

>>> c.value = '31.50'
>>> print(c.value)
31.5

保存

直接对实例化的工作簿调用openpyxl.workbook.Workbook.save()方法即可：

wb.save('sample.xlsx')

注意： 这种用法会覆盖同名文件，并且没有提示。保存的文件后缀没有强制要求为xlsx或者xlsm等，但若后缀不正确可能会打不开。由于excel文件本身就是zip压缩文件，所以后缀可以直接设为zip。

可以指定参数 as_template=True将文件保存为模板文件：

wb.save('document_template.xltx', as_template=True)

也可指定参数as_template=False(默认参数)将模板文件或普通文件保存为普通文件：

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

>>> wb = load_workbook('document.xlsx')
>>> wb.save('new_document.xlsx', as_template=False)

当打开或保存excel文件时，必须指定正确的后缀和必要的参数，否则将打开或保存失败。如以下操作均会失败：

>>> wb = load_workbook('document.xlsx')
>>> # 如果直接保存xlsx文件时，需指定xlsx后缀，否则保存的文件打不开
>>> wb.save('new_document.xlsm')
>>>
>>> # 启用宏的xlsm格式文件需指定参数：keep_vba=True， 否则保存的文件打不开
>>> wb = load_workbook('document.xlsm')
>>> wb.save('new_document.xlsm')
>>>
>>> wb = load_workbook('document.xltm', keep_vba=True)
>>> # 若果要保存为模板文件需指定后缀为xltm，并且指定参数as_template=True，否则保存的文件打不开
>>> wb.save('new_document.xlsm', as_template=True)

编辑文件

可以利用openpyxl.load_workbook()方法编辑已有文件：

>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print wb2.get_sheet_names()
['Sheet2', 'New Title', 'Sheet1']