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 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.
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"
User contributions licensed under CC BY-SA 3.0