C# – How to merge two Excel workbook into one workbook in C#

c

Let us consider that I have two Excel files (Workbooks) in local. Each Excel workbook is having 3 worksheets.

Lets say WorkBook1 is having Sheet1, Sheet2, Sheet3

Workbook2 is having Sheet1, Sheet2, Sheet3.

So here I need to merge these two excel workbook into one and the new excel workbook that is let's say Workbook3 which will have total 6 worksheets (combination of workbook1 and workbook2).

I need the code that how to perform this operation in c# without using any third party tool. If the third party tool is free version then its fine.

Best Answer

An easier solution is to copy the worksheets themselves, and not their cells.

This method takes any number of excel file paths and copy them into a new file:

private static void MergeWorkbooks(string destinationFilePath, params string[] sourceFilePaths)
{
  var app = new Application();
  app.DisplayAlerts = false; // No prompt when overriding

  // Create a new workbook (index=1) and open source workbooks (index=2,3,...)
  Workbook destinationWb = app.Workbooks.Add();
  foreach (var sourceFilePath in sourceFilePaths)
  {
    app.Workbooks.Add(sourceFilePath);
  }

  // Copy all worksheets
  Worksheet after = destinationWb.Worksheets[1];
  for (int wbIndex = app.Workbooks.Count; wbIndex >= 2; wbIndex--)
  {
    Workbook wb = app.Workbooks[wbIndex];
    for (int wsIndex = wb.Worksheets.Count; wsIndex >= 1; wsIndex--)
    {
      Worksheet ws = wb.Worksheets[wsIndex];
      ws.Copy(After: after);
    }
  }

  // Close source documents before saving destination. Otherwise, save will fail
  for (int wbIndex = 2; wbIndex <= app.Workbooks.Count; wbIndex++)
  {
    Workbook wb = app.Workbooks[wbIndex];
    wb.Close();
  }

  // Delete default worksheet
  after.Delete();

  // Save new workbook
  destinationWb.SaveAs(destinationFilePath);
  destinationWb.Close();

  app.Quit();
}

Edit: notice that you might want to Move method instead of Copy in case you have dependencies between the sheets, e.g. pivot table, charts, formulas, etc. Otherwise the data source will disconnect and any changes in one sheet won't effect the other.

Related Topic