excel python插件_Excel 神器——OpenPyXl

文 | 太阳雪

来源：Python 技术「ID: pythonall」

无论是日常办公还是编程，总是离不开 Excel，用来导入导出数据，记录数据，统计分析，画原型，甚至在日本有位老爷爷用 Excel 来创作绘画

虽然 Excel 功能强大，操作便利，但是有些场景下还是不太方便，例如 将大量数据导入到 Excel，将 Excel 中的数据读取到系统中，或者按照某种结构格式化下原有数据，批量处理大量 Excel 文档等，幸运的是，有很多 Python 库可以帮助我们用程序来控制 Excel，完成难以手工完成的任务，现在就来了解下吧

Python 下的 Excel 库

Python 中有大量的原生和第三方 Excel 操作包，各有所长，不过对于刚使用 Python 与 Excel 交互的同学来说，可能有点目不暇接，所以先简单梳理一下常见的一些 Excel 包

• OpenPyXL是个读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库，简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有，图表功能是其一大亮点
• xlwings是一个基于 BSD 授权协议的 Python 库，可以轻松的使用 Python 操作 Excel，也可以在 Excel 中调用 Python，以接近 VBA 语法的实现 Excel 编程，支持 Excel 宏，并且可以作为 Web 服务器，提供 REST API 接口
• pandas数据处理是 pandas 的立身之本，Excel 作为 pandas 输入/输出数据的容器
• win32com从命名上就可以看出，这是一个处理 windows 应用的扩展，Excel 只是该库能实现的一小部分功能。该库还支持 office 的众多操作。需要注意的是，该库不单独存在，可通过安装 pypiwin32 或者 pywin32 获取
• Xlsxwriter拥有丰富的特性，支持图片/表格/图表/筛选/格式/公式等，功能与 openpyxl 相似，优点是相比 openpyxl 还支持 VBA 文件导入，迷你图等功能，缺点是不能打开/修改已有文件，意味着使用 xlsxwriter 需要从零开始
• DataNitro一个 Excel 的付费插件，内嵌到 Excel 中，可完全替代 VBA，在 Excel 中使用 python 脚本。既然被称为 Excel 中的 python，同时可以与其他 python 库协同。
• xlutils基于 xlrd/xlwt，老牌 python 包，算是该领域的先驱，功能特点中规中矩，比较大的缺点是仅支持 xls 文件。

概括一下：

• 不想使用 GUI 而又希望赋予 Excel 更多的功能，openpyxl 与 xlsxwriter，二者可选其一；

• 需要进行科学计算，处理大量数据，建议 pandas+xlsxwriter 或者 pandas + openpyxl，是不错的选择；

• 想要写 Excel 脚本，会 Python 但不会 VBA，可考虑 xlwings 或 DataNitro；

• win32com 功能还是性能都很强大，不过需要一定的 windows 编程经验才能上手，它相当于是 windows COM 的封装，另外文档不够完善

OpenPyXL

OpenPyXl 几乎可以实现所有的 Excel 功能，而且接口清晰，文档丰富，学习成本相对较低，今天就以 OpenPyXL 为例，了解下如何操作 Excel

安装

用 pip 安装

pip install openpyxl

安装成功后，可以跑通下面测试：

python -c "import openpyxl"

基本概念

• workbook 相当于一个 Excel 文件档，每个被创建和打开的 Excel 文件都是独立的 Workbook 对象

• sheet Excel 文档中的表单，每个 Excel 文档至少需要一个 sheet

• cell 单元格，是不可分割的基本数据存储单元

小试牛刀

先来看跑个测试

from openpyxl import Workbook# 创建一个 workbookwb = Workbook# 获取被激活的 worksheetws = wb.active# 设置单元格内容ws['A1'] = 42# 设置一行内容ws.append([1, 2, 3])# python 数据类型可以被自动转换import datetimews['A2'] = datetime.datetime.now# 保存 Excel 文件wb.save("sample.xlsx")

需要注意的是：

• 新创建的 workbook 对象，会自带一个名为 Sheet 的表单，Office Excel 新建会创建 3 个

• 创建的 workbook 会将第一个 表单激活，通过 wb.active 获取引用
• 像 python-docxwork 库一样，save 方法会立即保存，不会有任何提示，建议选择不同文件名来保存

常用功能

OpenPyXl 功能很多，从单元格处理到图表展示，涵盖了几乎全部的 Excel 功能，这里就一些常用的功能做展示，更多的用法可以参考 OpenPyXl 文档(文末参考里有链接)

创建和打开 Excel

小试牛刀部分看到了如何创建一个 Excel

如果要加载一个已存在的 Excel 文件，需要用 load_workbook方法，给定文件路径，返回 workbook 对象：

from openpyxl import load_workbookwb = load_workbook('test.xlsx')# 显示文档中包含的 表单 名称print(wb.sheetnames)

load_workbook除了参数filename外为还有一些有用的参数：

• read_only：是否为只读模式，对于超大型文件，要提升效率有帮助
• keep_vba：是否保留 vba 代码，即打开 Excel 文件时，开启并保留宏
• guess_types：是否做在读取单元格数据类型时，做类型判断
• data_only：是否将公式转换为结果，即包含公式的单元格，是否显示最近的计算结果
• keep_links：是否保留外部链接

操作 sheet

from openpyxl import Workbookwb = Workbookws = wb.activews1 = wb.create_sheet("sheet") #创建一个 sheet 名为 sheetws1.title = "新表单" # 设置 sheet 标题ws2 = wb.create_sheet("mysheet