Python 使用openpyxl处理Excel文件

Python 使用openpyxl处理Excel文件

• 安装openpyxl模块

pip install openpyxl
1

• 导入模块

import openpyxl
1

• 官方文档：https://openpyxl.readthedocs.io/en/stable/index.html

1. Excel窗口

• 工作簿（workbook）：Excel的文件
• 工作表（worksheet）：一个工作簿由多个工作表组成
• 列（column）：工作表的列名为A、B、C等的大写字母
• 行（row）：工作表的行名称为1、2、3等的数字
• 单元格（cell）：工作表中的每个格子称为单元格，用(列名,行名)表示

2. 读取Excel文件

# author:mlnt
# createdate:2022/8/16

import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string

# 1.打开文件
# 使用openpyxl.load_workbook()方法打开Excel文件
filename = 'data.xlsx'
work_book = openpyxl.load_workbook(filename=filename)  # 加载Excel文件

# 2.获取工作表名称
"""
- Excel文件对象.sheetnames：获取工作簿文件的所有工作表，以列表数据类型返回
- Excel文件对象.active：获取当前工作表的名称
"""
# 获取所有工作表的名称
work_sheets = work_book.sheetnames
print(f'工作表列表：{work_sheets}')
# 工作表列表：['Sheet1', 'Sheet2', 'Sheet3']

# 获取当前工作表的名称
current_sheet = work_book.active
print(f'当前工作表：{current_sheet}')
# 当前工作表：

# 获取当前工作表的内容
title = current_sheet.title
print(f'当前工作表标题：{title}')
# 当前工作表标题：Sheet1

# 3.切换工作表
work_sheet = work_book['Sheet2']  # 返回名称相应的工作表
print(f'当前工作表：{work_sheet.title}')
# 当前工作表：Sheet2
work_sheet = work_book['Sheet1']  # 返回名称相应的工作表
print(f'当前工作表：{work_sheet.title}')
# 当前工作表：Sheet1

# 4.获取工作表的内容
print(f'单元格A1: {work_sheet["A1"].value}')
print(f'单元格B1: {work_sheet["B1"].value}')
print(f'单元格C1: {work_sheet["C1"].value}')
print(f'单元格D1: {work_sheet["D1"].value}')
print(f'单元格E1: {work_sheet["E1"].value}')
print(f'单元格F1: {work_sheet["F1"].value}')

# 单元格A1: 姓名
# 单元格B1: 字
# 单元格C1: 号
# 单元格D1: 所处时代
# 单元格E1: 别称
# 单元格F1: 代表作

# 获取单元格相对位置信息
# column：列，row：行，coordinate：坐标
print(f'单元格A1: {work_sheet["A1"].column}, {work_sheet["A1"].row}, {work_sheet["A1"].coordinate}')
print(f'单元格B1: {work_sheet["B1"].column}, {work_sheet["B1"].row}, {work_sheet["B1"].coordinate}')
print(f'单元格C1: {work_sheet["C1"].column}, {work_sheet["C1"].row}, {work_sheet["C1"].coordinate}')
print(f'单元格D1: {work_sheet["D1"].column}, {work_sheet["D1"].row}, {work_sheet["D1"].coordinate}')
print(f'单元格E1: {work_sheet["E1"].column}, {work_sheet["E1"].row}, {work_sheet["E1"].coordinate}')
print(f'单元格F1: {work_sheet["F1"].column}, {work_sheet["F1"].row}, {work_sheet["F1"].coordinate}')
# 单元格A1: 1, 1, A1
# 单元格B1: 2, 1, B1
# 单元格C1: 3, 1, C1
# 单元格D1: 4, 1, D1
# 单元格E1: 5, 1, E1
# 单元格F1: 6, 1, F1


# 5.获取工作表内容的列数和行数
print(f'工作表列数：{work_sheet.max_column}')
print(f'工作表行数：{work_sheet.max_row}')
# 工作表列数：6
# 工作表行数：20

# 6.获取单元格内容
# cell(column=n, row=m)
for j in range(1, work_sheet.max_row + 1):
    for i in range(1, work_sheet.max_column + 1):
        print(work_sheet.cell(column=i, row=j).value, end=' ')
    print()

# 7.工作表对象的rows和columns
"""
创建工作表对象成功后，会自动产生数据产生器（generators）：
rows: 工作表数据产生器以行方式包裹，每一行用一个Tuple包裹；
columns：工作表数据产生器以列方式包裹，每一列用一个Tuple包裹。
"""
print(type(work_sheet.rows))  # 
print(type(work_sheet.columns))  # 

for cell in list(work_sheet.columns)[0]:
    print(cell.value)

for cell in list(work_sheet.rows)[1]:
    print(cell.value, end=' ')

# 逐行遍历
print('逐行遍历开始...')
for row in work_sheet.rows:
    for cell in row:
        print(cell.value, end=' ')
    print()
print('逐行遍历结束...')

# 逐列遍历
print('逐列遍历开始...')
for column in work_sheet.columns:
    for cell in column:
        print(cell.value, end=' ')
    print()
print('逐列遍历结束...')

# 8.用整数取代域名
"""
get_column_letter(数值)：将数值转成字母
column_index_from_string(字母)：将字母转成数值
"""

print(f'列数：{get_column_letter(work_sheet.max_column)}')
print(f"3 --> {get_column_letter(3)}")
print(f"26 --> {get_column_letter(26)}")
print(f"39 --> {get_column_letter(39)}")
print(f"46 --> {get_column_letter(46)}")
print(f"120 --> {get_column_letter(120)}")
# 列数：F
# 3 --> C
# 26 --> Z
# 39 --> AM
# 46 --> AT
# 120 --> DP

print(f"A --> {column_index_from_string('A')}")
print(f"F --> {column_index_from_string('F')}")
print(f"AB --> {column_index_from_string('AB')}")
print(f"BBC --> {column_index_from_string('BBC')}")
print(f"CNN --> {column_index_from_string('CNN')}")
# A --> 1
# F --> 6
# AB --> 28
# BBC --> 1407
# CNN --> 2406

# 9.切片
# 使用切片的概念读取某区间数据
# 逐行读取
for row in work_sheet['A3':'F4']:
    for cell in row:
        print(cell.value, end=' ')
    print()
# 白居易 乐天 香山居士 唐朝 诗魔、诗王 《长恨歌》、《卖炭翁》、《琵琶行》
# 杜甫 子美 少陵野老 唐朝 诗圣 《春望》、《茅屋为秋风所破歌》、《登高》、《望岳》
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

data.xlsx：

3. 写入Excel文件

import openpyxl

# 1.创建空白工作簿
work_book = openpyxl.Workbook()
# 2.保存Excel文件
work_book.save('new_workbook.xlsx')
1
2
3
4
5
6

4. 复制Excel文件

import openpyxl

filename = 'data.xlsx'
work_book = openpyxl.load_workbook(filename=filename)  # 开启工作簿
backup_name = filename[:filename.find('.xlsx')] + '-backup.xlsx'
work_book.save(backup_name)
1
2
3
4
5
6

效果：

5. 创建工作表

# author:mlnt
# createdate:2022/8/16

import openpyxl

# 1.创建空白工作簿
work_book = openpyxl.Workbook()
print(f'工作表列表：{work_book.sheetnames}')  # 工作表列表：['Sheet']
# 2.创建新的工作表
work_book.create_sheet()
print(f'工作表列表：{work_book.sheetnames}')  # 工作表列表：['Sheet', 'Sheet1']
work_sheet = work_book.active  # 获取当前工作表
print(f'当前工作表：{work_sheet.title}')  # 当前工作表：Sheet

"""
在创建工作表时，预设的工作表名称为”SheetN“，N为数字编号，以递增方式显示；
新建的工作表放在工作表列的最右边。
可以通过在create_sheet()中添加参数title和index设置新工作表的名称及位置（工作表位置从0开始）
"""
work_book.create_sheet(index=0, title='工作表1')
work_book.create_sheet(index=2, title='工作表3')
print(f'工作表列表：{work_book.sheetnames}')
# 工作表列表：['工作表1', 'Sheet', '工作表3', 'Sheet1']

# 3.删除工作表
# 删除”工作表3“
work_book.remove(work_book['工作表3'])
print(f'工作表列表：{work_book.sheetnames}') # 工作表列表：['工作表1', 'Sheet', 'Sheet1']
# 删除”Sheet“
del work_book['Sheet']
print(f'工作表列表：{work_book.sheetnames}') # 工作表列表：['工作表1', 'Sheet1']

# 4.写入单元格
work_sheet = work_book.active  # 获取当前工作表
print(f'当前工作表：{work_sheet.title}')  # 当前工作表：Sheet
rows = [
    ['姓名', '年龄', '联系方式', '学历'],
    ['张三', '18', '18888886666', '大专'],
    ['王二狗', '28', '18888888888', '研究生'],
    ['苟恭芝', '38', '18888889999', '博士'],
    ['李华', '20', '18888887777', '本科'],
    ['曹亠强', '18', '18888883333', '大专']
]
for row in rows:
    work_sheet.append(row)
# 保存Excel文件
work_book.save('my_workbook.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

6. 设置单元格字体及颜色

# author:mlnt
# createdate:2022/8/16
import openpyxl
from openpyxl.styles import Font

wb = openpyxl.Workbook()  # 创建空白工作簿
ws = wb.active  # 获得当前工作表
"""
bold：加粗，值为True时表示粗体
italic：斜体，值为True时设置斜体
strike：删除线，值为True时设置删除线
name：字体名称，如：Arial
size：字号
color：字体颜色，color='FFFFFF'
"""
fontTitle1 = Font(name='微软雅黑', size=24)
ws['A1'].font = fontTitle1
ws['A1'] = '勿谓言之不预'
fontTitle2 = Font(name='楷体', size=18, bold=True)
ws['A2'].font = fontTitle2
ws['A2'] = '山不在高，有仙则名'
# 设置字体及颜色
# RGB颜色对照表：https://www.917118.com/tool/color_3.html
fontTitle3 = Font(name='Arial', size=20, italic=True, color='00FF7F')
ws['A3'].font = fontTitle3
ws['A3'] = 'The early bird catches the worm.'

# 保存Excel文件
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

效果：

7. 数学公式的使用

• 常用的数学公式：
• SUM()：总和，如：SUM(A1:A3)
• AVERAGE()：平均值，如：AVERAGE(A1:A3)
• MAX()：最大值，如：MAX(A1:A3)
• MIN()：最小值，如：MIN(A1:A3)

import openpyxl

wb = openpyxl.Workbook()  # 创建空白工作簿
ws = wb.active  # 获得当前工作表
rows = [
    ['学号', '姓名', '语文', '数学', '英语', '物理', '化学', '生物', '总分'],
    ['1001', '张三', 90, 98, 106, 80, 85, 78, '=SUM(C2:H2)'],
    ['1002', 'Tom', 93, 100, 96, 84, 75, 68, '=SUM(C3:H3)'],
    ['1003', 'Jack', 89, 80, 108, 70, 65, 88, '=SUM(C4:H4)'],
    ['1004', 'Mary', 110, 88, 88, 68, 68, 64, '=SUM(C5:H5)'],
    ['1005', 'Jane', 98, 78, 86, 56, 95, 72, '=SUM(C6:H6)']
]
for row in rows:
    # 将数据添加到工作表
    ws.append(row)
ws['B7'] = '总分'
ws['C7'] = '=SUM(C2:C6)'
ws['D7'] = '=SUM(D2:D6)'
ws['E7'] = '=SUM(E2:E6)'
ws['F7'] = '=SUM(F2:F6)'
ws['G7'] = '=SUM(G2:G6)'
ws['H7'] = '=SUM(H2:H6)'

ws['B8'] = '平均分'
ws['C8'] = '=AVERAGE(C2:C6)'
ws['D8'] = '=AVERAGE(D2:D6)'
ws['E8'] = '=AVERAGE(E2:E6)'
ws['F8'] = '=AVERAGE(F2:F6)'
ws['G8'] = '=AVERAGE(G2:G6)'
ws['H8'] = '=AVERAGE(H2:H6)'

ws['B9'] = '最高分'
ws['C9'] = '=MAX(C2:C6)'
ws['D9'] = '=MAX(D2:D6)'
ws['E9'] = '=MAX(E2:E6)'
ws['F9'] = '=MAX(F2:F6)'
ws['G9'] = '=MAX(G2:G6)'
ws['H9'] = '=MAX(H2:H6)'

ws['B10'] = '最低分'
ws['C10'] = '=MIN(C2:C6)'
ws['D10'] = '=MIN(D2:D6)'
ws['E10'] = '=MIN(E2:E6)'
ws['F10'] = '=MIN(F2:F6)'
ws['G10'] = '=MIN(G2:G6)'
ws['H10'] = '=MIN(H2:H6)'

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

效果：

8. 设置单元格宽高

单元格预设的高度为12.75pt，72pt等于1英寸，使用column_dimensions属性可以设置行高；单元格默认宽度为8.43个英文字符宽度，可使用row_dimensions设置单元格的宽度如果将宽度或高度设置为0，则具有隐藏单元格的效果。

import openpyxl

wb = openpyxl.Workbook()  # 创建空白工作簿
ws = wb.active  # 获得当前工作表

ws['A1'] = '海内存知己'
ws['A2'] = '天涯若比邻'
ws['B2'] = 'Hello world'
ws.row_dimensions[1].height = 30  # 设置高度为30pt
ws.column_dimensions['B'].width = 30  # 设置宽度为30个英文字符宽
wb.save('设置单元格宽高.xlsx')
1
2
3
4
5
6
7
8
9
10
11

效果：

9. 设置单元格对齐方式

使用Alignment()方法，需设置2个参数：

• horizontal（水平方向）:
  • left：靠左
  • right: 靠右
  • center: 居中
• vertical（垂直方向）:
  • top：靠上
  • center：居中
  • bottom：靠下

import openpyxl
from openpyxl.styles import Alignment

wb = openpyxl.Workbook()  # 创建空白工作簿
ws = wb.active  # 获得当前工作表
ws['A1'] = '测试1'
ws['B1'] = '测试2'
ws['C1'] = '测试3'
ws.row_dimensions[1].height = 30  # 设置高度为40pt
ws.column_dimensions['B'].width = 20  # 设置宽度为20个字符宽
ws['A1'].alignment = Alignment(horizontal='left', vertical='top')  # 居左靠上
ws['B1'].alignment = Alignment(horizontal='center', vertical='center')  # 水平居中，垂直居中
ws['C1'].alignment = Alignment(horizontal='right', vertical='bottom')  # 靠右居下

# 保存excel文件
wb.save('设置单元格对齐方式.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

效果：

10. 合并与取消单元格合并

• 合并单元格
  • 使用merge_cells()合并单元格，可以合并同一行（row）、同一列（column）或一个区域的单元格
• 取消合并单元格
  • unmerge_cells()

# author:mlnt
# createdate:2022/8/16

import openpyxl
from openpyxl.styles import Alignment

wb = openpyxl.Workbook()  # 创建空白工作簿
ws = wb.active  # 获得当前工作表

"""
1.合并单元格
使用merge_cells()合并单元格，可以合并同一行（row）、同一列（column）或一个区域的单元格
"""
ws['A1'] = '早起的鸟儿有虫吃'
ws['A2'] = 'The early bird catches the worm.'
ws['A3'] = '人生如戏'
ws['C4'] = 'Where there is a will there is a way.'
ws.merge_cells('A1:D1')   # 合并A1:D1单元格
ws.merge_cells('A3:A8')   # 合并A3:A8单元格
ws.merge_cells('C4:G6')   # 合并C4:G6单元格
ws['A1'].alignment = Alignment(horizontal='center')
ws['A3'].alignment = Alignment(vertical='center')
ws['C3'].alignment = Alignment(horizontal='center', vertical='center')

# 2.取消合并单元格
# unmerge_cells()
ws.unmerge_cells('A3:A8')  # 取消合并A3:A8单元格

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

效果：

11. 创建图表

11.1 柱状图

# author:mlnt
# createdate:2022/8/16
"""
BarChart：柱状图
BarChart3D：3D柱状图
PieChart：饼图
PieChart：3D饼图
BubleChart：泡泡图
AreaChart：分区图
AreaChart3D：3D分区图
LineChart：折线图
LineChart3D：3D折线图
RedarChart：雷达图
StockChart：股票图
"""
import openpyxl
from openpyxl.chart import BarChart, Reference

wb = openpyxl.Workbook()  # 创建空白工作簿
ws = wb.active  # 获得当前工作表
rows = [
    ['学号', '姓名', '语文', '数学', '英语', '物理', '化学', '生物'],
    ['1001', '张三', 90, 98, 106, 80, 85, 78],
    ['1002', 'Tom', 93, 100, 96, 84, 75, 68],
    ['1003', 'Jack', 89, 80, 108, 70, 65, 88],
    ['1004', 'Mary', 110, 88, 88, 68, 68, 64],
    ['1005', 'Jane', 98, 78, 86, 56, 95, 72]
]
for row in rows:
    # 将数据添加到工作表
    ws.append(row)

chart = BarChart()                          # 直方图
chart.title = '2022某班某小组学生成绩表'        # 图表标题
chart.y_axis.title = '分数'                  # y轴标题
chart.x_axis.title = '学员'                  # x轴标题
data = Reference(ws, min_col=3, max_col=8, min_row=1, max_row=6)  # 图表数据
chart.add_data(data, titles_from_data=True)  # 建立图表
x_title = Reference(ws, min_col=2, min_row=2, max_row=6)  # x轴标记名称
chart.set_categories(x_title)  # 设置x轴标记名称
ws.add_chart(chart, 'J1')      # 放置图标位置
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

效果：

11.2 饼图

# author:mlnt
# createdate:2022/8/16

import openpyxl
from openpyxl.chart import PieChart, Reference

wb = openpyxl.Workbook()  # 创建空白工作簿
ws = wb.active  # 获得当前工作表
rows = [
    ['科目', '分数'],
    ['语文', 90],
    ['数学', 98],
    ['英语', 106],
    ['物理', 80],
    ['化学', 85],
    ['生物', 78]
]
for row in rows:
    ws.append(row)

chart = PieChart()     # 饼图
chart.title = '某学员成绩分析表'

data = Reference(ws, min_col=2, min_row=1, max_row=7)  # 图表数据
chart.add_data(data, titles_from_data=True)  # 建立图表
labels = Reference(ws, min_col=1, min_row=2, max_row=7)  # 标签名称
chart.set_categories(labels)  # 设置标签名称
ws.add_chart(chart, 'D1')
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

效果：

---

参考：

• 官方文档：https://openpyxl.readthedocs.io/en/stable/index.html
• RGB颜色对照表：https://www.917118.com/tool/color_3.html