Pandas读取Excel失败案例分析
- Published on
- ...
- Authors
- Name
- Huashan
- @herohuashan
日期: 2025-10-16
标签: #Python #Pandas #Excel #Debug #经验总结
🔍 问题描述
在处理Excel文件（AP_LF 300 in DI Water 50 1Hz_1_20251016_100357上午.xlsx）时，使用pandas的read_excel()方法读取Oscillation工作表的B列数据时遇到异常：
- 肉眼可见: Excel中B2-B15有数据（2.64, -0.07, 2.64等17个数值）
- pandas读取: 只能读到3个值（B16-B18），前15行全是NaN
- 实际情况: B1为空，B2-B18都有数据
🔬 排查过程
1. 初步尝试（失败❌）
df = pd.read_excel(file, sheet_name='Oscillation', header=None)
b_column = df[1]
2. 尝试不同的pandas参数（均失败❌）
header=None- 失败header=0- B列被命名为__EMPTY，但数据仍是NaNskiprows=1- 失败engine='openpyxl'- 失败
3. 使用openpyxl直接读取（成功✅）
import openpyxl
wb = openpyxl.load_workbook(file, data_only=True)
ws = wb['Oscillation']
for row in range(1, 19):
value = ws[f'B**row**'].value
print(f"B**row**: **value**")
4. 验证数据存在
使用SheetJS（JavaScript）检查原始Excel文件结构：
const cell = oscillationSheet['B2'];
console.log(cell.v); // 2.64
console.log(cell.t); // 'n' (数字类型)
✅ 确认数据确实存在于Excel文件中。
🎯 根本原因分析
pandas的"智能"识别机制失效
1. 数据区域判断错误
- Excel内部的
dimension标签定义了工作表范围为A1:C18 - B1单元格为空
- pandas在解析时可能因为B1为空+某些内部标记异常，错误判断B2-B15不在有效数据区域内
2. pandas vs openpyxl的读取差异
pandas.read_excel():
└─ 调用openpyxl
└─ 智能识别数据区域（可能出错）
└─ 数据类型转换和优化
└─ 跳过"无效"区域
openpyxl.iter_rows():
└─ 直接按行迭代
└─ 读取所有单元格
└─ 不做智能判断（更可靠）
3. 可能的文件格式问题
- Excel文件可能有特殊的内部标记
- B列前15行可能被标记为"空白"而非NULL
- pandas的解析器无法正确处理这种边缘情况
✅ 解决方案
最佳方案：绕过pandas直接读取
import pandas as pd
import openpyxl
def read_excel_robust(filepath, sheet_name):
"""
使用openpyxl直接读取，绕过pandas的智能识别
适用于pandas读取失败的"奇怪"Excel文件
"""
wb = openpyxl.load_workbook(filepath, data_only=True)
ws = wb[sheet_name]
# 直接迭代所有行，不做任何假设
raw_rows = [row for row in ws.iter_rows(values_only=True)]
wb.close()
# 手动构建DataFrame
df = pd.DataFrame(raw_rows)
return df
# 使用示例
df = read_excel_robust('file.xlsx', 'Oscillation')
b_column = df[1] # B列数据（索引从0开始）
方案优势
✅ 完全可靠 - 不依赖pandas的数据区域判断
✅ 原始数据 - 读取Excel实际单元格内容
✅ 适应性强 - 适用于各种"异常"Excel文件
✅ 透明可控 - 清楚知道每一步在做什么
📚 关键参数说明
openpyxl.load_workbook(
filename,
data_only=True, # ⭐ 读取公式的计算值，而非公式本身
read_only=False # 如果只读数据，设为True可提升性能
)
🚀 生产环境最佳实践
def read_oscillation_columns(filepath: str, retries: int = 2):
"""
生产级别的Excel读取函数
包含重试机制、错误处理、资源管理
"""
last_err = None
# 重试机制（处理文件被占用的情况）
for attempt in range(retries + 1):
try:
wb = openpyxl.load_workbook(filepath, data_only=True)
break
except PermissionError as e:
last_err = e
if attempt < retries:
import time
time.sleep(0.4)
except Exception as e:
return None
else:
return None
try:
if 'Oscillation' not in wb.sheetnames:
return None
ws = wb['Oscillation']
raw_rows = [r for r in ws.iter_rows(values_only=True)]
# 确保资源释放
wb.close()
if not raw_rows:
return None
df = pd.DataFrame(raw_rows)
return df
except Exception:
wb.close()
return None
💡 经验总结
核心教训
不要盲目信任pandas的"智能"功能
- pandas的智能识别在大多数情况下很好用
- 但遇到特殊格式的Excel文件可能失效
- 关键数据处理时，直接读取更可靠
分层诊断
pandas读取失败 ↓ 尝试不同的pandas参数 ↓ 使用底层库（openpyxl）直接读取 ↓ 检查原始文件结构（SheetJS/openpyxl）生产环境的健壮性设计
- 重试机制（处理文件占用）
- 完善的错误处理
- 资源管理（确保wb.close()）
- 日志记录（便于调试）
适用场景
这个方案特别适用于：
- ❌ pandas读取返回异常数据
- ❌ 数据区域判断错误
- ❌ 特殊格式的Excel文件
- ❌ 需要精确控制读取过程的场景
性能考虑
- openpyxl直接读取略慢于pandas优化后的读取
- 但对于"奇怪"的文件，可靠性 > 性能
- 如果文件很大，可以考虑：
read_only=True参数- 只读取需要的特定范围
- 使用
iter_rows(min_row=x, max_row=y)限定范围
🔗 相关资源
- openpyxl官方文档
- pandas read_excel文档
- 项目代码:
oscillation_aggregator.py
🔗 相关笔记
最后更新: 2025-10-16
作者: 你的名字