1. 从“5分钟读取”的困惑说起为什么你的Python处理Excel这么慢最近在技术社区里经常能看到类似“python读取excel数据全部读取耗时5分钟仅读几列也是5分钟怎么回事”这样的问题。这其实是一个典型的“踩坑”场景很多刚接触Python处理Excel的朋友兴冲冲地写了几行代码结果发现性能差得离谱一个不大的文件都要等上好几分钟完全不符合Python“高效自动化”的预期。我自己在早期做数据清洗和报表自动化时也在这个问题上栽过跟头。问题的根源往往不在于Python本身而在于我们选择的工具库和使用方法。Python生态里处理Excel的库有好几个比如老牌的xlrd/xlwt功能强大的openpyxl以及基于数据分析的pandas。如果你用错了场景或者没掌握正确的“姿势”性能瓶颈就会立刻显现。比如你用openpyxl的默认方式去读取一个几十万行、带有复杂公式和格式的文件慢是必然的。这就像用瑞士军刀去砍大树不是刀不好是你用错了工具。这篇文章我就以一个过来人的身份手把手带你深入Python处理Excel的实战。我们不止要解决“怎么读怎么写”的基础问题更要彻底搞明白背后的“为什么”以及如何根据你的具体场景是简单数据搬运还是复杂格式保持或是大数据量分析选择最高效的方案。我会结合openpyxl、pandas以及一些性能优化技巧让你告别“5分钟读取”的尴尬真正实现高效、稳定的Excel自动化处理。2. 工具选型openpyxl、pandas还是其他先搞清楚你的核心需求在动手写第一行代码之前搞清楚你要处理的是什么类型的Excel文件以及你的核心操作是什么这比盲目开始更重要。选对了工具事半功倍选错了就是无尽的性能折磨和兼容性问题。2.1 主流工具库的定位与边界openpyxl这是目前处理.xlsx和.xlsm格式即Excel 2007及以上版本的事实标准。它的核心优势在于对Excel文件完整的对象模型支持。这意味着你不仅能读写单元格数据还能精细地操作字体、颜色、边框、合并单元格、公式、图表、甚至图像和注释。如果你需要生成一个格式复杂、要求与手工制作完全一致的报表或者需要修改一个现有模板的样式openpyxl几乎是唯一选择。注意openpyxl不支持老旧的.xls格式。如果你的文件是.xls需要先用Excel另存为.xlsx或者使用xlrd库仅读和xlwt库仅写来处理但后者功能有限且已停止维护。pandaspandas是一个强大的数据分析库它处理Excel可以看作是其数据I/O功能的一部分。pandas的核心是DataFrame一种二维表格型数据结构它处理Excel的思路是将整个工作表或指定区域快速加载到内存中的DataFrame里然后你利用pandas强大的数据清洗、转换、分析能力进行操作最后再写回Excel。它的优势在于处理纯数据的速度快特别是对于大数据量的读取和计算。但是pandas在写回Excel时会丢失绝大部分原始格式除非配合openpyxl或xlsxwriter引擎进行额外设置它主要关心数据本身。xlsxwriter这是一个专注于写入.xlsx文件的库特点是功能强大、性能好支持丰富的格式和图表。但它只能写不能读。通常用于从零开始生成复杂的、带有格式的Excel报告。pandas的默认Excel写入引擎就是它针对.xlsx格式。2.2 如何根据你的场景做选择为了更直观我列了一个对比表格特性 / 库openpyxlpandas (读/写Excel)说明与建议核心能力读写.xlsx/.xlsm完整对象模型数据分析快速I/O定位完全不同格式支持完美支持样式、公式、图表等基本不支持默认会丢失需要格式用openpyxl读取性能中等可优化非常高尤其大数据量纯数据读取首选pandas写入性能中等可优化高但格式简单复杂报表用openpyxl或xlsxwriter适用场景1. 报表模板填充与格式调整2. 操作图表、图像等对象3. 需要保留或设置复杂格式1. 大数据量的数据清洗与分析2. 简单的数据导出不关心格式3. 作为数据处理的中间环节关键决策点一个常见组合-用pandas读取数据 - 用pandas进行数据处理 - 用openpyxl加载模板并写入数据、调整格式兼顾效率与效果我的经验是绝大多数日常自动化需求是混合型的。比如你需要从一个源Excel里读取大量数据清洗计算后填入一个设计好的报表模板中。这时最佳实践往往是“pandas读数据 openpyxl写格式”的组合拳。用pandas的read_excel()快速读入数据到DataFrame利用pandas进行高效的筛选、计算、分组聚合然后将结果数据通过openpyxl精确地写入到模板文件的指定位置并可能设置一些简单的格式如数字格式、字体加粗。这样既保证了数据处理的效率又满足了报表呈现的要求。3. 环境准备与基础操作从安装到第一个可运行脚本理论说再多不如动手试一下。我们先把环境搭起来跑通一个最简单的读写流程。3.1 安装与虚拟环境强烈建议使用虚拟环境来管理项目依赖避免不同项目间的库版本冲突。这里以最常用的venv为例。# 1. 创建项目目录并进入 mkdir excel_automation cd excel_automation # 2. 创建虚拟环境假设你使用Python 3 python -m venv venv # 3. 激活虚拟环境 # 在 Windows 上 venv\Scripts\activate # 在 macOS/Linux 上 source venv/bin/activate # 激活后命令行提示符前通常会显示 (venv) # 4. 安装核心库 # 安装openpyxl pip install openpyxl # 安装pandas (它会自动安装numpy等依赖) pip install pandas安装完成后可以通过pip list查看已安装的包确认。3.2 openpyxl 基础创建、保存与简单读写让我们先用openpyxl完成一个最小化的操作创建一个新工作簿在第一个单元格写入“Hello Excel”然后保存。# basic_operation.py from openpyxl import Workbook # 1. 创建一个新的工作簿Workbook wb Workbook() # 默认会创建一个名为‘Sheet’的工作表Worksheet我们可以通过 .active 属性获取它 ws wb.active # 可以给工作表改个名 ws.title 我的第一个Sheet # 2. 操作单元格Cell # 方法一通过工作表对象和坐标如‘A1’直接赋值 ws[A1] Hello Excel ws[B2] 42 # 方法二使用 .cell(row, column, value) 方法行列从1开始计数 ws.cell(row3, column1, value这是第三行第一列) # 3. 保存工作簿到文件 # 保存为 .xlsx 文件 wb.save(my_first_excel.xlsx) print(Excel文件已创建并保存为 my_first_excel.xlsx)运行这个脚本你会在当前目录下看到生成的my_first_excel.xlsx文件打开后内容符合预期。读取现有文件同样简单# read_existing.py from openpyxl import load_workbook # 1. 加载一个已存在的Excel文件 wb load_workbook(my_first_excel.xlsx) # 加载我们刚才创建的文件 # 2. 获取工作表 # 方法一通过名称获取 ws wb[我的第一个Sheet] # 方法二获取所有工作表名 sheet_names wb.sheetnames print(f工作簿中的所有工作表{sheet_names}) # 3. 读取单元格内容 cell_a1 ws[A1].value cell_b2 ws[B2].value print(fA1单元格的值是{cell_a1}) print(fB2单元格的值是{cell_b2}) # 4. 遍历某个区域例如A1到C3 print(\n遍历A1:C3区域) for row in ws.iter_rows(min_row1, max_row3, min_col1, max_col3): for cell in row: print(cell.coordinate, cell.value, end | ) print() # 换行iter_rows是openpyxl中非常实用的方法可以按行生成单元格迭代器方便进行循环处理。3.3 pandas 基础用DataFrame的思维处理表格数据pandas的操作更贴近数据分析。我们创建一个简单的DataFrame并写入Excel然后再读回来。# pandas_basic.py import pandas as pd # 1. 创建一个DataFrame data { 姓名: [张三, 李四, 王五], 年龄: [28, 34, 25], 城市: [北京, 上海, 广州] } df pd.DataFrame(data) print(创建的DataFrame) print(df) print(\n) # 2. 将DataFrame写入Excel # 默认使用xlsxwriter引擎写入.xlsx不保存索引 df.to_excel(pandas_output.xlsx, indexFalse, sheet_name员工信息) # 3. 从Excel读取数据到DataFrame df_read pd.read_excel(pandas_output.xlsx, sheet_name员工信息) print(从Excel读取的DataFrame) print(df_read)运行后你会得到一个pandas_output.xlsx文件里面有一个名为“员工信息”的工作表包含我们的数据。indexFalse参数很重要它避免了将DataFrame的默认行索引也写入Excel让表格更整洁。到这里你已经掌握了两个核心库最基础的读写操作。但这只是开始真正的挑战和技巧都在后面。4. 性能陷阱深度剖析为什么“仅读几列也是5分钟”现在我们回到开头那个最典型的问题。假设你有一个100MB、20万行、50列的Excel文件这种规模在实际业务中很常见。你用openpyxl写了下面这段“看起来没问题”的代码# slow_reading.py (性能低下的写法) from openpyxl import load_workbook import time start time.time() wb load_workbook(large_file.xlsx) # 假设这个文件很大 ws wb.active data [] # 遍历所有行 for row in ws.iter_rows(values_onlyTrue): # values_onlyTrue 只取值稍好但还不够 # 假设我们只需要第1列和第10列的数据 needed_data (row[0], row[9]) # 索引从0开始 data.append(needed_data) end time.time() print(f读取耗时{end - start:.2f}秒)你会发现即使你只取两列循环依然遍历了所有行和所有列的单元格对象。openpyxl在load_workbook时默认加载模式read_onlyFalse会将整个工作簿的所有对象包括单元格值、公式、样式等都解析并加载到内存中构建一个完整的对象树。这个初始化过程本身就很耗时尤其对于大文件。之后的遍历虽然你只用了两个值但Python依然需要访问每一行的每个单元格对象开销巨大。那么pandas为什么快pandas的read_excel函数底层默认使用openpyxl或xlrd引擎但它采用了更高效的批量数据处理方式。它本质上是在引擎读取数据后直接将其转换为高度优化的numpy数组和DataFrame内部结构跳过了大量中间对象创建的消耗。而且pandas在读取时如果指定usecols参数引擎层可能进行优化只解析所需的列区域数据。4.1 openpyxl 的性能优化方案对于必须使用openpyxl且需要处理大文件的场景有两个关键优化开关1. 只读模式 (read_onlyTrue)此模式类似于“流式读取”它不会将整个文件加载到内存而是按需从磁盘读取数据。适用于只读取数据不修改也不关心样式的场景。# fast_reading_openpyxl.py from openpyxl import load_workbook import time start time.time() # 关键设置 read_onlyTrue wb load_workbook(large_file.xlsx, read_onlyTrue, data_onlyTrue) ws wb.active data [] # 在只读模式下必须使用 ws.iter_rows() 进行遍历 for row in ws.iter_rows(min_row2, max_col10, values_onlyTrue): # 假设从第2行开始最多读到第10列 # 只取第1列和第10列 needed_data (row[0], row[9]) data.append(needed_data) wb.close() # 只读模式用完记得关闭 end time.time() print(f只读模式读取耗时{end - start:.2f}秒) print(f共读取{len(data)}行数据)2. 只写模式 (write_onlyTrue)当需要生成一个非常大的Excel文件时使用只写模式可以显著降低内存消耗。你需要使用特殊的WriteOnlyWorksheet对象来添加数据。# fast_writing_openpyxl.py from openpyxl import Workbook import time start time.time() # 关键设置 write_onlyTrue wb Workbook(write_onlyTrue) ws wb.create_sheet(title海量数据) # 准备数据假设要生成10万行5列的数据 data_to_write [] for i in range(1, 100001): data_to_write.append([fProduct_{i}, i*10, i*0.5, fCategory_{i%10}, f2023-{i%121:02d}-01]) # 在只写模式下使用 ws.append() 批量添加行每次一行 for row in data_to_write: ws.append(row) wb.save(huge_file_write_only.xlsx) end time.time() print(f只写模式生成耗时{end - start:.2f}秒)3. 结合使用data_onlyTrue这个参数在加载工作簿时非常有用。如果Excel单元格里是公式如A1B1默认load_workbook会加载公式本身。设置data_onlyTrue后openpyxl会尝试读取该公式最后一次计算缓存的结果值。这有两个好处一是避免加载公式引擎的潜在开销二是直接拿到计算结果。但请注意如果文件从未被Excel计算保存过读取到的值可能是None。4.2 pandas 的精准读取策略pandas在读取时提供了更精细的控制参数能极大提升效率# efficient_reading_pandas.py import pandas as pd import time start time.time() # 使用 pandas 的 read_excel并指定参数优化 df pd.read_excel( large_file.xlsx, sheet_name0, # 读取第一个工作表也可以用名称 usecols[0, 9], # **关键**只读取第1列和第10列列索引从0开始 nrows50000, # 如果只需要前N行可以指定避免读全部 dtype{列名1: str, 列名10: float} # 指定列数据类型加速解析并避免类型推断错误 ) end time.time() print(fPandas精准读取耗时{end - start:.2f}秒) print(f读取数据形状{df.shape})usecols参数是性能提升的关键它告诉引擎只处理指定的列避免了无用数据的解析和内存占用。你可以传入列索引列表如[0, 2, 5]、列字母字符串如A:C, E或一个可调用函数进行筛选。我的踩坑经验曾经处理过一个包含大量“合并单元格”和“空行”的报表。用openpyxl默认方式读取速度奇慢。后来发现iter_rows()会遍历每一个定义过的单元格包括那些因为合并而实质为空的单元格。解决方案是结合ws.max_row和ws.max_column先判断有效范围或者用pandas读取时设置skiprows和skipfooter参数跳过表头表尾的非数据行。所以在处理来源复杂的Excel时先用Excel或文本编辑器打开看看文件结构规划好读取范围比直接上代码更重要。5. 进阶实战复杂格式操作与报表生成掌握了高效读写我们来看看更复杂的场景如何操作格式、公式、甚至多个工作表来生成一份专业的报表。5.1 使用 openpyxl 进行精细的格式控制假设我们要生成一个销售汇总表要求标题行居中加粗、数字有千位分隔符、突出显示超标数据。# format_cells.py from openpyxl import Workbook from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, numbers wb Workbook() ws wb.active ws.title 销售报表 # 1. 写入数据 headers [产品, 季度, 销售额, 完成率] data [ [产品A, Q1, 1250000, 0.98], [产品A, Q2, 1420000, 1.05], [产品B, Q1, 890000, 0.92], [产品B, Q2, 1100000, 1.12], ] ws.append(headers) for row in data: ws.append(row) # 2. 设置标题行格式 header_font Font(name微软雅黑, size12, boldTrue, colorFFFFFF) header_fill PatternFill(start_color366092, end_color366092, fill_typesolid) # 蓝色填充 header_alignment Alignment(horizontalcenter, verticalcenter) thin_border Border(leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin)) for cell in ws[1]: # 第一行 cell.font header_font cell.fill header_fill cell.alignment header_alignment cell.border thin_border # 3. 设置数据行格式 # 设置销售额列为货币格式带千位分隔符 for row in ws.iter_rows(min_row2, max_row5, min_col3, max_col3): for cell in row: cell.number_format numbers.FORMAT_NUMBER_COMMA_SEPARATED1 # #,##0.00 # 设置完成率列为百分比格式 for row in ws.iter_rows(min_row2, max_row5, min_col4, max_col4): for cell in row: cell.number_format 0.00% # 如果完成率超过100%标红 if cell.value and cell.value 1.0: cell.font Font(colorFF0000, boldTrue) # 红色加粗 # 4. 调整列宽大约 ws.column_dimensions[A].width 15 ws.column_dimensions[B].width 10 ws.column_dimensions[C].width 15 ws.column_dimensions[D].width 12 wb.save(formatted_report.xlsx)运行后生成的Excel文件将拥有清晰的格式。openpyxl.styles模块提供了丰富的样式类你可以像搭积木一样组合它们。5.2 操作公式在单元格中写入公式Excel会在打开时自动计算。# formulas.py from openpyxl import Workbook wb Workbook() ws wb.active ws[A1] 100 ws[B1] 200 # 在C1写入求和公式 ws[C1] SUM(A1:B1) # 写入一个带条件的公式 ws[A3] 是否达标 ws[B3] 150 ws[C3] IF(B3100, 是, 否) wb.save(with_formulas.xlsx)保存后打开with_formulas.xlsxC1显示300C3显示“是”。注意openpyxl本身不计算公式它只负责写入和读取公式字符串。计算是由Excel或兼容的电子表格软件完成的。如果你用data_onlyTrue模式读取这个文件并且文件被Excel保存过那么ws[C1].value将是计算结果300否则它仍然是公式字符串SUM(A1:B1)。5.3 多工作表操作与模板填充这是自动化报表中最常见的场景有一个设计好的模板文件包含表头、格式、图表框架只需要向里面填充计算好的数据。步骤用openpyxl加载模板文件。找到需要填充数据的起始单元格。将你的数据通常来自pandas DataFrame或列表按行或按列写入。保存为新文件。# fill_template.py from openpyxl import load_workbook import pandas as pd # 假设我们有一个计算好的月度销售数据DataFrame monthly_sales pd.DataFrame({ 月份: [1月, 2月, 3月, 4月], 销售额: [120, 135, 158, 142], 成本: [80, 85, 95, 88], 利润: [40, 50, 63, 54] }) # 1. 加载模板文件 template_path monthly_report_template.xlsx # 假设这个模板在A5单元格开始是数据区域 wb load_workbook(template_path) ws wb[DataSheet] # 假设数据要填到名为‘DataSheet’的工作表 # 2. 确定数据起始位置例如第5行第1列即A5 start_row 5 start_col 1 # 3. 写入表头如果需要 # 如果模板已有表头则跳过这里演示写入DataFrame的列名 for col_idx, column_name in enumerate(monthly_sales.columns, startstart_col): ws.cell(rowstart_row, columncol_idx, valuecolumn_name) # 4. 写入数据行 for df_row_idx, row_data in enumerate(monthly_sales.values, start1): # enumerate从1开始对应Excel行号偏移 excel_row start_row df_row_idx # 表头占了一行所以数据从start_row1开始 for df_col_idx, cell_value in enumerate(row_data, startstart_col): ws.cell(rowexcel_row, columndf_col_idx, valuecell_value) # 5. 保存为新文件避免覆盖模板 output_path monthly_report_filled_202304.xlsx wb.save(output_path) print(f报表已生成{output_path})关键技巧模板中的格式、公式、图表都会保留。你写入的数据会自动继承该单元格的格式除非你主动覆盖。如果数据区域有预设的公式比如合计行SUM(B5:B8)你填入数据后打开文件时公式会自动计算更新。6. 常见问题排查与实用技巧锦囊在实际操作中你肯定会遇到各种稀奇古怪的问题。这里分享几个我踩过的坑和对应的解决方案。6.1 中文乱码与路径问题问题用pandas的to_excel保存后用Excel打开中文字符显示为乱码。原因与解决这通常不是pandas或openpyxl的问题它们内部使用Unicode。乱码更可能出现在文件路径或工作表名称包含非ASCII字符时尤其是在某些旧版本Windows系统或特定环境下。确保你的Python脚本文件本身以UTF-8编码保存。如果问题依然存在可以尝试将字符串显式编码/解码。# 写入时确保字符串是Unicode (Python3中默认就是) ws[A1] 中文内容 # 或者如果从其他来源如GBK编码文件读取了字节串需要先解码 # content_bytes b\xd6\xd0\xce\xc4 # GBK编码的“中文” # ws[A1] content_bytes.decode(gbk)文件路径尽量使用英文和数字命名文件和目录。如果必须用中文确保使用完整的Unicode路径字符串。import os # 安全的方式 path rC:\报表\月度数据.xlsx # 使用原始字符串避免转义 # 或者 path C:/报表/月度数据.xlsx # 使用正斜杠Python和openpyxl都支持 wb.save(path)6.2 处理合并单元格合并单元格是Excel中常见的格式但会给数据处理带来麻烦。用openpyxl读取合并单元格读取合并区域左上角单元格会得到值其他单元格值为None。from openpyxl import load_workbook wb load_workbook(file_with_merged.xlsx) ws wb.active # 获取所有合并单元格范围 merged_ranges ws.merged_cells.ranges print(f合并单元格区域{merged_ranges}) # 遍历合并区域 for merged_range in merged_ranges: print(f合并区域{merged_range} 值{ws[merged_range.min_row][merged_range.min_col-1].value}) # 注意ws[row][col]索引从0开始而min_row/min_col从1开始用pandas读取合并单元格pandas的read_excel默认会看到合并区域左上角的值其他位置为NaN。如果你希望填充这些NaN可以使用fillna方法并指定填充方式如前向填充ffill。df pd.read_excel(file_with_merged.xlsx) # 假设第一列是合并的用上一行的值填充本行的空值 df[列名1] df[列名1].ffill()6.3 数据类型推断错误pandas在读取Excel时会尝试推断每一列的数据类型。有时会把数字组成的字符串如工号“001”推断为整数导致前面的零丢失。解决方案在read_excel中使用dtype参数或converters参数强制指定列类型。# 方法1使用dtype参数适用于整列类型一致 df pd.read_excel(data.xlsx, dtype{工号: str, 电话号码: str}) # 方法2使用converters参数可进行更复杂的转换 def force_string(x): return str(x) if pd.notnull(x) else x df pd.read_excel(data.xlsx, converters{工号: force_string})6.4 处理超链接如果单元格包含超链接openpyxl可以读写它。from openpyxl import Workbook from openpyxl.cell.cell import Hyperlink wb Workbook() ws wb.active # 创建超链接单元格 cell ws[A1] cell.value 访问Openpyxl官网 cell.hyperlink Hyperlink(targethttps://openpyxl.readthedocs.io/, displayOpenpyxl Docs) # 通常Excel会为超链接应用蓝色带下划线的样式我们可以手动添加 from openpyxl.styles import Font cell.font Font(color0563C1, underlinesingle) wb.save(with_hyperlink.xlsx)读取时通过cell.hyperlink.target获取链接地址。6.5 一个综合案例批量处理文件夹下的Excel文件最后分享一个非常实用的脚本框架批量读取某个文件夹下所有Excel文件提取特定信息汇总到一个新的Excel中。# batch_process_excel.py import os import pandas as pd from openpyxl import load_workbook from pathlib import Path def process_single_file(file_path, sheet_name, data_range): 处理单个Excel文件返回需要的数据 try: # 使用pandas快速读取指定区域的数据 df pd.read_excel(file_path, sheet_namesheet_name, usecolsdata_range, nrows100) # 示例 # 这里进行你的数据处理逻辑例如计算总和、平均值等 total_sales df[销售额].sum() if 销售额 in df.columns else 0 avg_cost df[成本].mean() if 成本 in df.columns else 0 return { 文件名: os.path.basename(file_path), 总销售额: total_sales, 平均成本: avg_cost, 数据行数: len(df) } except Exception as e: print(f处理文件 {file_path} 时出错{e}) return None def main(): input_folder Path(./月度数据/) # 你的Excel文件所在文件夹 output_file 月度汇总.xlsx all_results [] # 遍历文件夹下所有.xlsx文件 for excel_file in input_folder.glob(*.xlsx): print(f正在处理{excel_file.name}) # 假设每个文件的结构相同数据在‘Sheet1’的A到D列 result process_single_file(excel_file, sheet_nameSheet1, data_rangeA:D) if result: all_results.append(result) # 将汇总结果转换为DataFrame并保存 if all_results: summary_df pd.DataFrame(all_results) # 使用openpyxl引擎以便后续可能添加格式 with pd.ExcelWriter(output_file, engineopenpyxl) as writer: summary_df.to_excel(writer, indexFalse, sheet_name汇总) # 可以在这里获取workbook和worksheet对象进行额外的格式设置 workbook writer.book worksheet writer.sheets[汇总] # 例如设置列宽 worksheet.column_dimensions[A].width 30 worksheet.column_dimensions[B].width 15 worksheet.column_dimensions[C].width 15 print(f汇总完成结果已保存至{output_file}) else: print(未找到可处理的数据。) if __name__ __main__: main()这个脚本展示了如何将pandas的高效数据读取与openpyxl的格式控制能力通过pd.ExcelWriter结合起来完成一个典型的自动化任务。你可以根据自己的需求修改process_single_file函数内的逻辑。Python处理Excel的深度和灵活性远超一篇教程所能涵盖但掌握了openpyxl和pandas的核心用法、性能优化技巧以及组合拳策略你就能应对90%以上的自动化场景。记住在开始编码前花点时间分析文件结构、明确数据流向、选择正确的工具往往比埋头写代码更能提升效率和减少后期的调试时间。当你的脚本能稳定、快速地将你从重复的复制粘贴中解放出来时那种成就感就是学习这些技术最好的回报。