How to merge Excel files from more than one workbook to a single file?

8 Dec 20234 minutes to read

You can merge several Excel files from more than one workbook to a single file. The following code snippet illustrates this.

//Loads all the template documents from Data folder
string[] files = Directory.GetFiles("Data/");

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Create empty Excel workbook instance with one empty worksheet
  IWorkbook workbook = application.Workbooks.Create(1);

  //Enumerates all the workbook files from the data folder and clone and merge it into new workbook
  foreach (string file in files)
  {
    //Loads a template document from data folder
    FileStream inputStream = new FileStream(file, FileMode.Open, FileAccess.Read);

    //Opens the template workbook from stream
    IWorkbook tempWorkbook = application.Workbooks.Open(inputStream);

    //Disposes the stream
    inputStream.Dispose();

    //Cloning all workbook's worksheets
    workbook.Worksheets.AddCopy(tempWorkbook.Worksheets);
  }

  //Removing the first empty worksheet
  workbook.Worksheets.Remove(0);

  FileStream outputStream = new FileStream("MergingFiles.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
  workbook.SaveAs(outputStream);
  workbook.Close();
  excelEngine.Dispose();
}
//Loads all the template documents from Data folder
string[] files = Directory.GetFiles("../../Data/");

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Create empty Excel workbook instance with one empty worksheet
  IWorkbook workbook = application.Workbooks.Create(1);

  //Enumerates all the workbook files from the data folder and clone and merge it into new workbook
  foreach (string file in files)
  {
    //Loads the all template document from data folder
	FileStream inputStream = new FileStream(file, FileMode.Open, FileAccess.Read);

	//Opens the template workbook from stream
	IWorkbook tempWorkbook = application.Workbooks.Open(inputStream);

	//Disposes the stream
	inputStream.Dispose();

	//Cloning all workbook's worksheets
	workbook.Worksheets.AddCopy(tempWorkbook.Worksheets);
  }

  //Removing the first empty worksheet
  workbook.Worksheets.Remove(0);

  workbook.SaveAs("MergingFiles.xlsx");
}
'Loads the all template document from data folder
Dim files As String() = Directory.GetFiles("../../Data/")

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013

  'Create empty Excel workbook instance with one empty worksheet 
  Dim workbook As IWorkbook = application.Workbooks.Create(1)

  'Enumerates all the workbook files from the data folder and clone and merge it into new workbook
  For Each file As String In files
    'Loads the all template document from data folder
    Dim inputStream As New FileStream(file, FileMode.Open, FileAccess.Read)

    'Opens the template workbook from stream
    Dim tempWorkbook As IWorkbook = application.Workbooks.Open(inputStream)

    'Disposes the stream
    inputStream.Dispose()

    'Cloning all workbook's worksheets
    workbook.Worksheets.AddCopy(tempWorkbook.Worksheets)
  Next

  'Removing the first empty worksheet
  workbook.Worksheets.Remove(0)

  workbook.SaveAs("MergingFiles.xlsx")
End Using

See Also