Pandas write to different sheet

Question

I have the following code:

import pandas
data = pandas.DataFrame(dataset)
writer = pandas.ExcelWriter("C:/adhoc/test.xlsx", engine='xlsxwriter')
data.to_excel(writer, sheet_name='Test')
writer.save()

I have two sheets, Sheet1 and Test. When I run the code it is deleting Sheet1 and just writing the data onto Test. What am I doing wrong here? Expected output I want is to not write anything on Sheet1 and have the data written to Test.

Answer 1

You need to use append as the file mode in the ExcelWriter. But append does not supported with the xlsxwriter.

To append you need to specify the engine as openpyxl

This will write the data to the Test sheet and leave the Sheet1 as it is.

import pandas

file_path = "C:/adhoc/test.xlsx"

data = pandas.DataFrame(dataset)
writer = pandas.ExcelWriter(file_path, engine='openpyxl', mode='a')
data.to_excel(writer, sheet_name='Test')
writer.save()

Alternatively, you can use context manager here:

import pandas

file_path = "C:/adhoc/test.xlsx"
data = pandas.DataFrame(dataset)

with pandas.ExcelWriter(file_path, engine='openpyxl', mode='a') as writer:
    data.to_excel(writer, sheet_name='Test')