Copy data from several worksheets into one worksheet.

MergeMultipleWorksheetsIntoOne.cs

// Open an Excel file that contains the worksheets:
// Products1, Products2 and Products3
Workbook workbook = new Workbook("Products.xlsx");

// Add a worksheet named Summary_sheet
Worksheet summarySheet = workbook.Worksheets.Add("Summary_sheet");

// Iterate over source worksheets whose data you want to copy to the
// summary worksheet
string[] nameOfSourceWorksheets = { "Products1", "Products2", "Products3" };
int totalRowCount = 0;

foreach (string sheetName in nameOfSourceWorksheets)
{
    Worksheet sourceSheet = workbook.Worksheets[sheetName];

    Range sourceRange;
    Range destRange;
    // In case of Products1 worksheet, include all rows and cols.
    if (sheetName.Equals("Products1"))
    {
        sourceRange = sourceSheet.Cells.MaxDisplayRange;
        
        destRange = summarySheet.Cells.CreateRange(
                sourceRange.FirstRow + totalRowCount,
                sourceRange.FirstColumn,
                sourceRange.RowCount,
                sourceRange.ColumnCount);
    }
    // In case of Products2 and Products3 worksheets,
    // exclude the first row (which contains headings).
    else
    {
        int mdatarow = sourceSheet.Cells.MaxDataRow; // Zero-based
        int mdatacol = sourceSheet.Cells.MaxDataColumn; // Zero-based
        sourceRange = sourceSheet.Cells.CreateRange(0 + 1, 0, mdatarow, mdatacol + 1);

        destRange = summarySheet.Cells.CreateRange(
                sourceRange.FirstRow + totalRowCount -1,
                sourceRange.FirstColumn,
                sourceRange.RowCount,
                sourceRange.ColumnCount);
    }

    // Copies data, formatting, drawing objects etc. from a
    // source range to destination range.
    destRange.Copy(sourceRange);
    totalRowCount = sourceRange.RowCount + totalRowCount;
}

// Save the workbook 
workbook.Save("Summarized.xlsx");