Creating multiple Excel worksheets using data from a pandas DataFrame

Question

I have a worksheet which I have read into a dataframe and the applied forward fill (ffill) method to.

I would then like to create a single excel document with two worksheets in it.

One worksheet would have the data in the dataframe before the ffill method is applied and the next would have the dataframe which has had the ffill method applied.

Eventually I intend to create one worksheet for every unique instance of data in a certain column of the dataframe.

I would then like to apply some VBA formatting to the results - but I'm not sure which DLL or addon or something I would need to call excel vba using python to format headings as bold and add color etc.

I've had partial success in that xlsxwriter will create a new workbook and add sheets, but dataframe.to_excel operations don't seems to work on the workbooks it creates, the workbooks open but the sheets are blank.

import os
import time
import pandas as pd
import xlwt
from xlwt.Workbook import *
from pandas import ExcelWriter
import xlsxwriter

#set folder to import files from
path = r'path to some file'
#folder = os.listdir(path)

#for loop goes here

#get date
date = time.strftime('%Y-%m-%d',time.gmtime(os.path.getmtime(path)))

#import excel document
original = pd.DataFrame()
data = pd.DataFrame()

original = pd.read_excel(path,sheetname='Leave',skiprows=26)
data = pd.read_excel(path,sheetname='Leave',skiprows=26)

print (data.shape)
data.fillna(method='ffill',inplace=True)

#the code for creating the workbook and worksheets
wb= Workbook()
ws1 = wb.add_sheet('original')
ws2 = wb.add_sheet('result')
original.to_excel(writer,'original')
data.to_excel(writer,'result')
writer.save('final.xls')

The following example shows how to use the xlsxwriter python library to create a workbook, and insert worksheets, then insert data from pandas dataframes, (and even charts based on the dataframes into excel). pandas-xlsxwriter-charts.readthedocs.org/… — yoshiserry
– yoshiserry, Commented Feb 24, 2014 at 9:33

Att Righ · Accepted Answer · 2023-09-06 21:01:58Z

103

import pandas as pd

df1 = pd.DataFrame({'Data': ['a', 'b', 'c', 'd']})    
df2 = pd.DataFrame({'Data': [1, 2, 3, 4]})  
df3 = pd.DataFrame({'Data': [1.1, 1.2, 1.3, 1.4]})

with pd.ExcelWriter('multiple.xlsx', engine='xlsxwriter') as writer:
    df1.to_excel(writer, sheet_name='Sheeta')
    df2.to_excel(writer, sheet_name='Sheetb')
    df3.to_excel(writer, sheet_name='Sheetc')

edited Sep 6, 2023 at 21:01

Att Righ

1,9773 gold badges22 silver badges39 bronze badges

answered Jan 17, 2019 at 14:03

kalyan solasa

1,0291 gold badge7 silver badges3 bronze badges

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

3 Comments

Mark K Over a year ago

brilliant! it could be engine='openpyxl', when it says "ModuleNotFoundError: No module named 'xlsxwriter'"

Jake Ireland Over a year ago

This can be used in a with block, too. I.e., with pd.ExcelWriter('multiple.xlsx', engine='xlsxwriter') as writer:, meaning exiting the block will automatically close/save the file.

Att Righ Over a year ago

The previous example before I edited was not working with newer versions of pandas due to (stackoverflow.com/questions/76090979/…) I changed the code sample

jmcnamara · Accepted Answer · 2023-05-23 10:45:32Z

61

Your sample code is almost correct except you need to create the writer object and you don't need to use the add_sheet() methods. The following should work:

# ...
writer = pd.ExcelWriter('final.xlsx')
data.to_excel(writer,'original')

# data.fillna() or similar.

data.to_excel(writer,'result')
writer.close()
# ...

The correct syntax for this is shown at the end of the Pandas DataFrame.to_excel() docs.

See also Working with Python Pandas and XlsxWriter.

edited May 23, 2023 at 10:45

answered Feb 24, 2014 at 10:03

jmcnamara

42.1k7 gold badges98 silver badges114 bronze badges

4 Comments

yoshiserry Over a year ago

jmcnamara - note that syntax doesn't work when creating multiple workbooks. Use the xlsxwriter library instead as in my link.

jmcnamara Over a year ago

The syntax should work, I tested it. Also, I wrote that document that you linked to and the XlsxWriter module. :-)

yoshiserry Over a year ago

jmcnamara - accepted your answer thank you for writing the xlsxwriter module. my apologies.

yoshiserry Over a year ago

can you provide of the above function you wrote with the add_sheet command in it so that it would do something simple like, add the number of sheets defined in a list, and then add the same dataframe to each of those sheets?

ankur09011 · Accepted Answer · 2022-06-15 07:13:57Z

3

According the pandas documentation

with pandas.ExcelWriter('final.xlsx') as writer:
    df1.to_excel(writer, sheet_name='original')
    df2.to_excel(writer, sheet_name='result')

more details you can find here : official docs

answered Jun 15, 2022 at 7:13

ankur09011

4733 silver badges12 bronze badges

Comments

Gelzone · Accepted Answer · 2023-02-18 06:35:44Z

1

You can use loop to create more sheets if needed:

with pd.ExcelWriter(".xlsx", engine = "openpyxl", mode = "w") as writer:
    for i in range(5):
        #do something()
        df.to_excel(writer, sheet_name = "{}".format(i), index = False, engine = "openpyxl")

It will generate sheet0, sheet1,...etc. (and of course you can name sheets whatever you want by modifying 'sheet_name') in one workbook which means in a single .xlsx file.
Hope it can help you.

answered Feb 18, 2023 at 6:35

Gelzone

196 bronze badges

Collectives™ on Stack Overflow

Creating multiple Excel worksheets using data from a pandas DataFrame

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related