how to merge multiple excel files into one

Question

I have tried searching this on google but could get the answer for my problem. I am trying to merge multiple excel files into single one using below code. But it is giving me error like below (no additional info in innerexception) at sheet.Copy command.

Unable to get the Copy property of the Worksheet class

Code...

private void MergeXlsxFiles(string destXlsxFileName, params string[] sourceXlsxFileNames)
        {
            Application excelApp = null;
            Workbook destWorkBook = null;
            var temppathForTarget = Path.Combine(Directory.GetCurrentDirectory() , Guid.NewGuid() + ".xls");

            if (File.Exists(temppathForTarget))
                File.Delete(temppathForTarget);

            try
            {
                excelApp = new Application
                {
                    DisplayAlerts = false,
                    SheetsInNewWorkbook = 3
                };
                destWorkBook = excelApp.Workbooks.Add();
                destWorkBook.SaveAs(temppathForTarget);


                foreach (var sourceXlsxFile in sourceXlsxFileNames)
                {
                    var file = Path.Combine(Directory.GetCurrentDirectory(), sourceXlsxFile);
                    var sourceWorkBook = excelApp.Workbooks.Open(file);

                    foreach (Worksheet ws in sourceWorkBook.Worksheets)
                    {
                        var wSheet = destWorkBook.Worksheets[destWorkBook.Worksheets.Count];
                        ws.Copy(wSheet);
                        destWorkBook.Worksheets[destWorkBook.Worksheets.Count].Name =
                            ws.Name; 
                    }
                    sourceWorkBook.Close(XlSaveAction.xlDoNotSaveChanges);
                }
                destWorkBook.Sheets[1].Delete();
                destWorkBook.SaveAs(destXlsxFileName);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (destWorkBook != null)
                    destWorkBook.Close(XlSaveAction.xlSaveChanges);
                if (excelApp != null)
                    excelApp.Quit();
            }
        }

Does anyone knows what is wrong with this code.

I am referring Microsoft.Office.Interop.Excel dll from GAC with version 15.0.0.0 and I have MSOffice 2013 installed on my machine.

Answer 1

In general, when I test the code in the question, it runs into problems with data typing. Excel is "picky" about data typing - it's often necessary to explicitly cast a type. The following foreach works for me: Notice the (Excel.Worksheet) casts. With those I ran into no problems with the Copy (or the Name) method (property).

I also ran into "oddities" assigning sheet names. The logic used in the code in the question is not clear, so on the assumption the new sheets should be added after the default three empty sheets, I altered ws.Copy to put them at the end.

foreach (Excel.Worksheet ws in sourceWorkBook.Worksheets)
{
    var wSheet = (Excel.Worksheet) destWorkBook.Worksheets[destWorkBook.Worksheets.Count];
    ws.Copy(missing, wSheet);
    Excel.Worksheet wNewSheet = (Excel.Worksheet)destWorkBook.Worksheets[destWorkBook.Worksheets.Count];
    wNewSheet.Name = "New" + ws.Name; 
}