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?