How to merge multiple Excel Files into a single Excel file
Introduction
This C# recipe shows how you can use merge multiple Excel files within a directory into a single 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
// search for all XLSX files in the following path
var files = Directory.GetFiles(@"C:\Temp\ExcelRecipes\Merging\", "*.xlsx");
// specify the parh and name of final merged file
var resultFile = @"C:\Temp\ExcelRecipes\Merging\Result\final.xlsx";
ExcelPackage fileExcelFile = new ExcelPackage(new FileInfo(resultFile));
// go through each file
foreach (var file in files)
{
// open relevant file
using ExcelPackage excelFile = new ExcelPackage(new FileInfo(file));
// go through each worksheet and save it to the merged file
foreach (var sheet in excelFile.Workbook.Worksheets)
{
// watch out for duplicates. name of each worksheet must be unique
string workSheetName = sheet.Name;
foreach (var masterSheet in fileExcelFile.Workbook.Worksheets)
{
if (sheet.Name == masterSheet.Name)
{
workSheetName = string.Format("{0}_{1}", workSheetName, Guid.NewGuid());
}
}
//add sheet to merged file
fileExcelFile.Workbook.Worksheets.Add(workSheetName, sheet);
}
}
// save final file
fileExcelFile.SaveAs(new FileInfo(resultFile));
Steps
- Search for all files which you are looking to merge within a directory. In the below case, I am searching for all XLSX files in the C:\Temp\ExcelRecipes\Merging\ path. I am using the wildcard * character as part of the search pattern. This will give me all files with an xlsx extension regardless of their name.
var files = Directory.GetFiles(@"C:\Temp\ExcelRecipes\Merging\", "*.xlsx");
Here is another example, which retrieves all the files starting with ToBeMerged:
var files = Directory.GetFiles(@"C:\Temp\ExcelRecipes\Merging\", "ToBeMerged*.xlsx");