C# Merge Excel Sheets into one Sheet

See more:

C#

VS2015

Hello Expert:

I have Excel reports generated by application. the format is xls.

Due to row limits, it will split the content into multiple sheets if the total number of records is bigger than 65536.

I am trying to copy all records from each sheet and merge them into one sheet then save the file in xlsx format.

Originally I copy all the cells into array then write it to the target sheet.

It works fine but when I write it into the target sheet, the datetime is showing as double.

I want to remove human interaction when doing the merge, so I switch to PasteSpecial, which keep the format, but it has one problem, it seems I can only copy and paste the first sheet. when it tries to paste the second time I receive an error.

"The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:

Click a single cell, and then paste.
Select a rectangle that's the same size and shape, and then paste."
Following is my code:

app = Globals.ThisAddIn.Application;

sourceBook = app.ActiveWorkbook;

     
targetBook = app.Workbooks.Add(Type.Missing);                
targetSheet = targetBook.Worksheets.Add(Type.Missing);
targetSheet.Name = "Merge Result";

int rowCount = 0;
string targetFilePath = "";
foreach (Excel.Worksheet sheet in sourceBook.Worksheets)
{
Excel.Range workSheetRange = sheet.UsedRange;
Excel.Range startPasteCell = (Excel.Range)targetSheet.Cells[rowCount + 1, 1];
Excel.Range endPasteCell = (Excel.Range)targetSheet.Cells[rowCount + workSheetRange.Rows.Count, 
                            workSheetRange.Columns.Count];
Excel.Range pasteArea = targetSheet.get_Range(startPasteCell, endPasteCell);
                    workSheetRange.Copy(Type.Missing);
//I have tried both cell select then paste. or range paste same result.
startPasteCell.Select();
targetSheet.PasteSpecial(Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing);

//pasteArea.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                    //Type.Missing, Type.Missing);
   
rowCount += workSheetRange.Rows.Count;

}

Thank you very much