Add new excel sheets with a for loop in python

user_3706869 0
Viewed 8941

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.

pandas for-loop excel python
1 Answers

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()
user_37149400
Related