Merging Excel files is a common task for data analysts and financial teams working with large datasets. While Microsoft Excel supports manual merging, it becomes inefficient and error-prone when dealing with large volumes of files.
In this step-by-step guide, you will learn how to merge multiple Excel files (.xls and .xlsx) using Python and Spire.XLS for Python library. Whether you're combining workbooks, merging worksheets, or automating bulk Excel file processing, this guide will help you save time and streamline your workflow with practical solutions.
Table of Contents
- Why Merge Excel Files with Python?
- Getting Started with Spire.XLS for Python
- How to Merge Multiple Excel Files into One Workbook using Python
- How to Combine Multiple Excel Worksheets into a Single Worksheet using Python
- Conclusion
- FAQs: Merge Excel Files with Python
Why Merge Excel Files with Python?
Using Python to merge Excel files brings several key advantages:
- Automation: Save time and eliminate repetitive manual work by automating the merging process.
- No Excel Dependency: Merge files without installing Microsoft Excel—ideal for headless, server-side, or cloud environments.
- Flexible Merging: Customize merging by selecting specific sheets, ranges, columns, or rows.
- Scalability: Handle hundreds or even thousands of Excel files with consistent performance.
- Error Reduction: Reduce manual errors and ensure data accuracy with automated scripts.
Whether you’re consolidating monthly reports or merging large datasets, Python helps streamline the process efficiently.
Getting Started with Spire.XLS for Python
Spire.XLS for Python is a standalone library that allows developers to create, read, edit, and save Excel files without the need for Microsoft Excel installation.
Key Features Include:
- Supports Multiple Formats: .xls, .xlsx, and more.
- Worksheet Operations: Copy, rename, delete, and merge worksheets seamlessly across workbooks.
- Formula & Formatting Preservation: Retain formulas and formatting during editing or merging.
- Advanced Features: Includes chart creation, conditional formatting, pivot tables, and more.
- File Conversion: Convert Excel files to PDF, HTML, CSV, and more.
Installation
Run the following pip command in your terminal or command prompt to install Spire.XLS from PyPI:
pip install spire.xls
How to Merge Multiple Excel Files into One Workbook using Python
When working with multiple Excel files, consolidating all worksheets into a single workbook can simplify data management and reporting. This approach preserves each original worksheet separately, making it easy to organize and review data from different sources such as department budgets, regional reports, or monthly summaries.
Steps
To merge multiple Excel files into a single workbook using Python, follow these steps:
- Loop through the files.
- Load each Excel file using LoadFromFile().
- For the first file, assign it as the base workbook.
- For subsequent files, copy all worksheets into the base workbook using AddCopy().
- Save the final combined workbook to a new file.
Code Example
import os
from spire.xls import *
# Folder containing Excel files to merge
input_folder = './sample_files'
# Output file name for the merged workbook
output_file = 'merged_workbook.xlsx'
# Initialize merged workbook as None
merged_workbook = None
# Iterate over all files in the input folder
for filename in os.listdir(input_folder):
# Process only Excel files with .xls or .xlsx extensions
if filename.endswith('.xlsx') or filename.endswith('.xls'):
file_path = os.path.join(input_folder, filename)
# Load the current Excel file into a Workbook object
source_workbook = Workbook()
source_workbook.LoadFromFile(file_path)
if merged_workbook is None:
# For the first file, assign it as the base merged workbook
merged_workbook = source_workbook
else:
# For subsequent files, copy each worksheet into the merged workbook
for i in range(source_workbook.Worksheets.Count):
sheet = source_workbook.Worksheets.get_Item(i)
merged_workbook.Worksheets.AddCopy(sheet, WorksheetCopyType.CopyAll)
# Save the combined workbook to the specified output file
merged_workbook.SaveToFile(output_file, ExcelVersion.Version2016)
How to Combine Multiple Excel Worksheets into a Single Worksheet using Python
Merging data from multiple Excel worksheets into one worksheet allows you to aggregate information efficiently, especially when working with data such as sales logs, survey responses, or performance reports.
Steps
To combine worksheet data from multiple Excel files into a single worksheet using Python, follow these steps:
- Create a new workbook and select its first worksheet as the destination.
- Loop through the files.
- Load each Excel file using LoadFromFile().
- Get the desired worksheet that you want to merge from the current file.
- Copy the used cell range from the desired worksheet to the destination worksheet, placing data consecutively below the previously copied content.
- Save the combined data into a new Excel file.
Code Example
import os
from spire.xls import *
# Folder containing Excel files to merge
input_folder = './excel_worksheets'
# Output file name for the merged workbook
output_file = 'merged_into_one_sheet.xlsx'
# Create a new workbook to hold merged data
merged_workbook = Workbook()
# Use the first worksheet in the new workbook as the merge target
merged_sheet = merged_workbook.Worksheets[0]
# Initialize the starting row for copying data
current_row = 1
# Loop through all files in the input folder
for filename in os.listdir(input_folder):
# Process only Excel files (.xls or .xlsx)
if filename.endswith('.xlsx') or filename.endswith('.xls'):
file_path = os.path.join(input_folder, filename)
# Load the current Excel file
workbook = Workbook()
workbook.LoadFromFile(file_path)
# Get the first worksheet from the current workbook
sheet = workbook.Worksheets[0]
# Get the used range from the first worksheet
source_range = sheet.Range
# Set the destination range in the merged worksheet starting at current_row
dest_range = merged_sheet.Range[current_row, 1]
# Copy data from the used range to the destination range
source_range.Copy(dest_range)
# Update current_row to the row after the last copied row to prevent overlap
current_row += sheet.LastRow
# Save the merged workbook to the specified output file in Excel 2016 format
merged_workbook.SaveToFile(output_file, ExcelVersion.Version2016)
Conclusion
When merging multiple Excel files into a single document—whether by appending sheets or combining data row by row—using a Python library like Spire.XLS enables automation and improves accuracy. This approach can help streamline workflows, especially in enterprise scenarios that require handling large datasets without relying on Microsoft Excel.
FAQs: Merge Excel Files with Python
Q1: Can I merge .xls and .xlsx files together?
A1: Yes. Spire.XLS handles both formats without needing conversion.
Q2: Do I need Excel installed on my machine to use Spire.XLS?
A2: No. Spire.XLS is standalone and works without Microsoft Office installed.
Q3: Can I merge only specific sheets from each workbook?
A3: Yes. You can customize your code to merge sheets by name or index. For example:
sheet = source_workbook.Worksheets["Summary"]
Q4: How do I avoid copying header rows multiple times?
A4: Add logic like:
if current_row > 1:
start_row = 2 # Skip header
else:
start_row = 1
Q5: Can I keep track of which file each row came from?
A5: Yes. Add a new column in the merged sheet containing the source file name for each row.
Q6: Is there a file size or row limit when using Spire.XLS?
A6: Spire.XLS follows the same row and column limits as Excel: .xlsx supports up to 1,048,576 rows × 16,384 columns, and .xls supports up to 65,536 rows × 256 columns.
Q7: Can I preserve formulas and formatting while merging?
A7: Yes. When merging Excel files, formatting and formulas are preserved.
