[970] Combine multiple Excel files into one Excel file with multiple sheets

You can combine multiple Excel files into one Excel file with multiple sheets using the Pandas library in Python. Here's a general approach:

1. Read each Excel file into a Pandas DataFrame.
2. Create an Excel writer object using Pandas.
3. Write each DataFrame to a separate sheet in the Excel file.

Here's a code example demonstrating this process:

import pandas as pd
 
# List of Excel file names to combine
excel_files = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']
 
# Create a Pandas Excel writer object
with pd.ExcelWriter('combined_file.xlsx') as writer:
    # Iterate over each Excel file
    for file in excel_files:
        # Read the Excel file into a DataFrame
        df = pd.read_excel(file)
        
        # Extract the file name (without extension) to use as the sheet name
        sheet_name = file.split('.')[0]
        
        # Write the DataFrame to the Excel file with the sheet name
        df.to_excel(writer, sheet_name=sheet_name, index=False)

In this example:

• excel_files is a list containing the names of the Excel files you want to combine.
• pd.ExcelWriter('combined_file.xlsx') creates a Pandas Excel writer object that will write to a file named 'combined_file.xlsx'.
• Inside the loop, each Excel file is read into a DataFrame using pd.read_excel().
• The sheet name for each DataFrame is extracted from the file name (without the extension) using split('.').
• Finally, each DataFrame is written to the Excel file using df.to_excel() with the appropriate sheet name. The index=False parameter is used to prevent the DataFrame index from being written as a column in the Excel sheet.

This will result in a single Excel file named 'combined_file.xlsx' containing multiple sheets, each corresponding to one of the input Excel files.