How to get the separate sheet in single workbook while combining multiple Excel workbook using python pandas?

Question

my code :

Final_wb=pd.DataFrame()
 for filename in glob.glob("*.xlsx):
   ws=pd.read(filename)
   Final_wb=pd.concat([Final_wb,ws],ignore_index=True)
 Final_wb.to_excel("Output.xlsx",index=False)

The above code works.But it combine all worbook into single workbook with single sheet.I need to read all workbook and combine into single workbook with multiple worksheets.(For Example, say i have 3 workbook with each workbook has each sheets so i can get single workboook[output.xlsx] with 3 sheets).I dont want to use xlsx writer and openpyxl. Share your knowledge on this..Anyone done this before

Answer 1

I dont want to use xlsx writer and openpyxl

Not an option since you're using xslx. If you want to use the xlwt library then you have to change the file extension to xsl.

Assuming you did import pandas as pd:

with pd.ExcelWriter("Output.xlsx", mode = 'a') as writer:
    for filename in glob.glob("*.xlsx"):
        ws = pd.read(filename)
        df = DataFrame(ws)
        df.to_excel(writer, sheet_name = filename.strip('.xlsx'), index = False)

If you do use the XslxWriter library, then when trying to do pd.ExcelWriter("Output.xlsx", mode = 'a', engine = 'xlsxwriter') you'll get an exception stating that this engine doesn't support append mode. But since the file is being closed after all the workbooks have been exported, it'll work in the default mode ('w') anyway. Example:

"""pip install pandas XslxWriter"""

import pandas as pd

with pd.ExcelWriter("Output.xlsx", mode = 'w', engine = 'xslxwriter') as writer:
    for filename in glob.glob("*.xlsx"):
        ws = pd.read(filename)
        df = DataFrame(ws)
        df.to_excel(writer, sheet_name = filename.strip('.xlsx'), index = False)