Merge Multi .xls files into new One Using C#. How ?

Please kindly refer this article, it has offered 4 sulotion on this issue

http://www.c-sharpcorner.com/UploadFile/a08113/solutions-to-merge-multiple-excel-worksheets-into-one/

Hi, here is another solution, an alternative to an excel interop for your C# program.

// Retrieve targeted Excel file paths.
var excelFileNames = new string[] { "Book1.xls", "Book2.xls", "Book3.xls" };
 
// Create an empty Excel file.
var excelFile = new ExcelFile();
 
foreach (var fileName in excelFileNames)
{
    // Load an existing XLS file.
    var tempFile = ExcelFile.Load(fileName, LoadOptions.XlsDefault);
 
    foreach (var tempSheet in tempFile.Worksheets)
    {
        string sheetName = tempSheet.Name;
 
        // Add some fallback logic for cases where worksheet name is already contained.
        while (excelFile.Worksheets.Contains(sheetName))
            sheetName += " -C";
 
        // Copy worksheet.
        excelFile.Worksheets.AddCopy(sheetName, tempSheet);
    }
}
 
// Save as XLS file.
excelFile.Save("Merged Workbooks.xls", SaveOptions.XlsDefault);

Code uses this excel dll written in C#, you can easily adjust the code if you need any custom requirements, for example change the positioning/order of the merged worksheets.
Also check out the following link, it shows some tasks that you can do with ease on your targeted XLS files within a C#.

Hi Fahad,

How about fetching data form each selected file into a datatable, merge them all and then write back to an excel file?


Yes, there are few gotchas that you should be aware of. Depending on your file size, it will be covering up your memory, so this will limit the operation until the memory lasts, otherwise would work awesomely. You do have an option to create a temp excel file, open and update it each time you read new excel file and finally save it somewhere on desired location. This would be a little slow. Second thing is, while merging data, you will need to ensure that Headers in all the excel files are same, I mean the data in all the files are consistent to be merged, otherwise you'll have to check every column and read it one by one and then add it to the datatable.

And yes, do follow the link referred by Iftikar, it will let you move on with Coding part.

Hope you got the point.

Cheers!