How to write to an Excel sheet without exporting a dataframe first?

Question

I am trying to write some text to a specific sheet in an Excel file. I export a number of pandas dataframes to the other tabs, but in this one I need only some text - basically some comments explaining how the other tabs were calculated.

I have tried this but it doesn't work:

import pandas as pd
writer=pd.ExcelWriter('myfile.xlsx')
writer.sheets['mytab'].write(1,1,'This is a test')
writer.close()

I have tried adding writer.book.add_worksheet('mytab') and

ws=writer.sheets['mytab']
ws.write(1,1,'This is a test')

but in all cases I am getting: keyerror:'mytab'. The only solution I have found is to write an empty dataframe to the tab before writing my text to the same tab:

emptydf=pd.DataFrame()
emptydf['x']=[None]
emptydf.to_excel(writer,'mytab',header=False, index=False)

I could of course create a workbook instance, as in the example on the documentation of xlsxwriter: http://xlsxwriter.readthedocs.io/worksheet.html However, my problem is that I already have a pd.ExcelWriter instance, which is used in the rest of my code to create the other excel sheets.

I even tried passing a workbook instance to to_excel(), but it doesn't work:

workbook   = xlsxwriter.Workbook('filename.xlsx')
emptydf.to_excel(workbook,'mytab',header=False, index=False)

Is there any alternative to my solution of exporting an empty dataframe - which seems as unpythonic as it can get?

patrickjlong1 patrickjlong1 · Accepted Answer · 2017-11-07T15:36:58

You mentioned that you used add_worksheet() method from the writer.book object, but it seems to work and do what you wanted it to do. Below I've put in a reproducible example that worked successfully.

import pandas as pd

print(pd.__version__)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
workbook  = writer.book
ws = workbook.add_worksheet('mytab')

ws.write(1,1,'This is a test')

writer.close()

Thought I'd also mention that I'm using pandas 0.18.1.

How to write to an Excel sheet without exporting a dataframe first?

1 Answers