How to Automatically Merge Hundreds of Excel Files Without Losing Formatting in Python

import pandas as pd
from pathlib import Path

def merge_excel_files(folder_path: str, output_path: str) -> None:
    """
    Merge all .xlsx files in a folder into a single Excel file,
    preserving individual sheet structures.
    """
    folder = Path(folder_path)
    excel_files = list(folder.glob("*.xlsx"))
    
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        for file in excel_files:
            # Read all sheets from the current file
            sheets = pd.read_excel(file, sheet_name=None)
            for sheet_name, df in sheets.items():
                # Create a unique sheet name for the merged file
                unique_sheet = f"{file.stem}_{sheet_name}"[:31]  # Excel limit: 31 chars
                df.to_excel(writer, sheet_name=unique_sheet, index=False)
    
    print(f"Merged {len(excel_files)} files into {output_path}")

if __name__ == "__main__":
    # Example usage: merge all Excel files from 'input_folder' into 'merged_output.xlsx'
    merge_excel_files("input_folder", "merged_output.xlsx")