当前位置：首页 > news >正文

【Python自动化】 21.1 Pandas 读取 Excel 文件的完整指南

news 2025/9/7 5:33:47

一、基础读取方法

1. 最简单的读取方式

import pandas as pd# 基本读取
df = pd.read_excel("data.xlsx")
print(df.head())

2. 指定工作表

# 读取指定工作表
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")# 读取第二个工作表（索引从0开始）
df = pd.read_excel("data.xlsx", sheet_name=1)# 读取所有工作表，返回字典
all_sheets = pd.read_excel("data.xlsx", sheet_name=None)

二、常用参数详解

1. 表头和索引控制

# 跳过表头（从第3行开始读取）
df = pd.read_excel("data.xlsx", header=2)# 没有表头，自动生成列名
df = pd.read_excel("data.xlsx", header=None)# 自定义列名
df = pd.read_excel("data.xlsx", names=['ID', 'Name', 'Age'])# 指定索引列
df = pd.read_excel("data.xlsx", index_col=0)  # 第一列作为索引

2. 数据范围控制

# 只读取特定列
df = pd.read_excel("data.xlsx", usecols="A:C")  # 读取A-C列
df = pd.read_excel("data.xlsx", usecols=[0, 2, 4])  # 读取第0,2,4列
df = pd.read_excel("data.xlsx", usecols=["Name", "Age"])  # 读取指定列名# 跳过行和列
df = pd.read_excel("data.xlsx", skiprows=3)  # 跳过前3行
df = pd.read_excel("data.xlsx", skipfooter=5)  # 跳过最后5行
df = pd.read_excel("data.xlsx", skiprows=[0, 2, 4])  # 跳过指定行# 读取特定行数
df = pd.read_excel("data.xlsx", nrows=100)  # 只读取前100行

3. 数据类型处理

# 指定数据类型
dtype_dict = {"ID": str, "Age": int, "Salary": float}
df = pd.read_excel("data.xlsx", dtype=dtype_dict)# 处理缺失值
df = pd.read_excel("data.xlsx", na_values=["", "NA", "N/A", "null"])# 日期解析
df = pd.read_excel("data.xlsx", parse_dates=["BirthDate", "HireDate"])

三、高级读取技巧

1. 读取多个工作表

# 方法1：读取所有工作表
excel_file = pd.ExcelFile("data.xlsx")
sheet_names = excel_file.sheet_names# 分别读取每个工作表
for sheet in sheet_names:df = pd.read_excel("data.xlsx", sheet_name=sheet)print(f"工作表 '{sheet}' 的数据形状: {df.shape}")# 方法2：一次性读取所有工作表
all_data = {}
with pd.ExcelFile("data.xlsx") as xls:for sheet_name in xls.sheet_names:all_data[sheet_name] = pd.read_excel(xls, sheet_name)

2. 读取特定区域

# 读取特定单元格范围
df = pd.read_excel("data.xlsx", usecols="A:D", nrows=50)# 使用openpyxl引擎进行精细控制
df = pd.read_excel("data.xlsx", engine='openpyxl')

四、错误处理和验证

1. 完整的错误处理示例

import pandas as pd
import osdef read_excel_safely(file_path, **kwargs):"""安全读取Excel文件的函数"""try:# 检查文件是否存在if not os.path.exists(file_path):raise FileNotFoundError(f"文件不存在: {file_path}")# 检查文件格式if not file_path.endswith(('.xlsx', '.xls')):raise ValueError("文件格式必须是 .xlsx 或 .xls")# 读取文件df = pd.read_excel(file_path, **kwargs)# 验证数据if df.empty:print("⚠️  警告: 读取的数据为空")print(f"✅ 成功读取文件: {file_path}")print(f"📊 数据形状: {df.shape}")print(f"📝 列名: {list(df.columns)}")return dfexcept FileNotFoundError as e:print(f"❌ 文件错误: {e}")return Noneexcept PermissionError:print("❌ 权限错误: 无法读取文件，可能正在被其他程序使用")return Noneexcept Exception as e:print(f"❌ 读取错误: {e}")return None# 使用示例
df = read_excel_safely("data.xlsx",sheet_name="Sheet1",header=0,usecols=["ID", "Name", "Age"]
)

2. 文件信息检测

def get_excel_info(file_path):"""获取Excel文件信息"""try:with pd.ExcelFile(file_path) as xls:info = {"file_path": file_path,"sheet_names": xls.sheet_names,"sheet_count": len(xls.sheet_names),"file_size": f"{os.path.getsize(file_path) / 1024:.2f} KB"}# 获取每个工作表的信息for sheet in info["sheet_names"]:df_temp = pd.read_excel(xls, sheet_name=sheet, nrows=5)info[f"{sheet}_shape"] = df_temp.shapeinfo[f"{sheet}_columns"] = list(df_temp.columns)return infoexcept Exception as e:return {"error": str(e)}# 使用示例
file_info = get_excel_info("data.xlsx")
print("文件信息:", file_info)

五、实际应用示例

1. 读取并处理数据

def process_excel_data(file_path):"""读取并处理Excel数据"""# 读取数据df = pd.read_excel(file_path,sheet_name="Sales",usecols=["Date", "Product", "Quantity", "Price"],parse_dates=["Date"],na_values=["N/A", ""])# 数据清洗df = df.dropna()  # 删除空值df = df[df["Quantity"] > 0]  # 过滤无效数量# 计算总销售额df["Total"] = df["Quantity"] * df["Price"]# 数据分析summary = {"total_sales": df["Total"].sum(),"average_sale": df["Total"].mean(),"total_transactions": len(df),"unique_products": df["Product"].nunique()}return df, summary# 使用示例
data, stats = process_excel_data("sales_data.xlsx")
print("数据摘要:", stats)
print("前5行数据:")
print(data.head())

2. 批量处理多个文件

import globdef batch_process_excel_files(pattern):"""批量处理多个Excel文件"""results = {}excel_files = glob.glob(pattern)for file in excel_files:try:df = pd.read_excel(file)results[file] = {"rows": len(df),"columns": len(df.columns),"memory_usage": df.memory_usage(deep=True).sum() / 1024 / 1024  # MB}except Exception as e:results[file] = {"error": str(e)}return results# 使用示例
results = batch_process_excel_files("*.xlsx")
for file, info in results.items():print(f"{file}: {info}")

六、引擎选择和建议

1. 不同引擎的比较

# 自动选择引擎（默认）
df = pd.read_excel("data.xlsx")# 明确指定引擎
df = pd.read_excel("data.xlsx", engine='openpyxl')  # 用于 .xlsx
df = pd.read_excel("data.xlsx", engine='xlrd')      # 用于 .xls# 推荐做法：根据文件扩展名选择引擎
def read_excel_smart(file_path):if file_path.endswith('.xlsx'):return pd.read_excel(file_path, engine='openpyxl')elif file_path.endswith('.xls'):return pd.read_excel(file_path, engine='xlrd')else:raise ValueError("不支持的文件格式")

2. 性能优化建议

# 只读取需要的列
df = pd.read_excel("large_file.xlsx", usecols=["id", "name", "value"])# 分块读取大文件
chunk_size = 10000
chunks = pd.read_excel("large_file.xlsx", chunksize=chunk_size)for i, chunk in enumerate(chunks):print(f"处理第 {i+1} 块数据, 形状: {chunk.shape}")# 处理每个数据块

七、常见问题解决方案

1. 编码问题

# 处理中文或其他特殊字符
df = pd.read_excel("data.xlsx", engine='openpyxl')# 如果列名有特殊字符，可以重命名
df.columns = [col.strip().replace(' ', '_') for col in df.columns]

2. 日期格式问题

# 强制解析日期列
df = pd.read_excel("data.xlsx", parse_dates=['date_column'])# 自定义日期格式
df['date_column'] = pd.to_datetime(df['date_column'], format='%Y-%m-%d')

3. 内存优化

# 指定数据类型以减少内存使用
dtype_dict = {'id': 'int32','age': 'int8', 'price': 'float32','category': 'category'
}
df = pd.read_excel("data.xlsx", dtype=dtype_dict)