pd.ExcelWriter 实现数据写入不同sheet

pd.ExcelWriter 将数据写入不同sheet

当结合for循环使用时，需注意放在for循环前面

以下写法，仅生成一个sheet，原因在于pd.ExcelWriter 的mode默认是w，每次for循环写入数据都会对原有的数据进行覆盖，最终只会生成一个sheet。

import pandas as pd
df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])  
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
list_ = ["Sheet_1", "Sheet_2"]
for sheetname in list_:
    with pd.ExcelWriter("/Users/wang/Desktop/path_to_file.xlsx") as writer:
        df1.to_excel(writer, sheet_name=sheetname) 

修改后的写法，可以实现诉求。

方案一：with 放在for循环前

import pandas as pd
df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])  
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
list_ = ["Sheet_1", "Sheet_2"]
with pd.ExcelWriter("/Users/wang/Desktop/path_to_file1.xlsx") as writer:
    for sheetname in list_:
        df1.to_excel(writer, sheet_name=sheetname)  

方案二：修改engine和mode

import pandas as pd
df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])  
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
list_ = ["Sheet_1", "Sheet_2"]
for sheetname in list_:
    with pd.ExcelWriter("/Users/wang/Desktop/path_to_file.xlsx", engine="openpyxl", mode="a") as writer:
        df1.to_excel(writer, sheet_name=sheetname)