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
[email protected]  System 1
[email protected]  System 1
[email protected]  System 1
[email protected]  System 1
[email protected]  System 1

         email    System
[email protected]  System 2
[email protected]  System 2
[email protected]  System 2
[email protected]  System 2
[email protected]  System 2

         email    System
[email protected]  System 3
[email protected]  System 3
[email protected]  System 3
[email protected]  System 3
[email protected]  System 3

Thank you for taking your time to read this!

What library are you using to send that to Excel?

BruceWayne
– BruceWayne

2018-01-18 16:07:02 +00:00
Commented Jan 18, 2018 at 16:07 — BruceWayne
– BruceWayne, Commented Jan 18, 2018 at 16:07
Have you tried XlsxWriter? xlsxwriter.readthedocs.io

anishtain4
– anishtain4

2018-01-18 16:11:48 +00:00
Commented Jan 18, 2018 at 16:11 — anishtain4
– anishtain4, Commented Jan 18, 2018 at 16:11
Hey Sorry, I am using Pandas, ExcelWriter, and ExcelFile

BeMuRR187
– BeMuRR187

2018-01-18 16:12:45 +00:00
Commented Jan 18, 2018 at 16:12 — BeMuRR187
– BeMuRR187, Commented Jan 18, 2018 at 16:12

jack6e · Accepted Answer · 2018-01-18 16:25:29Z

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, 2018 at 16:25

answered Jan 18, 2018 at 16:08

jack6e

1,51211 silver badges12 bronze badges

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

2 Comments

BeMuRR187 Over a year ago

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

jack6e Over a year ago

Ah. In that case, you have a duplicate of stackoverflow.com/questions/14225676/…

Toby Petty · Accepted Answer · 2018-01-19 14:11:20Z

0

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

edited Jan 19, 2018 at 14:11

answered Jan 18, 2018 at 16:50

Toby Petty

4,6901 gold badge19 silver badges31 bronze badges

Collectives™ on Stack Overflow

Excel export using While loop

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related