Merge Excel Files into One in C#, VB.NET

Sometimes, people feel annoyed when they have to open a lot of Excel files simultaneously. Thus, Merging Excel files of the same type or category can help us avoid the trouble and save us much time. Besides, we would also like to merge multiple files in a single file for easy sharing and saving.

In the following section, an easy method is introduced to show how users can merge Excel files into one using Spire.XLS in C# and VB.NET.

Code Snippet:

Step 1: Create a new Workbook and remove the blank worksheets.

Workbook newbook = new Workbook();
newbook.Version = ExcelVersion.Version2010;
newbook.Worksheets.Clear();

Step 2: Create a temporary Workbook and load the sample files that you want to merge. Traversal every worksheet in tempbook, and add the copy of each worksheet to the new Workbook using AddCopy() method. By doing so, all the worksheets in the sample files will be added to the new Workbook.

Workbook tempbook = new Workbook();
string[] excelFiles = new String[] { "sample1.xlsx", "sample2.xlsx", "sample3.xlsx" };
for (int i = 0; i < excelFiles.Length; i++)
{
    tempbook.LoadFromFile(excelFiles[i]);
    foreach (Worksheet sheet in tempbook.Worksheets)
    {
        newbook.Worksheets.AddCopy(sheet);
    }
}

Step 3: Save the changes to the new Workbook and launch the file.

newbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");  

Output:

Each sample file only contains one worksheet in its workbook, therefore there would be three sheets in the result file.

Merge Excel Files into One in C#, VB.NET

Full Code:

[C#]
Workbook newbook = new Workbook();
newbook.Version = ExcelVersion.Version2010;
newbook.Worksheets.Clear();

Workbook tempbook = new Workbook();
string[] excelFiles = new String[] { "sample1.xlsx", "sample2.xlsx", "sample3.xlsx" };
for (int i = 0; i < excelFiles.Length; i++)
{
    tempbook.LoadFromFile(excelFiles[i]);
    foreach (Worksheet sheet in tempbook.Worksheets)
    {
        newbook.Worksheets.AddCopy(sheet);
    }
}

newbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
[VB.NET]
Dim newbook As New Workbook()
newbook.Version = ExcelVersion.Version2010
newbook.Worksheets.Clear()

Dim tempbook As New Workbook()
Dim excelFiles As String() = New [String]() {"sample1.xlsx", "sample2.xlsx", "sample3.xlsx"}
For i As Integer = 0 To excelFiles.Length - 1
	tempbook.LoadFromFile(excelFiles(i))
	For Each sheet As Worksheet In tempbook.Worksheets
		newbook.Worksheets.AddCopy(sheet)
	Next
Next

newbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("result.xlsx")