An Excel document may contain multiple worksheets. Sometimes in order to be convenient, we need to merge two or more Excel files into one Excel document. Since a single Excel file is easy to save and read. Imagine that you have five Excel files. You have to open five windows in order to get the information of each, which is very likely to mix and make mistakes. But things become different if you merge these files into one Excel document. Now, let us look at how to merge Excel files with C#, VB.NET.

In my method, I merge three Excel files into one. The Excel component that I use is Spire.XLS. It supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight. If necessary, you can Freely Install Spire.XLS on your system.

How to merge multiple Excel files into one document with C#, VB.NET

Using Spire.XLS, I only need three simple steps to realize the whole task in minutes. Please look at the below procedure.

Step1. Create a new project

1.      Create a new project in Visual Studio.

2.      Set the Target Framework in Property to be .NET Framework 2 or above.

3.      Add Spire.XLS DLL as reference.

Step2. Merge multiple Excel files into one document

1.      Load Excel files from system

C# Code:

            //load the first workbook
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\Data\parts.xls", ExcelVersion.Version97to2003);

            //load the second workbook
            Workbook workbook2 = new Workbook();
            workbook2.LoadFromFile(@"E:\Data\country.xls", ExcelVersion.Version97to2003);

            //load the third workbook
            Workbook workbook3 = new Workbook();
            workbook3.LoadFromFile(@"E:\Data\excel.xls", ExcelVersion.Version97to2003);

VB.NET Code:

           'load the first workbook
            Dim workbook As New Workbook()
            workbook.LoadFromFile("E:\Data\parts.xls", ExcelVersion.Version97to2003)

            'load the second workbook
            Dim workbook2 As New Workbook()
            workbook2.LoadFromFile("E:\Data\country.xls", ExcelVersion.Version97to2003)

            'load the third workbook

             Dim workbook3 As New Workbook()
             workbook3.LoadFromFile("E:\Data\excel.xls", ExcelVersion.Version97to2003)

2.      Merge three Excel files loaded from system into one document

C# Code:

            //insert the second workbook's worksheet into the first workbook using dataTable
            Worksheet sheet2 = workbook2.Worksheets[0];
            DataTable dataTable = sheet2.ExportDataTable();
            Worksheet sheetAdd = workbook.CreateEmptySheet("Country");
            sheetAdd.InsertDataTable(dataTable, true, 1, 1);

            //insert the third workbook's worksheet into the first workbook using dataTable2
            Worksheet sheet3 = workbook3.Worksheets[0];
            DataTable dataTable2 = sheet3.ExportDataTable();
            Worksheet sheetAdd2= workbook.CreateEmptySheet("Excel");
            sheetAdd2.InsertDataTable(dataTable2, true, 1, 1);

VB.NET Code:

           'insert the second workbook's worksheet into the first workbook using the 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)

            'insert the third workbook's worksheet into the first workbook using dataTable2
            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)

Step3. Save and Launch the file.

C# Code:

            //save the workbook
            workbook.SaveToFile("cellsMerge.xls", ExcelVersion.Version97to2003);

            //launch the workbook

VB.NET Code:

           'save the workbook
           workbook.SaveToFile("cellsMerge.xls", ExcelVersion.Version97to2003)

           'launch the workbook

As you see, the whole task can be very easy by using Spire.XLS. If you have more than three excel files you need to merge, you also can add another children step in Step2. Please note that the above method is just merge the data information of three excel files into one excel document. The Excel data format such as hyperlink, font color, background color and so on will not be merged into the final Excel document.  

More Excel Functions

Leave a Reply.