Pandas 数据读写：Excel 文件

本教程介绍如何使用 Pandas 读写 Excel 文件（.xlsx, .xls）。

注意: 需要安装 openpyxl 或 xlrd 库

pip install openpyxl xlrd

1. 基础读写操作

In [1]:

import pandas as pd
import numpy as np
# 创建示例数据
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'age': [25, 30, 35, 28, 32],
    'score': [88.5, 92.0, 75.5, 88.0, 95.0],
    'city': ['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen', 'Hangzhou']
})
print("原始数据:")
print(df)
# 保存到 Excel 文件
df.to_excel('sample_data.xlsx', sheet_name='Sheet1', index=False)
print("\n已保存到 sample_data.xlsx")
# 读取 Excel 文件
df_read = pd.read_excel('sample_data.xlsx')
print("\n从 Excel 读取的数据:")
print(df_read)

原始数据:
      name  age  score       city
0    Alice   25   88.5    Beijing
1      Bob   30   92.0   Shanghai
2  Charlie   35   75.5  Guangzhou
3    David   28   88.0   Shenzhen
4      Eve   32   95.0   Hangzhou

已保存到 sample_data.xlsx

从 Excel 读取的数据:
      name  age  score       city
0    Alice   25   88.5    Beijing
1      Bob   30   92.0   Shanghai
2  Charlie   35   75.5  Guangzhou
3    David   28   88.0   Shenzhen
4      Eve   32   95.0   Hangzhou

2. 多工作表操作

In [2]:

# 创建多个 DataFrame
df1 = pd.DataFrame({'A': [1, 2, 3], 'B': ['a', 'b', 'c']})
df2 = pd.DataFrame({'X': [10, 20, 30], 'Y': ['x', 'y', 'z']})
df3 = pd.DataFrame({'Name': ['Tom', 'Jerry'], 'Score': [85, 90]})
# 写入多个工作表
with pd.ExcelWriter('multi_sheet.xlsx') as writer:
    df1.to_excel(writer, sheet_name='数据1', index=False)
    df2.to_excel(writer, sheet_name='数据2', index=False)
    df3.to_excel(writer, sheet_name='数据3', index=False)
print("已保存多工作表文件")
# 读取指定工作表
df_sheet1 = pd.read_excel('multi_sheet.xlsx', sheet_name='数据1')
df_sheet2 = pd.read_excel('multi_sheet.xlsx', sheet_name='数据2')
print("\n读取 数据1:")
print(df_sheet1)
print("\n读取 数据2:")
print(df_sheet2)

已保存多工作表文件
读取 数据1:
   A  B
0  1  a
1  2  b
2  3  c
读取 数据2:
    X  Y
0  10  x
1  20  y
2  30  z

3. 读取所有工作表

In [3]:

# 读取所有工作表（返回字典）
all_sheets = pd.read_excel('multi_sheet.xlsx', sheet_name=None)
print(f"工作表列表: {list(all_sheets.keys())}")
for sheet_name, df_sheet in all_sheets.items():
    print(f"\n=== {sheet_name} ===")
    print(df_sheet)

工作表列表: ['数据1', '数据2', '数据3']
=== 数据1 ===
   A  B
0  1  a
1  2  b
2  3  c
=== 数据2 ===
    X  Y
0  10  x
1  20  y
2  30  z
=== 数据3 ===
    Name  Score
0    Tom     85
1  Jerry     90

4. 指定读取参数

In [4]:

# 指定列
df_cols = pd.read_excel('sample_data.xlsx', usecols=['name', 'age'])
print("只读取 name, age 列:")
print(df_cols)
# 指定列索引（A=0, B=1...）
df_cols_idx = pd.read_excel('sample_data.xlsx', usecols=[0, 2])
print("\n读取第1和第3列:")
print(df_cols_idx)
# 跳过行
df_skip = pd.read_excel('sample_data.xlsx', skiprows=2)
print("\n跳过前2行:")
print(df_skip)
# 只读前n行
df_nrows = pd.read_excel('sample_data.xlsx', nrows=3)
print("\n只读前3行:")
print(df_nrows)

只读取 name, age 列:
      name  age
0    Alice   25
1      Bob   30
2  Charlie   35
3    David   28
4      Eve   32
读取第1和第3列:
      name  score
0    Alice   88.5
1      Bob   92.0
2  Charlie   75.5
3    David   88.0
4      Eve   95.0
跳过前2行:
       Bob  30    92   Shanghai
0  Charlie  35  75.5  Guangzhou
1    David  28  88.0   Shenzhen
2      Eve  32  95.0   Hangzhou
只读前3行:
      name  age  score       city
0    Alice   25   88.5    Beijing
1      Bob   30   92.0   Shanghai
2  Charlie   35   75.5  Guangzhou

5. 指定数据类型和日期解析

In [5]:

# 创建含日期的数据
df_date = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'birth_date': ['1998-01-15', '1993-06-20', '1988-12-05'],
    'salary': [5000, 6000, 7000]
})
df_date.to_excel('with_date.xlsx', index=False)
# 读取时解析日期
df_parsed = pd.read_excel('with_date.xlsx', parse_dates=['birth_date'])
print("解析日期列:")
print(df_parsed)
print(f"\nbirth_date 类型: {df_parsed['birth_date'].dtype}")
# 指定数据类型
df_dtype = pd.read_excel('sample_data.xlsx', dtype={'age': 'int32', 'score': 'float32'})
print("\n指定数据类型:")
print(df_dtype.dtypes)

解析日期列:
      name birth_date  salary
0    Alice 1998-01-15    5000
1      Bob 1993-06-20    6000
2  Charlie 1988-12-05    7000
birth_date 类型: datetime64[ns]
指定数据类型:
name      object
age        int32
score    float32
city      object
dtype: object

6. 写入参数设置

In [6]:

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'score': [88.5, 92.0, 75.5]
})
# 不保存索引
df.to_excel('output1.xlsx', index=False)
# 保存索引
df.to_excel('output2.xlsx', index=True)
# 只保存指定列
df.to_excel('output3.xlsx', columns=['name', 'age'], index=False)
# 从指定行开始写入
with pd.ExcelWriter('output4.xlsx') as writer:
    df.to_excel(writer, sheet_name='Sheet1', startrow=2, startcol=1, index=False)
print("已生成多个 Excel 文件")

已生成多个 Excel 文件

7. 追加到现有文件

In [7]:

# 模式 'a' 表示追加（需要 openpyxl >= 3.0.0）
df1 = pd.DataFrame({'A': [1, 2], 'B': ['a', 'b']})
df2 = pd.DataFrame({'A': [3, 4], 'B': ['c', 'd']})
# 先创建文件
df1.to_excel('append_test.xlsx', sheet_name='Sheet1', index=False)
# 追加新工作表
with pd.ExcelWriter('append_test.xlsx', mode='a', engine='openpyxl') as writer:
    df2.to_excel(writer, sheet_name='Sheet2', index=False)
# 验证
all_sheets = pd.read_excel('append_test.xlsx', sheet_name=None)
print(f"工作表: {list(all_sheets.keys())}")
for name, sheet in all_sheets.items():
    print(f"\n{name}:")
    print(sheet)

工作表: ['Sheet1', 'Sheet2']
Sheet1:
   A  B
0  1  a
1  2  b
Sheet2:
   A  B
0  3  c
1  4  d

8. 格式化 Excel 输出

In [8]:

# 使用 Styler 格式化（需要 openpyxl）
df = pd.DataFrame({
    '产品': ['A', 'B', 'C', 'D'],
    '销售额': [1500, 2300, 1800, 3200],
    '利润': [300, 460, 360, 640]
})
# 创建样式化对象
styled = df.style\
    .format({'销售额': '¥{:,.0f}', '利润': '¥{:,.0f}'})\
    .highlight_max(color='lightgreen')\
    .highlight_min(color='lightcoral')
# 保存样式
styled.to_excel('styled_output.xlsx', index=False)
print("已保存带样式的 Excel 文件")

已保存带样式的 Excel 文件

9. 条件格式设置

In [9]:

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows
# 创建 DataFrame
df = pd.DataFrame({
    '姓名': ['Alice', 'Bob', 'Charlie', 'David'],
    '成绩': [85, 92, 78, 95]
})
# 保存基础数据
df.to_excel('formatted.xlsx', index=False)
# 使用 openpyxl 添加格式
wb = load_workbook('formatted.xlsx')
ws = wb.active
# 设置表头格式
for cell in ws[1]:
    cell.font = Font(bold=True, color='FFFFFF')
    cell.fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
    cell.alignment = Alignment(horizontal='center')
# 设置数据格式
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        if cell.value >= 90:
            cell.fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
            cell.font = Font(color='006100')
wb.save('formatted.xlsx')
print("已保存格式化后的 Excel 文件")

已保存格式化后的 Excel 文件

10. 读取特定单元格范围

In [10]:

# 创建测试文件
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = 'Header'
ws['A2'] = 'Data1'
ws['A3'] = 'Data2'
ws['B2'] = 100
ws['B3'] = 200
wb.save('range_test.xlsx')
# 使用 openpyxl 读取特定范围
wb = load_workbook('range_test.xlsx')
ws = wb.active
# 读取特定单元格
print(f"A1 单元格: {ws['A1'].value}")
print(f"B2 单元格: {ws['B2'].value}")
# 读取范围
data = []
for row in ws['A1':'B3']:
    data.append([cell.value for cell in row])
df_range = pd.DataFrame(data[1:], columns=data[0])
print("\n读取范围 A1:B3:")
print(df_range)

A1 单元格: Header

B2 单元格: 100
读取范围 A1:B3:
  Header  None
0  Data1   100
1  Data2   200

11. 处理大型 Excel 文件

In [11]:

# 对于大型文件，可以使用 chunksize 分块读取
# pd.read_excel('large_file.xlsx', chunksize=1000)
# 或者使用 openpyxl 的 read_only 模式
from openpyxl import load_workbook
# 创建大文件示例
large_df = pd.DataFrame({
    'id': range(1000),
    'value': np.random.randn(1000)
})
large_df.to_excel('large_file.xlsx', index=False)
# 使用 read_only 模式读取（节省内存）
wb = load_workbook('large_file.xlsx', read_only=True)
ws = wb.active
# 逐行读取
count = 0
for row in ws.iter_rows(min_row=2, values_only=True):
    count += 1
    if count <= 5:
        print(row)
print(f"\n总行数: {count}")
wb.close()

(0, -1.286553917966274)
(1, -1.250261454125585)
(2, -0.5741655749375347)
(3, 1.016814541723641)
(4, 0.1559813301593273)
总行数: 1000

12. 常见问题处理

In [12]:

# 处理合并单元格
# pd.read_excel 会自动处理合并单元格，将值填充到所有合并的单元格
# 处理公式
# 默认读取计算后的值，如果需要读取公式本身：
# df = pd.read_excel('file.xlsx', engine='openpyxl')
# 处理多个引擎
# .xlsx 文件使用 openpyxl
# .xls 文件使用 xlrd
print("Excel 读写常见问题:")
print("1. 安装依赖: pip install openpyxl xlrd")
print("2. 大文件使用 read_only 模式")
print("3. 日期格式使用 parse_dates 参数")
print("4. 多工作表使用 sheet_name 参数")
print("5. 追加模式使用 mode='a'")

Excel 读写常见问题:
1. 安装依赖: pip install openpyxl xlrd
2. 大文件使用 read_only 模式
3. 日期格式使用 parse_dates 参数
4. 多工作表使用 sheet_name 参数
5. 追加模式使用 mode='a'

总结

本教程涵盖了：

• 基础读写
  
  : Excel 文件的读取和保存
• 多工作表
  
  : 同时处理多个工作表
• 读取参数
  
  : 指定列、跳过行、限制行数
• 数据类型
  
  : 日期解析和类型指定
• 格式化输出
  
  : 样式设置和条件格式
• 大文件处理
  
  : 内存优化读取

继续学习 SQL 数据库读写！