Pandas: save dataframe to open Workbook

Question

I've got an open workbook which data is written to. How can I add sheets to write the content of a dataframe?

import xlsxwriter
import pandas as pd

workbook = xlsxwriter.Workbook('test.xlsx')
sheet1 = workbook.add_worksheet(name='sheet1')
sheet1.write(0, 0, 'test')
df = pd.DataFrame({'a': [1, 2, 3, 4], 'b': ['aa', 'bb', 'cc', 'dd']})
# write df somehow to next sheet. I usually use df.to_excel(filename, engine='xlsxwriter'), but this will create a new file
workbook.close()

If that is really what you tried to run, then you are simply missing a ' after 'test.xlsx and this will prevent your code from working — firelynx, May 18 '15 at 07:25
Thanks. I edited the example. Obviously this wasn't why I posted this question... — orange, May 18 '15 at 10:37
Have you checked this: http://stackoverflow.com/questions/21981820/creating-multiple-excel-worksheets-using-data-in-a-pandas-dataframe — etna, May 18 '15 at 11:24
@etna The problem is that the `ExcelWriter` isn't created by `pd.ExcelWriter()`, but through `xlsxwriter.Workbook()` to just write some arbitrary (non-pandas) data. Unfortunately, the approach from your link doesn't apply here. — orange, May 18 '15 at 11:46

score 3 · Accepted Answer · answered May 18 '15 at 16:27

As you figured out in your comments you cannot pass an XlsxWriter workbook object to pandas to_excel().

As a workaround you can create a worksheet with an empty dataframe and then access the XlsxWriter workbook and worksheet objects underneath. You can then add extra worksheets via the pandas interface.

Here is a small working example based on your code:

import pandas as pd

# Create an pandas excel writer based on xlsxwriter.
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Create a worksheet with an empty dataframe so the sheet is empty.
df = pd.DataFrame()
df.to_excel(writer, sheet_name='Sheet1')

# Access the underlying xlsxwriter worksheet and write to it.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
worksheet.write(0, 0, 'test')

# Create another dataframe with data.
df = pd.DataFrame({'a': [1, 2, 3, 4],
                   'b': ['aa', 'bb', 'cc', 'dd']})

# Write the dataframe to another worksheet.
df.to_excel(writer, sheet_name='Sheet2')

writer.save()

See also Working with Python Pandas and XlsxWriter.

Pandas: save dataframe to open Workbook

1 Answers1