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

Excel merge is frequently used to merge multiples files into a single excel file for the convenience of reading and saving. Sometimes, people feel annoyed when they have to open a lot of excel files simultaneously. Thus, Merging Excel of the same type or category saves much time and escapes many troubles. In this section, I will introduce a very simple method to merge excel files via a .NET Excel component with C#, VB.NET.

Spire.XLS for .NET, built in C#, VB.NET, enables you to merge Excel files into one through two main steps. One is to load all Excel files you need to merge from system by calling the method Workbook.LoadFromFile(string fileName, ExcelVersion version). The other is to import data of the second, third, fourth…last into the first worksheet by Worksheet.InsertDataTable(DataTable dataTable, bool columnHeaders, int firstRow, int firstColumn). Please see merge excel merge picture as below.

Merge Excel Files

Now, download Spire.XLS for .NET and view the whole procedure of Excel merge.

Sample Code:

[C#]
using Spire.Xls;
namespace MergeExFi
{
    class Program
    {
        static void Main(string[] args)
        {
            //load the first workbook
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\excel1.xls", ExcelVersion.Version97to2003);
            //load the second workbook
            Workbook workbook2 = new Workbook();
            workbook2.LoadFromFile(@"..\excel2.xls", ExcelVersion.Version97to2003);
            //load the third workbook
            Workbook workbook3 = new Workbook();
            workbook3.LoadFromFile(@"..\excel3.xls", ExcelVersion.Version97to2003);
            //import the second workbook's worksheet into the first workbook using a datatable
            Worksheet sheet2 = workbook2.Worksheets[0];
            DataTable dataTable = sheet2.ExportDataTable();
            Worksheet sheetAdd = workbook.CreateEmptySheet("Country");
            sheetAdd.InsertDataTable(dataTable, true, 1, 1);
            //import the third workbook's worksheet into the first workbook using a datatable
            Worksheet sheet3 = workbook3.Worksheets[0];
            DataTable dataTable2 = sheet3.ExportDataTable();
            Worksheet sheetAdd2= workbook.CreateEmptySheet("Excel");
            sheetAdd2.InsertDataTable(dataTable2, true, 1, 1);
            //save the workbook
            workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003);
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace MergeExFi
    Class Program
        Private Shared Sub Main(ByVal args() As String)
            'load the first workbook
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("..\excel1.xls", ExcelVersion.Version97to2003)
            'load the second workbook
            Dim workbook2 As Workbook = New Workbook
            workbook2.LoadFromFile("..\excel2.xls", ExcelVersion.Version97to2003)
            'load the third workbook
            Dim workbook3 As Workbook = New Workbook
            workbook3.LoadFromFile("..\excel3.xls", ExcelVersion.Version97to2003)
            'import the second workbook's worksheet into the first workbook using a datatable
            Dim sheet2 As Worksheet = workbook2.Worksheets(0)
            Dim dataTable As DataTable = sheet2.ExportDataTable
            Dim sheetAdd As Worksheet = workbook.CreateEmptySheet("Country")
            sheetAdd.InsertDataTable(dataTable, true, 1, 1)
            'import the third workbook's worksheet into the first workbook using a datatable
            Dim sheet3 As Worksheet = workbook3.Worksheets(0)
            Dim dataTable2 As DataTable = sheet3.ExportDataTable
            Dim sheetAdd2 As Worksheet = workbook.CreateEmptySheet("Excel")
            sheetAdd2.InsertDataTable(dataTable2, true, 1, 1)
            'save the workbook
            workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003)
        End Sub
    End Class
End Namespace