Automate Excel with C#
Back to all C# Recipes

How to merge two Excel Files into a single Excel file

Introduction

This C# recipe shows how you can merge two Excel files into one Excel file .

Ingredients

The recipe uses the EPPlus library version 4.5.3.3. This is the last version of EPPlus under the LGPL License (aka free for commercial uses). You can install it using Nuget Package manager:

Install-Package EPPlus -Version 4.5.3.3

C# Code

Here is the C# code. You are free to copy and use it in your own applications.

var file1 = new FileInfo(@"C:\Temp\Sample\Book 1.xlsx");
 var file2 = new FileInfo(@"C:\Temp\Sample\Book 2.xlsx");
 var mergedFileName = new FileInfo(@"C:\Temp\Sample\Merged.xlsx");

 //lets open the file
 using (var firstFile = new ExcelPackage(file1))
 {
      using (var secondFile = new ExcelPackage(file2))
      {
           foreach (var ws in secondFile.Workbook.Worksheets)
           {
               string worksheetName = ws.Name;
               // if worksheet name already exists - change name
               if (firstFile.Workbook.Worksheets.Any(ar => ar.Name == ws.Name))
                   worksheetName = string.Format("{0} - {1}", worksheetName, "Copy");

                firstFile.Workbook.Worksheets.Add(worksheetName, ws);
            }
       }

       firstFile.SaveAs(mergedFileName);
}

Steps

  1. We start out with two Excel files. Book 1.xlsx and Book 2.xlsx. The location and name of the files we define here:
var file1 = new FileInfo(@"C:\Temp\Sample\Book 1.xlsx");
 var file2 = new FileInfo(@"C:\Temp\Sample\Book 2.xlsx");
  1. We open both the files by using EPPlus's library ExcelPackage.
//lets open the files
 using (var firstFile = new ExcelPackage(file1))
 {
    using (var secondFile = new ExcelPackage(file2))
    {
        ....
    }
}

  1. We then cycle through each worksheet in the second file and add it to the first excel file. Take note, that if a worksheet name already exists in the first workbook, we add the word 'Copy' to the end of the sheet name to avoid duplicates.
foreach (var ws in secondFile.Workbook.Worksheets)
{
    string worksheetName = ws.Name;
    // if worksheet name already exists - change name
    if (firstFile.Workbook.Worksheets.Any(ar => ar.Name == ws.Name))
        worksheetName = string.Format("{0} - {1}", worksheetName, "Copy");

    firstFile.Workbook.Worksheets.Add(worksheetName, ws);
}

  1. Finally, we save the first Excel file as "Merged.xlsx" file
foreach (var ws in secondFile.Workbook.Worksheets)
{
    string worksheetName = ws.Name;
    // if worksheet name already exists - change name
    if (firstFile.Workbook.Worksheets.Any(ar => ar.Name == ws.Name))
        worksheetName = string.Format("{0} - {1}", worksheetName, "Copy");

    firstFile.Workbook.Worksheets.Add(worksheetName, ws);
}

After running the C# code; the two Excel files and their underlying worksheets are merged into a single Excel file called Merged.xlsx.

 var mergedFileName = new FileInfo(@"C:\Temp\Sample\Merged.xlsx");
 ....

 using (var firstFile = new ExcelPackage(file1))
 {
    ...

    firstFile.SaveAs(mergedFileName);
 }

You can see the result of running this C# Code by watching the small video at the top of the page.


Related C# Recipes

How to merge multiple Excel Files into a single Excel file

This C# recipe shows how you can use merge multiple Excel files within a directory into a single Excel file

How to merge multiple COLUMNS in an Excel File using C#

This C# recipe shows how you can use merge multiple columns in an Excel file using the EPPlus library