干数据分析这些年Python和Excel是我每天打交道最多的两样东西。经常有同行问我Python处理Excel到底该学哪个库这种问题我回答过很多次但每次都会因人而异给不同答案。处理Excel这件事看着简单实际上需求跨度极大——从读几百行销售数据做汇总到批量生成几十个格式统一的报表再到直接操纵Excel界面做自动化操作完全不是一个工具能覆盖的。今天就把我实战中真正用过的5种常用方式一次性讲清楚每种方式的适用场景、核心代码、踩过的坑都放出来。1. 工具选型不同场景下选哪种方式的判断逻辑1.1 五种方式各自的能力边界网上搜“Python处理Excel”跳出来的教程基本都在推pandas好像会一个pandas就能打天下。但真正做过项目就知道pandas解决的是“表格数据加工”这个环节而Excel处理还包含格式保留、样式控制、工作簿合并、调用Excel自身功能等一堆需求。我常用的这5种方式各有各的主场pandas最主流的表格数据读取、清洗、聚合、分析方案适合把Excel当成数据源来处理。openpyxl能直接读写xlsx文件并控制单元格样式、列宽、合并区域适合生成格式精致的交付报表。xlrd / xlwt / xlutils老牌三件套读xls、写xls、改xls适合和老旧系统对接。csv标准库当数据量大、格式要求低时先转成CSV再处理是最快路径也用不着装任何第三方库。pywin32 / xlwings调用本机Excel程序的COM接口能操作Excel的界面能力比如执行VBA宏、刷新透视表、弹窗交互。这5种不是互相替代的关系而是一条链路里不同环节的工具。比如一套数据从旧系统导出可能是xls格式我需要用xlrd读出来、用pandas清洗、再通过openpyxl输出成带格式的xlsx报表最后用xlwings打开检查一下视觉效果。每个工具解决一个环节的问题。1.2 选型判断矩阵与我的推荐组合新手最容易犯的错是“只学一个工具然后硬套所有场景”。用openpyxl去跑几万行的数据聚合速度慢到怀疑人生用pandas去改单元格背景色费半天劲还不如直接操作Excel。与其背API不如先建立一张选型判断表需求类型推荐方案理由大量数据读取、筛选、分组统计pandas底层是向量化运算性能强、语法简洁生成带样式、图表的Excel报表openpyxl对单元格样式、列宽、合并区域控制最细读写老式xls文件2003版xlrd / xlwt / xlutils专为xls设计兼容老系统数据量大、不需要格式、只做中转csv标准库无第三方依赖读写速度最快调用Excel程序自身能力xlwings / pywin32可以执行宏、操作Excel窗口、使用插件功能我本人目前的固定组合是pandas加上openpyxl打底遇到老格式文件补一个xlrd/xlwt数据大到内存吃紧就切csv中转只有必须用Excel原生功能时才上xlwings。这套组合跑过财务对账、运营报表、库存分析等几十个真实任务基本覆盖了日常能遇到的绝大多数情况后面每个部分我都会给出可直接落地的代码。2. 方式一pandas——最全能的通用数据加工方案2.1 读写Excel的基础操作pandas做Excel处理的入口非常简单最常用的就是read_excel和to_excel。但这里有几个参数值得认真掌握用好了能省掉大量代码。import pandas as pd # 读取单个sheet df pd.read_excel(销售明细.xlsx, sheet_name上海区) # 一次读取多个sheet返回一个字典 sheets pd.read_excel(销售明细.xlsx, sheet_name[上海区, 北京区]) # 读取全部sheet all_sheets pd.read_excel(销售明细.xlsx, sheet_nameNone)实际工作中Excel表第一行经常不是表头可能是大标题、备注甚至空行。比如我接过一份门店POS导出的数据前3行是门店信息和导出时间第4行才是真正的列名第5行开始是数据。这时候就用skiprows跳过去。df pd.read_excel(门店销售.xlsx, sheet_nameSheet1, skiprows3)如果文件里只有那几列需要可以用usecols减少加载量尤其是列特别多、每列数据量还很大的时候这个参数能明显缩短读取时间。df pd.read_excel(销售明细.xlsx, usecols[订单号, 客户名, 销售额])写入方向也一样简洁。一个DataFrame要落成Excel核心就是一个to_excel。df.to_excel(结果.xlsx, sheet_name汇总, indexFalse)注意我刻意写了indexFalse。如果不写这个参数pandas默认会把行号也写进Excel第一列这通常是别人不需要的脏数据。带多个DataFrame输出到一个文件时需要借助ExcelWriterwith pd.ExcelWriter(多表结果.xlsx) as writer: df_sales.to_excel(writer, sheet_name销售, indexFalse) df_cost.to_excel(writer, sheet_name成本, indexFalse)2.2 数据清洗与字段处理实战Excel文件读进来之后数据处理才是大头。这块我用一个真实场景举例一张从ERP导出的订单表里面有重复行、空值、日期列乱成三种格式、销售金额还是文本带横杠。先造一份类似的示例数据import pandas as pd # 模拟一份ERP导出的脏数据 df pd.DataFrame({ 订单号: [A001, A002, A002, A003, None], 下单日期: [2024/1/5, 2024-01-08, 2024/1/12, 20240115, 2024-02-03], 客户: [张三, 李四, 李四, 王五, 赵六], 金额: [1,200, 3,400, 3,400, 0, -] })处理脏数据的常规动作是先看整体结构再逐列处理。# 看数据概况 print(df.info()) print(df.describe())日期列是最乱的部分。不同系统导出的日期格式五花八门2024/1/5、2024-01-08、20240115这种实际是三种写法。我的经验是先把所有内容统一转成字符串再用pd.to_datetime的format参数挨个清洗。# 统一处理日期列 df[下单日期] df[下单日期].astype(str) # 识别三种常见格式 df[下单日期] df[下单日期].str.replace(/, -, regexFalse) # 处理“20240115”这种紧凑格式 df[下单日期] df[下单日期].apply( lambda x: f{x[:4]}-{x[4:6]}-{x[6:]} if x.isdigit() else x ) df[下单日期] pd.to_datetime(df[下单日期])金额列也麻烦。ERP导出的金额常常带千分位逗号缺失值则可能显示成横杠。处理逻辑是先把横杠替换成NaN再去掉逗号最后转成数值类型。df[金额] df[金额].replace(-, pd.NA) df[金额] df[金额].str.replace(,, , regexFalse) df[金额] pd.to_numeric(df[金额], errorscoerce)订单号列有重复和空值需要去重和填充。df df.drop_duplicates(subset[订单号]) df[订单号] df[订单号].fillna(未知)这几段代码汇总到一起就是一份能直接套用的清洗模板。我在很多项目里都是这个套路先info看类型再逐列处理格式问题最后去重去空。Excel里人工做这些操作可能要半个小时脚本跑下来不到一秒。2.3 pandas实操要点与常见坑用pandas处理Excel有几个坑我反复踩过写出来提醒一下。第一个是公式值的问题。pd.read_excel读到的单元格内容如果那个格子是Excel公式比如SUM(C2:C10)pandas读取时拿到的不是计算结果而是公式字符串。这其实是openpyxl引擎的读取方式导致的。解决办法是先用Excel打开文件计算并保存一遍或者干脆使用openpyxl的data_onlyTrue去读缓存结果再把数据交给pandas。第二个是数据类型被自动推断的问题。比如订单号明明是001Excel文件里也显示001但pandas读进来可能变成数字1导致关联时对不上。处理办法是在read_excel时指定dtype参数把订单号强制按字符串读取。df pd.read_excel(订单.xlsx, dtype{订单号: str})第三个是内存问题。一个几十MB的Excelpandas读进来可能占到几百MB内存因为Excel本身就是压缩格式解析成DataFrame后会膨胀。遇到大文件我的建议是能转CSV就转CSV或者分sheet读取尽量避免一次性装载全部数据。3. 方式二openpyxl——需要保留样式的精细写入方案3.1 读已有工作簿的关键细节openpyxl和pandas最大的不同在于它操作的是Excel文件本身精确到单元格、样式、合并区域这些颗粒度。先看一个最常见的读取需求从已有xlsx里读取数据同时还要保留原文件的样式。这里有一个关键参数要特别记住data_only。from openpyxl import load_workbook # 不写data_only时拿到的是公式文本 wb load_workbook(统计.xlsx) ws wb[Sheet1] print(ws[B2].value) # 可能是 SUM(B3:B10) # 写data_onlyTrue拿到的是Excel保存时的计算结果 wb2 load_workbook(统计.xlsx, data_onlyTrue) ws2 wb2[Sheet1] print(ws2[B2].value) # 可能是 45600这个坑非常隐蔽。我接过一个同事的需求他想读取一个模板文件里的汇总数字结果脚本明明没报错数字却读不到最终发现单元格里是公式而data_onlyFalse时openpyxl返回的就是公式文本。另外data_onlyTrue读到的计算值本质是Excel打开文件后缓存的结果。如果文件生成后从未被Excel或WPS打开过某些缓存值可能是None。最稳妥的办法是读文件前先确保它被一个能计算公式的程序保存过。3.2 批量写入与样式控制的完整示例openpyxl真正强的地方是写报表。我需要生成月度销售报表时经常要同时设置标题合并、表头底色、数据列宽、数字格式、冻结窗格。一个完整示例长这样from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter wb Workbook() ws wb.active ws.title 月度销售 # 写入标题并合并单元格 ws.merge_cells(A1:D1) ws[A1] 2025年1月销售汇总 ws[A1].font Font(name微软雅黑, size14, boldTrue) ws[A1].alignment Alignment(horizontalcenter, verticalcenter) # 表头样式 headers [区域, 销售额, 目标额, 完成率] ws.append(headers) header_font Font(name微软雅黑, boldTrue, size11) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) thin_border Border( leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin), ) for cell in ws[2]: cell.font header_font cell.fill header_fill cell.alignment Alignment(horizontalcenter, verticalcenter) cell.border thin_border # 写入数据 data [ [华东, 520000, 500000, 104%], [华北, 430000, 450000, 96%], [华南, 610000, 580000, 105%], ] for row in data: ws.append(row) # 所有数据行加边框 for row in ws.iter_rows(min_row2, max_rowws.max_row, min_col1, max_col4): for cell in row: cell.border thin_border # 设置列宽 widths [12, 14, 14, 12] for i, w in enumerate(widths, start1): ws.column_dimensions[get_column_letter(i)].width w # 设置行高与冻结窗格 ws.row_dimensions[1].height 30 ws.freeze_panes A3 wb.save(月度销售_报表.xlsx)这段代码生成的文件打开之后视觉效果和手工做的报表基本没差。字体、底色、边框、对齐这些属性都是Font、PatternFill、Border、Alignment这几个类控制的使用前先创建样式对象再赋值给单元格即可。3.3 适合哪些场景不适合哪些场景openpyxl适合的场景非常清晰生成交付给领导、客户看的正式报表批量套模板填数需要精确控制每行每列样式的任务。我接过一个需求要把几十个销售人员的月度KPI填进统一的模板每个人员一个sheet样式完全一致。用openpyxl循环创建sheet、赋值、设置样式几分钟就搞定手工复制粘贴得做半天。但不适合的场景也要说清楚。openpyxl虽然能读数据但数据量一大性能就明显下降。我测过读一个2万行、10列的xlsxload_workbook需要好几秒而pandas读取差不多是零点几秒的级别。所以做数据分析聚合时别用它替代pandas。还有一个容易忽略的限制openpyxl只能读写xlsx格式不能处理xls。碰到老式的xls文件我通常先用工具转成xlsx或者直接用下一部分讲的xlrd/xlwt三件套。4. 方式三xlrd / xlwt / xlutils——老牌方案里的取舍4.1 三件套各自承担的职责在很多老公司的内部系统里导出的数据仍然是xls格式。这类文件用openpyxl是读不了的pandas底层在读取xls时也会依赖xlrd。所以xlrd/xlwt/xlutils这套老方案虽然看起来不够时髦我依然认为有必要掌握。三个库的分工是xlrd读取xls文件能拿到单元格的值、日期、格式信息。xlwt创建xls文件并向单元格写入数据。xlutils在已有xls文件基础上做修改因为xlwt只能新建文件不能直接改已有内容。一个典型的读取示例import xlrd workbook xlrd.open_workbook(老数据.xls) sheet workbook.sheet_by_index(0) print(sheet.nrows, sheet.ncols) # 总行数、总列数 for row in range(sheet.nrows): print(sheet.row_values(row))写入新xls的示例import xlwt wb xlwt.Workbook() ws wb.add_sheet(Sheet1) ws.write(0, 0, 省份) ws.write(0, 1, 销量) ws.write(1, 0, 浙江) ws.write(1, 1, 1200) # 设置列宽 ws.col(0).width 256 * 20 wb.save(输出.xls)这里col(0).width的单位是1/256个字符宽度所以256 * 20表示20个字符宽。这个细节当年困扰了我一会儿写法很隐蔽但设置列宽又经常要用。4.2 典型批量报表改造案例真正能发挥xlutils价值的是“改旧文件”的场景。举个例子分公司发来一个固定的费用明细表表头、格式、已有公式都不能动我只想在每个月的汇总行旁边填入新的金额。实现方案是先用xlrd以formatting_infoTrue打开原文件再交给xlutils.copy复制一份新的工作簿最后在新工作簿上修改单元格。import xlrd from xlutils.copy import copy rb xlrd.open_workbook(费用表.xls, formatting_infoTrue) wb copy(rb) ws wb.get_sheet(0) # 假设第5行第3列是当月费用 ws.write(5, 2, 35999.0) wb.save(费用表_更新.xls)注意xlrd.open_workbook里的formatting_infoTrue必须开启否则复制出来的工作簿会丢失原有格式。这个参数在读取xlsx时并不支持所以这个方案仅针对xls文件。代码本身不复杂但它的价值在于不用重新生成整个文件原有样式、宏、公式都保留只是改动指定格子。这在处理由第三方系统生成、不允许大幅改动的模板文件时非常好用。4.3 使用中的注意事项这套三件套的问题也很明显我提几点实际经验第一xlwt只能写xls写xlsx会直接报错。如果你面对的交付物必须是xlsx建议绕道openpyxl。第二xlwt有行数上限最多65536行列数上限是256列。听起来数字不小但我处理过一份上百万行的明细数据时这个限制就成了硬伤。这时候只能拆分成多个sheet或者改用CSV。第三xlrd的新版本有个坑从2.0版本开始xlrd只支持xls不再支持xlsx。这意味着如果你用pip install xlrd装的是最新版想让它直接读xlsx会报xlsx file format not supported。解决方法是装旧版本或者读xlsx时老老实实用openpyxl。我现在的习惯是只有处理老系统输出的xls才用xlrd其他地方尽量统一用pandas和openpyxl减少依赖冲突。5. 方式四csv标准库——轻量场景下的最高效搬运5.1 为什么有些Excel处理直接用csv更省事你可能会问已经有pandas了为什么还提csv原因很简单很多Excel处理场景里真正的瓶颈不是计算而是文件IO和格式兼容。Excel文件本质是压缩包读取时要解压、解析XML天然比纯文本的csv慢。一个10MB的csv读取速度可能是同样数据量xlsx的10倍以上。如果只是做简单的数据筛选、转存、对接完全没必要动用Excel格式。另一个场景是上下游系统对接。很多数据平台导出功能Excel直接导出到几万行就开始卡但CSV可以轻松导出几十万行。所以我的习惯是在大数据量场景下先让数据以CSV格式落地再用pandas等工具处理最后需要给业务方看时才转成带格式的Excel。import csv with open(大文件.csv, r, encodingutf-8) as f: reader csv.reader(f) header next(reader) for row in reader: # 逐行处理 pass5.2 编码问题的正确处理方式用csv最头疼的永远是编码。同一个CSV文件在Windows上用Excel打开正常用Python默认方式读就可能乱码在Mac上正常拷到Windows又变乱码。原因在于Excel在不同语言环境下默认编码不一样。国内Windows环境的Excel打开csv时优先按ANSI编码解析而ANSI在国内环境就是GBK。如果Python用UTF-8写入csvExcel直接双击打开就会看到一堆乱码。解决办法是写文件时用带BOM的UTF-8编码也就是utf-8-sigimport csv data [ [地区, 销量], [华东, 1200], [华北, 900], ] with open(销售_out.csv, w, newline, encodingutf-8-sig) as f: writer csv.writer(f) writer.writerows(data)utf-8-sig会在文件开头加一个BOM头Excel识别到BOM就知道这是UTF-8编码不会按GBK去解析。这个细节我记了很多年每次写CSV都默认带utf-8-sig再也没有乱码反馈。读取时也要注意编码判断。我常用的方式是先试UTF-8失败再回退GBKdef read_csv_flexible(path): for encoding in [utf-8, gbk, gb18030]: try: return pd.read_csv(path, encodingencoding) except UnicodeDecodeError: continue raise ValueError(无法识别的编码)5.3 从Excel导出csv再加工的完整脚本把一个多sheet的xlsx转成多个csv再逐个读取处理是我在大数据量场景下经常用的操作模板。import pandas as pd from pathlib import Path # 第一步把xlsx的每个sheet导出为csv src_path Path(超大报表.xlsx) out_dir Path(csv_output) out_dir.mkdir(exist_okTrue) sheets pd.read_excel(src_path, sheet_nameNone) for sheet_name, df in sheets.items(): safe_name sheet_name.replace( , _).replace(/, _) df.to_csv(out_dir / f{safe_name}.csv, indexFalse, encodingutf-8-sig) # 第二步按需读取单个csv并处理 df_part pd.read_csv(out_dir / 销售明细.csv, encodingutf-8-sig)这套流程的最大优势是中间产物可见。xlsx转csv之后可以先用文本编辑器确认编码和字段再跑后续逻辑。出问题时排查路径也比直接内存操作清晰。6. 方式五pywin32/xlwings——调用Excel自身能力的自动化路径6.1 为什么需要调用Excel COM接口前面几种方式都是在“文件”层面操作Excel但有些场景绕不开Excel程序本身。比如工作簿里有透视表需要刷新、有VBA宏需要执行、有外部数据连接需要更新甚至有些插件只提供Excel界面操作入口。这时候就只能把Excel程序启动起来像人一样通过界面去操作它。pywin32和xlwings就是干这个的。它们在Windows上通过COM接口控制Excel应用程序能做到和手工操作几乎一致的效果。我举一个具体例子某公司的月报模板里有一个透视表数据源是另一个关闭的xlsx。每次拿到新数据都得手动打开Excel、点击“刷新全部”再另存为新文件。用pywin32就能自动完成这整套动作。import win32com.client as win32 excel win32.Dispatch(Excel.Application) excel.Visible False # 不显示Excel窗口 excel.DisplayAlerts False # 不弹出保存提示 wb excel.Workbooks.Open(rC:\工作目录\月报模板.xlsx) wb.RefreshAll() # 刷新所有数据连接和透视表 wb.SaveAs(rC:\工作目录\月报_已更新.xlsx) wb.Close() excel.Quit()这段代码里的Visible False很关键适用于服务器或后台执行。如果调试时想观察操作过程可以把它改成TrueExcel窗口就会显示出来。6.2 一个批量格式调整的实战案例pywin32做批量格式化也非常顺手。我之前有个需求把工作簿里所有sheet的A1单元格都加上黄色底纹列宽调成18同时在第一行前面插入两行说明。手工做要重复几十次用脚本一次搞定。import win32com.client as win32 excel win32.Dispatch(Excel.Application) excel.Visible False excel.DisplayAlerts False wb excel.Workbooks.Open(rC:\工作目录\多表统计.xlsx) for ws in wb.Worksheets: # 插入两行 ws.Rows(1).Insert() ws.Rows(1).Insert() # 写说明 ws.Cells(1, 1).Value 本表由系统自动生成 ws.Cells(2, 1).Value 生成日期 str(excel.WorksheetFunction.Text(excel.Now(), yyyy-mm-dd)) # 设置列宽 ws.Columns(1).ColumnWidth 18 # 给A1加黄色底纹 ws.Cells(1, 1).Interior.Color 65535 # 黄色 wb.Save() wb.Close() excel.Quit()注意Interior.Color给的是BGR格式的整数值65535是黄色255是红色65280是绿色。想用RGB颜色的话可以自己写个换算函数red green * 256 blue * 65536这种方式。6.3 xlwings与pywin32的选择建议pywin32是最底层的COM封装功能最强但写起来比较啰嗦。如果你不太想记那么多COM对象的属性名可以用xlwings。xlwings的语法更接近人的阅读习惯。同样一个“打开工作簿、改A1、保存”的操作pywin32要写好几行xlwings可以这样import xlwings as xw app xw.App(visibleFalse) wb app.books.open(rC:\工作目录\测试.xlsx) ws wb.sheets[Sheet1] ws.range(A1).value 你好 wb.save() wb.close() app.quit()xlwings还有一个杀手级功能可以从Excel中调用Python函数作为自定义公式UDF不过这个功能在公司内部分发时配置比较麻烦我目前用得不多。我的选型建议是脚本自己用、追求可控选pywin32要交付给别人维护或者你刚开始接触COM对象选xlwings更省心。还有一个重要提醒这两种方式都要求本机装有完整的Excel应用Linux服务器上用不了。生产环境如果跑在Linux上需要操作Excel文件的场景还是老老实实走前面的pandas/openpyxl方案。7. 新手必看环境安装与常见问题排查7.1 Python环境与依赖库的快速安装很多人卡在第一步的Python安装上。国内用户建议直接从官网下载安装包安装在Windows上时一定要勾选“Add Python to PATH”这一步不勾后面在命令行执行python会提示找不到命令。如果已经安装过检查PATH的方法是打开命令行输入python --version能输出版本号就说明环境正常。依赖库的安装就一条命令pip install pandas openpyxl xlrd xlwt xlutils xlwings其中xlwings在macOS和Windows都能用但pywin32只在Windows上有意义。如果只需要最基本的Excel读写装pandas和openpyxl两个库就够了。我自己写代码时用的编辑器是VS Code配置Python环境时注意右下角要选中正确的解释器不然pip install装完的包在另一个解释器里根本导入不了。这个问题非常多见尤其在电脑上装过多个Python版本的时候。7.2 常见报错与解决方法我在实战中遇到的报错几乎都集中在下面这几个点。整理成表格方便你排查报错或现象原因解决办法ModuleNotFoundError: No module named pandas没装库或解释器选错执行pip install pandas检查VS Code解释器Excel file format cant be determined文件不是真正的xlsx可能是xls或伪后缀文件用file命令看真实格式或改用xlrd读取xlrd.biffh.XLRDError: Excel xlsx file not supportedxlrd 2.0以上不支持xlsx读xlsx改用openpyxl读xls保留xlrdKeyError: Sheet1文件中没有叫Sheet1的工作表先打印wb.sheetnames确认sheet名PermissionError: [Errno 13] Permission denied目标Excel文件正被WPS/Excel打开占用关闭打开的文件或换文件名保存读取单元格拿到SUM(...)openpyxl默认拿公式文本load_workbook(..., data_onlyTrue)Excel打开CSV乱码编码不对缺少BOM写入时用encodingutf-8-sig双击Excel出现“此操作只对当前安装的产品有效”某类COM组件或插件注册异常修复Office安装或重装对应Excel插件这些坑我一个一个都踩过最频繁的是文件占用和sheet名写错。我的习惯是脚本里读取前先打印一下可用的sheet名再做后续操作能省掉很多调试时间。7.3 我个人的工作流建议最后分享一套我常用的工作流新项目接到手基本按这个顺序走先判断数据量级。小文件几千行直接pd.read_excel硬读大文件先转csv或分sheet处理。再判断输出要求。如果只要数据结果写csv或者用to_excel就够了要交付正式报表用openpyxl把样式一并生成。最后检查有没有必须调用Excel功能的步骤。有透视表刷新或宏执行再用xlwings补上。整个过程可以用一个Python脚本串起来也可以拆成多个脚本按需运行。我的建议是不要追求一步到位先让每个环节跑通再串成完整流程。这个方式适合处理从几万行到几十万行的数据覆盖面广、坑踩得少也是我向团队新人推荐的学习路径。