Merging Excel Files with Python: Preserve All Formatting & Formulas

If you work with Excel files daily, you know how tedious it is to combine dozens of scattered spreadsheets. Weekly store sales reports, departmental expense details, and other structured data—manually copying and pasting rows is time-consuming, error-prone, and downright frustrating for files with merged cells or complex formulas.

Previously, I relied on pandas for Excel merging, but it lacks robust support for native Excel formatting. Formulas break, styles disappear, and merged cells are often lost in the process. Then I discovered Free Spire.XLS for Python: a free library that perfectly preserves Excel’s original formatting, formulas, merged cells, and layouts. In this guide, I’ll share two efficient, production-ready methods to merge Excel files automatically.

---

Solution Overview

Free Spire.XLS for Python is a standalone Python library for reading and writing Excel files—no Microsoft Excel installation required. The free edition handles most daily merging tasks, and we’ll cover two widely used workflows:

1. Independent Worksheet Mode: Import every worksheet from each source file as a separate tab in the final workbook. Ideal for tracking data by its original file source.
2. Data Append Mode: Combine all source data into a single worksheet. Perfect for aggregating identical structured datasets (e.g., monthly sales reports).

---

Environment Setup

Ensure you have Python 3.7 or later installed, then install the library via pip:

pip install Spire.Xls.Free

Important Limitation: For .xls files, the free version restricts processing to 5 worksheets per file and 200 rows per worksheet.

---

Mode 1: One File = One Worksheet Set

This mode retains the full original structure of each file, with separate worksheets for easy source identification. Use it for reports from different departments, regions, or teams.

Full Code

import os
from spire.xls import *

# Path to the folder containing Excel files to merge
input_folder = './sample_files'
# Output path for the merged workbook
output_file = 'merged_workbook.xlsx'

merged_workbook = None

# Iterate through all files in the target folder
for filename in os.listdir(input_folder):
    # Filter for Excel file formats only
    if filename.endswith(('.xlsx', '.xls')):
        file_path = os.path.join(input_folder, filename)

        # Load the source Excel file
        source_workbook = Workbook()
        source_workbook.LoadFromFile(file_path)

        # Use the first file as the base workbook
        if merged_workbook is None:
            merged_workbook = source_workbook
        # Copy all worksheets from subsequent files to the merged workbook
        else:
            for i in range(source_workbook.Worksheets.Count):
                worksheet = source_workbook.Worksheets.get_Item(i)
                merged_workbook.Worksheets.AddCopy(worksheet, WorksheetCopyType.CopyAll)

# Save the final merged workbook
merged_workbook.SaveToFile(output_file, ExcelVersion.Version2016)

Key Details

• The first file acts as a template to preserve its original worksheet order.
• AddCopy(..., CopyAll) duplicates everything: cell styles, merged cells, charts, and formulas.
• Worksheet names auto-format to FileName_WorksheetName to prevent duplicates (customize the naming logic if needed).

---

Mode 2: All Data Combined into One Worksheet

This mode aggregates identically structured Excel files into a single master sheet (e.g., consolidating monthly transaction data). By default, we keep the header row from the first file and append only data rows from all other files.

Full Code

import os
from spire.xls import *

# Path to the folder with source Excel files
input_folder = './excel_worksheets'
# Output path for the consolidated file
output_file = 'merged_into_one_sheet.xlsx'

# Create a blank workbook for merging
merged_workbook = Workbook()
merged_sheet = merged_workbook.Worksheets[0]

# Track the next row to write data (Excel rows start at 1)
current_row = 1

# Process all Excel files in the folder
for filename in os.listdir(input_folder):
    if filename.endswith(('.xlsx', '.xls')):
        file_path = os.path.join(input_folder, filename)

        # Load and read the source file
        workbook = Workbook()
        workbook.LoadFromFile(file_path)
        worksheet = workbook.Worksheets[0]

        # Copy the used cell range to the merged sheet
        source_range = worksheet.Range
        target_range = merged_sheet.Range[current_row, 1]
        source_range.Copy(target_range)

        # Update row position for the next dataset
        current_row += worksheet.LastRow

# Save the consolidated workbook
merged_workbook.SaveToFile(output_file, ExcelVersion.Version2016)

Critical Notes

• Range.Copy() preserves cell values and formulas while maintaining formatting.
• current_row ensures seamless, non-overlapping data appending.
• Header Fix: The code above copies headers for all files. To keep only one header, replace the data range for subsequent files with:

  source_range = worksheet.Range[2, 1]  # Start copying from row 2 (skip header)

---

Final Thoughts

These two Python solutions automate Excel merging without needing Microsoft Excel, eliminating manual errors and saving hours of repetitive work. The Free Spire.XLS library’s native Excel support makes it far more reliable than pandas for formatting-sensitive tasks. Adapt the code to your workflow and integrate it into your daily data processing routine.