Need to merge the worksheets of some workbooks into one new workbook using C#

Question

My project is about getting four worksheets from different workbooks (Excel files) and add them into a completely new workbook. I'm using this logic to get Excel sheet into worksheet object and add those into new worksheet:

var App = new Microsoft.Office.Interop.Excel.Application();

Workbook  book1 = App.Workbooks.Open(@"path");
Worksheet sheet1 = book1.Worksheets\[1\];

Workbook  book2 = App.Workbooks.Open(@"path");
Worksheet sheet2 = book2.Worksheets\[1\];

Workbook  book3 = App.Workbooks.Open(@"path");
Worksheet sheet3 = book3.Worksheets\[1\];

Now how to create a new workbook and add sheet1, sheet2 and sheet3 to that workbook?

Answer 1

Above solution works for only one sheet if you want more sheet then this the simplest solution I got so far

var App = new Microsoft.Office.Interop.Excel.Application();

Workbook  book1 = App.Workbooks.Open(@"path");
Worksheet sheet1 = book1.Worksheets\[1\];

Workbook  book2 = App.Workbooks.Open(@"path");
Worksheet sheet2 = book2.Worksheets\[1\];

Workbook  book3 = App.Workbooks.Open(@"path");
Worksheet sheet3 = book3.Worksheets\[1\];

var newBook = App.Workbooks.Add();

sheet1.Copy(newBook.Worksheets[1]);
sheet2.Copy(newBook.Worksheets[2]);
sheet3.Copy(newBook.Worksheets[3]);

//delete sheet which created by default while creating newBook
newBook.Worksheets[4].delete();

string path = @"path";
if(File.Exits(path))
{
File.Delete(path);
}
newBook.SaveAs(path);
book1.Close();
book2.Close();
book3.Close();
App.Quit();

Answer 2

You can try something like this

var newBook = App.Workbooks.Add();
sheet1.UsedRange.Copy(newBook.Worksheets[1].Range["A1"]);
sheet2.UsedRange.Copy(newBook.Worksheets[2].Range["A1"]);
sheet3.UsedRange.Copy(newBook.Worksheets[3].Range["A1"]);
newBook.SaveAs(@"path\newWorkbook.xlsx");
newBook.Close();
App.Quit();