Skip to content

Instantly share code, notes, and snippets.

@conholdate-gists
Last active January 13, 2021 09:54
Show Gist options
  • Save conholdate-gists/1e17dff0bc63859b4db1f697e5381192 to your computer and use it in GitHub Desktop.
Save conholdate-gists/1e17dff0bc63859b4db1f697e5381192 to your computer and use it in GitHub Desktop.
Copy data from several worksheets into one worksheet.
// 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");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment