I've read a lot of different threads and articles on stack and other websites, but I'm still having some issues cleaning up EXCEL Interop COM processes when I finish with my application.
I've created myself a little class which allows me to load in worksheets, modify cells, rename work sheets and to also format different cells.
Here are the variables i'm using and the constructor.
Excel.Application _excelApp = null;
Excel.Workbook _excelBook = null;
Excel.Worksheet _excelSheet = null;
Excel.Range _excelRange = null;
When I create my class object, the constructor does this:
FileName = excelFileName; // Just used to reference a creation name
_excelApp = new Excel.Application();
Now, the next thing I do is load in a sheet for modification:
public bool LoadExcelSheet(string location, string file)
{
string startupPath = Environment.CurrentDirectory; // Get the path where the software is held
_excelBook = _excelApp.Workbooks.Open(Path.Combine(startupPath, location, file)); // Load in the workbook
_excelSheet = _excelBook.Worksheets[1]; // sets the excel sheet to the init worksheet
sheetName = _excelSheet.Name; // set the sheetname variable
isSheetLoaded = CheckIfWorkBookExists(); // a little check to see if the workbook exists - yes shows it has been loaded, if a no then it hasn't
return isSheetLoaded;
}
The next thing I do in my software is run through a function that allows me to set any cell (by an int ID defining the row and column) and modify it with a given string:
public void ModifyCell(int row, int column, string data)
{
int[] cellRange = new int[2] { row, column };
_excelRange = _excelSheet.Cells;
_excelRange.set_Item(row, column, data);
dataFormat.Add(cellRange); // this adds each row and column into a list which holds every modified cell for easy formatting
Marshal.ReleaseComObject(_excelRange); // Releases the COM object
}
So, when I finish my excel operations I the call my cleanup function:
public void Cleanup()
{
if (_excelApp != null) // if a cleanup hasn't been initiated
{
// set all the com objects to null, this is so the GC can clean them up
_excelRange = null;
_excelSheet = null;
_excelBook = null;
_excelApp = null;
}
// These garbage collectors will free any unused memory - more specifically the EXCEL.EXE*32 process that LOVES to stay, forever and ever. Like a mother inlaw...
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
}
}
I run the cleanup when I finish modifying the excel sheet, and also when I close the program just to double check that everything has been cleaned up. Though, that darn EXCEL.EXE*32
is still there!
With Excel (any Office application, really) interop, you have to be very diligent when managing resources. You should always use resources for as short a time as possible and release them as soon as you don't need them anymore. You should also explicityly release all objects to make sure they're cleaned up properly.
Here's an older answer of mine with more detail: COM object excel interop clean up
If you follow the steps outlined in the answer, you won't have stray Excel instances sticking around.