How to Merge Excel XLSX Spreadsheets in Python

Learn how to programmatically combine multiple Excel spreadsheets into a single document.

If you work in an environment (like many) where Excel spreadsheets are used exhaustively for data management and report generation, you’ve probably dreamed of automating Excel workflows. Excel workflow automations have the potential to save tons of time in the long run, especially if you carefully target the most repetitive, monotonous tasks.

If automating Excel file merging is one of the workflows you’ve dreamed of, I have good news for you: there’s an API for that! Programmatically merging Excel files is a perfect way to consolidate and manage data sets more efficiently.

In this tutorial, we’ll learn how to call a Cloudmersive API that allows us to merge multiple (10+) Excel files into a single document. Our resulting spreadsheet will display worksheets from all our original input Excel files in the order we loaded them. We’ll have our API call ready to go in a few quick steps!

Step 1: Install the SDK

Our first order of business is client SDK installation. To install the client, we can run the following pip install command:

pip install cloudmersive-convert-api-client

Step 2: Add the Imports

With installation out of the way, it’s time to add the following imports to our controller:

from __future__ import print_function
import time
import cloudmersive_convert_api_client
from cloudmersive_convert_api_client.rest import ApiException
from pprint import pprint

Step 3: API Key Configuration

At this point, we can take care of our API configuration. We’ll use this code to authenticate our Cloudmersive API key:

# Configure API key authorization: Apikey
configuration = cloudmersive_convert_api_client.Configuration()
configuration.api_key['Apikey'] = 'YOUR_API_KEY'

If we don’t have a Cloudmersive API key, we can fix that easily - we’ll just need to create a free account on the Cloudmersive website. That’ll give us a free API key with a limit of 800 API calls per month and zero additional commitments.

Step 4: Instance the API and Call the Function

We’re at the point now where we can 1) create an instance of the Excel merge API and 2) call the file merging function. This API requires at least two file inputs, and we can expand our operation past 10 inputs by adjusting the code appropriately:

# create an instance of the API class
api_instance = cloudmersive_convert_api_client.MergeDocumentApi(cloudmersive_convert_api_client.ApiClient(configuration))
input_file1 = '/path/to/inputfile' # file | First input file to perform the operation on.
input_file2 = '/path/to/inputfile' # file | Second input file to perform the operation on.
input_file3 = '/path/to/inputfile' # file | Third input file to perform the operation on. (optional)
input_file4 = '/path/to/inputfile' # file | Fourth input file to perform the operation on. (optional)
input_file5 = '/path/to/inputfile' # file | Fifth input file to perform the operation on. (optional)
input_file6 = '/path/to/inputfile' # file | Sixth input file to perform the operation on. (optional)
input_file7 = '/path/to/inputfile' # file | Seventh input file to perform the operation on. (optional)
input_file8 = '/path/to/inputfile' # file | Eighth input file to perform the operation on. (optional)
input_file9 = '/path/to/inputfile' # file | Ninth input file to perform the operation on. (optional)
input_file10 = '/path/to/inputfile' # file | Tenth input file to perform the operation on. (optional)

try:
    # Merge Multple Excel XLSX Together
    api_response = api_instance.merge_document_xlsx_multi(input_file1, input_file2, input_file3=input_file3, input_file4=input_file4, input_file5=input_file5, input_file6=input_file6, input_file7=input_file7, input_file8=input_file8, input_file9=input_file9, input_file10=input_file10)
    pprint(api_response)
except ApiException as e:
    print("Exception when calling MergeDocumentApi->merge_document_xlsx_multi: %s\n" % e)

That’s all the code we’ll need! We can write the response from this operation to a new Excel (XLSX) file, and our file merge operation is complete.

If you have any questions for us after this tutorial, please don’t hesitate to reach out!