I have two excel workbooks.
One with 3 sheets and the other with only one sheet. I am trying to combine these two into one workbook. This workbook should have 4 sheets.
from pandas import ExcelWriter
writer = ExcelWriter("Sample.xlsx")
for filename in glob.glob("*.xlsx"):
df_excel = pd.read_excel(filename)
(_, f_name) = os.path.split(filename)
(f_short_name, _) = os.path.splitext(f_name)
df_excel.to_excel(writer, f_short_name, index=False)
writer.save()
Doing this gives me a workbook, but with only 2 sheets. First sheet of the first workbook and second sheet of second workbook.
How to get all the 4 sheets in one workbook?
You have to loop through the sheet names. See the below code:
from pandas import ExcelWriter
import glob
import os
import pandas as pd
writer = ExcelWriter("output.xlsx")
for filename in glob.glob("*.xlsx"):
excel_file = pd.ExcelFile(filename)
(_, f_name) = os.path.split(filename)
(f_short_name, _) = os.path.splitext(f_name)
for sheet_name in excel_file.sheet_names:
df_excel = pd.read_excel(filename, sheet_name=sheet_name)
df_excel.to_excel(writer, f_short_name+'_'+sheet_name, index=False)
writer.save()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With