Published January 5, 2024
How to Concatenate or Merge Excel Files in C# Combine XLS
Introduction
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# combine xls.
How to merge Excel files in C#
- Create a new project in the Visual Studio
- Install IronXL library into the project
- Load all the Excel files that need to be merged.
- Extract all the sheets from the loaded Excel sheet.
- Add the Sheets to the new Excel file.
- 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: Because IronXL does not depend on the Excel application being installed on the PC, it performs better in these areas than Microsoft Interop.
- Readability and Simplicity: Without the disadvantages of Microsoft Interop, IronXL provides a more straightforward API that facilitates the reading, writing, and manipulation of Excel files.
- Compatibility and Dependency: Because IronXL does not require Microsoft Excel to be installed on the computer, it eliminates dependencies and compatibility problems with various Office or Excel versions.
- Platform Independence: IronXL offers greater flexibility and ease of deployment across a broader range of settings and platforms, whereas Microsoft Interop is more strongly associated with certain Microsoft Office versions.
For .NET developers who must operate with Excel files programmatically, IronXL is frequently a better option because of its speed, user-friendliness, and reduced requirement for installing additional applications.
The user's decision between IronXL and Microsoft Interop, however, may be influenced by variables such as the project's particulars, the infrastructure that already exists, and their level of expertise with each library.
Always keep your application's demands in mind when selecting among these options. Visit this page to learn more about the IronXL library.
Creating a New Project in Visual Studio
Click File from the File menu to launch the Visual Studio application. Click "new project," then choose "Console application."
Enter the project name in the designated text field after choosing the file location. Next, as seen in the sample below, click the Create button and choose the required .NET Framework.
The Visual Studio project's structure will then depend on the selected application. You may use the console, Windows, or web application to build or execute the application and add code by entering the program.cs file.
The library may then be added, and the code tested.
Installing IronXL Library
Install the IronXL library since the upcoming patch depends on it. Lastly, to finish this, open the NuGet Package Manager Console and type the following command:
Install-Package IronXL.Excel
As an alternative, you may look for the package "IronXL" using the NuGet Package Manager. From this list of all the NuGet packages linked to IronXL, we can select which one needs to be downloaded.
Combine Excel files using IronXL
We are able to combine multiple Excel files or merge multiple Excel worksheets, into a single Excel file or Worksheet using the IronXL library with a few lines of the below code.
using IronXL;
using IronBarCode;
using System;
using System.Data;
string[] Filpath = { "file1.xlsx", "file2.xlsx" };
WorkBook book=null;
WorkBook newbook = null;
WorksheetsCollection sheetcollection = null;
try
{
newbook = WorkBook.Create();
foreach (var file in Filpath)
{
book=WorkBook.LoadExcel(file);
sheetcollection = book.WorkSheets;
foreach(var sheet in sheetcollection)
{
newbook.WorkSheets.Add(sheet);
}
}
newbook.SaveAs("MergedBook.xls");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}using IronXL;
using IronBarCode;
using System;
using System.Data;
string[] Filpath = { "file1.xlsx", "file2.xlsx" };
WorkBook book=null;
WorkBook newbook = null;
WorksheetsCollection sheetcollection = null;
try
{
newbook = WorkBook.Create();
foreach (var file in Filpath)
{
book=WorkBook.LoadExcel(file);
sheetcollection = book.WorkSheets;
foreach(var sheet in sheetcollection)
{
newbook.WorkSheets.Add(sheet);
}
}
newbook.SaveAs("MergedBook.xls");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}Imports IronXL
Imports IronBarCode
Imports System
Imports System.Data
Private Filpath() As String = { "file1.xlsx", "file2.xlsx" }
Private book As WorkBook=Nothing
Private newbook As WorkBook = Nothing
Private sheetcollection As WorksheetsCollection = Nothing
Try
newbook = WorkBook.Create()
For Each file In Filpath
book=WorkBook.LoadExcel(file)
sheetcollection = book.WorkSheets
For Each sheet In sheetcollection
newbook.WorkSheets.Add(sheet)
Next sheet
Next file
newbook.SaveAs("MergedBook.xls")
Catch ex As Exception
Console.WriteLine(ex.ToString())
End TryIn the above first, we are creating a list of multiple files. We are creating the two objects for the Excel workbook to load the existing file and to create new merge Excel files. By using the for-each loop we are passing the input Excel workbooks into the previously created objects.
We are loading the Excel file with the help of the LoadExcel method which is available in the WorkBook object. After Loading the Excel files we try fetch multiple Excel worksheets with the help of the worksheet method into worksheet collection.
The worksheet collection can be processed only once with the help of the for-each loop. Merging Excel files can easily be done with the help of the Add method on the worksheet object. With this, we can merge multiple Excel workbooks and save the merge multiple worksheets into new Excel files with the help of the SaveAs method which allows us to pass the file name as a parameter. The merged Excel workbook, Excel file, or Excel worksheet can be saved at the end of the process.
Below is the sample file that we are using to merge Excel files.
Result:
To know more about the IronXL code check here.
Conclusion
One of the most popular add-ons for Excel is IronXL. It is independent of any further external libraries. Since Microsoft Excel is a self-contained program, there is no need to install it. It operates through a variety of channels. On the other hand, to edit Word documents, the Interop library needs other libraries to parse files.
For any programming procedure that makes use of Microsoft Excel documents, IronXL is a complete solution. Calculations, text or number sorting, trimming, adding, finding and replacing, merging and unmerging, and file storing are some potential operations. It is possible to generate and assess spreadsheet data as well as new cell data types. It facilitates reading, writing, and managing Excel data.
The initial cost of IronXL is $liteVersion. Alternatively, customers can pay a one-year membership fee to receive support and software upgrades. For a charge, IronXL protects unauthorized redistribution. For a free trial of IronXL, click this link. For more specific pricing details, go to the IronXL licensing website. Visit this page for further details about Iron Software products.
