事情是这样的，上个月我接手了一个活，帮朋友整理公司里一堆Excel报表。不是吹，那一摞文件少说也有五六十个，每个里面七八个工作表，数据格式还他妈不统一。朋友的原话是：“猫哥，你帮我搞个程序，跑一下就行。”我想着Python搞Excel那不是分分钟的事？结果真动手才发现，坑比我想象的多。
先说说场景。朋友公司是做电商代运营的，每个月要给不同品牌方出对账报表。每个品牌一个文件夹，里面Excel文件名统一规范叫“品牌名_月份_对账单.xlsx”，但Excel里面工作表名字就五花八门了，有的叫“Sheet1”，有的叫“数据汇总”，有的叫“1月”，甚至有个叫“不要删”。每个表的结构也不一样，有的第一行是表头，有的前两行是合并单元格的标题，第三行才是真正的列名。我的任务是把所有品牌的“订单明细”和“退款明细”这两个关键表的数据提取出来，合并成一个总表，再生成一个汇总统计的Excel。
我一开始想的很简单：用pandas，read_excel，指定sheet_name，搞定。结果一跑，报错：`ValueError: Sheet name '订单明细' not in list`。打开一看，好家伙，那个工作表名字叫“订单明细表”，多了个“表”字。更骚的是，有个品牌把退款明细写在“退货单”这个工作表里，但内容其实是退款数据。所以第一个坑就来了：工作表命名不统一。
我的解决方案是，不写死工作表名，而是写一个模糊匹配的逻辑。具体做法是，先用`pd.ExcelFile`拿到所有工作表名列表，然后遍历列表，对每个表名做字符串相似度匹配。我用了`difflib.get_close_matches`，设置cutoff=0.6，把目标关键词“订单明细”和“退款明细”分别拿去匹配。如果匹配到就读取，没匹配到就报个警告并跳过。代码大概长这样：
```python
import difflib
import pandas as pd
def find_sheet_name(sheet_list, keyword, cutoff=0.6):
matches = difflib.get_close_matches(keyword, sheet_list, n=1, cutoff=cutoff)
return matches[0] if matches else None
```
这个办法解决了大部分问题，但还有更恶心的。有个品牌的Excel文件里，“订单明细”这个工作表的内容居然分成了两个工作表，一个叫“订单1-15日”，一个叫“订单16-31日”。我总不能只抓一个吧？所以我又加了一个逻辑：如果匹配到多个相似的工作表，就把它们全部读取然后用`pd.concat`纵向拼接。这里要注意索引重置，不然合并后索引会乱掉。
```python
matches = difflib.get_close_matches(keyword, sheet_list, n=3, cutoff=0.6)
if len(matches) >= 1:
dfs = [pd.read_excel(file_path, sheet_name=name) for name in matches]
df = pd.concat(dfs, ignore_index=True)
```
第二个坑是表头结构不一致。有些Excel的前面几行是合并单元格的标题，比如第一行写“XX品牌对账数据”，第二行是空的，第三行才是“订单号、商品名称、金额、日期”。pandas默认把第一行当表头，读出来就是一堆NaN。我一开始手动指定`header=2`，但不同文件表头行数不一样，有的甚至没有标题行，第一行就是数据。这就没法硬编码。
我的做法是，读取文件前先检查前几行的内容。用`pd.read_excel`先不指定header，把原始数据全读进来，然后看第一列的第一个单元格是不是中文或者包含“品牌”、“对账”这类词。如果是，就说明是标题行，需要跳过。我写了一个函数，遍历前5行，检查每一行第一列的值，如果全是数字或者日期格式，就认为这一行是真正的数据起始行。代码有点粗糙，但够用：
```python
def detect_header_row(file_path, sheet_name, max_rows=5):
df_raw = pd.read_excel(file_path, sheet_name=sheet_name, header=None, nrows=max_rows)
for i in range(max_rows):
val = df_raw.iloc[i, 0]
if pd.isna(val):
continue
try:
float(val)
return i # 第一个能转成数字的行就是数据开始
except:
if isinstance(val, str) and not any(kw in val for kw in ['品牌', '对账', '汇总']):
return i
return 0
```
这个函数返回的索引作为`skiprows`参数传给`pd.read_excel`。但注意，如果返回0，就是没有要跳过的行。实测下来，90%的文件都能正确识别，剩下10%我手动在配置里补了一个字典，指定某些文件的skiprows值。没办法，自动化做不到100%，留个后门给手动修正。
第三个坑是数据类型。Excel里的金额列，有些是文本格式，比如“1,234.56”这种带千分位逗号的，pandas读进来是字符串。还有日期列，有的存成“2024-01-15”，有的存成“2024/1/15”，有的存成“1/15/2024”，甚至有个文件把日期存成了Excel序列号（比如45292这种数字）。我统一做一个清洗：金额列用`pd.to_numeric`配合`errors='coerce'`，先把逗号去掉再转；日期列先用`pd.to_datetime`，如果报错了就尝试用`xlrd`的日期转换逻辑。这里我偷了个懒，直接用`pd.to_datetime`的`format`参数自动推断，但遇到序列号就得手动处理一下：
```python
def parse_excel_date(val):
try:
return pd.to_datetime(val)
except:
try:
# 尝试把数字当作Excel序列号处理（1900日期系统）
from datetime import datetime, timedelta
base = datetime(1899, 12, 30)
return base + timedelta(days=int(float(val)))
except:
return pd.NaT
```
第四个大坑是文件编码。Excel文件本身是二进制格式，按理说没编码问题，但朋友给的文件里有一个是.xls格式（老版Excel），不是.xlsx。pandas的`read_excel`本身支持.xls，但要装`xlrd`库，而且xlrd 2.0以上版本不支持.xlsx了，所以得同时装`openpyxl`和`xlrd`。我的`requirements.txt`里写了：
```
pandas==2.0.3
openpyxl==3.1.2
xlrd==2.0.1
```
但注意，xlrd 2.0.1默认只支持.xls，如果遇到.xlsx文件，pandas会自动切换用openpyxl或calamine。我一开始没装xlrd，结果跑.xls文件时报错：`ImportError: Missing optional dependency 'xlrd'`。装完之后又碰到一个文件报错：`xlrd.biffh.XLRDError: Excel xlsx file; not supported`。这才知道那个文件虽然是.xls后缀，但实际是xlsx格式。我干脆在代码里加了一个文件格式判断，用`openpyxl`尝试打开，如果报错再用`xlrd`。但后来发现更简单的方法：直接用`pd.read_excel`的`engine`参数，先试`openpyxl`，不行再试`xlrd`。
```python
def safe_read_excel(file_path, sheet_name, **kwargs):
for engine in ['openpyxl', 'xlrd']:
try:
return pd.read_excel(file_path, sheet_name=sheet_name, engine=engine, **kwargs)
except Exception as e:
continue
raise ValueError(f"Cannot read {file_path} with any engine")
```
第五个坑是内存。五六十个Excel，每个里面工作表又多，合并起来的数据量其实不小。我一开始直接一个个读，然后append到一个列表里，最后concat。结果跑了一半内存飙到4个G，电脑风扇狂转。我优化了一下，读每个文件的时候只保留需要的列，并且把不需要的中间变量及时删除，用`del`和`gc.collect()`。另外，把最终结果分批写入Excel，用`pd.ExcelWriter`配合`mode='a'`追加模式，而不是全部在内存里拼完再写。这样内存占用降到了1.5G左右，勉强能跑。
```python
with pd.ExcelWriter('汇总结果.xlsx', engine='openpyxl', mode='w') as writer:
for file_path in file_list:
df = process_one_file(file_path)
if df is not None:
df.to_excel(writer, sheet_name='汇总', index=False, header=False, startrow=writer.sheets['汇总'].max_row if '汇总' in writer.sheets else 0)
```
这里有个细节，如果多次写入同一个工作表，需要用`startrow`参数控制写入位置，否则会覆盖。`writer.sheets['汇总'].max_row`可以拿到当前工作表已写入的行数，这样就能追加。但注意，第一次写入时工作表还不存在，所以得判断一下。
最后说说踩坑经验总结。第一，永远不要相信Excel文件命名和结构是统一的，哪怕对方拍胸脯说“都一样”。第二，模糊匹配和自动检测表头是必备操作，但一定要留手动配置的接口，毕竟总有奇葩文件。第三，数据类型转换要写健壮，尤其是日期和金额，各种格式都有可能。第四，内存管理要提前考虑，文件多了之后pandas很容易爆内存，分批处理或逐行写入是王道。第五，报错信息不要直接抛出来就完事，最好写个日志，记录哪个文件、哪个工作表、什么原因失败，方便事后排查。
我最后给朋友交付的程序，是一个命令行工具，接受一个文件夹路径，输出一个汇总Excel和一个日志文件。运行前会先扫描所有文件，打印出每个文件的结构预览，让用户确认。确认后跑脚本，跑完自动打开汇总文件。虽然中间修修补补花了两天时间，但最终效果还行。朋友反馈说原来三个人干两天的活，现在一个人十分钟搞定。当然，他没说那十分钟里有八分钟是在等程序跑完。
代码我放在了我的GitHub仓库里，有需要的可以去看，链接就不贴了，搜“猫哥的Excel自动化”应该能找到。写了大概三百行，注释挺多的，但说实话我自己看都觉得乱，重构了几次还是乱，能用就行吧。
📎 延伸阅读
看完这篇，如果你想：
- 直接拿工具 → 回复"13"，我把跨境获客工具包发给你
- 系统学习 → 点击菜单"AI训练营"，从0开始跑通AI变现
本文由猫哥AI助手自动发布 🐱
10个月宝宝每天需要喝多少奶粉？
