Pandas and xlsxwriter: how to create a new sheet without exporting a dataframe?

Question

If I call the xlsxwriter module directly, it is very easy to create a new sheet in a new file, and write to its cells, e.g.:

import xlsxwriter
workbook = xlsxwriter.Workbook('test 1.xlsx')
wks1=workbook.add_worksheet('Test sheet')
wks1.write(0,0,'some random text')
workbook.close()

Howeer, my question is: how can I create a new sheet using a Pandas.ExcelWriter object? The object can create a new sheet when exporting a dataframe, but what if I don't have any dataframes to export?

E.g. say I have exported 4 dataframes to 4 separate sheets, and now I just want to write some text to a new sheet. The only solution I have found is to create an empty dataframe, export that (which creates the new sheet), then write to the sheet:

import pandas as pd
writer = pd.ExcelWriter('test 2 .xlsx',  engine='xlsxwriter')
df=pd.DataFrame()
df.to_excel(writer, 'new sheet', index=False, startrow=0,startcol=0)
writer.sheets['new sheet'].write(0,0,'some random text')
writer.close()

Is there another way? add_worksheet() seems to be a method of the workbook class only, not of ExcelWriter

Idk if there's a way to do it or not, but doesn't it seems to you like the correct behaviour? Use xlxwriter object to write general changes to the Excel, while the Pandas implementation only offers df methods which would affect the Excel file — Josh Friedlander
– Josh Friedlander, Commented Mar 18, 2019 at 14:00
Unless it's improved dramatically over the last year or so, openpyxl was much slower than xlsxwriter. Not a big difference when exporting small sets of data, but quite a difference when exporting large tables which are the result of numerical simulations. And, before csv is proposed, Excel is more convenient because it lets you apply some formatting and because it lets you store 10 tables in the same file (one per worksheet). But openpyxl has the advantage of being able to read existing Excel files, which xlsxwriter cannot do. — Pythonista anonymous
– Pythonista anonymous, Commented Mar 18, 2019 at 15:19

jmcnamara · Accepted Answer · 2019-03-18 17:27:44Z

3

I don't see anything wrong with the way you are doing it but you could also use the XlsxWriter workbook object from the ExcelWriter as follows:

writer = pd.ExcelWriter('test 2 .xlsx', engine='xlsxwriter')

workbook = writer.book
worksheet = workbook.add_worksheet('new sheet')
worksheet.write(0, 0, 'some random text')

answered Mar 18, 2019 at 17:27

jmcnamara

42.1k7 gold badges98 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pythonista anonymous Over a year ago

Ah - I need writer.book! I was trying to use writer.add_worksheet, and that didn't work. I see now - thanks!

Collectives™ on Stack Overflow

Pandas and xlsxwriter: how to create a new sheet without exporting a dataframe?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related