Aggregate multiple excel files with multiple worksheets into a single excel file with multiple worksheets

user_18880462 0
Viewed 46

What is the most pythonic way of achieving what the title states? This is my solution and it worked, although I'm not sure if there's a better way of doing it.

def aggregate_excel_files(excel_files: list, output: str):
    # eval how many worksheets in a file, assuming all excel files have the same worksheets
    n_worksheets = pd.read_excel(excel_files[0], sheet_name=None).__len__()
    # Initialize dataframes, one for each worksheet in each excel file
    for i in range(0, n_worksheets):
        globals()[f"mydf{str(i)}"] = pd.DataFrame()
        globals()[f"mydf{str(i)}_temp"] = pd.DataFrame()
    # For each file, read each worksheet and append to a temporary dataframe
    # Then, append to the aggregate dataframe, one for each worksheet
    for file in excel_files:
        dict_df = pd.read_excel(
            file, sheet_name=pd.ExcelFile(file).sheet_names)
        for i in range(0, len(pd.ExcelFile(file).sheet_names)):
            globals()[f"mydf{str(i)}_temp"] = dict_df.get(
                pd.ExcelFile(file).sheet_names[i])
            globals()[f"mydf{str(i)}"] = pd.concat(
                [globals()[f"mydf{str(i)}"], globals()[f"mydf{str(i)}_temp"]])
    # Resets the index for each generated and aggregated dataframe
    for i in range(0, n_worksheets):
        globals()[f"mydf{str(i)}"].reset_index(inplace=True, drop=True)
    # Create a Pandas Excel writer using XlsxWriter as the engine.
    writer = pd.ExcelWriter(output+'.xlsx', engine='xlsxwriter')
    # Write each dataframe to a different worksheet.
    for i in range(0, n_worksheets):
        globals()[f"mydf{str(i)}"].to_excel(
            writer, sheet_name=f"Sheet{str(i)}", index=False)
    # Close the Pandas Excel writer and output the Excel file.
    writer.save()
    # return excel file ready for reading
    return pd.ExcelFile(output+'.xlsx')

Thank you in advance

pandas python-3.x
1 Answers

I would use xlwings:

import os

import xlwings as xw


common = xw.Book()

#Make the excel invisible so that it will not bother us
app = common.app
app.interactive = False
app.visible = False

#A little cludge, we need to specify the sheet with respect to which we put new ones
reference_sheet = common.sheets[0]
for file_name in os.listdir(PATH):
    for sheet in xw.Book(fr"{PATH}\{file_name}").sheets:
        sheet.copy(before=reference_sheet)

reference_sheet.delete()
common.save(fr"{PATH}\Common book.xlsx")
app.kill()
user_173091080
Related