脚本之家,脚本语言编程技术及教程分享平台!
分类导航

Python|VBS|Ruby|Lua|perl|VBA|Golang|PowerShell|Erlang|autoit|Dos|bat|

服务器之家 - 脚本之家 - Python - Python 使用openpyxl处理Excel文件详情

Python 使用openpyxl处理Excel文件详情

2022-08-17 20:12梦里逆天 Python

这篇文章主要介绍了Python 使用openpyxl处理Excel文件详情,文章围绕主题展开详细的内容介绍,具有一定的参考价值,需要的小伙伴可以参考一下

前言

安装openpyxl模块:

?
1
pip install openpyxl

Python 使用openpyxl处理Excel文件详情

导入模块:

?
1
import openpyxl

官方文档:

 

1. Excel窗口

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

Python 使用openpyxl处理Excel文件详情

 

2. 读取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
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
# 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}')
# 当前工作表:<Worksheet "Sheet1">
 
# 获取当前工作表的内容
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))  # <class 'generator'>
print(type(work_sheet.columns))  # <class 'generator'>
 
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()
# 白居易 乐天 香山居士 唐朝 诗魔、诗王 《长恨歌》、《卖炭翁》、《琵琶行》
# 杜甫 子美 少陵野老 唐朝 诗圣 《春望》、《茅屋为秋风所破歌》、《登高》、《望岳》

data.xlsx:

Python 使用openpyxl处理Excel文件详情

 

3. 写入Excel文件

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

 

4. 复制Excel文件

?
1
2
3
4
5
6
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)

效果:

Python 使用openpyxl处理Excel文件详情

 

5. 创建工作表

?
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
# 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')

Python 使用openpyxl处理Excel文件详情

 

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

?
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
# 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')

效果:

Python 使用openpyxl处理Excel文件详情

 

7. 数学公式的使用

常用的数学公式:

  • SUM():总和,如:SUM(A1:A3)
  • AVERAGE():平均值,如:AVERAGE(A1:A3)
  • MAX():最大值,如:MAX(A1:A3)
  • MIN():最小值,如:MIN(A1:A3)
?
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
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')

效果:

Python 使用openpyxl处理Excel文件详情

 

8. 设置单元格宽高

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

?
1
2
3
4
5
6
7
8
9
10
11
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')

效果:

Python 使用openpyxl处理Excel文件详情

 

9. 设置单元格对齐方式

使用Alignment()方法,需设置2个参数:

horizontal(水平方向):

  • left:靠左
  • right: 靠右
  • center: 居中

vertical(垂直方向):

  • top:靠上
  • center:居中
  • bottom:靠下
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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')

效果:

Python 使用openpyxl处理Excel文件详情

 

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

合并单元格:

使用merge_cells()合并单元格,可以合并同一行(row)、同一列(column)或一个区域的单元格

取消合并单元格:

unmerge_cells()

?
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
# 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')

效果:

Python 使用openpyxl处理Excel文件详情

 

11. 创建图表

 

11.1 柱状图

?
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
# 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')

效果:

Python 使用openpyxl处理Excel文件详情

 

11.2 饼图

?
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
# 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')

效果:

Python 使用openpyxl处理Excel文件详情

到此这篇关于Python 使用openpyxl处理Excel文件详情的文章就介绍到这了,更多相关Python 处理Excel文件 内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!

原文链接:https://blog.csdn.net/username666/article/details/126371916

延伸 · 阅读

精彩推荐
  • PythonPython-split()函数实例用法讲解

    Python-split()函数实例用法讲解

    在本篇文章里小编给大家整理的是一篇关于Python-split()函数实例用法讲解,有兴趣的朋友们可以学习下。...

    宋宋大人6942021-08-15
  • PythonPython中celery的使用

    Python中celery的使用

    Celery是一个简单、灵活且可靠的,处理大量消息的分布式系统,专注于实时处理的异步任务队列,同时也支持任务调度。接下来通过本文给大家介绍Python中...

    fivenian7112021-12-24
  • PythonPython利用memory_profiler查看内存占用情况

    Python利用memory_profiler查看内存占用情况

    memory_profiler是第三方模块,用于监视进程的内存消耗以及python程序内存消耗的逐行分析。本文将利用memory_profiler查看代码运行占用内存情况,感兴趣的可以...

    玩转测试开发4172022-06-28
  • PythonPython实现基于标记的分水岭分割算法

    Python实现基于标记的分水岭分割算法

    分水岭技术是一种众所周知的分割算法,特别适用于提取图片中的相邻或重叠对象。本文将用Python实现基于标记的分水岭分割算法,感兴趣的可以了解一下...

    求则得之,舍则失之7742022-07-29
  • PythonPython运行出现DeprecationWarning的问题及解决

    Python运行出现DeprecationWarning的问题及解决

    这篇文章主要介绍了Python运行出现DeprecationWarning的问题及解决方案,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐...

    旺 崽11242022-07-13
  • Python基于python的Paxos算法实现

    基于python的Paxos算法实现

    这篇文章主要介绍了基于python的Paxos算法实现,理解一个算法最快,最深刻的做法,我觉着可能是自己手动实现,虽然项目中不用自己实现,有已经封装好...

    charles_lun7962021-07-31
  • PythonPython float函数实例用法

    Python float函数实例用法

    在本篇文章里小编给大家整理的了一篇关于Python float函数实例用法,有兴趣的朋友们可以学习下。...

    小妮浅浅6552021-09-22
  • PythonPytest实现setup和teardown的详细使用详解

    Pytest实现setup和teardown的详细使用详解

    这篇文章主要介绍了Pytest实现setup和teardown的详细使用详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋...

    小菠萝测试笔记5932021-10-13