creating multiple excel worksheets using data in a pandas dataframe
Question: Just started using pandas and python.

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.

Thanks in advance.

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


Answer: 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.save()
    # ...
    

The correct syntax for this is shown at the end of the Pandas
[`DataFrame.to_excel()`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.to_excel.html) docs.

See also [Working with Python Pandas and
XlsxWriter](https://xlsxwriter.readthedocs.org/working_with_pandas.html).

