使用Python提高工作效率之Excel

发表于 | 更新于 | 分类于 | 阅读次数：

Excel本身提供了非常强大的功能，在数据统计和分析时经常会导出到CSV或者Excel等文件，也常常会从Excel读取数据进行计算。日常的自动化脚本读写数据时我使用比较多的也是Excel。对Excel的操作有各种不同的第三方库，效率以及平台支持都各有千秋。根据综合情况，我选择了支持最多平台，并且语法最简单易用的openpyxl这个库，在此做个简单的使用示例记录，根据每次使用遇到的问题以及解决方法都会增量修改文章，积累并总结问题能够方便日后遇到相同的问题时能够快速查阅并解决。

基础部分

openpyxl支持的Python版本

python版本必须大于2.7，并且不能是3.0.x到3.3.x的版本。
openpyxl库不支持.xls后缀的文件只支持.xlsx后缀的Excel文件,不过只要是Office 2007 及后续的版本都支持.xlsx后缀的文件
安装openpyxl:

1
pip install openpyxl

openpyxl中API对应的Excel概念

  • Workbook 工作簿，对应一个.xls或.xlsx后缀的文件
  • Worksheet 工作表，一个Excel工作簿中，包含一个或多个工作表，通常在Excel的顶部或者底部标签栏中可以看到。

首先用Workbook构造或者打开一个Excel文件得到工作簿，然后通过工作簿对象获取Worksheet工作表。
通过得到的工作表对象，就可以对该工作表的行和列进行读写操作了。

快速使用

写入Excel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import datetime
from openpyxl import Workbook

# 创建工作簿
wb = Workbook()

# 获取激活的工作表（默认为第一个）
ws = wb.active

# 将当前工作表的A1单元格赋值为42
ws['A1'] = 42

# 从有数据单元格的下一行开始，从最左往右单元格分别赋值为 1 2 3
ws.append([1, 2, 3])

# 将当前工作表的A2单元格赋值为当前时间
ws['A2'] = datetime.datetime.now()

# 从有数据单元格的下一行开始，从最左往右单元格分别赋值为 1 2 3
ws.append([1, 2, 3, 4])

# 将文件内容保存到当前目录的 sample.xlsx 文件中
wb.save("sample.xlsx")

上述代码中ws['A2']将A2原本的值1修改成了当前时间。
保存后的Excel工作簿打开如下所示

读取Excel

1
2
3
4
5
6
7
8
9
10
from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook(filename='sample.xlsx')

# 获取激活的工作表
ws = wb.active

# 打印工作表A1单元格的值
print(ws['A1'].value)

复杂一点的用法

写入Excel文件

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
59
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import colors
from openpyxl.styles import Alignment

# 创建工作簿
wb = Workbook()

# 获取激活的工作表（默认名称为Sheet）
ws = wb.active

# 设置工作表名称
ws.title = "99乘法表"

for x in range(1, 10):
    for y in range(1, 10):
        if x >= y:
            ws.cell(x, y, str(y) + "*" + str(x) + "=" + str(x * y))
        else:
            continue

# 创建 "教师信息" 工作表，工作表位置为默认值
ws_teacher = wb.create_sheet("教师信息")

# 创建 "学生信息" 工作表,并设置工作表位置为第1个
ws_student = wb.create_sheet("学生信息", 1)

# 设置 "学生信息" 工作表的标签颜色，该值为RGB值
ws_student.sheet_properties.tabColor = "1072BA"

# 将"学生信息" 工作表的第1行第1列赋值为"姓名"
ws_student.cell(row=1, column=1, value="姓名")

# 将"学生信息" 工作表的第2行第1列赋值为"蓝士钦"
ws_student.cell(row=2, column=1, value="蓝士钦")

# 合并A1到F1单元格
cell = ws_teacher.merge_cells('A1:F1')

# 设置单元格字体为蓝色，15号大小，斜体
ws_teacher['A1'].font = Font(color=colors.BLUE, size=15, italic=True)

# 设置为垂直居中对齐,水平居中对齐
ws_teacher['A1'].alignment = Alignment(vertical='center', horizontal='center')

# 为单元格赋值
ws_teacher['A1'] = "教师信息标题"

# 将"教师信息" 工作表的第6行第6列赋值为"测试"
ws_teacher.cell(row=6, column=6, value="测试")

# 从 "学生信息" 工作表拷贝到新工作表
target = wb.copy_worksheet(ws_student)

# 设置工作表的名称
target.title = "新工作表"

# 将文件内容保存到当前目录的 sample2.xlsx 文件中
wb.save("sample2.xlsx")

保存后的Excel工作簿打开，对应有多个工作表，每个工作表的内容分别如下所示：


读取Excel文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook(filename='sample2.xlsx')

# 获取所有工作表
wss = wb.worksheets

# 遍历工作表
for ws in wss:
    # 打印指定工作表的所有单元格的值
    if ws.title == "99乘法表":
        for row in ws.rows:
            for index in row:
                if index.value is None:
                    continue
                else:
                    print(index.value + '\t', end='')
            print('')
            continue

有关openpyxl操作Excel单元格的样式和更详细的用法，参考其他作者的文章：
https://www.jianshu.com/p/7af9a7c5b27d

openpyxl官方文档地址：
https://openpyxl.readthedocs.io/en/stable/

0%