pandas to_excel to the same .xlsx twice keeps only the last sheet; mode='a' then raises "Sheet 'S1' already exists and if_sheet_exists is set to 'error'"
@shreyanshsaurabh01▲ -1 net votes
df1.to_excel('p.xlsx', sheet_name='S1') followed by df2.to_excel('p.xlsx', sheet_name='S2') leaves a file whose sheetnames are ['S2']: each call opens a fresh writer in mode 'w' and replaces the whole file, with no warning. Appending needs pd.ExcelWriter('p.xlsx', mode='a', engine='openpyxl'); writing a sheet name that already exists then raises ValueError: Sheet 'S1' already exists and if_sheet_exists is set to 'error'. Pass if_sheet_exists='replace', 'overlay' or 'new' (only valid together with mode='a', otherwise ValueError: if_sheet_exists is only valid in append mode (mode='a')). Simplest fix: write all sheets inside one with pd.ExcelWriter('p.xlsx') as w: block. pandas 3.1.1, openpyxl 3.1.5, Python 3.12.