如果你的第一个Python数据处理项目是“把Excel表格读出来算一算再写回去”那大概率会同时撞上pandas和openpyxl这两块牌子。pandas擅长把数据装进内存后做各种变换、筛选、聚合openpyxl则擅长把结果按你想要的格式写回Excel文件。这篇文章我就以这两个库为主线从安装踩坑讲起经过读写Excel、数据类型转换、格式美化一直做到一个可以直接套用的批量报表小案例。适合刚入门Python、想用脚本替代手工处理表格的朋友也适合已经在用但想把细节补全的进阶选手。1. 两个库到底怎么分工选型思路与环境准备1.1 pandas和openpyxl的定位差异很多人第一次接触这两个库时都会犯迷糊都是处理Excel为什么还要装两个其实它们的定位完全不同我习惯这样理解pandas是“数据加工车间”它只关心数据怎么算、怎么分组、怎么汇总不太关心单元格长什么样openpyxl是“排版师傅”它直接操作xlsx文件里的单元格、字体、边框、合并区域特别在意表格长得齐不整齐。两者最典型的协作关系是先用pandas把数据处理完再交给openpyxl做精细化排版。反过来如果只是读数据做统计用pandas就够了根本不需要手动逐格取数如果只是改个表头颜色、调整列宽用openpyxl就行没必要引入pandas的重量级结构。让我用一张表把它们的差异说清楚对比维度pandasopenpyxl核心数据结构DataFrame、SeriesWorkbook、Worksheet、Cell强项数据清洗、分组聚合、统计分析单元格样式、合并、公式、图表读取xlsx方式依赖引擎通常用openpyxl原生支持xlsx是否支持xls老格式pandas可用xlrd引擎读取不支持只能xlsx/xlsm写Excel时的格式控制较弱只能控制是否带索引等强可精确到每个单元格适用场景数据分析、结果计算报表生成、模板填充、格式美化还有个容易被忽略的点pandas读xlsx时引擎参数engineopenpyxl就是直接调用openpyxl在底层解析文件。也就是说你日常用pd.read_excel()时其实背后已经间接用到了openpyxl。只是pandas把细节封装掉了你看不到而已。1.2 安装与离线安装的实操方案在线安装很简单一条命令搞定pip install pandas openpyxl如果你用的是Anaconda也可以conda install pandas openpyxl但很多朋友会碰到一个在热搜里反复出现的经典问题pycharm 下载pandas包 connected time out。这类超时大多是网络连接不稳定导致的最简单的办法是切换国内镜像源实测我用清华源基本秒下pip install pandas openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple如果是公司内网、完全离线的环境那就走离线安装路线。离线安装的核心思路是在一台联网机器上把包以及依赖包全部下载成whl文件拷贝到目标机器后再本地安装。以openpyxl为例它依赖et_xmlfilepandas依赖numpy、python-dateutil、pytz等。稳妥的做法是直接用pip下载全量依赖pip download pandas openpyxl -d ./packages -i https://pypi.tuna.tsinghua.edu.cn/simple把packages文件夹整个拷到离线机器上然后执行pip install --no-index --find-links./packages pandas openpyxl这样批量安装时不会因为缺一个依赖就中断。装完后在Python里验证一下import pandas as pd import openpyxl print(pd.__version__) print(openpyxl.__version__)能正常打印版本号环境就算通了。1.3 什么时候选哪个我的建议决策路径我自己在实际项目里总结了一套选择逻辑遇到问题可以按这个顺序判断任务只是“读取数据、算个平均值、做个透视表、导出结果”直接用pandas一条链走完别让openpyxl插手。任务是要生成一张对外交付的报表要求有标题行、合并单元格、指定字体颜色、列宽行高、甚至图表那就pandas算完数据后交给openpyxl排版。任务是修改一个已有模板比如把某个区域的数值填进去、把标题加粗、把某列标红这种精确控制场景直接上openpyxl省得pandas读进来写出去把模板格式全冲掉。任务是处理超大文件比如几万行甚至几十万行的数据读取时优先考虑pandas的性能优化技巧后面会讲输出时如果不需要复杂格式也尽量用pandas直接落盘。不要盲目迷信“pandas什么都能干”。它可以做很多事但不是所有事都做得优雅尤其是格式这块。两个库配合使用才是处理Excel最顺手的姿势。2. pandas核心操作数据读写与类型转换2.1 DataFrame和Series的结构创建既然要用pandas脑子里必须先建立两个数据结构的概念Series是一列带标签的数据DataFrame是多列的表格。可以简单理解成Excel里的一列和整个工作表。创建DataFrame最直观的方式是传入字典键是列名值是列表。比如import pandas as pd data { 姓名: [张三, 李四, 王五], 部门: [销售部, 市场部, 技术部], 销售额: [12800, 9800, 15300], 入职日期: [2023-01-15, 2022-06-01, 2024-03-20] } df pd.DataFrame(data) print(df)输出的表格会自动生成行索引从0开始。如果你希望某列作为索引可以用set_indexdf df.set_index(姓名)平时创建Series用得少但在处理单列数据、或者给DataFrame添加新列时会出现。比如s pd.Series([100, 200, 300], name绩效) df[绩效] s为什么我建议先搞懂数据结构因为后面所有操作——筛选、分组、透视、合并——全部建立在DataFrame的“列名索引”这套逻辑上。你越理解这套结构读pandas代码就越轻松。2.2 读写Excel文件的关键参数读写Excel是pandas最常用的功能之一但很多人只停留在pd.read_excel()和df.to_excel()这两个裸调用上遇到真实场景就露馅。这里我把常用参数逐个讲透。读取时重点参数如下df pd.read_excel( io销售明细.xlsx, # 文件路径 sheet_nameSheet1, # 工作表名也可以是索引0、1 header0, # 第几行作为列名默认0即第一行 usecolsA:D, # 只读A到D列也可以传[姓名,部门] dtype{销售额: float}, # 强制指定列类型 skiprows2, # 跳过前2行 nrows100, # 只读100行 engineopenpyxl # 指定引擎 )usecols是提升读取效率的利器几百列的大表你只需要其中5列那就不要全部读进内存。dtype特别适合处理“号码”“工号”这类容易被当成数值的列比如手机号用dtype{手机号: str}可以避免科学计数法。写入时重点参数如下df.to_excel( 汇总输出.xlsx, sheet_name月度汇总, indexFalse, # 不写出行索引 columns[部门, 销售额], # 只导出指定列 engineopenpyxl )indexFalse几乎是必写的不然你会看到第一列多出0、1、2、3这种行号非常丑。columns参数可以控制输出列的顺序和范围适合从宽表里抽需要的内容。还有一个多工作表写入的技巧需要借助ExcelWriter来实现with pd.ExcelWriter(多表输出.xlsx, engineopenpyxl) as writer: df1.to_excel(writer, sheet_name销售汇总, indexFalse) df2.to_excel(writer, sheet_name离职统计, indexFalse)这个写法比循环里反复to_excel安全得多不会出现只保留最后一个sheet的问题。2.3 数据类型转换解决Excel里“数字变文本”的老大难问题热词榜里有“pandas 数据类型转换”这个确实是高频痛点。最常见的烂摊子是Excel里某列看起来是数字但用pandas读进来发现是字符串一排序就乱或者反过来身份证号被读成了科学计数法。遇到这种情况核心思路是读取时就用dtype兜住实在读进来了就事后转换。事后转换主要靠三个方法# 强制转成整型 df[数量] df[数量].astype(int) # 转成数值类型遇到无法转换的置为NaN比astype安全 df[金额] pd.to_numeric(df[金额], errorscoerce) # 转成日期时间 df[日期] pd.to_datetime(df[日期], format%Y-%m-%d)pd.to_numeric的errorscoerce参数很实用。数据里混着“N/A”“-”这类垃圾值astype会直接报错而to_numeric会把这些值替换成NaN方便后面统一清洗。格式化为字符串也是常见操作尤其在拼接编号或日期时df[日期字符串] df[日期].dt.strftime(%Y%m%d)类型转换有个原则尽量在数据刚进来时就把类型定对。pandas有一个设计习惯一列都是数字时它会自动推断为float或int空值多时会变成float因为NaN在pandas里是浮点值。理解了这个你就不会抱怨“为什么明明都是整数读出来却带小数点”。2.4 缺失值和重复值清洗真实数据永远比你想象的脏。常见几板斧# 查看缺失值统计 print(df.isnull().sum()) # 删除含缺失值的行 df_clean df.dropna() # 填充缺失值 df[备注] df[备注].fillna(无) # 删除重复行 df_unique df.drop_duplicates(subset[姓名, 部门], keepfirst)这里有两个细节值得说。drop_duplicates的subset指定判断重复的列不传就按整行判断。keepfirst表示保留第一条记录如果你希望保留最后一条就用keeplast。还有字符串清洗df[姓名] df[姓名].str.strip() # 去掉首尾空格 df[部门] df[部门].str.replace(销售 , 销售) # 替换异常空格Excel表格里肉眼看不到的前后空格往往是分组聚合时结果对不上的元凶我踩过太多次这个坑了。3. openpyxl核心操作从单元格到报表美化3.1 加载工作簿与单元格定位openpyxl的操作对象是工作簿Workbook和工作表Worksheet。加载已有文件用load_workbook新建文件用Workbook()。from openpyxl import load_workbook, Workbook # 读取已有文件 wb load_workbook(模板.xlsx) ws wb[Sheet1] # 新建文件 wb_new Workbook() ws_new wb_new.active单元格定位有两种方式# 方式一坐标访问 cell_a1 ws[A1] cell_c3 ws[C3] # 方式二行列号访问适合循环 cell_a1 ws.cell(row1, column1)写入数据同样简单ws[A1] 标题 ws.cell(row2, column1, value张三)获取工作表范围信息也是高频操作print(ws.max_row) # 最大行号 print(ws.max_column) # 最大列号max_row和max_column在自动遍历时特别好用。比如你想把某列所有非空单元格加粗就可以写一个for i in range(1, ws.max_row 1)的循环。读取批量数据时建议用iter_rowsfor row in ws.iter_rows(min_row1, max_row5, min_col1, max_col3, values_onlyTrue): print(row)values_onlyTrue直接返回元组数据比手动一个个cell.value快代码也干净。3.2 样式、合并单元格与行列调整为什么不用pandas直接导出结果因为默认导出实在太素了。要想做出能交付的报表格式美化必须上openpyxl。最基本的字体和填充色设置from openpyxl.styles import Font, PatternFill, Alignment, Border, Side fill PatternFill(start_colorFFC000, end_colorFFC000, fill_typesolid) bold_font Font(name微软雅黑, size11, boldTrue) center_align Alignment(horizontalcenter, verticalcenter) ws[A1].font bold_font ws[A1].fill fill ws[A1].alignment center_align设置边框稍微绕一点因为边框有上下左右四个方向thin_border Border( leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin) ) ws[B2].border thin_border合并单元格在报表标题里几乎必用ws.merge_cells(A1:F1) # 合并A1到F1 ws[A1] 2025年5月销售汇总表合并后取消合并用unmerge_cells参数一致。这里有一个真实的坑合并单元格后只有左上角那个单元格能存值其他区域的值会被清空。所以顺序必须是先合并再赋值。行高列宽的调整ws.row_dimensions[1].height 24 ws.column_dimensions[A].width 12算列宽建议按内容长度换算中文大约占2个字符宽度所以我一般写类似max(len(value) * 2, 10)的逻辑。冻结窗格也是个实用功能大数据表下拉时保留表头ws.freeze_panes A2 # 冻结第一行3.3 公式、图表与条件格式openpyxl可以写入Excel公式单元格里以等号开头就行ws[F2] SUM(B2:E2)要注意的是openpyxl写入公式后不会主动计算缓存结果。你用Excel打开文件时Excel会自动计算并显示结果但如果你用pandas读这个文件读到的可能是没有缓存值的公式字符串。这个现象不是bug理解它的机制就不会慌。图表插入看起来复杂实际很模板化。下面这段画一个简单的柱状图from openpyxl.chart import BarChart, Reference chart BarChart() chart.title 各部门销售额对比 chart.style 10 data Reference(ws, min_col2, min_row1, max_row7, max_col2) cats Reference(ws, min_col1, min_row2, max_row7) chart.add_data(data, titles_from_dataTrue) chart.set_categories(cats) ws.add_chart(chart, H2)Reference的作用是告诉图表数据在哪块区域和Excel里框选数据源是一个道理。条件格式能让异常数值一眼识别。比如把销售额低于8000的标红from openpyxl.formatting.rule import CellIsRule ws.conditional_formatting.add( B2:B20, CellIsRule(operatorlessThan, formula[8000], fillPatternFill(start_colorFFC7CE, end_colorFFC7CE, fill_typesolid)) )这种规则写到文件里后Excel里会自动生效用于交付报表很加分。4. 协同实战批量生成带格式的月度报表4.1 场景拆解与代码骨架纸上谈兵没有意义我拿一个真实感很强的场景来串一遍每月初要生成一份“销售汇总报表”原始数据是一张明细Excel表里面是每个销售员每一天的成交记录。最终交付的报表要求有标题、表头底色、合计行、柱状图并且每个部门单独一张工作表。整个流程分成三步用pandas读取明细表按部门销售员分组汇总。用pandas计算合计行。用openpyxl创建新的工作簿把汇总数据写进去再添加样式和图表。第一步的pandas处理df_detail pd.read_excel(销售明细.xlsx, engineopenpyxl) df_summary df_detail.groupby([部门, 销售员], as_indexFalse)[销售额].sum()as_indexFalse非常关键。不加的话分组列会变成索引后面写Excel时还得手动reset_index容易踩坑。加上之后部门、销售员、销售额就是规整的三列。第二步计算合计行total df_summary[销售额].sum() df_total_row pd.DataFrame({部门: [合计], 销售员: [], 销售额: [total]}) df_final pd.concat([df_summary, df_total_row], ignore_indexTrue)第三步交给openpyxl渲染。为了让汇总表写起来更省事我先把DataFrame转成二维列表data_rows df_final.values.tolist()然后创建新工作簿并写入wb Workbook() ws wb.active ws.title 月度汇总 # 写标题 ws.merge_cells(A1:C1) ws[A1] 2025年5月销售汇总 ws[A1].font Font(boldTrue, size14) ws[A1].alignment Alignment(horizontalcenter) ws.row_dimensions[1].height 28 # 写表头 headers [部门, 销售员, 销售额] for col_idx, header in enumerate(headers, start1): cell ws.cell(row2, columncol_idx, valueheader) cell.font Font(boldTrue, colorFFFFFF) cell.fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) cell.alignment Alignment(horizontalcenter)接着把数据行循环写入for row_idx, row_data in enumerate(data_rows, start3): for col_idx, value in enumerate(row_data, start1): ws.cell(rowrow_idx, columncol_idx, valuevalue) if row_data[0] 合计: for col_idx in range(1, 4): ws.cell(rowrow_idx, columncol_idx).font Font(boldTrue)最后加边框和列宽for row_idx in range(2, ws.max_row 1): for col_idx in range(1, 4): ws.cell(rowrow_idx, columncol_idx).border thin_border ws.column_dimensions[A].width 15 ws.column_dimensions[B].width 15 ws.column_dimensions[C].width 154.2 多部门拆表与命名防坑刚才的例子只做了一张汇总表。如果还要每个部门单独一张sheet可以这样from openpyxl.utils.dataframe import dataframe_to_rows departments df_summary[部门].unique() for dept in departments: dept_data df_summary[df_summary[部门] dept] ws_dept wb.create_sheet(titledept) for r_idx, row in enumerate(dataframe_to_rows(dept_data, indexFalse, headerTrue), start1): for c_idx, value in enumerate(row, start1): ws_dept.cell(rowr_idx, columnc_idx, valuevalue)dataframe_to_rows是openpyxl自带工具函数把DataFrame转成逐行数据比values.tolist()更灵活还能控制是否带表头。这里提醒一个命名坑workbook里sheet名不能重复。如果原始数据里有两个同名部门第二次create_sheet(titledept)不会报错但会生成“部门1”这种带后缀的名字可能打乱你的预期。稳妥做法是给sheet名加个序号或时间戳ws_dept wb.create_sheet(titlef{dept}_{idx})最后保存时用日期命名避免每天覆盖from datetime import date filename f销售报表_{date.today().strftime(%Y%m%d)}.xlsx wb.save(filename)这一步跑完一张带完整格式和分离sheet的报表就问世了。整个过程手工操作可能要半小时脚本运行只要几秒钟。5. 常见问题与排查技巧实录5.1 安装失败与版本冲突安装阶段最常撞见的就是网络超时上面已经给了镜像源方案。还有一类是“装好了但是import报错”通常是多个Python环境混乱导致包装进了另一个环境。建议用pip list查一下包是否真的在当前Python里或者直接用python -m pip install减少环境漂移。如果公司离线环境装老版本包注意pandas和numpy的版本要兼容否则会出现类似numpy.dtype size changed的报错。碰到这种问题优先检查numpy版本一般升级到和pandas匹配的版本就能解决。5.2 读写文件时的异常FileNotFoundError是新手最常遇到的十有八九是路径写错了。建议先用绝对路径或者把Excel放在和脚本同一目录再直接用文件名。Windows下写路径记得用正斜杠C:/Users/xxx/文件.xlsx或者用pathlib.Path避免反斜杠转义问题。读取时报错说没有找到sheet通常是文件名和实际sheet名不一致。可以先打印pd.ExcelFile(文件.xlsx).sheet_names看看里面有哪些表。还有个高频错误是openpyxl does not support the old .xls file format。这是格式不兼容老版xls文件需要用xlrd引擎读取或者先用Excel另存为xlsx再处理。pandas里指定enginexlrd也可以但xlrd新版只支持xls旧版才支持xlsx比较容易混乱我一般见到xls就先转格式。日期显示成数字也是常见问题。Excel内部把日期存成序列号openpyxl读出来的可能是45000这样的值。处理思路是写入前先用Python的datetime对象赋值openpyxl会把datetime自动转成Excel日期格式如果读出来是数字再用pandas的pd.to_datetime配合origin1899-12-30还原具体公式是DateTime 序列号 1899-12-30。5.3 效率与内存优化大文件是pandas的考验。几十万行的Excel默认读法会吃掉大量内存。三个最有效的优化手段第一只读需要的列用usecols。第二设置合理的数据类型日期列用parse_dates类别少的列用category类型能省不少内存。第三如果文件实在太大考虑分块读取chunks pd.read_excel(超大文件.xlsx, sheet_nameSheet1, usecols[部门, 销售额], chunksize50000) result pd.concat(chunks)注意chunksize需要配合iteratorTrue使用对大文件来说这种流式处理是救命稻草。openpyxl写入性能也要注意。最忌讳在循环里频繁调用wb.save()保存动作非常昂贵。正确做法是把所有单元格写入和样式设置都完成最后调用一次wb.save()。如果数据量很大还可以临时关闭自动计算提高写入速度wb.calculation.fullCalcOnLoad False值得一提的是openpyxl对单元格逐个写样式比较慢如果几千行都要边框建议先用一个循环把样式配好再统一应用而不是在写值时反复创建Side对象。5.4 我在实际项目里记下的几条经验项目做多了你会发现坑往往不在功能实现上而在细节习惯里。我现在都会定期备份运行前的数据因为pandas的to_excel一旦覆盖同名文件没有任何撤销通道。处理重要表格前先copy一份原始文件是顺手的好习惯。另一个习惯是写代码前先打印前几行数据看看列名和内容比如print(df.head())、print(df.dtypes)。多数逻辑错误都是因为列名拼错、数据类型不符合预期导致的提前看两眼能省下大量排查时间。还有能用pandas完成的操作尽量别用openpyxl手写循环。pandas的groupby一行搞定的事情用openpyxl从单元格里取数再自己聚合代码量大而且容易错。好的代码应该在各司其职的基础上做最清晰的协作pandas管数据openpyxl管排版各干各擅长的部分。按这个思路走处理Excel的工作会轻松很多。
