Excel Interop works in one solution in another same code produces an error (Parsing xlsx to DataTable)

Question

I have a code for parsing Excel to Datatable. It works in one solution, but in another one it produces an error:

System.Runtime.InteropServices.COMException: 'Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).'

pointing at:

NsExcel.Application xlApp = new NsExcel.Application();

I just have tested both and same error is coming in one of the solutions again and again. How this is possible? I have also checked this: https://stackoverflow.com/a/39725040/7202022 and my settings are already as suggested in this answer. I have also tried to run Visual Studio as administrator without any success.

I know I can move to ClosedXML, but how come that it is working in one project and does not work in another?

Here is my full code:

    using NsExcel = Microsoft.Office.Interop.Excel;

    public static void ParseFromExcelFileToList(string FolderPath)
    {

        var FileToParse = "";

        DirectoryInfo d = new DirectoryInfo(FolderPath);
        foreach (var file in d.GetFiles("*.xlsx"))
        {
            // Rest of the code goes here 
            Console.WriteLine(file.FullName);

            FileToParse = file.FullName;
        }

        //Create COM Objects. Create a COM object for everything that is referenced
        NsExcel.Application xlApp = new NsExcel.Application();
        NsExcel.Workbook xlWorkbook = xlApp.Workbooks.Open(FileToParse);
        NsExcel._Worksheet xlWorksheet = xlWorkbook.Sheets[2];
        NsExcel.Range xlRange = xlWorksheet.UsedRange;

        int rowCount = xlRange.Rows.Count - 5;
        int colCount = xlRange.Columns.Count;

        //iterate over the rows and columns and print to the console as it appears in the file
        //excel is not zero based!!
        for (int i = 2; i <= rowCount; i++)
        {
            // Parsing Excel column number 1
            string ProjectNumber = xlRange.Cells[i, 1].Value2;

            // Parsing Excel column number 2
            string SubProjectNumber = xlRange.Cells[i, 2].Value2;

            // Parsing Excel column number 3
            string SubProjectName = xlRange.Cells[i, 3].Value2;

            // Parsing Excel column number 4
            string ProjectManager = xlRange.Cells[i, 4].Value2;

            foreach (var item in Enumerable.Range(1, 1))
                DeliverySubProjectList.Add(new SubProjectsList (ProjectNumber, SubProjectNumber, SubProjectName, "0", ProjectManager, 0));
        }

        //cleanup
        GC.Collect();
        GC.WaitForPendingFinalizers();

        //rule of thumb for releasing com objects:
        //  never use two dots, all COM objects must be referenced and released individually
        //  ex: [somthing].[something].[something] is bad

        //release com objects to fully kill excel process from running in the background
        Marshal.ReleaseComObject(xlRange);
        Marshal.ReleaseComObject(xlWorksheet);

        //close and release
        xlWorkbook.Close();
        Marshal.ReleaseComObject(xlWorkbook);

        //quit and release
        xlApp.Quit();
        Marshal.ReleaseComObject(xlApp);

    }

I really wouldn't use Interop for anything if you can help it. It's not reliable. If you're reading from an excel file just use a library which reads the file directly instead of trying to invoke the whole Excel application in memory. There EppPlus, NPOI and a few others which can do the job — ADyson, Sep 16 '20 at 17:38
Yes. But there are several options besides closedXML. Almost anything is preferable to Interop though. That's only for when you want to actually directly control excel on screen really — ADyson, Sep 16 '20 at 19:26

score 1 · Answer 1 · answered Sep 16 '20 at 17:56

1

I don't know the error you faced. But why not try another solution? I was faced some issues with interop so I came up with this solution using OleDbDataAdapter.

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);
var dataTable = new DataTable();
adapter.Fill(dataTable);

Interop also depends on Microsoft Excel, where OleDbDataAdapter doesn't.

answered Sep 16 '20 at 17:56

Md. Rasedur Rahman Roxy

401
1
4
11

Thank you for this! Is there any way to assign columns separately? I mean something like what I am doing `DeliverySubProjectList.Add(new SubProjectsList (ProjectNumber, SubProjectNumber, SubProjectName, "0", ProjectManager, 0));` – 10101 Sep 16 '20 at 19:56
Yes you can do it. It is just like sql query. – Md. Rasedur Rahman Roxy Sep 17 '20 at 03:19

score 1 · Accepted Answer · answered Sep 16 '20 at 20:36

This was my third attempt to open my project in the middle of the night. I remembered having x64 Office installed. So I have tried to change settings and it worked. Please don't close this question if you don't find this being a duplicate. I think somebody else maybe find this one useful.

Issue can be solved by changing properties:

In Visual Studio (I have Microsoft Visual Studio Professional 2019) go to Project - > Properties -> Build -> Uncheck "Prefer 32-bit" and Select correct "Platform target"

Excel Interop works in one solution in another same code produces an error (Parsing xlsx to DataTable)

2 Answers2