Python自动化处理Excel：pandas+openpyxl实战脚手架

简介：本资源是一套面向Python初学者与数据处理入门者的Excel自动化操作实践脚本集，聚焦解决日常办公中批量读取、筛选、写入Excel文件的核心需求。压缩包共含4个Python源文件，全部为可直接运行的.py脚本，总大小仅6KB，轻量易用，涵盖基础读取（readExcel.py）、数值型数据专项处理（readExcelNumber.py）、JSON格式交互式读取（readExcelJS.py）及读写一体化流程（readWriteExcel.py），覆盖pandas与openpyxl双库典型用法。目前已有704人学习下载，适合希望快速掌握Excel I/O实战技巧、理解DataFrame筛选逻辑、并了解样式/公式等进阶场景衔接路径的学习者。资源代码简洁规范，注释清晰，每份脚本对应明确功能边界，便于按需调用、组合扩展或嵌入自动化报表流程。

1. 一个Excel处理脚本，为什么能省掉每天两小时的机械复制粘贴？

上周帮某高校实验室处理一批学生实验数据，原始文件是23个Excel表格，每个表里有8列字段、平均200行数据，需要统一提取“学号”“实验得分”“日期”三列，清洗掉空行和异常值（比如得分写成“缺考”或“-1”），再合并到一张新表里，最后按学号去重、按日期排序、生成统计图表。他们之前靠人工拖拽+复制粘贴+Excel公式硬凑，平均每人每天耗时117分钟——这还没算改错重来的返工时间。我甩出这个python读取Excel并写入.zip包，解压、改两行路径、双击运行，48秒跑完全部流程，输出带格式的汇总表+柱状图PDF。它不是炫技的AI大模型，就是一个干净利落的Python工程包：不依赖数据库、不调用云API、不弹窗交互，纯命令行驱动，核心逻辑全在main.py里，连pandas版本冲突都预判好了。如果你正被重复性Excel操作钉在工位上动弹不得，又不想啃《Python for Data Analysis》整本书——这个包就是你今天该下载、该跑通、该改着用的起点。

2. 技术栈与项目结构：为什么选pandas+openpyxl而不是xlrd或csv？

这个包没搞花活，技术选型全是血泪经验堆出来的。早期用过xlrd读.xls，结果客户发来.xlsx就直接报错；试过纯csv处理，但Excel里的合并单元格、日期格式、颜色标记全丢了；还踩过pandas默认引擎读取大文件内存爆满的坑。最终锁定pandas + openpyxl组合——前者负责数据清洗和计算，后者专攻Excel原生格式读写（保留样式、公式、合并单元格）。整个包只有5个文件，结构极简：

python读取Excel并写入/ ├── config/ │ └── settings.json # 路径、字段名、过滤规则等配置项 ├── data/ │ ├── input/ # 原始Excel存放目录（自动扫描所有.xlsx/.xls） │ └── output/ # 输出目录（含汇总表、图表、日志） ├── src/ │ ├── main.py # 主入口：调度读取→清洗→合并→写入→绘图 │ ├── reader.py # 核心读取器：支持多sheet、跳过空行、类型推断 │ └── writer.py # 写入器：按模板写入、自动调整列宽、添加边框 ├── requirements.txt └── run.bat # Windows一键运行脚本（含虚拟环境激活）

提示：requirements.txt锁定了pandas==1.5.3和openpyxl==3.1.2，这是经过200+份真实Excel文件实测最稳的组合。高版本pandas对中文路径支持有玄学bug，低版本openpyxl写入图表会丢坐标轴标签——这些坑我都替你踩过了。

2.1 配置驱动设计：为什么把字段名和过滤条件抽到JSON里？

硬编码字段名是二次开发最大的雷区。比如原始表头可能是“学号”“StudentID”“S_ID”，下次换一批数据就全崩。config/settings.json用三层结构解耦：

{ "input": { "base_path": "./data/input/", "file_pattern": "*.xlsx", "sheet_names": ["Sheet1", "数据页"] }, "fields": { "id": ["学号", "StudentID", "S_ID"], "score": ["实验得分", "Score", "result"], "date": ["日期", "Date", "submit_time"] }, "filter_rules": { "score_min": 0, "score_max": 100, "exclude_keywords": ["缺考", "未提交", "N/A"] } }

reader.py读取时会遍历fields.id数组，在每张表的首行逐个匹配列名，找到第一个命中项即为ID列索引。这样换表头不用改代码，只改JSON就行。

2.2 读取器核心逻辑：如何安全处理“表头不在第一行”的脏数据？

真实场景中，Excel常有前3行是标题、说明、空行。src/reader.py的read_excel_safe()方法用两步定位：

def read_excel_safe(file_path, sheet_name): # 第一步：用openpyxl定位真实表头行（找包含至少3个非空单元格的行） wb = load_workbook(file_path, read_only=True) ws = wb[sheet_name] header_row = None for row_idx in range(1, min(20, ws.max_row + 1)): # 只扫前20行 cells = [ws.cell(row=row_idx, column=col_idx).value for col_idx in range(1, min(10, ws.max_column + 1))] if sum(1 for c in cells if c and str(c).strip()) >= 3: header_row = row_idx break wb.close() # 第二步：用pandas从header_row开始读，自动设表头 df = pd.read_excel( file_path, sheet_name=sheet_name, skiprows=header_row - 1, # pandas的skiprows是从0开始计数 dtype=str # 先全读成字符串，避免数字被转成float ) return df

关键点：skiprows=header_row - 1是易错点——openpyxl的行号从1开始，pandas的skiprows参数却是从0开始计数。这里少减1就会漏掉表头，多减1就多跳一行。我加了注释，但第一次跑还是翻车了两次。

2.3 写入器的样式控制：怎么让输出表看起来像人手做的？

src/writer.py的write_with_style()方法用openpyxl手动控制样式，避开pandas.to_excel()的样式短板：

def write_with_style(df, output_path, sheet_name="汇总"): wb = Workbook() ws = wb.active ws.title = sheet_name # 写入表头（加粗+背景色） for col_idx, col_name in enumerate(df.columns, 1): cell = ws.cell(row=1, column=col_idx, value=col_name) cell.font = Font(bold=True) cell.fill = PatternFill("solid", fgColor="DCE6F1") cell.alignment = Alignment(horizontal="center") # 写入数据（自动调整列宽） for row_idx, row in enumerate(df.values, 2): # 从第2行开始写数据 for col_idx, value in enumerate(row, 1): ws.cell(row=row_idx, column=col_idx, value=value) # 设置列宽：中文字符按2个英文算 for col_idx, col_name in enumerate(df.columns, 1): max_len = max( len(str(col_name)), max(len(str(x)) for x in df.iloc[:, col_idx-1]) if not df.empty else 0 ) ws.column_dimensions[get_column_letter(col_idx)].width = min(max_len * 1.2, 50) wb.save(output_path)

get_column_letter()是openpyxl内置函数，把数字列号转成"A"、"B"、"AA"这种。列宽计算用了min(..., 50)防止单列过宽撑爆页面——这是从客户打印需求里抠出来的细节。

3. 运行全流程：从解压到看到PDF图表，只需三步

别被“Python项目”吓住，这个包对新手极其友好。全程不需要装Python环境（自带便携版），也不用开终端敲命令（有双击脚本）。

3.1 环境准备：为什么推荐用run.bat而不是直接python main.py？

run.bat不是简单包装，它解决了三个隐形问题：

1. Python路径隔离：自动检测系统是否已装Python，若无则启用包内python-3.9.13-embed-amd64便携版；
2. 虚拟环境兜底：创建独立venv，避免你本地pandas版本冲突；
3. 错误捕获可视化：所有print和异常都重定向到data/output/run.log，双击bat后即使黑窗一闪而过，日志里也留痕。

@echo off setlocal enabledelayedexpansion :: 检查系统Python where python >nul 2>&1 if %errorlevel% == 0 ( echo [INFO] 使用系统Python... python -m venv .venv call .venv\Scripts\activate.bat pip install -r requirements.txt ) else ( echo [INFO] 使用内置便携Python... set PATH=%cd%\python-3.9.13-embed-amd64;%PATH% python -m venv .venv call .venv\Scripts\activate.bat pip install -r requirements.txt ) echo [INFO] 开始执行main.py... python src\main.py > data\output\run.log 2>&1 if %errorlevel% == 0 ( echo [SUCCESS] 执行完成！结果见 data\output\ ) else ( echo [ERROR] 执行失败，请查看 data\output\run.log ) pause

注意：.venv文件夹默认被Git忽略，但首次运行后会生成。如果后续想换环境，删掉.venv再双击bat即可重建。

3.2 数据准备：input目录下放什么文件才有效？

data/input/是唯一需要你动手的地方，规则极简：

• ✅ 支持.xlsx和.xls（测试过Office 2003到365全兼容）；
• ✅ 文件名任意，但建议用中文无空格（如实验数据_202405.xlsx）；
• ✅ 每个文件可含多个sheet，程序自动扫描config/settings.json里定义的sheet_names；
• ❌ 不支持密码保护的Excel（openpyxl不支持解密）；
• ❌ 不支持宏（.xlsm），会静默跳过。

实测发现：当input/下有17个文件时，main.py会先打印扫描日志：

[INFO] 扫描到17个Excel文件 [INFO] 正在处理: 实验数据_202405.xlsx -> Sheet1 [INFO] 正在处理: 实验数据_202405.xlsx -> 数据页 [INFO] 正在处理: 期中成绩.xlsx -> 成绩单 ...

如果某文件打不开，会在run.log里记录[ERROR] 无法读取 XXX.xlsx: Permission denied，不影响其他文件继续处理。

3.3 输出物详解：output目录里每个文件都是干什么的？

跑完后data/output/下会生成4类文件，各司其职：

特别说明chart_summary_*.pdf：图表用matplotlib生成，但导出PDF前做了两件事——

1. 设置中文字体：plt.rcParams['font.sans-serif'] = ['SimHei', 'Arial Unicode MS']；
2. 关闭交互模式：plt.ioff()，防止生成过程中弹窗卡死。

所以你不会看到任何matplotlib窗口，只有静默生成的PDF。

4. 常见问题排查：那些让我凌晨三点还在改的坑

4.1 现象：双击run.bat后黑窗闪退，output目录空空如也

原因：系统缺少Microsoft Visual C++ 2015-2022运行库（便携Python依赖它）
解决：去微软官网下载vc_redist.x64.exe安装，或直接用包内tools/vc_redist_fix.bat（已打包好）

4.2 现象：summary_*.xlsx里日期列显示为44205.0这种数字

原因：Excel日期在底层存的是序列号（1900-01-01为1），pandas读取时没识别为datetime类型
解决：在config/settings.json的fields.date数组里，把日期列名加到末尾，并在src/reader.py的read_excel_safe()里加强制转换：

# 在df读取后插入 if "date" in config["fields"] and config["fields"]["date"]: date_col_candidates = config["fields"]["date"] for col in date_col_candidates: if col in df.columns: try: df[col] = pd.to_datetime(df[col], errors='coerce') except: pass break

4.3 现象：chart_summary_*.pdf图表里中文全变成方块

原因：系统没有SimHei字体，且matplotlib缓存了旧字体列表
解决：

1. 下载simhei.ttf放到src/目录下；
2. 修改src/main.py开头，强制指定字体路径：

import matplotlib matplotlib.use('Agg') # 必须在import pyplot前 import matplotlib.pyplot as plt plt.rcParams['font.sans-serif'] = ['simhei'] plt.rcParams['axes.unicode_minus'] = False # 解决负号'-'显示为方块的问题

4.4 现象：处理大文件（>50MB）时内存飙升到10GB，电脑卡死

原因：pandas默认把整个Excel加载进内存，openpyxl的read_only=True模式不支持样式读取
解决：在src/reader.py里加内存保护开关：

def read_excel_safe(file_path, sheet_name): # 新增：文件大小超30MB时启用流式读取（牺牲样式，保内存） if os.path.getsize(file_path) > 30 * 1024 * 1024: print(f"[WARN] {file_path}过大，启用流式读取（不保留样式）") return pd.read_excel(file_path, sheet_name=sheet_name, engine='openpyxl') else: # 原有逻辑...

4.5 现象：run.log里报错KeyError: '学号'，但Excel表头明明写着“学号”

原因：Excel表头有不可见空格（如“学号 ”全角空格）或换行符
解决：在reader.py的表头匹配逻辑里加清洗：

# 替换原有匹配逻辑 for col_idx, cell in enumerate(ws[header_row], 1): raw_header = str(cell.value) if cell.value else "" clean_header = raw_header.strip().replace("\n", "").replace("\r", "") if clean_header in config["fields"]["id"]: id_col_index = col_idx break

5. 二次开发实战：如何把“读Excel写Excel”改成“读Excel+调API+写数据库”？

这才是这个包真正的价值——它不是终点，而是你定制化开发的脚手架。上周帮某公司把流程升级为：读Excel → 校验学号合法性（调内部API）→ 补充班级信息（查MySQL）→ 写入新Excel并邮件发送。整个改造只动了3个文件，不到2小时。

5.1 扩展字段映射：从静态JSON到动态API校验

原逻辑只做字段名匹配，现在要加API校验。在config/settings.json里新增api_config段：

"api_config": { "student_check_url": "http://internal-api/check-student", "timeout": 5, "headers": {"Authorization": "Bearer xxx"} }

然后在src/reader.py的read_excel_safe()返回df后，插入校验环节：

def validate_student_ids(df, config): if not config.get("api_config") or "id" not in config["fields"]: return df id_col = config["fields"]["id"][0] # 取第一个匹配列名 if id_col not in df.columns: return df # 批量校验（避免单个请求太慢） student_ids = df[id_col].dropna().astype(str).str.strip().tolist() if not student_ids: return df try: response = requests.post( config["api_config"]["student_check_url"], json={"ids": student_ids}, headers=config["api_config"]["headers"], timeout=config["api_config"]["timeout"] ) valid_data = response.json().get("valid", []) # 只保留校验通过的行 df = df[df[id_col].isin(valid_data)] except Exception as e: print(f"[WARN] 学号校验API调用失败: {e}") return df

提示：API返回格式约定为{"valid": ["2021001", "2021002"]}，这样不用改业务逻辑就能对接不同系统。

5.2 数据库写入：用SQLAlchemy替换Excel写入

原writer.py只写Excel，现在要写MySQL。新建src/db_writer.py：

from sqlalchemy import create_engine, text import pandas as pd def write_to_mysql(df, config): db_url = config["db_config"]["url"] # 如 mysql+pymysql://user:pwd@host:3306/db table_name = config["db_config"]["table_name"] engine = create_engine(db_url) with engine.connect() as conn: # 先清空表（根据业务需求可改为upsert） conn.execute(text(f"TRUNCATE TABLE {table_name}")) conn.commit() # 用pandas直接写入（自动建表？不，必须提前建好） df.to_sql(table_name, engine, if_exists='append', index=False) print(f"[INFO] 已写入{len(df)}行到MySQL表{table_name}")

关键点：if_exists='append'要求目标表已存在，字段名需和df列名严格一致。所以DBA建表时，CREATE TABLE scores (student_id VARCHAR(20), score INT, submit_date DATE);字段名必须和config/settings.json里fields.id/score/date的最终映射一致。

5.3 流程编排：修改main.py串联新模块

src/main.py原流程是read → clean → merge → write，现在扩展为：

# 原有导入 from src.reader import read_all_excels from src.writer import write_summary_excel # 新增导入 from src.db_writer import write_to_mysql from src.email_sender import send_report_email def main(): config = load_config() df = read_all_excels(config) # 原逻辑 # 新增：API校验 df = validate_student_ids(df, config) # 新增：数据库写入 if config.get("db_config"): write_to_mysql(df, config) # 原有：写Excel write_summary_excel(df, config) # 新增：邮件发送 if config.get("email_config"): send_report_email(df, config)

send_report_email()用smtplib实现，附件自动绑定summary_*.xlsx和chart_*.pdf，主题带日期——这部分代码我放在src/email_sender.py里，但没放进初始包，因为涉及邮箱密码明文风险。你需要自己补上，或者用环境变量注入。

5.4 验证技巧：如何确保改完的代码没破坏原有功能？

我给自己定的铁律：每次改完必须跑三关验证。

从那以后我每次加新功能，都强制走一遍这三关——哪怕只是改一行注释。因为Excel处理的玄学在于：99%的文件跑得通，1%的脏数据能让整个流程在深夜11点崩给你看。希望帮到你。

本文还有配套的精品资源，点击获取