[971] [Keep original formats] Combine multiple Excel files into one Excel file with multiple sheets

If the existing Excel file has special formatting that prevents reading it directly with Pandas, you can use a library like openpyxl to handle the appending of new sheets. Here's how you can achieve this:

import os
from openpyxl import load_workbook
 
# Path to the existing Excel file
existing_excel_file = 'existing_file.xlsx'
 
# Path to the folder containing the Excel files to append
folder_path = 'folder_with_excel_files/'
 
# Load the existing Excel workbook
existing_wb = load_workbook(existing_excel_file)
 
# Iterate through the Excel files in the folder
for file_name in os.listdir(folder_path):
    if file_name.endswith('.xlsx'):
        # Construct the full path to the Excel file
        file_path = os.path.join(folder_path, file_name)
        
        # Load the Excel workbook to be appended
        append_wb = load_workbook(file_path)
        
        # Iterate through sheets in the workbook to be appended
        for sheet in append_wb.sheetnames:
            # Copy each sheet from the workbook to be appended to the existing workbook
            append_sheet = append_wb[sheet]
            existing_wb.create_sheet(title=sheet)
            existing_sheet = existing_wb[sheet]
            for row in append_sheet.iter_rows():
                for cell in row:
                    existing_sheet[cell.coordinate].value = cell.value
 
# Save the updated existing Excel workbook
existing_wb.save(existing_excel_file)

In this code:

• existing_excel_file is the path to the existing Excel file.
• folder_path is the path to the folder containing the Excel files you want to append.
• We load the existing Excel workbook using openpyxl.load_workbook().
• We iterate through the Excel files in the specified folder, load each workbook to be appended, and then iterate through its sheets.
• For each sheet in the workbook to be appended, we create a corresponding sheet in the existing workbook, then copy the values from each cell in the sheet to the corresponding cell in the existing workbook.
• Finally, we save the updated existing workbook.