Why if_sheet_exists='replace' creating new excel sheet. while df exporting to a existing e-CodePudding

I'm trying to export df value to a excel file without affecting other sheet. but above code is creating a new sheet instead replacing in existing sheet. Can anyone please help on this?

here is my code

import pandas as pd
import openpyxl

df1 = pd.read_excel(r'D:\FY 22 - 23\Apr - 22\Daily Sales\Daily_level_data.xlsx',sheet_name = 'test')

with pd.ExcelWriter(r'D:\FY 22 - 23\Apr - 22\Daily Sales\Daily Sales Master_test.xlsx', mode="a",
                    engine="openpyxl", on_sheet_exists ="replace") as writer:
    df1.to_excel(writer, sheet_name="Day_level")
    

print('data  imported in daily sales master - sucessfully')

CodePudding user response：

Well looking at the documentation, it should be if_sheet_exists not on_sheet_exists. However, it still doesn't work correctly.

You could just pass the sheets to the writer with writer.sheets. Sort of annoying, but it works:

import pandas as pd
import openpyxl

book = openpyxl.load_workbook(r'D:\FY 22 - 23\Apr - 22\Daily Sales\Daily Sales Master_test1.xlsx')
df1 = pd.read_excel(r'D:\FY 22 - 23\Apr - 22\Daily Sales\Daily_level_data.xlsx',sheet_name = 'test')

with pd.ExcelWriter(r'D:\FY 22 - 23\Apr - 22\Daily Sales\Daily Sales Master_test1.xlsx', mode="a",
                    engine="openpyxl", ) as writer:
    writer.book = book
    writer.sheets = {ws.title:ws for ws in book.worksheets}
    df1.to_excel(writer, sheet_name="Day_level")
    

print('data  imported in daily sales master - sucessfully')