操作Excel文件
新建工作簿
新建工作簿不需要再系统中创建新文件,在内存中操作即可。
from openpyxlimport 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 iin range(1,101):...for jin 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 rowin ws.iter_rows('A1:C2'):...for cellin 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 openpyxlimport load_workbook>>>wb2 = load_workbook('test.xlsx')>>>print wb2.get_sheet_names()
['Sheet2','New Title','Sheet1']