CODEWITHSUNDEEP
pythonpandasdataframe
RSM
RSM

Reputation: 673

Unable to create multiple sheets in excel via for loop

I am writing a code where I am iterating on multiple sheets of an input excel file. From the data collected by reading those sheets one by one, I create an output dataframe and need to write the output of each iteration in a new sheet of the same excel file. I have tried multiple ways but I am unable to create multiple sheets.

Can anyone please help with modifications on the below code which will help me achieve the desired output, thanks?

for sheet in sheets:
    <do calculations>
    <do manipulations>
    <do slicing>
    
    print("Writing Ouput Sheet " + sheet)
    file_name = 'Output.xlsx'
    df_output.to_excel(file_name) # To create a empty file
    # To create one sheet for each iteration
    with pd.ExcelWriter(file_name, engine="openpyxl", mode = 'a') as writer:
        df.to_excel(writer, sheet_name=sheet)

More info:

The issue I am facing with the above code is that it generates an excel file with 2 sheets, 1 having the empty Sheet1 and the other the last sheet name from the list of sheets. What I am expecting is that it should write each sheet output in a separate worksheet in the same excel file.

Upvotes: 0

Views: 746

Answers (2)

RSM
RSM

Reputation: 673

I was able to solve the issue by changing the sequence of the code:

writer = ExcelWriter('Output.xlsx')
for sheet in all_sheets:
    <do calculations>
    <do manipulations>
    <do slicing>
    
    df.to_excel(writer, sheet_name=sheet)
writer.save()

This is basically because we want to write multiple sheets in the same file so the initialization or definition of the writer should be only done once.

However, in case, only one sheet is required to be written then the writer can be defined anywhere inside the loop.

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150745

I'm not sure if mode='a' is supported by the engine. One way you can go around is to move the writer creation outside the for loop:

print("Writing Ouput Sheet " + sheet)
file_name = 'Output.xlsx'

# To create one sheet for each iteration
with pd.ExcelWriter(file_name, engine="openpyxl", mode = 'a') as writer:
    for sheet in sheets:
        <do calculations>
        <do manipulations>
        <do slicing>
        df.to_excel(writer, sheet_name=sheet)

Note: I realized that in your code, you rewrite the file every iteration with:

df_output.to_excel(file_name) # To create a empty file

Remove it and your code may work, which is then better as you don't need to open the file all the time.

Upvotes: 1

Related Questions