Python 读取表格

官网:www.xservx.com|备案号:蜀ICP备2026035467号-3

Python 读取表格|从零掌握Excel、CSV、ODS数据处理

全面解析Python读取表格的核心技术方案,涵盖pandas、openpyxl、xlrd、csv模块等主流方式,详解Excel(.xlsx/.xls)、CSV、ODS等格式处理技巧,提供真实项目案例、性能对比、避坑指南与最佳实践,助您高效、稳定、安全地完成数据导入任务。

开始学习

⚡ 为什么需要学习Python读取表格?

在数据驱动决策的时代,表格数据(尤其是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

优先用内置csv模块或pandas.read_csv(),适合轻量级数据

③ 读取XLSX

推荐pandas + openpyxl组合,兼顾速度与功能

④ 处理XLS

仅用xlrd 1.2.0(旧版),注意兼容性限制

⑤ 高级场景

合并单元格解析、公式值提取、内存优化技巧

〔XLSX读取方案〕

Excel 2007+格式(.xlsx)的5种主流处理方式

① openpyxl:纯Python首选方案

专为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%,但不可写)
  • 避免频繁访问ws.cell(),改用iter_rows()遍历
  • 关闭日志:wb = load_workbook(..., keep_vba=False)

② pandas.read_excel():数据分析首选

底层自动调用openpyxl/xlrd,提供统一接口,支持数据类型推断、缺失值处理等高级功能。

核心优势:自动识别表头、处理日期、支持多Sheet、无缝衔接数据分析流程

参数详解

参数 说明 典型值
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']

实战案例:读取多Sheet并合并

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转换

③ xlwings:与Excel交互的“桥梁”

通过COM接口控制Excel进程,支持读写、宏调用、图表操作,适合需要实时操作Excel的场景。

⚠️ 仅限Windows:需安装Microsoft Excel,Linux/macOS不可用

基础用法

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()

适用场景

  • 自动化生成带格式报表
  • 调用Excel内置函数(如VLOOKUP、PivotTable)
  • 处理含ActiveX控件的复杂模板

④ win32com:Windows底层COM接口

直接调用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()

优势与局限

  • 优势:支持所有Excel功能(包括VBA宏)
  • 局限:启动Excel进程开销大,多线程不可用

⑤ xlrd旧版(≤1.2.0):仅支持XLS

注意: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)

⚠️ 重要提醒

  • 不支持XLSX格式(报错:XLRDError: Excel xlsx file; not supported)
  • 无法读取公式计算值(仅返回公式字符串)
  • 建议仅用于读取旧版XLS文件

〔CSV读取方案〕

纯文本表格的4种高效处理方式

① 内置csv模块:轻量级标准方案

无需额外安装,适合简单结构化数据,支持自定义分隔符。

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])  # 处理每行
编码技巧:Windows保存的CSV常为gbk,用utf-8-sig可自动跳过BOM头

② pandas.read_csv():数据处理首选

支持分块读取、缺失值处理、类型推断,是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)

③ numpy.loadtxt():数值型数据专用

速度极快,适合纯数字CSV,但不支持字符串。

import numpy as np
data = np.loadtxt('numbers.csv', delimiter=',', skiprows=1)
print(data.shape)  # (10000, 5)

④ csv.DictReader:字典式读取

每行返回字典,键为表头,可读性强。

import csv
with open('data.csv', encoding='utf-8-sig') as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row['姓名'], row['部门'])

〔Pandas深度指南〕

表格数据处理的核心引擎

① 数据类型智能推断

pandas自动识别数值、日期、字符串类型,但需注意陷阱:

② 处理缺失值

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自动转换但可能偏移:

注意:Excel for Mac默认日期起始为1904(偏移1462天),需用dateutil校准
import pandas as pd
from dateutil.parser import parse
# 修复日期偏移
df['日期'] = pd.to_datetime(df['日期'], unit='D', origin='1899-12-30')  # Excel Windows基准

④ 内存优化技巧

# 类型优化示例
df['城市'] = df['城市'].astype('category')
df['是否 VIP'] = df['是否 VIP'].astype('bool')

〔xlrd专项解析〕

XLS文件读取的最后堡垒

① 安装与版本注意

仅xlrd ≤1.2.0支持XLS,新版本需降级:

pip uninstall xlrd
pip install xlrd==1.2.0

② 读取技巧与限制

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文件建议先用Excel另存为XLSX,再用openpyxl处理

若必须读取XLS,可考虑:

〔openpyxl高级应用〕

超越读取:格式保留、样式复制、公式计算

① 保留Excel格式

读取时保留字体、颜色、边框等样式:

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('$', ''))  # 注意:仅限简单公式

〔ODS读取方案〕

LibreOffice格式的处理方法

① 使用pandas + odfpy

安装依赖:pip install odfpy

import pandas as pd
# 读取ODS文件
df = pd.read_excel('data.ods', engine='odf')

② 使用odsio库

更底层的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

③ 格式转换建议

最佳实践:ODS文件建议先转为XLSX(用LibreOffice CLI),再用openpyxl处理
libreoffice --headless --convert-to xlsx data.ods

〔方案对比〕

不同场景下的最优选择

方案 支持格式 是否保留格式 性能(10万行) 适用场景
pandas.read_excel XLSX/XLS(旧版) 否(仅数据) ★★★☆ 通用数据处理、数据分析
openpyxl XLSX 是 ★★★ 需保留样式/公式/图表
csv模块 CSV 否 ★★★★★ 轻量级数据、无格式需求
xlwings 所有Excel格式 是 ★☆ 需控制Excel进程、调用宏
win32com 所有Excel格式 是 ★ Windows专属、深度集成Excel

① 性能实测(10万行×20列)

方案 读取时间 内存占用
pandas + openpyxl 2.8s 420MB
openpyxl (read_only) 3.1s 280MB
pandas.read_csv 1.2s 180MB
csv模块 1.5s 150MB

② 决策流程图

Step 1:确认文件格式

  • XLSX → 进入Step 2
  • XLS → 用xlrd 1.2.0 或 转换格式
  • CSV → 直接用pandas.read_csv
  • ODS → 转XLSX 或 用odfpy

Step 2:是否需保留格式?

  • 是 → openpyxl
  • 否 → pandas.read_excel

Step 3:文件是否超大(>500MB)?

  • 是 → 分块读取(chunksize)+ 内存优化
  • 否 → 普通读取

〔实战案例〕

真实业务场景解决方案

① 电商订单数据清洗

需求:读取多张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')

〔网友最关心的10个问题〕

高频问题深度解答

① 为什么读取Excel后日期变成数字?

原因:Excel内部将日期存储为序列号(1900-01-01=1),pandas默认保留原始值。

解决方案:

② 如何读取合并单元格的值?

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)

③ 读取超大Excel(>1GB)内存溢出怎么办?

解决方案:

④ 如何处理含公式的单元格?

关键点:

⑤ 中文路径报错UnicodeDecodeError?

解决方案:

⑥ 为什么xlrd无法读取XLSX?

真相:xlrd 2.0+已移除XLSX支持(官方公告),仅支持XLS(≤1.2.0)。

正确做法:

⑦ 如何读取特定行/列?

pandas高效方案:

# 只读取A、C列和前1000行
df = pd.read_excel('data.xlsx',
                   usecols=['A', 'C'],
                   nrows=1000,
                   skiprows=0)

⑧ 如何读取所有Sheet?

两种方案:

  1. 字典合并:pd.read_excel(file, sheet_name=None) → 返回字典
  2. 循环读取:[pd.read_excel(file, sheet_name=i) for i in range(sheet_count)]

⑨ 如何跳过前N行?

方法:

# 跳过前5行(含表头)
df = pd.read_excel('data.xlsx', skiprows=5)

⑩ 如何读取含密码保护的Excel?

方案: