Python操作Excel文件(3):优雅干将openpyxl
本文共4000余字,预计阅读时间16分钟,本文同步发布于知乎专栏和微信公众号平台。
关注学习了解更多的Cygwin、Linux、Python技术。
openpyxl
诞生于Python生态中缺乏原生读写
Office Open XML
格式文件(也就是xlsx格式)的背景下,由一群志愿者在业余时间开发维护,项目地址
http://bitbucket.org/openpyxl/openpyxl
。相较
pyexcel
、
xlrd/xlwt/xlutils
,
openpyxl
对Excel的功能支持更加丰富,同时在实现上又十分优雅,操作逻辑与直接用Excel软件接近,运行效率也很高,堪称Excel文件操作的优雅干将。
本文所用
openpyxl
版本为3.0,旧版本可能部分API有所不同。
0x00 读文件
API
openpyxl
对外的API十分简练,加载已有表格文件就1个函数——
load_workbook
,返回
Workbook
对象,总共接收5个参数:
True
;
示例
假使当前路径下,样例文件名称是
data.xlsx
,有3个表单,仅Sheet1有数据,内容如下图,其中C列2行是公式,
sum(A2,B2)
,C列3行是日期,A列4行是
TRUE
。
可按下述示例代码加载文件。
1 |
import openpyxl # 导入包 |
0x01 数据访问
索引表单
读入Excel文件拿到Workbook后,下一步就是定位到Worksheet。
Workbook
类对象有几个重要的属性和方法,用于索引Sheet。
Worksheet
对象的序号,后者已废弃,
建议用index方法
。
Workbook
类对象还支持直接for循环迭代,遍历
Worksheet
对象。
比较诡异的是,
Workbook
类对象给表单分配了序号,却
不支持通过序号索引表单,也
没有
get_sheet_by_index 方法
,官方认为用名称作关键字、按字典的方式索引更自然。
1 |
In [8]: bk.active # 建议使用 |
索引单元格
Cell
对象;
Cell
对象生成器;
除了上述cell、iter_rows、iter_cols方法,
Worksheet
对象支持通过
切片方式索引
单元格,返回嵌套元组,既接受行(列)序号的形式,也接受Excel风格地址的形式,而且
不必区分大小写
(说的就是
xlrd
/
xlwt
,必须大写,小写错误)。无论切片方式索引或调用iter_rows、iter_cols方法,返回的是可迭代对象,可以直接用在for循环迭代中,可谓相当优雅。
1 |
In [23]: sh1.dimensions |
读取单元格的值
Cell
类对象有个value属性,指示单元格的值,除此外还有类型、显示、风格等属性值得关注。
openpyxl
中分类如下图;
openpyxl
内部表示;
1 |
In [44]: cC3 = sh1['C3'] # C3单元格 |
除了通过value属性获取单元格的值,还可以在迭代时明确仅返回值,也就是
Worksheet
对象的iter_rows、iter_cols方法第5个参数设为True。
1 |
# 按行遍历,生成器转换为元组 |
0x02 改写文件
流程概述
Workbook
对象;
Workbook
对象中增加、删除
Worksheet
对象,也可直接索引需要编辑的
Worksheet
;
Worksheet
对象,包括对单元格赋值,单元格合并、分拆,设置单元格风格,增加、删除行(列),插入图片、图表等;
Workbook
对象至xlsx格式文件。
创建工作簿
load_workbook函数从已有Excel文件基础上创建,前文已描述。
Workbook函数创建新的工作簿,有2个可选参数:
1 |
In [67]: bk2 = openpyxl.Workbook() # 创建新工作簿 |
创建、删除表单
Workbook
对象还有个
copy_worksheet
方法用于复制表单,但
仅限于复制同一个工作簿内的表单,不可跨Workbook对象,而且表单内图片、图表均忽略
,接受1个必选参数,即源
Worksheet
对象,该
Workbook
自动将复制的表单插在最后。
1 |
In [74]: bk2.create_sheet('表单A') |
Workbook
对象的remove、remove_sheet方法均可删除表单对象,后者已废弃,建议用前者,仅接受1个必选参数,即要删除的
Worksheet
对象。这是
xlwt
所不具备的。
1 |
In [92]: bk2.remove(bk2['Sheet']) |
编辑数据
1 |
In [97]: for cx in range(2,4): |
Worksheet
对象以下方法用于整行(列)增加和删除,但需要注意,
增删行列后,公式所引用的单元格地址并未自动更新,与Excel软件的逻辑
不一致
!
1 |
In [110]: sh1.insert_rows(1,2) |
其他高阶编辑
openpyxl.drawing
包可将图片文件转换为
Image
对象,但需要在Python中事先安装
Pillow
做后端支持,否则转换报错。
Worksheet
对象支持通过add_image方法插入图片,接受2个参数:
Image
对象;
1 |
In [126]: img = openpyxl.drawing.image.Image('../pic/python-logo.jpg') |
openpyxl.chart
包实现了各种图表类型及对象创建、绘制方法,
Worksheet
对象支持通过add_chart方法插入图表。图表的绘制过程无法交互,不能实时反映出效果,不如直接在Excel软件中绘制。
openpyxl.style
包实现了字体、颜色、边框、对齐、填充、风格等多种类型及对象创建、制作方法,
Cell
对象的font、fill、alignment、style等属性可以直接赋值,实现单元格风格自定义。
openpyxl.formatting
包实现了风格、规则等类型及对象创建、制作方法,
Worksheet
对象的conditional_formatting属性,提供了add方法用于添加规格和应用的风格,实现单元格的条件格式。
openpyxl.comments
包实现了注释类型及对象创建、制作方法,
Cell
对象的comment属性可以直接赋值,实现单元格添加注释。
openpyxl.worksheet
包实现了数据有效性等类型及对象创建、制作方法,
Worksheet
对象提供了add_data_validation方法进行添加,实现单元格数据有效性提示和检验。
上述高阶编辑属于数据处理之后“锦上添花”的功能,但同时编写代码的逻辑繁琐,不如在Excel软件中交互式操作更直观、方便,本文不推荐也不做描述。
保存
将
Workbook
对象保存到文件,就1个方法——save,接受参数就1个——文件名。以下示例将上述编辑过的
Workbook
对象保存至新的Excel文件。
1 |
In [136]: bk.save('data-openpyxl.xlsx') |
保存后
ata-openpyxl.xlsx
文件内容如下图。
对于保存,有几点需要提醒:
openpyxl
也不例外,“保存”实际上是“另存为”,只是指定保存到原文件的话,原文件被覆盖;
openpyxl
仅支持xlsx格式,保存的文件名必须使用.xlsx扩展名;
Workbook
对象时使用了write_only模式,则仅能调用save方法一次,不可多次保存。
0x03 总结
openpyxl
堪称优雅的得力干将,唯一的遗憾,大概就是缺少对xls格式的支持。