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
- 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");
- 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))
{
....
}
}
- 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);
}
- 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.