pandas同时生成Excel的多个sheet、往现有的Excel中追加sheet、以及覆盖sheet

楔子

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

• 想将多个DataFrame导入到一个Excel文件的多个sheet中，但是却发现生成的Excel文件里面只有最后一个sheet
• 想给一个现有的Excel文件中追加一个sheet，结果发现其它的sheet都没了，只剩下新追加的sheet

那么下面我们来看看如何解决这个问题。

同时导入多个sheet

如果是导入多个sheet的话，那么肯定不能直接使用原来to_excel("文件名")的方式，而是需要使用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(r"test.xlsx", mode="a", engine="openpyxl")
"""

# 因此首先我们需要生成这个文件
df1.to_excel("test.xlsx", index=False, sheet_name="a")

# 然后再实例化ExcelWriter
writer = pd.ExcelWriter(r"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(r"test.xlsx", mode="a", engine="openpyxl")
# pandas操作Excel底层也是依赖于其它的模块, 比如xlrd、openpyxl
# 所以这里的 wb = writer.book  就相当于  from openpyxl import load_workbook; wb = load_workbook("xxx.xlsx")
wb = writer.book

# 查看已存在的所有的sheet, 总共是5个, "b1"和"b2"是自动创建的, 因为"b"已经存在了, 我们又导入了两次
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"这个sheet的时候就不会出现"b3"了
# 当然我们顺便把"b1"和"b2"也给删掉
df.to_excel(writer, index=True, sheet_name="b")

writer.save()
writer.close()

我们看到"b1"、"b2"两个sheet就没了，当然我们删除的还有"b"这个sheet，只不过我们又重新创建了"b"这个sheet，当然数据也是我们创建的新数据。另外可能有人发现多个sheet的顺序不再是原来的"a"、"b"、"c"，这是因为在删除"b"之后，"a"和"c"就靠在一起了，所以新写入"b"的时候就排在"c"的后面了，当然个人觉得这没有什么太大影响。