I am trying to write multiple dataframes to one excel file in separate sheets. When I use the following code for one dataframe, it works:

path = 'path.xlsx'
df1.to_excel(path, sheet_name = 'sheet')

however, when I try to do the following which follows the syntax I got for pd.ExcelWriter from pandas, it does not work. My code is as follows:

with pd.ExcelWriter(path) as writer
     df1.to_excel(writer, sheet_name = 'sheet1')
     df2.to_excel(writer, sheet_name = 'sheet2')

I get an error saying at least one sheet must be visible. I have tried various solutions such as putting the path directly in excelwriter but I get the same error.

NB: Answers including xlsxwriter would not be useful to me but those using openpyxl would be useful.