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

3 Aug 20219 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 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
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);

    //Instantiates the File Picker
    FileOpenPicker openPicker = new FileOpenPicker();
    openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
    openPicker.FileTypeFilter.Add(".xlsx");
    openPicker.FileTypeFilter.Add(".xls");
    IReadOnlyList<StorageFile> files = await openPicker.PickMultipleFilesAsync();

    //Enumerates all the workbook files from the data folder and clone and merge it into new workbook
    foreach (StorageFile file in files)
    {
        //Opens the template workbook from stream
        IWorkbook tempWorkbook = await application.Workbooks.OpenAsync(file);

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

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

    //Initializes FileSavePicker
    FileSavePicker savePicker = new FileSavePicker();
    savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
    savePicker.SuggestedFileName = "MergingFiles";
    savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

    //Creates a storage file from FileSavePicker
    StorageFile storageFile = await savePicker.PickSaveFileAsync();

    //Saves changes to the specified storage file
    await workbook.SaveAsAsync(storageFile);
}
//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();
}
//Get the path with files to merge
string path = Path.Combine(Environment.CurrentDirectory);
string[] files = Directory.GetFiles(path, "Sample*");

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 folder and clone and merge it into new workbook
    foreach (string file in files)
    {        
        //Loads a template document from the 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);
     
    MemoryStream stream = new MemoryStream();
    workbook.SaveAs(stream);

    stream.Position = 0;

    //Save the stream as a file in the device and invoke it for viewing
    Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("MergingFiles.xlsx", "application/msexcel", stream);
}

See Also