如何使用C#将3个工作表从不同的Excel文件合并到一个工作表中 [英] How to merge 3 Sheets from different Excel files into one sheet with C#

问题描述

在这里，我尝试使用下面提到的代码(Spire.Xls dll)将两个Excel文件合并到一个Excel工作表中.

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

这是两个excel合并的代码.

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);

但是问题是，当我尝试使用相同的逻辑合并三个excel文件时，输出与两个合并的excel输出相同，第三个excel没有合并.

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.

这是三个excel文件的代码.

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);

推荐答案

在第二个示例中，您没有使用相同的逻辑，因此它不起作用.在第二个代码上尝试类似的操作或对其进行一些更改:

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)

上面的代码使用sheet3，并向其中插入2个数据表-一个来自sheet2的数据表，另一个来自sheet1的数据表.

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

这篇关于如何使用C#将3个工作表从不同的Excel文件合并到一个工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！