pandas+openpyxl：从数据清洗到Excel报表自动化全流程实战

处理 Excel 文件和做数据分析Python 里有两个绕不开的库pandas 和 openpyxl。我最近用它们完成了一整套线下订单数据的清洗、汇总和报表自动化不少朋友也在问怎么把这两个库真正用起来而不是停留在能跑通示例的阶段。这篇文章就围绕这两个库结合真实项目中的步骤和踩过的坑聊一聊怎么用 pandas 做数据读取、清洗和计算再用 openpyxl 控制 Excel 的格式、样式和输出细节最终落地到一套完整可复用的报表流程。无论你是做运营、财务还是刚入门 Python 数据分析这份实操记录都能让你少走几周弯路。1. 开始之前搞懂这两个库的分工1.1 为什么不是只有一个库很多新手一开始会很困惑pandas 明明能读 Excel、能写 Excel为什么还要单独学 openpyxl这其实是个典型的分工问题。pandas 的核心强项是数据处理它能把 Excel 表格变成 DataFrame然后用一行代码完成筛选、分组、去重、求和、透视等操作。它的弱点也很明显——对 Excel 文件本身的外观控制很弱。你用df.to_excel()写出来的文件基本就是纯数据表格没有字体、没有填充色、没有边框也不会帮你合并单元格。openpyxl 正好相反它可以直接操作 Excel 文件的底层结构比如某个单元格的背景色、某个工作表的列宽、某个区域的边框样式甚至还能插入图表。但如果你让它自己从头开始逐行逐列地计算数据那效率就太低了而且写出来的代码冗长又难维护。所以标准打法就是pandas 负责算数openpyxl 负责装修。先用 pandas 把数据处理好再用 openpyxl 把结果做成一份能直接交出去、看起来专业规范的 Excel 文件。我在这套流程里跑了几个月稳定性非常好。1.2 它们的适用场景差异拿我实际处理过的场景举例月初要出一份各门店销售汇总表原始数据是多个 CSV 文件拼出来的里面有脏数据、重复记录、空值还有格式不统一的日期。这种活儿如果只靠 openpyxl 硬生生地一项项判断和计算代码量会非常恐怖但交给 pandas 也就是十来行的事情去重、格式化、分组、聚合都是一次成型。反过来如果我要在最终报表里做仅表头行加粗并填充灰色背景金额列使用保留两位小数的会计格式前三行合并单元格作为标题这些操作用 pandas 几乎无法优雅实现而 openpyxl 是一件很简单的事。项目里只要涉及数据加工 报表输出的组合需求这两个库几乎就是标配。2. 环境准备与安装实操2.1 确定 Python 环境动手之前先确认 Python 版本。我推荐使用 Python 3.8 以上的版本目前 3.10、3.11、3.12 都可以稳定运行这两个库。你可以在命令行里执行python --version确认一下。如果你是刚入门建议直接从官网安装 Python安装时勾选Add Python to PATH这样后面在终端里执行pip命令就不会找不到程序。如果你的电脑里已经装了多个 Python 版本或者想避免项目之间依赖冲突最好用虚拟环境。我习惯在项目目录下执行python -m venv venv然后激活虚拟环境Windows 下运行venv\Scripts\activatemacOS 或 Linux 下运行source venv/bin/activate。这一步能避免系统级 Python 环境被污染部署到别的机器时也能用requirements.txt精确还原环境。2.2 openpyxl 的安装方式openpyxl 的安装非常简单通常一条命令就能搞定pip install openpyxl如果你在安装时速度很慢或卡住多半是网络源的问题我一般会临时更换国内镜像源pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple那离线安装 openpyxl怎么操作这个场景我在内网服务器上真遇到过。你的办公电脑能联网但生产服务器完全隔离外网。处理方法是在外网电脑上访问 PyPI 网站搜索 openpyxl下载对应 Python 版本和系统平台的.whl文件同时也把它的依赖库比如et_xmlfile一并下载下来然后用 U 盘或者内网传输工具拷到目标机器上执行pip install openpyxl-3.x.x-py2.py3-none-any.whl需要注意openpyxl 3.x通常依赖et_xmlfile库如果缺了它会报ModuleNotFoundError。下载依赖的时候仔细看下 PyPI 页面上的 Requires 部分记得一起装好。2.3 pandas 的安装与常见超时问题pandas 的安装命令同样简单pip install pandas不过 pandas 依赖 numpy如果你是新环境pip 会自动把 numpy 一起装上。如果用的是 Anaconda 或 Miniconda也可以执行conda install pandas。很多人在 PyCharm 里装 pandas 会卡在 connected time out 之类的错误这基本是网络问题而不是环境问题。解决思路和 openpyxl 一样换镜像源就好。在 PyCharm 的 Terminal 里执行pip install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple要么就在 PyCharm 的 Settings - Project - Python Interpreter 点加号后在 Manage Repositories 里把默认源替换成国内镜像再搜索 pandas 安装。这个方法对 PyCharm 装任何包都通用。3. pandas 核心操作与数据分析基础3.1 DataFrame 和 Series先分清两个数据结构pandas 里最重要的两个数据结构Series是一列带索引的数据DataFrame是一个二维表格结构由多个 Series 拼成。你可以把 DataFrame 想象成一张 Excel 工作表它的每一行对应一条记录每一列对应一个字段。创建 DataFrame 的方式很灵活最常用的是从字典创建字典的 key 会成为列名import pandas as pd data { 门店: [上海店, 北京店, 广州店], 销售额: [12800, 9600, 15300], 订单数: [210, 168, 236] } df pd.DataFrame(data) print(df)执行后你会看到表格数据。此时每行自动带一个从 0 开始的索引index这就是 DataFrame 的行标签。你可以通过df[门店]取出一列返回的就是 Series也可以用df.iloc[0]、df.loc[0]取出某一行或某个单元格。这些是后续一切操作的基础。在实际项目中数据往往来自多个地方常见的创建方式还有从 CSV 读入、从 Excel 读入、从列表嵌套列表创建以及从数据库查询结果转换。我建议把字典创建 DataFrame作为最优先掌握的入门操作理解起来直观调试也方便。3.2 读取和写入 Excelread_excel 与 to_excelpandas 读取 Excel 文件的核心函数是pd.read_excel()它底层需要依赖某个引擎。openpyxl负责处理.xlsx格式所以这也是为什么两个库经常同时安装的原因之一。基础用法import pandas as pd df pd.read_excel(销售数据.xlsx, sheet_nameSheet1) print(df.head())实际项目里你还会用到几个高频参数sheet_name可以填工作表名称字符串也可以填索引整数从 0 开始如果填None则返回所有工作表组成的字典。header指定哪一行作为列名默认是第 0 行如果你的 Excel 文件前面有几行标题说明通常设置header3之类跳过前面的行。dtype指定列的数据类型比如dtype{订单号: str}防止订单号被当成数字导致前面的 0 丢失。skiprows跳过前面几行适合带诺干行说明的表格。usecols只读取某些列比如usecolsA:C或usecols[门店, 销售额]。写回 Excel 的方式也很直接df.to_excel(结果.xlsx, indexFalse, sheet_name汇总)这里最关键的参数是indexFalse它决定要不要把 DataFrame 的索引写成 Excel 的一列。大多数时候我们不需要索引所以默认写False。如果不设置的话Excel 里会多出一列无意义的序号报表很难看。3.3 数据类型转换与清洗数据处理里最耗时间的一步就是类型清洗。Excel 里同一个字段经常会混着数字、文本、空值读进来以后并不是我们想要的目标类型。pandas 里常用的转换方法有pd.to_numeric(column, errorscoerce)将列强制转为数值转换失败时变成 NaN。pd.to_datetime(column, format%Y-%m-%d)将字符串列转为时间类型format参数是要解析的日期格式。df[column].astype(str)将列转为字符串。我在清洗销售数据时经常会这样做df[销售额] pd.to_numeric(df[销售额], errorscoerce) df[日期] pd.to_datetime(df[日期], errorscoerce) df df.dropna(subset[销售额, 日期])第一行把销售额变成数字不能转的变成空值第二行把日期统一成时间格式解析不了的也变成空值最后一行把关键字段为空的行整体丢掉避免脏数据进入后续统计。这三行组合几乎可以应付绝大多数日常表。另外绝对值函数abs()是 Python 的内置函数但 pandas 的 DataFrame 和 Series 也直接支持它df[差值].abs()在计算误差或差异统计时很常用。3.4 分组、筛选和聚合的实战用法数据分析的核心动作其实就那么几个筛选、排序、分组、聚合。我拿一个实战场景举例有一个订单明细表包含门店、品类、销售额、日期四列想按门店和品类统计总销售和订单数。result df.groupby([门店, 品类]).agg( 总销售额(销售额, sum), 订单数(订单号, count) ).reset_index()groupby指定分组维度agg里用元组指定对哪一列做哪种聚合。输出是一个新的 DataFramereset_index()把分组索引恢复成普通列后面导出 Excel 更干净。筛选操作可以用布尔索引比如找出销售额大于一万的行high_sales df[df[销售额] 10000]pandas 的布尔索引非常灵活可以组合多个条件用表示且、|表示或、~表示非注意每个条件都要用括号括起来。关于热词里提到的ewm函数这是指数加权移动平均常用于时间序列平滑或量化交易里的均线计算。核心参数有span窗口跨度、com质心、alpha衰减系数、adjust和min_periods。日常最常使用的是df[销售额_ewm] df[销售额].ewm(span7, adjustFalse).mean()span7相当于 7 日移动平均但它给最近的数据更高的权重对趋势变化反应更快比简单移动平均更适合做短期趋势观察。adjustFalse表示直接从第一行开始计算不进行早期均值修正结果和很多行情软件里的 EMA 一致。4. openpyxl 深度探索从读取到样式控制4.1 工作簿、工作表和单元格的基础操作openpyxl 的核心模型是三个层级Workbook工作簿、Worksheet工作表、Cell单元格。读取已有文件用load_workbookfrom openpyxl import load_workbook wb load_workbook(模板.xlsx) ws wb[Sheet1] print(ws.max_row, ws.max_column)ws.max_row和ws.max_column返回工作表已使用区域的最大行列数这个信息在遍历数据时很有用。遍历单元格可以用行循环for row in ws.iter_rows(min_row2, max_rowws.max_row, max_colws.max_column, values_onlyTrue): print(row)values_onlyTrue表示直接返回单元格的值而不是单元格对象调试和数据处理时非常方便。新建文件用Workbook()from openpyxl import Workbook wb Workbook() ws wb.active ws.title 汇总报表 ws[A1] 门店名称 wb.save(新建报表.xlsx)这些是最基础的操作。真正体现 openpyxl 价值的其实是它不光是写值它还能控制 Excel 里几乎所有可见格式。4.2 样式控制字体、填充、边框和对齐Excel 报表要做得好看离不开字体、填充、边框、对齐这些样式。openpyxl 里每一个样式都是独立的类从openpyxl.styles导入。给表头加粗并填充灰色背景from openpyxl.styles import Font, PatternFill, Alignment, Border, Side header_font Font(boldTrue, size11, colorFFFFFF) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) header_alignment Alignment(horizontalcenter, verticalcenter) for cell in ws[1]: cell.font header_font cell.fill header_fill cell.alignment header_alignmentPatternFill的start_color和end_color都设成同一个值然后fill_typesolid才能实现纯色填充。我第一次用的时候只设置了PatternFill(solid, fgColorFFFF00)结果颜色没生效后来才发现 3.x 版本里标准写法就是start_color和end_color一致加fill_typesolid。细边框的写法也常被忽略正确姿势是定义一个Side再把它组合进Borderthin Side(stylethin, colorBFBFBF) border Border(leftthin, rightthin, topthin, bottomthin) for row in ws.iter_rows(min_row1, max_rowws.max_row, max_colws.max_column): for cell in row: cell.border border对齐方式Alignment除了水平对齐horizontal和垂直对齐vertical还能设置wrap_textTrue让长文本自动换行这在填备注列时特别有用。4.3 列宽、行高与冻结窗格一张表如果列宽完全默认文字挤在一起阅读体验会很差。openpyxl 调整列宽是按列对象的column_dimensions控制的ws.column_dimensions[A].width 15 ws.column_dimensions[B].width 20如果你不知道具体哪一列可以遍历列头然后根据内容长度估算列宽但更稳妥的做法是给关键列手动指定宽度。行高类似ws.row_dimensions[1].height 24通常表头行我会设高一点。冻结窗格这项工作在 Excel 里很常用表头几百行往下滚的时候希望第一行始终可见。openpyxl 里一行就能实现ws.freeze_panes A2这个赋值的意思是从 A2 单元格开始右下方的区域可以滚动所以 A 列第一行会被冻住。如果想冻结前两列就设置为ws.freeze_panes C1。这招在生成大报表时几乎每次都用。4.4 合并单元格、日期格式和图表合并单元格在制作标题行时很常见比如第一行要放一个跨列的大标题ws.merge_cells(A1:F1) ws[A1] 2025年第一季度门店销售汇总 ws[A1].alignment Alignment(horizontalcenter, verticalcenter)注意合并之后只有左上角这个单元格是有值的其他被合并的单元格会变成None读取时要小心。日期格式的控制也经常被问到。如果你用 openpyxl 给单元格写入一个 Python 的 datetime 对象Excel 会默认按日期显示。但如果你直接写入的是字符串2025-01-15后续排序和计算会出问题。我一般会先设置好number_format再写入值from datetime import datetime cell ws[C2] cell.value datetime(2025, 1, 15) cell.number_format YYYY-MM-DD插入图表是 openpyxl 的进阶功能。比如给销售额增加一个柱状图from openpyxl.chart import BarChart, Reference chart BarChart() data Reference(ws, min_col2, min_row1, max_row5) cats Reference(ws, min_col1, min_row2, max_row5) chart.add_data(data, titles_from_dataTrue) chart.set_categories(cats) ws.add_chart(chart, E2)Reference对象用来指定图表数据区域titles_from_dataTrue表示把第一行当作系列名称。图表对象最终用ws.add_chart(chart, E2)放到指定单元格位置。这个功能足够生成简单的柱状图和折线图满足日常报表需求基本没问题。5. 结合实战从清洗数据到生成专业报表5.1 整个流程怎么设计我处理报表项目的固定套路可以落地成四步读取原始数据、用 pandas 清洗计算、用 openpyxl 写入并美化、保存并二次检查。下面结合一个具体案例完整走一遍。假设我有两个 Excel 文件订单明细.xlsx里面是每笔订单的门店、品类、销售额、日期门店信息.xlsx里面是门店名称和区域归属。我要生成一份按区域汇总的月度销售报表同时要求表头加粗、金额列数字格式为千分位保留两位小数、标题合并单元格居中对齐。5.2 用 pandas 完成数据清洗和汇总先读取两份文件并合并import pandas as pd orders pd.read_excel(订单明细.xlsx, dtype{订单号: str}) stores pd.read_excel(门店信息.xlsx) # 检查订单号有没有重复 print(重复订单数:, orders[订单号].duplicated().sum()) orders orders.drop_duplicates(subset[订单号]) # 将销售额转为数字无效值丢弃 orders[销售额] pd.to_numeric(orders[销售额], errorscoerce) orders orders.dropna(subset[销售额]) # 合并门店区域 merged orders.merge(stores, on门店, howleft) merged[月份] merged[日期].dt.to_period(M) # 按区域和月份汇总 summary merged.groupby([区域, 月份]).agg( 销售额(销售额, sum), 订单量(订单号, count) ).reset_index() # 按区域排序 summary summary.sort_values([区域, 月份])这段代码里orders.merge(stores, on门店, howleft)是按门店列把区域信息关联进来howleft表示保留订单表里的所有行。dt.to_period(M)把日期列变成年-月的统计周期后面分组汇总就非常干净。5.3 用 openpyxl 定制最终报表汇总完成后我不用df.to_excel()直接输出而是先用 openpyxl 创建一个空白工作簿把 DataFrame 的内容手动写进去这样每一种格式都在我的控制范围内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 月度销售汇总 # 写入标题行 headers [区域, 月份, 销售额, 订单量] for col_idx, header in enumerate(headers, start1): cell ws.cell(row1, columncol_idx, valueheader) # 把 DataFrame 数据写入工作表 for r_idx, row_data in enumerate(summary.itertuples(indexFalse), start2): for c_idx, value in enumerate(row_data, start1): ws.cell(rowr_idx, columnc_idx, valuevalue)itertuples(indexFalse)是遍历 DataFrame 行的最高效方式比iterrows()快很多数据量大的时候性能差异非常明显。ws.cell(row..., column..., value...)是 openpyxl 另一种按行列坐标写入单元格的方法适合动态写入场景。接着做格式美化。这里我把表头加粗填充然后对销售额列设置数字格式# 表头样式 header_font Font(boldTrue, colorFFFFFF) header_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) center_alignment Alignment(horizontalcenter, verticalcenter) for c_idx in range(1, len(headers) 1): cell ws.cell(row1, columnc_idx) cell.font header_font cell.fill header_fill cell.alignment center_alignment # 销售额列数字格式 for r_idx in range(2, ws.max_row 1): ws.cell(rowr_idx, column3).number_format #,##0.00 # 设置边框 thin Side(stylethin, colorB0B0B0) border Border(leftthin, rightthin, topthin, bottomthin) for row in ws.iter_rows(min_row1, max_rowws.max_row, max_colws.max_column): for cell in row: cell.border border # 调整列宽 for c_idx in range(1, len(headers) 1): ws.column_dimensions[get_column_letter(c_idx)].width 18 # 增加一行总计 total_row ws.max_row 1 ws.cell(rowtotal_row, column1, value总计) ws.cell(rowtotal_row, column3, valuesummary[销售额].sum()) ws.cell(rowtotal_row, column4, valuesummary[订单量].sum()) wb.save(月度销售汇总_成品.xlsx)这里有一个容易栽的坑如果你在 pandas 阶段直接用df.to_excel()生成文件然后再用 openpyxl 打开去加样式原来的列宽、字体、边框全都会被保留但如果你要重新调整格式操作顺序和覆盖逻辑会比较绕。我这个直接先开 openpyxl 再逐格写值的方案从零开始完全控制所有单元格对复杂报表更省心。5.4 加上图表和最后的检查如果要在报表里加上简单的柱状图展示各区域销售额可以用前面提到的BarChart和Referencefrom openpyxl.chart import BarChart, Reference chart BarChart() chart.type col chart.title 各区域销售额对比 chart.y_axis.title 销售额 data Reference(ws, min_col3, min_row1, max_rowtotal_row - 1) cats Reference(ws, min_col1, min_row2, max_rowtotal_row - 1) chart.add_data(data, titles_from_dataTrue) chart.set_categories(cats) ws.add_chart(chart, F2) wb.save(月度销售汇总_成品.xlsx)保存以后我习惯重新用load_workbook把文件打开先用ws.max_row和ws.max_column确认数据写入完整再随机抽查几个单元格的值和格式。这一步看似多余但在自动跑报表任务时非常有效能及早发现数据错位、列宽异常、样式丢失等问题比让业务同事发现错误再返工强得多。6. 常见问题与排查技巧实录6.1 安装环节的高频报错第一个高频问题就是pip install pandas或pip install openpyxl时一直转圈、超时。处理方法我在第 2 节里提过换国内镜像源。如果换了镜像源还是报错看一下是不是 Python 版本太旧老版本 Python 可能没有对应版本的 pandas 预编译包pip 会尝试从源码编译这时候就容易各种报错。建议把 Python 升级到 3.9 以上。还有人在 Linux 环境里遇到OSError: libpython3.x.so之类的错误这通常是系统里缺少 Python 的动态库或者环境变量没配好。可以考虑用apt install python3-venv补全开发包或者使用pyenv管理 Python 版本。如果你看到ModuleNotFoundError: No module named openpyxl但是明明已经 pip install 过了多半是终端里用的 Python 和当前项目解释器不是同一个。在 PyCharm 里检查一下右下角的 Python Interpreter 选的是不是当前虚拟环境或者在终端里执行pip --version确认 pip 指向哪个 Python。6.2 读取 Excel 时的引擎报错pd.read_excel()报ValueError: Excel file format cannot be determined, you must specify an engine manually这个错误很典型通常发生在文件扩展名与实际格式不一致或者文件本身已损坏的情况下。我的排查思路是先看文件是不是被某些程序改成了.xlsx扩展名但实际内容其实是旧版.xls格式此时可以在read_excel中手动指定enginexlrd或engineopenpyxl。另外检查文件是否真的能手动打开如果 Excel 打不开或提示修复基本就是文件损坏换原始文件再试。另一个常见问题是读取.xls老格式时提示缺少xlrd库因为新版 xlrd 不支持.xlsx只支持.xls。我在遇到老系统导出的.xls文件时会直接安装xlrd2.0.1这个兼容版本或者干脆让业务方另存为新格式。6.3 写入报表时格式和性能的坑用 pandas 的to_excel生成报表时最容易出现两个问题一是写出的表没有样式二是默认会把索引列也写进去。第一个问题没办法因为to_excel本身不具备样式控制能力你想做样式就得回到第 5 节的方法用 openpyxl 重新构建。第二个问题靠indexFalse解决。如果已经写出来了Excel 里第一列是无意义的序号处理办法是重新跑一遍加上indexFalse。性能问题主要出现在大批量写入时。openpyxl 写入大量行速度不快是出了名的如果你要写几万行数据直接在循环里逐个ws.cell(row..., column..., value...)也能用但在循环里频繁触发样式赋值速度会进一步下降。我的优化策略是数据量大的时候先用 pandas 的to_excel把纯数据先写入文件再用 openpyxl 打开这个文件用ws.iter_rows()去批量套用样式。但如果数据量在几千行以内直接 openpyxl 逐行写入其实是完全可以接受的没必要过度优化。6.4 数据类型和精度导致的隐形错误这一类错误最隐蔽因为代码不报错但结果不对。最常见的场景是Excel 里的订单号是文本比如 00123pandas 读进来以后会自动转成数字 123丢失了前面的零另一个场景是销售额列里混了中文备注pandas 读进来变成字符串求和结果直接报错或产生错误大数。处理方案就是前面讲过的dtype参数和pd.to_numeric(errorscoerce)。我在读取数据时凡是原本应该作为文本标识的列全部显式声明为str类型这样能避免绝大多数隐式转换问题。还有一个细节是 pandas 里的空值NaN。如果你把 DataFrame 写回 Excel空值默认会变成空单元格这是正常的但如果某个数值列里有 NaN它会被写成 NaN 字符串这在 Excel 里看会是一个文本单元格非常碍眼。处理办法是df df.fillna()或者先对数据做fillna后再写入。我用完这个方法以后Excel 里终于看不到乱七八糟的 NaN 字样了。7. 从项目出发的几点心得做数据分析相关的工作工具永远是为目的服务的。你有多少种花哨的格式控制能力都不如先把源数据的质量管好。我这两年在处理十几份不同来源的 Excel 报表时最大的体会是pandas 擅长的是把脏数据变成整齐数据openpyxl 擅长的是把整齐数据变成好看数据真正的价值往往在两者的接口处——你先把数据逻辑理清楚再去纠结格式细节效率会翻倍。另一个小技巧是办公自动化的脚本尽量写成读模板、填数据、另存为的模式。我始终保留一份格式模板openpyxl 每次基于模板生成新文件而不是从零画样式。这样一来一旦需要调整表头字体或列宽我只需要改模板不改代码整个流程的维护成本会低很多。最后说一句网上教程很多但真正有用的信息往往来自跑通一次实际业务需求。找一个你手头真实的 Excel 报表踏踏实实用 pandas 清洗一遍、用 openpyxl 美化一遍踩过一遍坑之后你对这两个库的理解一定比看十篇文章都管用。