71

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')
Xiddoc
  • 3,369
  • 3
  • 11
  • 37
yoshiserry
  • 20,175
  • 35
  • 77
  • 104
  • 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). http://pandas-xlsxwriter-charts.readthedocs.org/chart_grouped_column.html#chart-grouped-column – yoshiserry Feb 24 '14 at 09:33

4 Answers4

84
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]})

writer = pd.ExcelWriter('multiple.xlsx', engine='xlsxwriter')

df1.to_excel(writer, sheet_name='Sheeta')

df2.to_excel(writer, sheet_name='Sheetb')

df3.to_excel(writer, sheet_name='Sheetc')

writer.save()
taras
  • 6,566
  • 10
  • 39
  • 50
kalyan solasa
  • 841
  • 6
  • 3
  • 7
    brilliant! it could be engine='openpyxl', when it says "ModuleNotFoundError: No module named 'xlsxwriter'" – Mark K Oct 13 '20 at 01:44
  • Thanks, Kalyan... It is defined so easily – Shyam Bhagat Feb 20 '21 at 09:55
  • 3
    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. – Jake Ireland Mar 19 '22 at 13:38
56

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.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • jmcnamara - note that syntax doesn't work when creating multiple workbooks. Use the xlsxwriter library instead as in my link. – yoshiserry Feb 24 '14 at 13:49
  • 8
    The syntax should work, I tested it. Also, I wrote that document that you linked to and the XlsxWriter module. :-) – jmcnamara Feb 24 '14 at 15:22
  • 1
    jmcnamara - accepted your answer thank you for writing the xlsxwriter module. my apologies. – yoshiserry Feb 25 '14 at 02:35
  • 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? – yoshiserry Feb 25 '14 at 21:00
1

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

ankur09011
  • 453
  • 3
  • 12
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.

Gelzone
  • 11
  • 5