How to merge 3 Sheets from different Excel files i

Question

Here I tried to merge two excel files into one excel sheet using below mentioned code (Spire.Xls dll) its working fine.

Here is code for two excel merging.

 workbook = new Workbook();
 //load the first workbook
 workbook.LoadFromFile(ArrayExcelFiles[0]);
 //load the second workbook
 Workbook workbook2 = new Workbook();
 workbook2.LoadFromFile(ArrayExcelFiles[1]);
 //import the second workbook's worksheet into the first workbook using a datatable
 Worksheet sheet2 = workbook2.Worksheets[0];
 DataTable dataTable = sheet2.ExportDataTable();
 Worksheet sheet1 = workbook.Worksheets[0];
 sheet1.InsertDataTable(dataTable, false, sheet1.LastRow + 1, 1);

 workbook.SaveToFile(OutputPath + "Merged.xls", ExcelVersion.Version2007);

But the problem is that when am trying to merge three excel files using same logic the output was same as two merged excel output third excel is not merged.

Here is the code for three excel files.

Workbook workbook1 = new Workbook();
//load the first workbook
workbook1.LoadFromFile(ArrayExcelFiles[0]);
//load the second workbook
Workbook workbook2 = new Workbook();
workbook2.LoadFromFile(ArrayExcelFiles[1]);
//load the third workbook
Workbook workbook3 = new Workbook();
workbook3.LoadFromFile(ArrayExcelFiles[2]);

//import the second workbook's worksheet into the first workbook using a datatable
Worksheet sheet3 = workbook3.Worksheets[0];
DataTable dataTable = sheet3.ExportDataTable();
Worksheet sheet2 = workbook2.Worksheets[0];
dataTable = sheet2.ExportDataTable();
Worksheet sheet1 = workbook1.Worksheets[0];
sheet1.InsertDataTable(dataTable, false, sheet1.LastRow + 1, 1);
workbook1.SaveToFile(OutputPath + "Merged.xls", ExcelVersion.Version2007);

Answer 1

You are not using the same logic in the second example, thus it does not work. Try something like this on the second code or change it a bit:

//import the second workbook's worksheet into the first workbook using a datatable
Worksheet sheet3 = workbook3.Worksheets[0];

Worksheet sheet2 = workbook2.Worksheets[0];
DataTable dataTable2 = sheet2.ExportDataTable();

Worksheet sheet1 = workbook1.Worksheets[0];
DataTable dataTable1 = sheet1.ExportDataTable();

sheet3.InsertDataTable(dataTable2, false, sheet3.LastRow + 1, 1)
sheet3.InsertDataTable(dataTable1, false, sheet3.LastRow + 1, 1)

The code above takes sheet3 and inserts 2 data tables to it - one from sheet2 and one from sheet1.

Answer 2

You only ever insert the sheet from workbook2, so it's not surprising. You got datatable from workbook3, but then immediately over-wrote it with data from workbook2, before you did anything else with it. You can't store two tables in one DataTable object. The last one simply replaces the first one (just like any other variable assignment).

You need to run two separate insertDataTable commands with two separate data tables, one from each sheet:

//import the second and third workbooks' worksheets into the first workbook using a datatable
Worksheet sheet2 = workbook2.Worksheets[0];
DataTable dataTable = sheet2.ExportDataTable();

Worksheet sheet3 = workbook3.Worksheets[0];
DataTable dataTable2 = sheet3.ExportDataTable();

Worksheet sheet1 = workbook1.Worksheets[0];
sheet1.InsertDataTable(dataTable, false, sheet1.LastRow + 1, 1);
sheet1.InsertDataTable(dataTable2, false, sheet1.LastRow + 1, 1);
workbook1.SaveToFile(OutputPath + "Merged.xls", ExcelVersion.Version2007);

Of course the code could be made a lot neater and less repetitive by using loops etc, but this is the basic solution.