简介
Openpyxl是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库,其功能非常强大。Excel表格可以理解为一个二维矩阵,行用1,2,3,4,5.....等表示,在Openpyxl可以用来作为行索引。列用A,B,C,D,E,F.......表示,在Openpyxl中可以用来当做列索引,描述一个单元格的位置需要一个行标、一个列标。如A1表示第一行第一列对应的单元格,E4表示第四行第五列对应的单元格。
简单示例:
from openpyxl import Workbook
wb = Workbook() #实例化一个工作簿对象
#获取当前活跃的工作簿
ws = wb.active
#数据可以直接分配给具体单元格
ws['A1'] = 42
#行也可以被追加
ws.append([1, 2, 3])
#Python类型将自动转换
import datetime
ws['A2'] = datetime.datetime.now()
#保存成Excel文件
wb.save("sample.xlsx")
用法示例
在内存中操作工作簿
没有必要在文件系统上创建一个文件来使用openpyxl,直接导入Workbook类就可以使用它
>>> from openpyxl import Workbook
>>> wb = Workbook() #实例化一个工作簿对象
Workbook()类通常至少创建一个工作表,可以使用active()方法获得当前活跃的工作簿
>>> ws = wb.active
注意:这个函数使用_active_sheet_index属性,默认设置为0。除非您修改它的值,否则您将总是使用这个方法获得第一个工作表。
也可以用openpyxl.workbook.Workbook.create_sheet()方法创建新的工作簿
>>> ws1 = wb.create_sheet("Mysheet") #默认在末尾插入#or
>>> ws2 = wb.create_sheet("Mysheet", 0) #在第一个位置插入
表格在创建的时候就会自动的按照顺序以(Sheet, Sheet1, Sheet2, …)的方式命名,你随时都可以用title属性来改变表格的名称
ws.title = "New Title"
在默认情况下,持有这个标题的标签的背景颜色是白色的。你可以通过给sheet_properties.tabColor属性赋一个RRGGBB值来改变它。
ws.sheet_properties.tabColor = "1072BA"
一旦你给了一个工作表一个名字,你就可以把它作为一个键来获取这个工作簿
>>> ws3 = wb["New Title"]
您可以通过openpyxl.workbook.Workbook.sheetnames()方法查看工作簿的所有工作表的名称
>>> print(wb.sheetnames)
['Sheet2', 'New Title', 'Sheet1']
你也可以循工作表
>>> for sheet in wb:
... print(sheet.title)
你可以用openpyxl.workbook.Workbook.copy_worksheet()方法在一个工作簿中创建工作表的副本
>>> source = wb.active
>>> target = wb.copy_worksheet(source)
注意:只有单元格(包括值、样式、超链接和注释)和某些工作表属性(包括维度、格式和属性)被复制。所有其他工作簿/工作表属性都不会被复制——例如图像、图表。
你不能在工作簿之间复制工作表。如果工作簿是以只读或写方式打开的,那么您也不能复制工作表。
玩转数据
现在我们知道了如何访问工作表,我们可以开始修改单元内容
可以直接以工作表键的方式来访问单元格
>>> c = ws['A4']
上面可以返回一个A4单元格,如果A4单元格不存在,就会创建一个。可以直接分配值。
>>> ws['A4'] = 4
同时还有一个openpyxl.worksheet.Worksheet.cell()方法,这提供了使用行和列表示法访问单元格的方法。
>>> d = ws.cell(row=4, column=2, value=10)
注意:当一个工作表在内存中创建时,它不包含任何单元格。它们是在第一次访问时创建的。由于这个特性,滚动浏览单元而不是直接访问它们将在内存中创建它们,即使您没有给它们赋值。
例如:
>>> for i in range(1,101):
... for j in range(1,101):
... ws.cell(row=i, column=j)
将在内存中创建100x100个单元格,什么都不做。
访问多个单元格
可以使用切片来访问单元格范围
>>> cell_range = ws['A1':'C2'] #获取A1到C2之间的单元格
行或列的范围也可以得到类似的结果
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]
也可以使用openpyxl.worksheet.Worksheet.iter_rows()方法
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
... for cell in row:
... print(cell)
除此之外openpyxl.worksheet.Worksheet.iter_cols()方法可以返回列
>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
... for cell in col:
... print(cell)
如果您需要遍历文件的所有行或列,您可以使用openpyxl.worksheet.Worksheet.rows()方法
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
((, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ),
(, , ))
或者openpyxl.worksheet.Worksheet.columns()方法
>>> tuple(ws.columns)
((,
,
,
,
,
,
...
,
,
),
(,
,
,
,
,
,
,
,
))
数据存储
一旦我们有了openpyxl.cell.Cell类,我们可以给它赋值
>>> c.value = 'hello, world'
>>> print(c.value)
'hello, world'
>>> d.value = 3.14
>>> print(d.value)
3.14
你也可以启用类型和格式推断
>>> 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对象的openpyxl.workbook.Workbook.save()方法
>>> wb = Workbook()
>>> wb.save('balances.xlsx')
警告:这个操作将在没有警告的情况下覆盖现有的文件。
扩展并不一定是xlsx或xlsm,如果您不使用官方扩展,直接用另一个应用程序打开它,您可能会遇到一些麻烦。
由于OOXML文件基本上是ZIP文件,所以您也可以用.ZIP结束文件名,并使用您最喜欢的ZIP归档管理器打开它。
你可以指定属性template=True,以保存工作簿作为模板。
>>> wb = load_workbook('document.xlsx')
>>> wb.template = True
>>> wb.save('document_template.xltx')
或者将该属性设置为False(缺省值),以保存为文档
>>> wb = load_workbook('document_template.xltx')
>>> wb.template = False
>>> wb.save('document.xlsx', as_template=False)
注意:您应该监视数据属性和文档扩展,以便在文档模板中保存文档,反之亦然,否则结果表引擎不能打开文档
以下将会导致错误
>>> wb = load_workbook('document.xlsx')
>>> #需要用 *.xlsx扩展名保存
>>> wb.save('new_document.xlsm')
>>> #MS Excel不能打开文档
>>>
>>> #or
>>>
>>> #需要指定属性keep_vba = True
>>> wb = load_workbook('document.xlsm')
>>> wb.save('new_document.xlsm')
>>> #MS Excel不能打开文档
>>>
>>> #or
>>>
>>> wb = load_workbook('document.xltm', keep_vba=True)
>>> #如果我们需要一个模板文档,那么我们必须指定扩展为.xltm
>>> wb.save('new_document.xlsm')
>>> #MS Excel不能打开文档
从文件加载
和写一样,您可以导入openpyxl.load workbook()来打开现有的工作簿
>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print wb2.get_sheet_names()
['Sheet2', 'New Title', 'Sheet1']