Python使用openpyxl处理Excel文件的方法_F11 - 专业站长和开发者的学习网站

前言

安装openpyxl模块：

1	pip install openpyxl

导入模块：

1	import openpyxl

官方文档：

1. Excel窗口

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

2. 读取Excel文件

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}')

# 当前工作表：

# 获取当前工作表的内容

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()

# 白居易乐天香山居士唐朝诗魔、诗王《长恨歌》、《卖炭翁》、《琵琶行》

# 杜甫子美少陵野老唐朝诗圣《春望》、《茅屋为秋风所破歌》、《登高》、《望岳》

data.xlsx：

3. 写入Excel文件

import openpyxl

# 1.创建空白工作簿

work_book = openpyxl.Workbook()

# 2.保存Excel文件

work_book.save('new_workbook.xlsx')

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)

效果：

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

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

效果：

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

效果：

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

效果：

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

效果：

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

效果：

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

效果：

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

效果：