Tutorial: Merging Multiple Excel Files with Aspose.Cells Cloud API

Prerequisites

Before starting this tutorial, make sure you have:

An Aspose Cloud account with an active subscription or free trial
Your Client ID and Client Secret from the Aspose Cloud Dashboard
Basic understanding of REST API concepts
Multiple Excel files for testing merge operations
Familiarity with your preferred programming language

Understanding Excel Workbook Merging

Merging Excel files is a common business requirement that allows you to:

Consolidate data from different departments
Combine reports from multiple time periods
Create comprehensive dashboards from various data sources
Build master documents from individual contributor sheets

Aspose.Cells Cloud provides multiple approaches to merge workbooks:

Merging one workbook into another
Merging multiple workbooks into a new file
Direct merging without using cloud storage

Let’s explore each approach step by step.

1. Merging One Workbook into Another

This method allows you to add worksheets from one workbook into an existing target workbook.

Try It Yourself: Merging Workbooks

Follow these steps to merge two Excel files:

Upload both workbooks to your cloud storage
Make the API request to merge the source workbook into the target
Download and verify the merged workbook

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/target.xlsx/merge?mergeWith=source.xlsx" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN"

SDK Examples

Python

# Tutorial Code Example - Merging One Workbook into Another
import asposecellscloud
from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.requests import *

# Configure authentication
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"
api = CellsApi(client_id, client_secret)

# Specify file names
target_file = "target.xlsx"  # The file that will receive the merged content
source_file = "source.xlsx"  # The file to merge from

# Create a request to merge workbooks
request = PostWorkbooksMergeRequest(
    name=target_file,
    merge_with=source_file
)

# Execute the request
response = api.post_workbooks_merge(request)

# Check if the merge was successful
if response.code == 200:
    print(f"Success! '{source_file}' has been merged into '{target_file}'.")
    print(f"Merged workbook properties: {response.workbook}")
else:
    print(f"Error merging workbooks: {response.status}")

# You can download the merged file using:
# download_request = DownloadFileRequest(path=target_file)
# api.download_file(download_request)

C#

// Tutorial Code Example - Merging One Workbook into Another
using System;
using Aspose.Cells.Cloud.SDK.Api;
using Aspose.Cells.Cloud.SDK.Request;

namespace AsposeCellsCloudTutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            // Configure authentication
            var clientId = "YOUR_CLIENT_ID";
            var clientSecret = "YOUR_CLIENT_SECRET";
            var api = new CellsApi(clientId, clientSecret);
            
            // Specify file names
            var targetFile = "target.xlsx";  // The file that will receive the merged content
            var sourceFile = "source.xlsx";  // The file to merge from
            
            // Create a request to merge workbooks
            var request = new PostWorkbooksMergeRequest
            {
                Name = targetFile,
                MergeWith = sourceFile
            };
            
            try
            {
                // Execute the request
                var response = api.PostWorkbooksMerge(request);
                
                // Check if the merge was successful
                Console.WriteLine($"Success! '{sourceFile}' has been merged into '{targetFile}'.");
                Console.WriteLine($"Merged workbook properties: {response.Workbook}");
                
                // You can download the merged file using:
                // var downloadRequest = new DownloadFileRequest(targetFile);
                // api.DownloadFile(downloadRequest);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error merging workbooks: {ex.Message}");
            }
        }
    }
}

2. Merging Multiple Files Without Using Storage

For scenarios where you want to merge files without first uploading them to cloud storage, you can use the direct merge approach.

Try It Yourself: Direct Merging

Prepare your Excel files locally
Make the API request to merge them directly
Save the returned merged file

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/merge?format=xlsx&mergeToOneSheet=false" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN" \
-H "Content-Type: multipart/form-data" \
-F "file1=@workbook1.xlsx" \
-F "file2=@workbook2.xlsx" \
-F "file3=@workbook3.xlsx"

Python Example

# Tutorial Code Example - Merging Multiple Files Without Storage
import requests
import json
import base64

# Configure authentication (get your own bearer token using client credentials)
bearer_token = "YOUR_ACCESS_TOKEN"

# API endpoint
url = "https://api.aspose.cloud/v3.0/cells/merge"

# Merge parameters
format = "xlsx"  # Output format
merge_to_one_sheet = "false"  # Keep sheets separate

# Local file paths
file_paths = [
    "workbook1.xlsx",
    "workbook2.xlsx",
    "workbook3.xlsx"
]

# Prepare the request
headers = {
    "accept": "application/json",
    "authorization": f"Bearer {bearer_token}"
}

# Prepare the files
files = {}
for i, path in enumerate(file_paths):
    files[f"file{i+1}"] = open(path, "rb")

# Submit the request
response = requests.post(
    f"{url}?format={format}&mergeToOneSheet={merge_to_one_sheet}",
    headers=headers,
    files=files
)

# Remember to close the file handles
for file in files.values():
    file.close()

# Process the response
if response.status_code == 200:
    result = response.json()
    
    # The API returns the merged file in the response
    for file_info in result["Files"]:
        filename = file_info["Filename"]
        file_content_base64 = file_info["FileContent"]
        
        # Decode the file content
        file_content = base64.b64decode(file_content_base64)
        
        # Save the merged file
        with open(f"merged_{filename}", "wb") as f:
            f.write(file_content)
        
        print(f"Merged file saved as merged_{filename}")
else:
    print(f"Error merging files: {response.status_code}")
    print(response.text)

3. Advanced Merging Options

Aspose.Cells Cloud provides various options to control how workbooks are merged.

Merging to a Single Sheet

If you want to combine all data into a single worksheet instead of maintaining separate sheets, you can set the mergeToOneSheet parameter to true.

# Tutorial Code Example - Merging to a Single Sheet
import requests
import base64

# Configure authentication
bearer_token = "YOUR_ACCESS_TOKEN"
url = "https://api.aspose.cloud/v3.0/cells/merge"

# Set merge_to_one_sheet to true to combine all data in one sheet
merge_to_one_sheet = "true"
format = "xlsx"

# Prepare files and request (similar to previous example)
# ...

# Submit the request
response = requests.post(
    f"{url}?format={format}&mergeToOneSheet={merge_to_one_sheet}",
    headers=headers,
    files=files
)

# Process response (similar to previous example)
# ...

Troubleshooting Tips

When merging Excel workbooks, you might encounter these common issues:

Duplicate sheet names:
- By default, Aspose.Cells Cloud handles duplicate sheet names by adding numbers
- Be aware that references between sheets might break during merging
Formula references:
- Formulas that reference cells across workbooks might not work after merging
- Review and update cross-sheet references after merging
Styles and formatting:
- Different workbooks might have different styles and themes
- The merged workbook will maintain original formatting from each source
File size limitations:
- Be mindful of the total size when merging many workbooks
- Consider batch processing for very large merge operations

What You’ve Learned

In this tutorial, you’ve learned how to:

Merge one Excel workbook into another using cloud storage
Combine multiple workbooks without using cloud storage
Control merge options like combining sheets or keeping them separate
Handle common issues that arise during workbook merging

Further Practice

To reinforce your learning, try these exercises:

Merge multiple workbooks with different structures and formatting
Create a utility that merges all Excel files in a specified directory
Experiment with merging workbooks into a single sheet versus keeping separate sheets

Tutorial: Merging Multiple Excel Files with Aspose.Cells Cloud API

Prerequisites

Understanding Excel Workbook Merging

1. Merging One Workbook into Another

Try It Yourself: Merging Workbooks

Using cURL

SDK Examples

Python

C#

2. Merging Multiple Files Without Using Storage

Try It Yourself: Direct Merging

Using cURL

Python Example

3. Advanced Merging Options

Merging to a Single Sheet

Troubleshooting Tips

What You’ve Learned

Further Practice

Additional Resources