解决Pandas生成Excel时的sheet问题的方法总结

楔子

同时导入多个 sheet

覆盖一个 sheet

楔子

估计有不少小伙伴在将 DataFrame 导入到 Excel 的时候，都遇到过下面这种尴尬的情况：

想将多个 DataFrame 导入到一个 Excel 文件的多个 sheet 中，但是却发现生成的 Excel 文件里面只有最后一个 sheet；

想给一个现有的 Excel 文件追加一个 sheet，结果发现其它的 sheet 都没了，只剩下新追加的 sheet；

那么下面就来看看如何解决这些问题。

同时导入多个 sheet

如果想导入多个 sheet，那么肯定不能使用原来 to_excel("文件名") 的方式，那样只会保留最后一个 sheet。我们应该使用类 ExcelWriter 实现：

import pandas as pd
df1 = pd.DataFrame({"a": [1, 2], "b": [3, 4]})
df2 = pd.DataFrame({"a": [2, 3], "b": [4, 5]})
df3 = pd.DataFrame({"a": [3, 4], "b": [5, 6]})
# 调用pd.ExcelWriter, 需要指定mode="a", engine="openpyxl"
# 注意: 将mode设置为"a"表示追加, 但是它要求文件必须存在, 否则报错
"""
writer = pd.ExcelWriter("test.xlsx", mode="a", engine="openpyxl")
"""
# 因此我们需要生成这个文件，此时顺便将第一个 DataFrame 导进去
df1.to_excel("test.xlsx", index=False, sheet_name="a")
# 然后再实例化ExcelWriter
writer = pd.ExcelWriter("test.xlsx", mode="a", engine="openpyxl")
# 接下来还是调用to_excel, 但是第一个参数不再是文件名, 而是上面的writer
# 将剩下的两个DataFrame写进去
df2.to_excel(writer, index=False, sheet_name="b")
df3.to_excel(writer, index=False, sheet_name="c")
# 保存并关闭writer, 写入磁盘
writer.save()
writer.close()

执行代码，然后打开文件看一下。

此时我们看到结果是没有问题的，当然向已存在的 Excel 文件追加 sheet 也是同理。

覆盖一个 sheet

向 Excel 文件同时写入多个sheet，以及追加sheet，我们已经知道该怎么做了，然后是覆盖 sheet。首先我们覆盖 sheet 的时候还要保证其它 sheet 不受影响，所以 mode 仍然要设置为追加模式。

下面问题来了，我们上面的 Excel 文件有 "a"、"b"、"c" 三个 sheet，假设我们想将 "b" 这个 sheet 覆盖掉，应该怎么做呢？可能有人认为，在追加的时候还指定 sheet_name="b" 不就行了，然鹅答案是不行的。

我们看到如果已有同名 sheet，那么不会覆盖，还是创建一个新的 sheet，并自动在结尾处加一个 1。如果我们在此基础上再写入 "b" 这个 sheet 的话，那么又会多出一个名为 "b2" 的sheet。所以最好的办法是，在导入之前先将 sheet 删除。

import pandas as pd
writer = pd.ExcelWriter("test.xlsx", mode="a", 
                        engine="openpyxl")
wb = writer.book
# pandas操作Excel底层也是依赖于其它的模块, 比如xlrd、openpyxl
# 所以这里的 wb = writer.book  就相当于
"""
from openpyxl import load_workbook
wb = load_workbook("test.xlsx")
"""
# 查看已存在的所有的sheet, 总共是5个
# 其中 "b1"和"b2" 是自动创建的
print(wb.sheetnames)  # ['a', 'b', 'c', 'b1', 'b2']
# 下面我们来删除sheet
wb.remove(wb["b1"])
wb.remove(wb["b2"])
wb.remove(wb["b"])
df = pd.DataFrame({"name": ["古明地觉", "古明地恋"]})
# 我们将 b 这个 sheet 给删除了
# 所以再导入 "b" 的时候就不会出现 "b3" 了
# 当然 "b1" 和 "b2" 也顺便被我们给删掉了
df.to_excel(writer, index=True, sheet_name="b")
writer.save()
writer.close()

我们看到 "b1"、"b2" 两个 sheet 就没了，当然我们删除的还有 "b" 这个sheet，只不过又重新创建了，当然数据也是我们创建的新数据。