Add new excel sheets with a for loop in python

Question

Following problem: each iteration of a for loop a new sheet to an existing excel workbook should be added. in the same iteration a pandas series has to be written into this sheet. At the end there have to be one excel file with multiple sheets each containing one pandas series. I was trying by:

from pandas import Series
for counter in range(n):
   sheet_name = 'Sheet%s' % counter
   series.to_frame(name = '').to_excel('output.xls', sheet_name=sheet_name)

Unfortunately, this code generates a new excel workbook each iteration. As a result, there is only one sheet in there. Please help, I have absolutely no idea.

SiHa · Accepted Answer · 2018-04-03 07:07:21Z

6

According to the documentation, you can pass in an existing ExcelWriter object:

If passing an existing ExcelWriter object, then the sheet will be added to the existing workbook. This can be used to save different DataFrames to one workbook:
>>> writer = pd.ExcelWriter('output.xlsx')
>>> df1.to_excel(writer,'Sheet1')
>>> df2.to_excel(writer,'Sheet2')
>>> writer.save()

So, this should work:

from pandas import Series, ExcelWriter

writer = ExcelWriter('output.xls')
for counter in range(n):
   sheet_name = 'Sheet%s' % counter
   series.to_frame(name = '').to_excel(writer, sheet_name=sheet_name)

writer.save()

edited Apr 3, 2018 at 7:07

answered Oct 6, 2017 at 18:51

SiHa

8,49713 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Add new excel sheets with a for loop in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related