Pandas DataFrame 写入 Excel 的三种场景及方法

一、引言

本文主要介绍如何将 pandas 的 DataFrame 数据写入 Excel 文件中，涉及三个不同的应用场景：

• 单个工作表写入：将单个 DataFrame 写入 Excel 表中；
• 多个工作表写入：将多个 DataFrame 写入到同一个 Excel 表中的不同工作表中；
• 追加模式写入：针对已有 Excel 文件，在不覆盖原有数据的前提下，将新的 DataFrame 数据追加至指定的工作表中。

二、准备工作

安装必要的库：pandas 和 openpyxl

pip install pandas
pip install openpyxl

准备测试数据

import pandas as pd

data1 = {
    '姓名': ['张三', '李四', '王五'],
    '年龄': [25, 30, 35],
    '城市': ['北京', '上海', '深圳']
}

data2 = {
    '课程': ['语文', '数学', '英语'],
    '分数': [90, 80, 70]
}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

print(df1)
print(df2)

三、将 DataFrame 数据写入 Excel 表

3.1 场景一：将单个 DataFrame 写入 Excel 单个工作表

这是最常用的方法，直接调用to_excel()方法写入即可。
假设我写入到 D 盘下的 write_to_excel.xlsx 文件中，即“D:\write_to_excel.xlsx”，参考代码如下：

filepath = r'D:\write_to_excel.xlsx'
df1.to_excel(filepath, index=False)

注：

• 在Python中，字符串前面的r表示原始字符串（raw string）。在原始字符串中，反斜杠\不会被当作转义字符。'D:\\write_to_excel.xlsx' 和 r'D:\write_to_excel.xlsx' 是等价的。
• index = False是不写入 DataFrame 的索引列，默认是写入的，此处设置为不写入。
  

3.2 场景二：将多个 DataFrame 写入 Excel 多个工作表

在 3.1 中，直接使用to_excel()方法写入时一次性的操作，如果多次调用，后面写入的数据会覆盖前面的数据。比如以下代码，执行完，write_to_excel.xlsx 文件中只有 df2 的数据。

filepath = r'D:\write_to_excel.xlsx'
df1.to_excel(filepath, index=False)
df2.to_excel(filepath, index=False)

如果要将两个 DataFrame 写入同一个 Excel 表，则需要构建一个对象。
我们可以使用pd.ExcelWriter()来构建对象，示例代码如下：

filepath = r'D:\write_to_excel.xlsx'
with pd.ExcelWriter(filepath) as writer:  
    df1.to_excel(writer, index=False)
    df2.to_excel(writer, index=False)

此时，如果打开 Excel 文件，我们会发现，还是原来的结果，说好的多个写入呢？？？
这是一个小坑，当写入多个工作表的时候，需要加上sheet_name参数指定工作表的名称。默认是 Sheet1，所以两次都是写入到 Sheet1 工作表中，导致数据不符合预期。
通过 sheet_name指定工作表名称，参考代码如下：

filepath = r'D:\write_to_excel.xlsx'
with pd.ExcelWriter(filepath) as writer:  
    df1.to_excel(writer, index=False, sheet_name='测试-用户信息')
    df2.to_excel(writer, index=False, sheet_name='测试-课程信息')

可以看到，此时的数据便符合我们的预期了，将 df1 写入工作表“测试-用户信息”，将 df2 写入工作表“测试-课程信息”。

3.3 场景三：追加数据到原有Excel文件的工作表中

前面介绍的场景都是覆盖式的写入，但是如果我是处理了 Excel 表的数据之后，还想把处理好的数据通过新增一个工作表直接追加到原来的 Excel 表中，又该怎么操作呢？
本文提供的方法是借助openpyxl模块来实现，本测试直接在上面 3.2 的文件基础上再写入一次 df1。
具体的操作如下：

• 使用 openpyxl 模块的load_workbook()方法读取 Excel 表单
• 对返回的 workbook 对象，使用create_sheet()方法新建一个工作表
• 再把 df1 的数据写入。df1 的数据无法直接写入 workbook 对象，所以写入前，需要使用dataframe_to_rows()用于将 DataFrame 按行转为列表，然后遍历写入 Excel 新建的工作表中。
• 最后，一定要记得使用save()保存。否则白干。

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# 加载现有的Excel文件
filepath = r'D:\write_to_excel.xlsx'
workbook = load_workbook(filepath)
# 创建新的sheet表
new_sheet = workbook.create_sheet('测试追加用户信息表')

for r in dataframe_to_rows(df1, index=False, header=True):
    new_sheet.append(r)

# 保存更改到原Excel文件
workbook.save(filepath)

执行结果如下：

四、总结

本文介绍了 DataFrame 写入 Excel 的三种场景及方法，代码小结如下：

• 数据准备

import pandas as pd

data1 = {
    '姓名': ['张三', '李四', '王五'],
    '年龄': [25, 30, 35],
    '城市': ['北京', '上海', '深圳']
}

data2 = {
    '课程': ['语文', '数学', '英语'],
    '分数': [90, 80, 70]
}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

• 场景一：将单个 DataFrame 写入 Excel 单个工作表

filepath = r'D:\write_to_excel.xlsx'
df1.to_excel(filepath, index=False)

• 场景二：将多个 DataFrame 写入 Excel 多个工作表

filepath = r'D:\write_to_excel.xlsx'
with pd.ExcelWriter(filepath) as writer:  
    df1.to_excel(writer, index=False, sheet_name='测试-用户信息')
    df2.to_excel(writer, index=False, sheet_name='测试-课程信息')

• 场景三：追加数据到原有Excel文件的工作表中

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# 加载现有的Excel文件
filepath = r'D:\write_to_excel.xlsx'
workbook = load_workbook(filepath)
# 创建新的sheet表
new_sheet = workbook.create_sheet('测试追加用户信息表')

for r in dataframe_to_rows(df1, index=False, header=True):
    new_sheet.append(r)

# 保存更改到原Excel文件
workbook.save(filepath)