全面解析Python读取表格的核心技术方案,涵盖pandas、openpyxl、xlrd、csv模块等主流方式,详解Excel(.xlsx/.xls)、CSV、ODS等格式处理技巧,提供真实项目案例、性能对比、避坑指南与最佳实践,助您高效、稳定、安全地完成数据导入任务。
开始学习在数据驱动决策的时代,表格数据(尤其是Excel和CSV)是企业最常用的格式。据2024年Stack Overflow开发者调查,Python已成为全球最受欢迎的编程语言,而数据处理是其核心应用场景之一。无论是金融分析、电商运营、教育科研还是政府统计,都离不开对表格数据的高效读取与清洗。
然而,许多初学者在实际操作中常遇到以下问题:
本文将系统讲解各主流方案的原理、适用场景、性能表现及常见陷阱,助您精准选型、高效开发。
不同格式的底层结构差异显著,直接影响读取方式:
⚠️ 注意:xlrd自2.0.0起已移除XLSX支持!读取.xlsx文件请改用openpyxl或pandas。
安装Python 3.8+,使用pip安装核心库:
pip install pandas openpyxl xlrd lxml
优先用内置csv模块或pandas.read_csv(),适合轻量级数据
推荐pandas + openpyxl组合,兼顾速度与功能
仅用xlrd 1.2.0(旧版),注意兼容性限制
合并单元格解析、公式值提取、内存优化技巧
Excel 2007+格式(.xlsx)的5种主流处理方式
专为XLSX设计的开源库,支持读写单元格、样式、图表、公式等,是pandas默认引擎。
# 加载工作簿(注意:read_only=True可减少内存占用)
from openpyxl import load_workbook
wb = load_workbook('data.xlsx', data_only=True)
ws = wb['Sheet1']
# 读取单个单元格
cell_value = ws['A2'].value
# 读取整行(跳过表头)
rows = list(ws.iter_rows(min_row=2, values_only=True))
for row in rows:
print(row[0], row[1]) # A列和B列
openpyxl默认返回合并区域左上角值,需手动扩展:
merged_cells = ws.merged_cells.ranges
# 构建合并区域映射表
merged_map = {}
for merged_range in merged_cells:
for row in merged_range:
for col in row:
merged_map[col] = ws[merged_range.min_row][merged_range.min_col].value
read_only=True(内存↓70%,但不可写)wb = load_workbook(..., keep_vba=False)底层自动调用openpyxl/xlrd,提供统一接口,支持数据类型推断、缺失值处理等高级功能。
| 参数 | 说明 | 典型值 |
|---|---|---|
sheet_name |
工作表名/索引(0=第一张) | 'Sheet1' 或 0 |
header |
表头行索引(None=无表头) | 0(默认) |
usecols |
指定列(A:C 或 [0,2]) | 'A,C' 或 [0,2] |
dtype |
强制指定列类型 | {'ID': str, 'Date': 'datetime64'} |
na_values |
自定义缺失值标记 | ['.', 'NA', 'null'] |
import pandas as pd
# 读取所有Sheet
all_sheets = pd.read_excel('sales.xlsx', sheet_name=None)
# 合并(假设各Sheet结构相同)
df = pd.concat(all_sheets.values(), ignore_index=True)
# 处理日期列(自动识别)
df['销售日期'] = pd.to_datetime(df['销售日期'], errors='coerce')
ValueError: Excel file format cannot be determined → 文件损坏或非XLSX格式UnicodeDecodeError → 路径含中文 → 用os.path.abspath()转绝对路径dtype={'日期列': str},后续用pd.to_datetime转换通过COM接口控制Excel进程,支持读写、宏调用、图表操作,适合需要实时操作Excel的场景。
import xlwings as xw
app = xw.App(visible=False) # 后台运行
wb = app.books.open('report.xlsx')
sheet = wb.sheets['数据']
# 读取区域(返回numpy数组)
data = sheet.range('A1:D100').value
df = pd.DataFrame(data[1:], columns=data[0]) # 跳过表头
wb.close()
app.quit()
直接调用Windows COM对象,功能最全但性能较低,需配合pywin32库。
import win32com.client as win32
excel = win32.Dispatch('Excel.Application')
excel.Visible = False
wb = excel.Workbooks.Open(r'C:\data\report.xlsx')
ws = wb.Worksheets('Sheet1')
# 读取最后单元格(用于动态获取范围)
last_row = ws.Cells(ws.Rows.Count, 1).End(-4162).Row # xlUp=-4162
last_col = ws.Cells(1, ws.Columns.Count).End(-4159).Column # xlToLeft=-4159
data = ws.Range(ws.Cells(1, 1), ws.Cells(last_row, last_col)).Value
wb.Close(SaveChanges=False)
excel.Quit()
注意:xlrd 2.0+已移除XLSX支持!读取XLS文件请安装xlrd==1.2.0。
import xlrd
wb = xlrd.open_workbook('old_report.xls')
sheet = wb.sheet_by_name('Sheet1')
# 读取单元格
value = sheet.cell_value(1, 0) # 第2行第1列
# 读取整行(注意:日期是Excel序列号)
row_values = sheet.row_values(1)
# 日期转换
if xlrd.xldate_as_tuple(row_values[2], wb.datemode):
date_val = xlrd.xldate_as_datetime(row_values[2], wb.datemode)
XLRDError: Excel xlsx file; not supported)纯文本表格的4种高效处理方式
无需额外安装,适合简单结构化数据,支持自定义分隔符。
import csv
# 读取为列表
with open('data.csv', encoding='utf-8-sig') as f:
reader = csv.reader(f)
header = next(reader) # 获取表头
for row in reader:
print(row[0], row[1]) # 处理每行
gbk,用utf-8-sig可自动跳过BOM头
支持分块读取、缺失值处理、类型推断,是CSV操作的行业标准。
import pandas as pd
# 每次读取10万行
chunk_iter = pd.read_csv('big_data.csv', chunksize=100000)
for chunk in chunk_iter:
# 处理当前块(如过滤、聚合)
filtered = chunk[chunk['金额'] > 1000]
filtered.to_csv('result.csv', mode='a', header=False, index=False)
速度极快,适合纯数字CSV,但不支持字符串。
import numpy as np
data = np.loadtxt('numbers.csv', delimiter=',', skiprows=1)
print(data.shape) # (10000, 5)
每行返回字典,键为表头,可读性强。
import csv
with open('data.csv', encoding='utf-8-sig') as f:
reader = csv.DictReader(f)
for row in reader:
print(row['姓名'], row['部门'])
表格数据处理的核心引擎
pandas自动识别数值、日期、字符串类型,但需注意陷阱:
dtype={'ID': str}强制parse_dates=['Date']指定converters={'Num': str}Excel中的空单元格、#N/A、#VALUE!在pandas中对应NaN/NaT:
# 读取时指定缺失值标记
df = pd.read_excel('data.xlsx', na_values=['#N/A', 'NA', 'null', ''])
# 填充缺失值
df['价格'] = df['价格'].fillna(df['价格'].mean()) # 均值填充
df['日期'] = df['日期'].fillna(method='ffill') # 前向填充
Excel日期本质是序列号(1900-01-01=1),pandas自动转换但可能偏移:
dateutil校准
import pandas as pd
from dateutil.parser import parse
# 修复日期偏移
df['日期'] = pd.to_datetime(df['日期'], unit='D', origin='1899-12-30') # Excel Windows基准
category替代字符串(内存↓80%)chunksize参数df.drop_duplicates(inplace=True)# 类型优化示例
df['城市'] = df['城市'].astype('category')
df['是否 VIP'] = df['是否 VIP'].astype('bool')
XLS文件读取的最后堡垒
仅xlrd ≤1.2.0支持XLS,新版本需降级:
pip uninstall xlrd
pip install xlrd==1.2.0
XLRDError: Excel xlsx file; not supported=SUM(A1:A10)),非计算结果xlrd.xldate_as_datetime()转换import xlrd
wb = xlrd.open_workbook('old_data.xls')
sheet = wb.sheet_by_index(0)
# 读取日期单元格
cell = sheet.cell(1, 2)
if cell.ctype == xlrd.XL_CELL_DATE:
date_val = xlrd.xldate_as_datetime(cell.value, wb.datemode)
print(date_val.strftime('%Y-%m-%d'))
若必须读取XLS,可考虑:
libreoffice --headless --convert-to xlsx old.xls超越读取:格式保留、样式复制、公式计算
读取时保留字体、颜色、边框等样式:
from openpyxl import load_workbook
wb = load_workbook('styled.xlsx')
ws = wb.active
# 读取单元格样式
cell = ws['A2']
font_color = cell.font.color.rgb # '#FF0000' (RGB)
fill_color = cell.fill.start_color.rgb
from openpyxl import Workbook
from copy import copy
wb_new = Workbook()
ws_new = wb_new.active
# 复制格式
for row in ws.iter_rows(min_row=1, max_row=10):
for cell in row:
new_cell = ws_new.cell(row=cell.row, column=cell.column, value=cell.value)
new_cell.font = copy(cell.font)
new_cell.fill = copy(cell.fill)
openpyxl默认返回公式字符串,但可计算值:
from openpyxl.utils import FORMULAE
from openpyxl.formula import Tokenizer
# 简单公式计算(仅支持基础运算)
def calc_formula(formula):
if not formula.startswith('='):
return formula
# 去掉等号
expr = formula[1:]
return eval(expr.replace('$', '')) # 注意:仅限简单公式
LibreOffice格式的处理方法
安装依赖:pip install odfpy
import pandas as pd
# 读取ODS文件
df = pd.read_excel('data.ods', engine='odf')
更底层的ODS解析工具:
import odf
from odf.table import Table, TableRow, TableCell
def read_ods(filepath):
doc = odf.opendoc(filepath)
table = doc.spreadsheet.tables[0]
data = []
for row in table.rows:
cells = [cell.innerText for cell in row.cells]
data.append(cells)
return data
libreoffice --headless --convert-to xlsx data.ods
不同场景下的最优选择
| 方案 | 支持格式 | 是否保留格式 | 性能(10万行) | 适用场景 |
|---|---|---|---|---|
| pandas.read_excel | XLSX/XLS(旧版) | 否(仅数据) | ★★★☆ | 通用数据处理、数据分析 |
| openpyxl | XLSX | 是 | ★★★ | 需保留样式/公式/图表 |
| csv模块 | CSV | 否 | ★★★★★ | 轻量级数据、无格式需求 |
| xlwings | 所有Excel格式 | 是 | ★☆ | 需控制Excel进程、调用宏 |
| win32com | 所有Excel格式 | 是 | ★ | Windows专属、深度集成Excel |
| 方案 | 读取时间 | 内存占用 |
|---|---|---|
| pandas + openpyxl | 2.8s | 420MB |
| openpyxl (read_only) | 3.1s | 280MB |
| pandas.read_csv | 1.2s | 180MB |
| csv模块 | 1.5s | 150MB |
真实业务场景解决方案
需求:读取多张XLSX订单表,合并后清洗数据,导出统计报表
import pandas as pd
import glob
import os
# 合并多文件
files = glob.glob('orders/*.xlsx')
all_data = pd.concat(
[pd.read_excel(f, dtype={'订单号': str}) for f in files],
ignore_index=True
)
# 清洗数据
all_data['订单金额'] = pd.to_numeric(all_data['订单金额'], errors='coerce')
all_data['订单时间'] = pd.to_datetime(all_data['订单时间'], errors='coerce')
all_data.dropna(subset=['订单金额', '订单时间'], inplace=True)
# 导出结果
all_data.to_excel('cleaned_orders.xlsx', index=False)
需求:读取Excel成绩表,按班级计算平均分,生成带图表的报告
import pandas as pd
import matplotlib.pyplot as plt
# 读取数据
df = pd.read_excel('scores.xlsx', sheet_name='高一')
# 按班级统计
class_avg = df.groupby('班级')['数学'].mean().sort_values()
# 绘制柱状图
class_avg.plot(kind='bar', color='#037ef3', title='各班数学平均分')
plt.savefig('score_report.png')
需求:每日读取CSV流水数据,生成带格式的日报Excel
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
# 读取流水数据
df = pd.read_csv('daily_flow.csv', encoding='utf-8-sig')
# 计算汇总
summary = df.groupby('类型')['金额'].sum().reset_index()
# 导出到Excel模板
with pd.ExcelWriter('report.xlsx', engine='openpyxl') as writer:
summary.to_excel(writer, sheet_name='汇总', index=False)
# 设置样式
wb = writer.book
ws = writer.sheets['汇总']
for cell in ws['A1']:
cell.font = Font(bold=True)
for cell in ws['B2':ws.cell(row=summary.shape[0]+1, column=2)]:
cell.fill = PatternFill('solid', start_color='E6F2FF')
高频问题深度解答
原因:Excel内部将日期存储为序列号(1900-01-01=1),pandas默认保留原始值。
解决方案:
pd.read_excel(..., parse_dates=['Date'])pd.to_datetime(df['Date'], unit='D', origin='1899-12-30')openpyxl方案:
# 构建合并区域映射
merged_map = {}
for merged_range in ws.merged_cells.ranges:
for row in merged_range:
for col in row:
merged_map[(col.row, col.column)] = ws[merged_range.min_row][merged_range.min_col].value
# 读取时查询映射表
value = merged_map.get((row, col), ws.cell(row, col).value)
解决方案:
pd.read_excel(..., chunksize=100000)read_only=True(内存↓70%)关键点:
=SUM(A1:A10))data_only=True可读取计算结果(需Excel曾计算过)解决方案:
os.path.abspath('数据/订单.xlsx')encoding='utf-8-sig'真相:xlrd 2.0+已移除XLSX支持(官方公告),仅支持XLS(≤1.2.0)。
正确做法:
xlrd==1.2.0pandas高效方案:
# 只读取A、C列和前1000行
df = pd.read_excel('data.xlsx',
usecols=['A', 'C'],
nrows=1000,
skiprows=0)
两种方案:
pd.read_excel(file, sheet_name=None) → 返回字典[pd.read_excel(file, sheet_name=i) for i in range(sheet_count)]方法:
# 跳过前5行(含表头)
df = pd.read_excel('data.xlsx', skiprows=5)
方案:
Encryption not supported)