Merge multiple Excel sheets into one sheet

Question

I'm using GemBox.Spreadsheet to process some Excel files, now I need to combine them into one file with one sheet.
I know how to do sheets copying, but that will result in multiple sheets. What I need is a single output sheet that will contain all of them, one after another.

Currently what I'm doing is I export each sheet as a DataTable and then import it one by one:

string[] files = { "Book1.xlsx", "Book2.xlsx", "Book3.xlsx" };

var destination = new ExcelFile();
var destinationSheet = destination.Worksheets.Add("Sheets");
int startRow = 0;

foreach (string file in files)
{
    var source = ExcelFile.Load(file);
    foreach (var sourceSheet in source.Worksheets)
    {
        var table = sourceSheet.CreateDataTable(new CreateDataTableOptions());
        destinationSheet.InsertDataTable(table, new InsertDataTableOptions() { StartRow = startRow });
        startRow += table.Rows.Count;
    }
}

destination.Save("Merged Output.xlsx");

But with this, I lose the cell styles and text formatting.
Is there any way to preserve the style with DataTable?

Answer 1

EDIT (2022-10-28):

In the current latest version of GemBox.Spreadsheet there is another set of CellRange.CopyTo() overload methods that accept the CopyOptions parameter with which you can specify what you want to copy.

For example, you could specify that you want column widths and row heights to be copied as well, like this:

range.CopyTo(destinationSheet, destinationSheet.Rows.Count, 0,
    new CopyOptions() { CopyTypes = CopyTypes.Default | CopyTypes.ColumnWidths | CopyTypes.RowHeights });

ORIGINAL:

For this you can use CellRange.CopyTo method, like the following:

string[] files = { "Book1.xlsx", "Book2.xlsx", "Book3.xlsx" };

var destination = new ExcelFile();
var destinationSheet = destination.Worksheets.Add("Sheets");

foreach (string file in files)
{
    var source = ExcelFile.Load(file);
    foreach (var sourceSheet in source.Worksheets)
    {
        var range = sourceSheet.GetUsedCellRange(true);
        range.CopyTo(destinationSheet, destinationSheet.Rows.Count, 0);
    }
}

destination.Save("Merged Output.xlsx");

Note that CopyTo will just copy the value and style of the cells.

But if needed, you can use something like this to copy the column widths and row heights as well.

string[] files = { "Book1.xlsx", "Book2.xlsx", "Book3.xlsx" };

var destination = new ExcelFile();
var destinationSheet = destination.Worksheets.Add("Sheets");

int lastColumn = 0;
foreach (string file in files)
{
    var source = ExcelFile.Load(file);
    foreach (var sourceSheet in source.Worksheets)
    {
        var range = sourceSheet.GetUsedCellRange(true);
        int startRow = destinationSheet.Rows.Count;
        range.CopyTo(destinationSheet, startRow, 0);

        for (int r = 0; r < range.Height; r++)
            destinationSheet.Rows[r + startRow].Height = sourceSheet.Rows[r].Height;

        for (; lastColumn < range.Width; lastColumn++)
            destinationSheet.Columns[lastColumn].Width = sourceSheet.Columns[lastColumn].Width;
    }
}

destination.Save("Merged Output.xlsx");

Also if needed, you can use this answer to copy the images as well.

You can use that same solution to copy shapes and charts as well, they all have that Position property that you need to adjust after copying.