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

alex_bn_lee / 2024-02-12 / 原文

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

Read each Excel file into a Pandas DataFrame.
Create an Excel writer object using Pandas.
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.