一文拿下Python的Excel神器——“Openpyxl”

一文拿下Python的Excel神器——“Openpyxl”

无论是日常办公还是编程，总是离不开 Excel，用来导入导出数据，记录数据，统计分析，画原型。虽然 Excel 功能强大，操作便利，但是有些场景下还是需要人工比对数据，例如 将大量数据导入到 Excel，将 Excel 中的数据读取到系统中，或者按照某种结构格式化下原有数据，批量处理大量 Excel 文档等，幸运的是，有很多 Python 库可以帮助我们用程序来控制Excel，利用python的Excel库开发自动化操作Excel工具，解放双手！！！

01.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 的封装，除此之外想概念的技术文档不够完善，可参考的资料较少

02.Openpyxl使用实操

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

2.1 开源库安装

pip install openpyxl
1

在pycharm终端或者cmd的Python安装路径下均可通过上述命令进行openpyxl库的下载

2.2 openpyxl基础

• workbook 相当于一个 Excel 文件档，每个被创建和打开的 Excel 文件都是独立的 Workbook 对象（相当于工作簿，即多个表单的集合）
• sheet Excel 文档中的表单，每个 Excel 文档至少需要一个 sheet
• cell 单元格，是不可分割的基本数据存储单元

2.3 openpyxl示例

下面通过一段代码实现Excel表格的创建、表单获取、数据写入、循环追加数据以及文件保存等功能

import openpyxl

# 创建一个新的工作簿对象
wb = openpyxl.Workbook()

# 获取工作表对象(sheet)
ws = wb.active
print(ws)

# 设置Sheet名称
ws.title = '学生表'

# 创建一个新sheet，可以指定名称，index表示新创建的工作簿放在第几个位置, index从0开始计数
ws_1 = wb.create_sheet(index=1, title='成绩表')
ws_2 = wb.create_sheet(index=2, title='科目表')
ws_3 = wb.create_sheet(index=3, title='值日表')

# 获取所有工作表名称
print(wb.sheetnames)

# 方法一：写入单个单元格
ws['A1'] = '姓名'
ws['B1'] = '班级'
ws['c1'] = '年龄'

# 方法二：写入单个单元格(行，列，内容); 也可以直接ws.cell(1, 4, '学校')
ws.cell(row=1, column=4, value='学校')

# 写入多个单元格(追加模式，不会覆盖之前的，从有数据的下一行开始)
ws.append(['王明', '三年级一班', '9岁'])

# 第一列和第三列插入数据，第二列插入[None]值
ws.append(['王五', '六年一班', '10岁'])

# 追加第二行数据，过滤空值
# 获取工作表总行数
max_row = ws.max_row
col_values = []
# 遍历第二列所有行
for row in range(1, max_row+1):
    # 获取当前单元格的值
    cell_value = ws.cell(row=row, column=2).value
    # 如果当前单元格的值不为空
    if cell_value is not None:
        # 将当前单元格的值添加到列表中
        col_values.append(cell_value)
print("第二列有 %d 行数据" % len(col_values))
ws.cell(len(col_values)+1, 2, "三年级二班")

# 将新数据追加到最后一行，忽略第二列
data = [['张三','四年一班', '10岁'], ['李四','四年一班', '15岁']]
for row in data:
    # 在第二列插入 None 值，实现跳过该列
    #row.insert(1, None)
    ws.append(row)

# 保存
wb.save('学生表.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

运行结果如图：

注意：

• 新创建的 workbook 对象，会自带一个名为 Sheet 的表单，Office Excel 新建会创建 3 个
• 创建的 workbook 会将第一个 表单激活，通过 wb.active 获取引用
• 像 python-docx work 库一样，save 方法会立即保存，不会有任何提示，建议选择不同文件名来保存

2.4 常用功能

2.4.1 操作Excel：

from openpyxl import load_workbook

wb = load_workbook('demo.xlsx')

# 显示文档中包含的 表单 名称
print(wb.sheetnames)
1
2
3
4
5
6

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

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

2.4.2 操作单元格

单独操作：即通过 Excel 单元格名称或者行列坐标获取单元格，进行操作

ws1 = wb.create_sheet("Mysheet")  #创建一个sheet
# 通过单元格名称设置
ws1["A1"]=123.11
ws1["B2"]="你好"

# 通过行列坐标设置
d = ws1.cell(row=4, column=2, value=10)
1
2
3
4
5
6
7

• 可以通过单元格名称设置，类似于 sheet 的某种属性
• 也可以通过行列坐标类设置

批量操作：需要一下子操作多个单元格时，可以用批量操作来提高效率

import xlsxwriter


# 写excel
def write_excel(all):
    workbook = xlsxwriter.Workbook('test.xlsx')  # 表名
    worksheet = workbook.add_worksheet(u'sheet1')

    # 表头 也可使用worksheet.write('0', '0', 'A')  worksheet.write('0', '1', 'B')的方式
    worksheet.write('A1', 'A')
    worksheet.write('B1', 'B')
    worksheet.write('C1', 'C')
    worksheet.write('D1', 'D')

    # 二维数组多行写入
    for x, one in enumerate(all):
        for y, i in enumerate(one):
            # 第一行为表头，x需+1
            worksheet.write(x + 1, y, i)

    workbook.close()


if __name__ == '__main__':
    all = []
    for i in range(1, 5):
        one = []
        for j in range(6, 10):
            one.append(i * j)
        all.append(one)
    write_excel(all)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

运行结果如下：

2.4.3 合并单元格

# 合并
ws.merge_cells('A2:D2')
# 解除合并
ws.unmerge_cells('A2:D2')

ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
1
2
3
4
5
6
7

• sheet 对象的 merge_cells 方法是合并单元格，unmerge_cells 是解除合并
• 分别有两种参数形式，一种是用单元格名称方式指定，另一种是通过命名参数指定
• 注意：对于没有合并过单元格的位置调用 unmerge_cells 时会报错

2.5 单元格格式

OpenPyXl 用6种类来设置单元格的样式

• NumberFormat 数字
• Alignment 对齐
• Font 字体
• Border 边框
• PatternFill 填充
• Protection 保护

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protection
from openpyxl.styles import numbers

wb = Workbook()
ws = wb.active
ws.cell(row=1, column=1, value='宋体').font = Font(name=u'宋体', size=12, bold=True, color='FF0000')
ws.cell(row=2, column=2, value='右对齐').alignment = Alignment(horizontal='right')
ws.cell(row=3, column=3, value='填充渐变色').fill = PatternFill(fill_type='solid', start_color='FF0000')
ws.cell(row=4, column=4, value='设置边线').border = Border(left=Side(border_style='thin', color='FF0000'), right= Side(border_style='thin', color='FF0000'))
ws.cell(row=5, column=5, value='受保护的').protection = Protection(locked=True, hidden=True)
ws.cell(row=6, column=6, value=0.54).number_format =numbers.FORMAT_PERCENTAGE
1
2
3
4
5
6
7
8
9
10
11

• 引入字体类
• 用 cell 方法，为单元格设置值的同时，设置格式
• 每种格式都有特定的属性，为其设置特定的格式对象
• 数字格式有点区别，通过设置格式名称来完成，numbers.FORMAT_PERCENTAGE 是个字符串
• Border 类，需要配合 Side 类使用，它们都在 openpyxl.styles 中定义
  ell(row=6, column=6, value=0.54).number_format =numbers.FORMAT_PERCENTAGE

- 引入字体类
- 用 cell 方法，为单元格设置值的同时，设置格式
- 每种格式都有特定的属性，为其设置特定的格式对象
- 数字格式有点区别，通过设置格式名称来完成，numbers.FORMAT_PERCENTAGE 是个字符串
- Border 类，需要配合 Side 类使用，它们都在 openpyxl.styles 中定义
- 需要注意的是，单元格样式属性只能通过样式对象赋予，而无法通过样式属性来修改，例如 `ws.cell(1, 1).font.color = '00FF00'` 会报错，如果真要换，需要重新创建一个样式实体，重新赋值
1
2
3
4
5
6
7