Excel export using While loop

Question

I am new to Python. I am working on a large analytic program, and this is a snippet of it. Right now, this snippet exports multiple excel files. Is it possible to save what is done per loop on a sheet within a single excel document? So basically right now, it exports 5 files, rather than exporting 5 separate files, can I use this loop and export 1 file, that has 5 sheets?

x = 0
y = 0
#these are empty variables for the while loop


#while loop that loops up to the system amount
#breaks up df into systems
#exports excel for each system
while x < int(SystemCount): 
    x += 1
    y += 1 
    System = minus4[minus4['System'] == "System " + str(y)]
    System.to_excel('U4Sys' +  str(y) + '.xlsx', sheet_name='sheet1', index=False)
    print(System.head())

the print at the end prints this

    email    System
test1@test.com  System 1
test2@test.com  System 1
test3@test.com  System 1
test4@test.com  System 1
test5@test.com  System 1

         email    System
test1@test.com  System 2
test2@test.com  System 2
test3@test.com  System 2
test4@test.com  System 2
test5@test.com  System 2

         email    System
test1@test.com  System 3
test2@test.com  System 3
test3@test.com  System 3
test4@test.com  System 3
test5@test.com  System 3

Thank you for taking your time to read this!

Have you tried XlsxWriter? http://xlsxwriter.readthedocs.io/ — anishtain4, Jan 18 '18 at 16:11

jack6e · Accepted Answer · 2018-01-18T16:25:29.043

1

EDIT (to account for OP using pandas and ExcelWriter):

You need to define your target file with ExcelWriter and then write to it with variable sheet names. Also offering some Python clean-up for your iteration:

#breaks up df into systems
#exports excel for each system

writer = ExcelWriter('U4SysOutput.xlsx')
for x in range(1, int(SystemCount)+1): 

    System = minus4[minus4['System'] == "System " + str(x)]
    System.to_excel(writer, sheet_name='sheet{}'.format(x), index=False)
    print(System.head())

edited Jan 18 '18 at 16:25

answered Jan 18 '18 at 16:08

jack6e

1,512
10
12

I am using Pandas, ExcelWriter and Excel File. I tried the code above and it just saves the last iteration of the loop. So only System 5 is saved into the excel and not 1, 2, 3 or 4 – BeMuRR187 Jan 18 '18 at 16:17
Ah. In that case, you have a duplicate of https://stackoverflow.com/questions/14225676/save-list-of-dataframes-to-multisheet-excel-spreadsheet – jack6e Jan 18 '18 at 16:20

Toby Petty · Answer 2 · 2018-01-19T14:11:20.007

You need to use a pandas.ExcelWriter() in pandas.to_excel()

Here is a simplified version of your process:

import numpy as np
import pandas as pd

# Output Excel file:
writer = pd.ExcelWriter("your_excel_filepath.xlsx")

# Your variables:
x, y = 0, 0

# The loop:             
while x < 5:

    x += 1
    y += 1 

    # The DataFrame for this iteration:
    df = pd.DataFrame(np.random.randn(5,4), columns=list("ABCD"))

    # Write the DataFrame to a new sheet:
    df.to_excel(writer, "sheet_{}".format(x))
writer.save()

Excel export using While loop

2 Answers2