Python从入门到精通day25

Python读写Excel文件-2

Excel简介

上一节课我们学习了使用xlrd、xlwt、xlutils三个三方库操作Excel的xls格式文件，这类库适用于Excel 2007以前的旧版本，且存在“读、写需分开调用不同库”的不便。本节课我们将讲解更实用、功能更强大的三方库——openpyxl，它专门用于操作Excel 2007及以后的xlsx格式文件，支持读写一体，样式设置、公式计算、图表生成等操作更简洁，是日常办公自动化和项目开发中操作Excel的首选工具。首先需要先安装它。

1. 什么是openpyxl

openpyxl是Python中专门用于操作xlsx格式Excel文件的三方库，由社区维护，功能全面且操作便捷。与上一节课的xlrd/xlwt相比，它无需区分“读库”和“写库”，打开一个Excel文件后可同时完成读、写、修改操作，还支持数据透视表、统计图表插入等高级功能，极大提升了Excel操作的效率。

补充说明：与Excel的竞品（Google Sheets、LibreOffice Calc等）类似，openpyxl仅兼容xlsx格式，不支持Excel 2007以前的xls格式，这是使用前必须注意的前提；若需处理xls格式，仍需使用上一节课的xlrd/xlwt组合。

2. openpyxl核心优势（对比xlrd/xlwt）

为了让大家清晰区分两者差异，快速选择合适的库，整理核心优势对比如下：

• 读写一体：无需切换多个库，一个openpyxl即可完成Excel文件的读取、写入、修改操作，简化代码逻辑；

• 操作便捷：单元格定位、样式设置、公式计算的语法更简洁，贴合Excel的操作习惯（如行/列索引从1开始）；

• 功能强大：支持插入统计图表、创建数据透视表、复杂样式设置，远超xlrd/xlwt的基础功能；

• 兼容性好：完美支持xlsx格式的所有核心功能，适配Excel 2007及以后的所有版本，生成的文件可直接用Excel打开。

3. 三方库安装

openpyxl为独立三方库，无需依赖其他辅助库，打开终端（或CMD），输入以下命令即可完成安装（国内用户可添加清华源加速）：

pip install openpyxl

读取Excel文件

例如在当前文件夹下有一个名为“阿里巴巴2020年股票数据.xlsx”的 Excel 文件，如果想读取并显示该文件的内容，可以通过如下所示的代码来完成。

import datetimeimport openpyxl# 加载一个工作簿 ---> Workbookwb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')# 获取工作表的名字print(wb.sheetnames)# 获取工作表 ---> Worksheetsheet = wb.worksheets[0]# 获得单元格的范围print(sheet.dimensions)# 获得行数和列数print(sheet.max_row, sheet.max_column)# 获取指定单元格的值print(sheet.cell(3, 3).value)print(sheet['C3'].value)print(sheet['G255'].value)# 获取多个单元格（嵌套元组）print(sheet['A2:C5'])# 读取所有单元格的数据for row_ch inrange(2, sheet.max_row + 1):for col_ch in'ABCDEFG':        value = sheet[f'{col_ch}{row_ch}'].valueiftype(value) == datetime.datetime:print(value.strftime('%Y年%m月%d日'), end='\t')eliftype(value) == int:print(f'{value:<10d}', end='\t')eliftype(value) == float:print(f'{value:.4f}', end='\t')else:print(value, end='\t')print()

提示：上面代码中使用的Excel文件“阿里巴巴2020年股票数据.xlsx”可以通过后面的百度云盘地址进行获取。链接:https://pan.baidu.com/s/1rQujl5RQn9R7PadB2Z5g_g 提取码:e7b4。

需要提醒大家一点，openpyxl获取指定的单元格有两种方式，一种是通过cell方法，需要注意，该方法的行索引和列索引都是从1开始的，这是为了照顾用惯了 Excel 的人的习惯；另一种是通过索引运算，通过指定单元格的坐标，例如C3、G255，也可以取得对应的单元格，再通过单元格对象的value属性，就可以获取到单元格的值。通过上面的代码，相信大家还注意到了，可以通过类似sheet['A2:C5']或sheet['A2':'C5']这样的切片操作获取多个单元格，该操作将返回嵌套的元组，相当于获取到了多行多列。

写Excel文件

下面我们使用openpyxl来进行写 Excel 操作。

import randomimport openpyxl# 第一步：创建工作簿（Workbook）wb = openpyxl.Workbook()# 第二步：添加工作表（Worksheet）sheet = wb.activesheet.title = '期末成绩'titles = ('姓名', '语文', '数学', '英语')for col_index, title inenumerate(titles):    sheet.cell(1, col_index + 1, title)names = ('关羽', '张飞', '赵云', '马超', '黄忠')for row_index, name inenumerate(names):    sheet.cell(row_index + 2, 1, name)for col_index inrange(2, 5):        sheet.cell(row_index + 2, col_index, random.randrange(50, 101))# 第四步：保存工作簿wb.save('考试成绩表.xlsx')

调整样式和公式计算

在使用openpyxl操作 Excel 时，如果要调整单元格的样式，可以直接通过单元格对象（Cell对象）的属性进行操作。单元格对象的属性包括字体（font）、对齐（alignment）、边框（border）等，具体的可以参考openpyxl的官方文档。在使用openpyxl时，如果需要做公式计算，可以完全按照 Excel 中的操作方式来进行，具体的代码如下所示。

import openpyxlfrom openpyxl.styles import Font, Alignment, Border, Side# 对齐方式alignment = Alignment(horizontal='center', vertical='center')# 边框线条side = Side(color='ff7f50', style='mediumDashed')wb = openpyxl.load_workbook('考试成绩表.xlsx')sheet = wb.worksheets[0]# 调整行高和列宽sheet.row_dimensions[1].height = 30sheet.column_dimensions['E'].width = 120sheet['E1'] = '平均分'# 设置字体sheet.cell(1, 5).font = Font(size=18, bold=True, color='ff1493', name='华文楷体')# 设置对齐方式sheet.cell(1, 5).alignment = alignment# 设置单元格边框sheet.cell(1, 5).border = Border(left=side, top=side, right=side, bottom=side)for i inrange(2, 7):# 公式计算每个学生的平均分    sheet[f'E{i}'] = f'=average(B{i}:D{i})'    sheet.cell(i, 5).font = Font(size=12, color='4169e1', italic=True)    sheet.cell(i, 5).alignment = alignmentwb.save('考试成绩表.xlsx')

生成统计图表

通过openpyxl库，可以直接向 Excel 中插入统计图表，具体的做法跟在 Excel 中插入图表大体一致。我们可以创建指定类型的图表对象，然后通过该对象的属性对图表进行设置。当然，最为重要的是为图表绑定数据，即横轴代表什么，纵轴代表什么，具体的数值是多少。最后，可以将图表对象添加到表单中，具体的代码如下所示。

from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencewb = Workbook(write_only=True)sheet = wb.create_sheet()rows = [    ('类别', '销售A组', '销售B组'),    ('手机', 40, 30),    ('平板', 50, 60),    ('笔记本', 80, 70),    ('外围设备', 20, 10),]# 向表单中添加行for row in rows:    sheet.append(row)# 创建图表对象chart = BarChart()chart.type = 'col'chart.style = 10# 设置图表的标题chart.title = '销售统计图'# 设置图表纵轴的标题chart.y_axis.title = '销量'# 设置图表横轴的标题chart.x_axis.title = '商品类别'# 设置数据的范围data = Reference(sheet, min_col=2, min_row=1, max_row=5, max_col=3)# 设置分类的范围cats = Reference(sheet, min_col=1, min_row=2, max_row=5)# 给图表添加数据chart.add_data(data, titles_from_data=True)# 给图表设置分类chart.set_categories(cats)chart.shape = 4# 将图表添加到表单指定的单元格中sheet.add_chart(chart, 'A10')wb.save('demo.xlsx')

运行上面的代码，打开生成的 Excel 文件，效果如下图所示。

今日总结

本节课我们全面掌握了openpyxl库操作xlsx格式Excel文件的核心方法，结合上一节课的xlrd/xlwt，完成了Python操作Excel的完整知识点覆盖，重点总结如下：

1. 库的选择建议：

1. 处理xls格式（Excel 2007以前）：使用xlrd（读）+ xlwt（写）+ xlutils（辅助）；

2. 处理xlsx格式（Excel 2007及以后）：优先使用openpyxl（读写一体、功能强大、操作简洁）。

2. openpyxl核心亮点：行/列索引贴合Excel习惯（从1开始）、读写一体无需切换库、样式设置简洁、支持公式计算和统计图表插入，是办公自动化的首选工具。

3. 易错点汇总：

1. 索引问题：openpyxl行/列索引从1开始，xlrd从0开始，切换使用时极易出错；

2. 格式问题：openpyxl仅支持xlsx格式，xlwt仅支持xls格式，后缀错误会导致文件无法生成；

3. 公式问题：写入公式时，需用Excel原生公式语法，且公式中的单元格坐标要与代码中的行/列索引对应。

实战建议：

• 课后练习：读取“销售统计图.xlsx”，添加“总销量”列（计算每组商品的总销量），插入折线图展示总销量变化，设置对应样式；

• 实际应用：结合上一节课的知识，实现“批量读取多个xls/xlsx文件 → 合并数据 → 写入新文件 → 添加样式和图表”的完整办公自动化流程；

• 效率提升：若需处理大量Excel数据（如千行万行），后续可学习pandas库，它封装了更简洁的Excel读写方法，结合openpyxl可实现高效数据处理。