Like others, I am struggling with an excel application operation in C# wherein I cannot properly close out a background excel process despite attempting to unregister all COM objects. In my application, I am pulling tag name data for a data historian. The application refers to an excel spreadsheet to get the names of the tags used in a controller which I process downstream.
The method in question is as follows:
public class StoreGroupTags
{
public static TagGroup StoreDCSTags()
{
// Diagnostic timer
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
Console.WriteLine("Storing DCS Tags...");
TagGroup Tags = new TagGroup();
XlObjHandler xlObj = new XlObjHandler();
try
{
Excel.Application xlApp = xlObj.Add(new Excel.Application());
Excel.Workbooks xlWorkbooks = xlObj.Add(xlApp.Workbooks);
Excel.Workbook xlWorkbook = xlObj.Add(xlWorkbooks.Open(@"C:\Temp\Master File.xlsx"));
Excel.Sheets xlWorkSheets = xlObj.Add(xlWorkbook.Sheets);
Excel.Worksheet xlWorkSheet = xlObj.Add(xlWorkSheets[1]);
Excel.Range xlRange = xlObj.Add(xlWorkSheet.Cells);
int colCount = 119; // Column(Indirect((REF)) in excel to get this value
for (int i = 1; i < colCount + 1; i++)
{
string tagName = xlObj.Add(xlRange.Cells[4, i]).Value.ToString();
Tag readData = new Tag($"{tagName}");
readData = CheckTagsForStrings(readData);
Tags.AddTag(readData);
}
xlWorkbook.Close();
xlWorkbooks.Close();
xlApp.Quit();
}
catch (Exception exception)
{
Console.WriteLine("Exception thrown during StoreDCsTags(): " + exception);
}
finally
{
xlObj.Unregister();
// Clean up of anything else...
GC.Collect();
GC.WaitForPendingFinalizers();
}
stopwatch.Stop();
Console.WriteLine("StoreDCSTags() took: " + stopwatch.ElapsedMilliseconds + "ms");
return Tags;
}
You may notice that I am "registering" each excel object in a wrapper. I have referred to the method presented by: https://stackoverflow.com/a/27176626 which has been very helpful is keeping track of what I am calling and what is being compelled to unregister using the Marshal. Using this (and before I tried this, without using the wrapper), I have been able to identify that the for loop is preventing the application from closing in the background. Namely, if I comment out the for loop, the application successfully closes. Therefore, I believe the problem is isolated to the xlRange.Cells[4,i].Value reference. There are no further calls to Excel inside that for loop except for the xlRange.Cells.
I'm not sure, but while I can wrap each xlRange.Cells[4,i] object and have the wrapper attempt to close it, I can't tell if the .Value is AlSO creating a COM object (although the type is dynamic until I append .ToString()) which is preventing the application from closing. I have read that double dots are a no-no in Interop calls but would this qualify as a double-dot situation? When trying to warp the xlRange.Cells[4,i].Value I get an invalid argument error from the wrapper (which makes sense because it is not an "Excel.Application/Workbooks/Workbook/etc" type). Can anyone clarify if .Value dynamic types are in fact COM objects? Could anyone shed any light as to what COM objects may still be 'open' in this class and maybe how to successfully capture and close them out?
Thank you very much!