[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:
- 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. Theindex=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.