How to Concatenate or Merge Excel Files in C# Combine XLS

Microsoft created Microsoft Excel, a powerful spreadsheet program. It is a commonly used tool for organizing, analyzing, and visualizing data. It is a component of the Microsoft Office suite. Excel is a flexible application that may be used by people, professionals, academics, and corporations due to its many capabilities.

Many industries, including finance, accounting, business analysis, data analysis, research, education, and more, utilize Excel extensively. It is a preferred tool for organizing, evaluating, and displaying data in both personal and professional contexts due to its broad feature set and adaptability.

IronXL is a powerful Excel library that can be used to perform various types of Excel operations. In this article, we are going learn how to concatenate or merge Excel files in C#.

How to merge Excel files in C#

1. Create a new project in Visual Studio
2. Install IronXL library into the project
3. Load all the Excel files that need to be merged
4. Extract all the sheets from the loaded Excel sheet
5. Add the Sheets to a new Excel file
6. Save them into a separate file

IronXL Library

IronXL is a substitute for Microsoft Interop when it comes to managing Excel files in .NET applications. While IronXL offers a more straightforward, effective, and powerful method of manipulating Excel files programmatically in .NET settings, Microsoft Interop requires the use of the Interop components to communicate with Excel.

The use of IronXL has the following benefits:

• Performance and Resource Economy: IronXL performs better because it does not depend on the Excel application being installed on the PC.
• Readability and Simplicity: IronXL provides a more straightforward API, making it easier to read, write, and manipulate Excel files without the complexities of Microsoft Interop.
• Compatibility and Dependency: Since IronXL does not require Microsoft Excel to be installed, it eliminates dependencies and compatibility issues with various Office or Excel versions.
• Platform Independence: IronXL offers greater flexibility and ease of deployment across different environments and platforms, whereas Microsoft Interop is more closely tied to specific Microsoft Office versions.

For .NET developers who need to work with Excel files programmatically, IronXL is often a better choice due to its performance, user-friendliness, and reduced reliance on additional applications.

The decision between IronXL and Microsoft Interop may be influenced by factors such as the project's specific requirements, existing infrastructure, and the developer's familiarity with each library. Always consider your application's needs in making this choice. Visit this page to learn more about the IronXL library.

Creating a New Project in Visual Studio

To launch the Visual Studio application, click "File" from the menu, then "New Project," and choose "Console application."

Enter the project name and the file location. Click the "Create" button and choose the required .NET Framework as shown below.

The project's structure will depend on the selected application type. Use the console, Windows, or web application to build or execute the application and add code by entering the Program.cs file.

Then the library can be added, and the code tested.

Installing IronXL Library

To install the IronXL library, open the NuGet Package Manager Console and type the following command:

Install-Package IronXL.Excel

Alternatively, use the NuGet Package Manager to search for "IronXL" and download it from the list of related packages.

Combine Excel files using IronXL

With IronXL, we can combine multiple Excel files or worksheets into a single Excel file or worksheet using the following code:

using IronXL;
using System;

class Program
{
    static void Main(string[] args)
    {
        // Array of file paths for the Excel files to be merged
        string[] filePaths = { "file1.xlsx", "file2.xlsx" };

        // Create a new workbook to add sheets from the existing files
        WorkBook newWorkBook = WorkBook.Create();

        try
        {
            foreach (var filePath in filePaths)
            {
                // Load the existing Excel file into a workbook
                WorkBook existingWorkbook = WorkBook.LoadExcel(filePath);

                // Retrieve the sheets from the loaded workbook
                WorksheetsCollection sheetCollection = existingWorkbook.WorkSheets;

                // Add each sheet from the existing workbook to the new workbook
                foreach (var sheet in sheetCollection)
                {
                    newWorkBook.WorkSheets.Add(sheet);
                }
            }

            // Save the new workbook with a merged sheet collection
            newWorkBook.SaveAs("MergedBook.xls");
        }
        catch (Exception ex)
        {
            // Output any exceptions encountered during the merging process
            Console.WriteLine(ex.ToString());
        }
    }
}

using IronXL;
using System;

class Program
{
    static void Main(string[] args)
    {
        // Array of file paths for the Excel files to be merged
        string[] filePaths = { "file1.xlsx", "file2.xlsx" };

        // Create a new workbook to add sheets from the existing files
        WorkBook newWorkBook = WorkBook.Create();

        try
        {
            foreach (var filePath in filePaths)
            {
                // Load the existing Excel file into a workbook
                WorkBook existingWorkbook = WorkBook.LoadExcel(filePath);

                // Retrieve the sheets from the loaded workbook
                WorksheetsCollection sheetCollection = existingWorkbook.WorkSheets;

                // Add each sheet from the existing workbook to the new workbook
                foreach (var sheet in sheetCollection)
                {
                    newWorkBook.WorkSheets.Add(sheet);
                }
            }

            // Save the new workbook with a merged sheet collection
            newWorkBook.SaveAs("MergedBook.xls");
        }
        catch (Exception ex)
        {
            // Output any exceptions encountered during the merging process
            Console.WriteLine(ex.ToString());
        }
    }
}

Imports IronXL
Imports System

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		' Array of file paths for the Excel files to be merged
		Dim filePaths() As String = { "file1.xlsx", "file2.xlsx" }

		' Create a new workbook to add sheets from the existing files
		Dim newWorkBook As WorkBook = WorkBook.Create()

		Try
			For Each filePath In filePaths
				' Load the existing Excel file into a workbook
				Dim existingWorkbook As WorkBook = WorkBook.LoadExcel(filePath)

				' Retrieve the sheets from the loaded workbook
				Dim sheetCollection As WorksheetsCollection = existingWorkbook.WorkSheets

				' Add each sheet from the existing workbook to the new workbook
				For Each sheet In sheetCollection
					newWorkBook.WorkSheets.Add(sheet)
				Next sheet
			Next filePath

			' Save the new workbook with a merged sheet collection
			newWorkBook.SaveAs("MergedBook.xls")
		Catch ex As Exception
			' Output any exceptions encountered during the merging process
			Console.WriteLine(ex.ToString())
		End Try
	End Sub
End Class

Explanation:

1. File Paths: An array is used to store the file paths for all Excel files intended to be merged.
2. Workbooks Creation: A new Excel workbook is created with WorkBook.Create() to store all combined sheets.
3. File Loading: Iterates over the file paths, loading each Excel file into a WorkBook object using WorkBook.LoadExcel(filePath).
4. Sheet Collection: Retrieves all the worksheets from each loaded workbook.
5. Sheet Addition: Adds each worksheet from the existing files to the new workbook using the Add method.
6. File Saving: The new workbook is saved as "MergedBook.xls", thus completing the merge process.

Below is the sample input file used for merging Excel files.

Resulting merged file:

For more information about IronXL, visit this page.

Conclusion

IronXL is a popular add-on for Excel as it operates independently of any other external libraries. Since Microsoft Excel is self-contained, there is no need to have it installed separately, and tasks can be performed without additional dependencies. This contrasts with the Interop library, which requires other libraries to parse files for Word documents.

IronXL offers new ways to handle Microsoft Excel documents in programming. It allows operations like calculations, sorting, trimming, finding and replacing, and file storing, among others. It enables reading, writing, and managing Excel data efficiently.

The initial cost of IronXL is a $749. Alternatively, customers can opt for a one-year membership fee to receive support and software upgrades. A paid plan also offers protection against unauthorized redistribution. For a free trial of IronXL, click this link. For detailed pricing information, visit the IronXL licensing website. For further details on Iron Software products, visit this page.