Excel Generation Memory Clear using c# :

Here, I will explain how to kill excel.exe or releasing the Excel object from memory after generation of the excel document using c#.

First, we have to use “Excel.ApplicationClass” and “Excel.WorkbookClass” to initialize the object. This class is not available in previous versions of VSTO.

Example :

Excel.ApplicationClass excelApplication = new Excel.ApplicationClass();

Excel.Workbooks excelBooks = excelApplication.Workbooks;

Excel.WorkbookClass book = (Excel.WorkbookClass)excelBooks.Open(FilePath, 0, false, 5, "", "", true,Excel.XlPlatform.xlWindows, "\t", true, false, 0, 0, 0, true);

Second, we need to make sure that the object for th class like “Excel.Worksheet”, Excel.Sheets and “Excel.Range” should be cleared after the use. The order of killing these object is important , that last opened object must killed first.

Example :

Excel.Sheets sheets = (Excel.Sheets)book.Worksheets;

Excel.Worksheet sheet = (Excel.Worksheet)sheets[1];

Excel.Range rangeCells = sheet.UsedRange;

System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeCells);

System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);

System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);

Finally, we have to close all global objects, before make those set to null. Then, using “FinalReleaseComObject” method inside while loop, it will set opended object count for the application to zero after that make it to “NULL”.

Example :

book.Close(false, null, null);

excelBooks.Close();

excelApplication.Quit();

ExcelUtility.ReleaseObject(sheets);

ExcelUtility.ReleaseObject(sheet);

while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelBooks) != 0)

While (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(book) != 0) ;

while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApplication) != 0) ;

excelBooks = null;

book = null;

excelApplication = null;

GC.Collect();

GC.WaitForPendingFinalizers();