CODEWITHSUNDEEP
c#exceloffice-interopexcel-interop
Abhash786
Abhash786

Reputation: 901

how to merge multiple excel files into one

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.

Upvotes: 0

Views: 2305

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

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; 
}

Upvotes: 1

Related Questions