Merge multiple Excel sheets into one sheet

0 votes

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?

Apr 1 in Database by Edureka
• 9,320 points
88 views

1 answer to this question.

0 votes

Merging Excel Sheets: A Step-by-Step Guide
Make sure the sheets you want to merge are open.
To move or copy a sheet, go to Home > Format > Move or Copy Sheet.
Select an option from the dropdown menu (new book).
Click the OK button.

Consolidate in Excel: Merge multiple sheets into one - Ablebits.com

answered Apr 4 by Edureka
• 8,820 points

Related Questions In Database

0 votes
1 answer

How to transform multiple tables in one excel sheet to one table with Power BI?

If my prediction is correct- You have a ...READ MORE

answered Apr 4 in Database by Edureka
• 9,320 points
29 views
0 votes
0 answers

Merging Two excel files as two sheets in one workbook in java

I have two xlsx files at folder ...READ MORE

Mar 10 in Database by Edureka
• 9,320 points
31 views
0 votes
1 answer

Merge excel files into a new excel file based on filename

How do I merge Excel files that ...READ MORE

answered Mar 31 in Database by Edureka
• 8,820 points
38 views
0 votes
1 answer

Excel - how to calculate sum of multiple rows into different columns

The AutoSum button or formula can be ...READ MORE

answered Apr 4 in Database by Edureka
• 9,320 points
62 views
0 votes
1 answer

How to add a named sheet at the end of all Excel sheets?

Kindly use this one liner: Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name" Or ...READ MORE

answered Apr 5 in Database by Edureka
• 8,820 points
25 views
0 votes
0 answers

Convert a vertical data in a excel sheet with one header

I have a problem converting a long ...READ MORE

Apr 7 in Database by Edureka
• 9,320 points
9 views
0 votes
1 answer

What to combine two tables into one.

Hii Nikola, I think you need to UNION ALL otherwise you ...READ MORE

answered May 21, 2020 in Database by Niroj
• 82,680 points
273 views
+1 vote
1 answer

How to migrate a PostgreSQL database into a SQLServer one?

Hello @kartik, The easier way to accomplish this. First ...READ MORE

answered May 13, 2020 in Database by Niroj
• 82,680 points
226 views
0 votes
1 answer

Excel - Combine multiple columns into one column

In Excel, how can I concatenate (combine) ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
27 views
0 votes
1 answer

Merging Two excel files as two sheets in one workbook in java

Basically for this, you need to create ...READ MORE

answered Feb 21 in Database by Edureka
• 8,820 points
23 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP