Excel process does not close

Question

I cannot complete my EXCEL (32) process when I have finished using it.

As you can see in the code below, the ProcessRFAFData function terminates, the EXCEL process does not close (I still see EXCEL.EXE*32 in the task manager).

For this reason, when SaveErrors starts its execution, I get the following exception:

 System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot open or save any more documents because there is not enough available memory or disk space. • To make more memory available, close workbooks or programs you no longer need. • To free disk space, delete files you no longer need from the disk you are saving to. at Microsoft.Office.Interop.Excel.Workbooks.Add(Object Template) at NextG.RFAFImport.Layouts.NextG.RFAFImport.RFAFDataImporter.<>c__DisplayClass9.b__6() at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass4.b__2() at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode) at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param) at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode) at NextG.RFAFImport.Layouts.NextG.RFAFImport.RFAFDataImporter.SaveErrors() 

Here is the code that executes Excel processes:

 try { ProcessRFAFData(FileName); } catch (Exception ex) { Status = "ERROR: " + ex.ToString(); } if (Errors.Count() > 0) { SaveErrors(); } 

Here are all the features that interact with Excel:

 private void ReleaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception) { } finally { GC.Collect(); } } private void ProcessRFAFData(string FileName) { Microsoft.Office.Interop.Excel.Application XLA = null; Microsoft.Office.Interop.Excel.Workbook XLW = null; Microsoft.Office.Interop.Excel.Worksheet XLS = null; bool error = false; try { SPSecurity.RunWithElevatedPrivileges(delegate() { XLA = new Microsoft.Office.Interop.Excel.Application(); XLW = XLA.Workbooks.Open(FileName, 0, true, Type.Missing, null, null, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing, false, false, Type.Missing, false, Type.Missing, Type.Missing); int index = RFAFTabExists(ref XLW); if (index == 0) return; XLS = (Microsoft.Office.Interop.Excel.Worksheet)XLW.Worksheets.get_Item(index); if (!ValidProjectID(ref XLS)) return; ParseData(ref XLS); XLW.Close(true, Type.Missing, Type.Missing); XLA.Quit(); ReleaseObject(XLS); ReleaseObject(XLW); ReleaseObject(XLA); }); } catch (SP.ServerException ex) { // output error } catch (Exception ex) { // output error } } private int RFAFTabExists(ref Microsoft.Office.Interop.Excel.Workbook XLW) { int index = 0; foreach (Microsoft.Office.Interop.Excel.Worksheet w in XLW.Worksheets) { if (w.Name.Equals(settings.Collection["RFAFTabName"])) index++; } return index; } private bool ValidProjectID(ref Microsoft.Office.Interop.Excel.Worksheet XLS) { using (SP.ClientContext CTX = new SP.ClientContext(SiteURL)) { var projectId = XLS.Cells.get_Range(settings.Collection["ProjectIDCell"], Type.Missing).Text.ToString(); var project = // getting list of projects from SharePoint if (project.Count() > 0) { ProjectID = XLS.Cells.get_Range(settings.Collection["ProjectIDCell"], Type.Missing).Text.ToString(); return true; } } return false; } private void ParseData(ref Microsoft.Office.Interop.Excel.Worksheet XLS) { ListData.Add("HID", GetHID(XLS.Cells.get_Range(settings.Collection["HIDCell"], Type.Missing).Text.ToString())); if (ListData["HID"].Equals("0")) Errors.Add(new ImportError { Reason = "Hub ID does not exist in this project workspace.", Reference = string.Format("Hub ID: {0}", XLS.Cells.get_Range(settings.Collection["HIDCell"], Type.Missing).Text.ToString()) }); int row = Int32.Parse(settings.Collection["StartRow"]); while (!NoMoreData(ref XLS, row)) { string PRSIN = XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["PRSIN"], row), Type.Missing).Text.ToString(); string NOC = ValidateNumber(XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["NOC"], row), Type.Missing).Text.ToString()); string UEIRP = ValidateNumber(XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["UEIRP"], row), Type.Missing).Text.ToString()); string LAT = ValidateLatLon(XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["LAT"], row), Type.Missing).Text.ToString()); string LON = ValidateLatLon(XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["LON"], row), Type.Missing).Text.ToString()); string PJ = GetPJ(XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["JurisdictionCol"], row), Type.Missing).Text.ToString(), XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["StateCol"], row), Type.Missing).Text.ToString()); string ST = GetState(XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["JurisdictionCol"], row), Type.Missing).Text.ToString(), XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["StateCol"], row), Type.Missing).Text.ToString()); ListItemData.Add(new ListItem { ProposedRemoteSiteItemNumber = PRSIN, NumberOfCarriers = NOC, UsableEIRP = UEIRP, Latitude = LAT, Longitude = LON, PrimaryJurisdiction = PJ, State = ST }); row++; } } private bool NoMoreData(ref Microsoft.Office.Interop.Excel.Worksheet XLS, int row) { return string.IsNullOrEmpty(XLS.Cells.get_Range(string.Format("{0}{1}", settings.Collection["ProposedRemoteSiteItemNumberCol"], row), Type.Missing).Text.ToString()); } private void SaveErrors() { Microsoft.Office.Interop.Excel.Application XLA = null; Microsoft.Office.Interop.Excel.Workbook XLW = null; Microsoft.Office.Interop.Excel.Worksheet XLS = null; object MissingValue = System.Reflection.Missing.Value; try { try { SPSecurity.RunWithElevatedPrivileges(delegate() { XLA = new Microsoft.Office.Interop.Excel.Application(); XLW = XLA.Workbooks.Add(MissingValue); XLS = (Microsoft.Office.Interop.Excel.Worksheet)XLW.Worksheets.get_Item(1); XLS.Cells[1, 1] = "Reason for error"; XLS.Cells[1, 2] = "Reference"; XLS.get_Range("A1").Font.Bold = true; XLS.get_Range("B1").Font.Bold = true; int row = 2; foreach (ImportError e in Errors) { XLS.Cells[row, 1] = e.Reason; XLS.Cells[row, 2] = e.Reference; row++; } XLW.SaveAs(ErrorLogFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, MissingValue, MissingValue, MissingValue, MissingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue); XLW.Close(true, MissingValue, MissingValue); XLA.Quit(); ReleaseObject(XLS); ReleaseObject(XLW); ReleaseObject(XLA); }); } catch (Exception ex) { Status = "ERROR: " + ex.ToString(); } // Uploading excel file to SharePoint document library } catch (Exception) { } } 

Answer 1

You may have to laugh explicit :

 excelWorkbook.Close (false, System.Reflection.Missing.Value,System.Reflection.Missing.Value) ; excelWorkbooks.Close(); excelApp.Quit(); Marshal.ReleaseComObject(excelWorksheet); Marshal.ReleaseComObject(excelSheets); Marshal.ReleaseComObject(excelWorkbooks); Marshal.ReleaseComObject(excelWorkbook); Marshal.ReleaseComObject(excelApp); excelWorksheet = null; excelSheets = null; excelWorkbooks = null; excelWorkbook = null; excelApp = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); 

I faced situations when even this was not done. I resorted to searching for an Excel process and called Kill () on it.

Answer 2

Release your Excel objects in the finally block, in case of exceptions.

 try { ... } catch { ... } finally { ReleaseObject(XLS); ReleaseObject(XLW); ReleaseObject(XLA); } 

Answer 3

First suggestion: http://code.google.com/p/excellibrary This is a great library that I have used with great success.

Second suggestion: if you MUST completely close Excel

 /// <summary> /// Gets all currently running instances of Excel, so we don't kill active windows. /// </summary> private void GetInstancesToSave() { if (_instancesToSaveList != null) { _instancesToSaveList.Clear(); } _instancesToSaveList = Process.GetProcesses().ToList<Process>(); _instancesToSaveList = _instancesToSaveList.FindAll(proc => proc.ProcessName == "EXCEL"); } /// <summary> /// Kills any instances of Excel that were created by the program. /// </summary> /// <param name="zInstancesToSave">Instances that were not </param> private static void KillExcel(List<Process> zInstancesToSave) { List<Process> xProcesslist = Process.GetProcesses().ToList<Process>(); xProcesslist = xProcesslist.FindAll(proc => proc.ProcessName == "EXCEL"); foreach (Process xTheprocess in xProcesslist)//read through all running programs { bool killit = true; foreach (Process proc in zInstancesToSave)//read through all running programs { if (xTheprocess.Id == proc.Id) { killit = false; } } if (killit) { xTheprocess.Kill(); } } } 

You can use these 2 methods to keep track of which excel instances start at startup, and then find all the Excel instances opened by your application, and then kill them. This, of course, is not a good solution, but sometimes you just need to bite a bullet.

If you don't need previous instances, you can also just do:

 /// <summary> /// Kills any instances of Excel that were created by the program. /// </summary> /// <param name="zInstancesToSave">Instances that were not </param> private static void KillExcel(List<Process> zInstancesToSave) { List<Process> xProcesslist = Process.GetProcesses().ToList<Process>(); xProcesslist = xProcesslist.FindAll(proc => proc.ProcessName == "EXCEL"); foreach(Proc process in xProcesslist) { process.Kill(); } } 

Answer 4

I would consider using EPPlus from Plex code.
This example shows how to read data. You are always better on a server that goes along this route - the only problem is that you need call formulas - then this approach will not work.
Most of your code will be very similar to your current code, so I would appreciate a few hours to go to this library.

Answer 5

In my case, the Excel add-in lived in a separate AppDomain that I created earlier, but when the add-in was unloaded, I never called:

 childDomain.Unload(); 

After the call, the Excel Zombie process no longer exists ...

Answer 6

In addition to the solutions above, and here is my LOGICAL solution. I was thinking why I have so many EXCEL.EXE applications and is being processed this way. Here I check the conditions when creating the New Excel application. if (_masterApp == null) _masterApp = new EXCEL.Application (); Only create new instances of Excel if the variable you want to create is Null, otherwise override the existing variable.

and use the solutions mentioned on this blog to CLOSE _masterApp.

Advantages: in this way you only close the EXCEL application that you opened through Solution, and not close the open Excel application manually.