Python|Python 使用openpyxl处理Excel文件详情 Python使用openpyxl处理Excel文件详

前言
1. Excel窗口
2. 读取Excel文件
3. 写入Excel文件
4. 复制Excel文件
5. 创建工作表
6. 设置单元格字体及颜色
7. 数学公式的使用
8. 设置单元格宽高
9. 设置单元格对齐方式
10. 合并与取消单元格合并
11. 创建图表

11.1 柱状图
11.2 饼图

前言安装openpyxl模块：

pip install openpyxl

文章图片

导入模块：

import openpyxl

官方文档：

1. Excel窗口

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

文章图片

2. 读取Excel文件

# author:mlnt# createdate:2022/8/16import openpyxlfrom 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.sheetnamesprint(f'工作表列表：{work_sheets}')# 工作表列表：['Sheet1', 'Sheet2', 'Sheet3']# 获取当前工作表的名称current_sheet = work_book.activeprint(f'当前工作表：{current_sheet}')# 当前工作表：# 获取当前工作表的内容title = current_sheet.titleprint(f'当前工作表标题：{title}')# 当前工作表标题：Sheet1# 3.切换工作表work_sheet = work_book['Sheet2']# 返回名称相应的工作表print(f'当前工作表：{work_sheet.title}')# 当前工作表：Sheet2work_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 --> DPprint(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 openpyxlfilename = '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/16import 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}')# 当前工作表：Sheetrows = [['姓名', '年龄', '联系方式', '学历'],['张三', '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/16import openpyxlfrom openpyxl.styles import Fontwb = openpyxl.Workbook()# 创建空白工作簿ws = wb.active# 获得当前工作表"""bold：加粗，值为True时表示粗体italic：斜体，值为True时设置斜体strike：删除线，值为True时设置删除线name：字体名称，如：Arialsize：字号color：字体颜色，color='FFFFFF'"""fontTitle1 = Font(name='微软雅黑', size=24)ws['A1'].font = fontTitle1ws['A1'] = '勿谓言之不预'fontTitle2 = Font(name='楷体', size=18, bold=True)ws['A2'].font = fontTitle2ws['A2'] = '山不在高，有仙则名'# 设置字体及颜色# RGB颜色对照表：https://www.917118.com/tool/color_3.htmlfontTitle3 = Font(name='Arial', size=20, italic=True, color='00FF7F')ws['A3'].font = fontTitle3ws['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 openpyxlwb = 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 openpyxlwb = openpyxl.Workbook()# 创建空白工作簿ws = wb.active# 获得当前工作表ws['A1'] = '海内存知己'ws['A2'] = '天涯若比邻'ws['B2'] = 'Hello world'ws.row_dimensions[1].height = 30# 设置高度为30ptws.column_dimensions['B'].width = 30# 设置宽度为30个英文字符宽wb.save('设置单元格宽高.xlsx')

效果：

文章图片

9. 设置单元格对齐方式使用Alignment()方法，需设置2个参数：
horizontal（水平方向）:

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

vertical（垂直方向）:

top：靠上
center：居中
bottom：靠下

import openpyxlfrom openpyxl.styles import Alignmentwb = openpyxl.Workbook()# 创建空白工作簿ws = wb.active# 获得当前工作表ws['A1'] = '测试1'ws['B1'] = '测试2'ws['C1'] = '测试3'ws.row_dimensions[1].height = 30# 设置高度为40ptws.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|Python 使用openpyxl处理Excel文件详情】效果：

文章图片

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

# author:mlnt# createdate:2022/8/16import openpyxlfrom openpyxl.styles import Alignmentwb = 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 openpyxlfrom openpyxl.chart import BarChart, Referencewb = 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 = https://www.it610.com/article/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/16import openpyxlfrom openpyxl.chart import PieChart, Referencewb = openpyxl.Workbook()# 创建空白工作簿ws = wb.active# 获得当前工作表rows = [['科目', '分数'],['语文', 90],['数学', 98],['英语', 106],['物理', 80],['化学', 85],['生物', 78]]for row in rows:ws.append(row)chart = PieChart()# 饼图chart.title = '某学员成绩分析表'data = https://www.it610.com/article/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')

效果：