Excel process doesn't get closed

Question

I am not able to get my EXCEL (32) process closed once I am done using it.

As you can see in the code below, once ProcessRFAFData function finishes its execution, the EXCEL process doesn't get closed (I can 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 the Excel processes:

try {
    ProcessRFAFData(FileName);
} catch (Exception ex) {
    Status = "ERROR: " + ex.ToString();
}

if (Errors.Count() > 0) {
    SaveErrors();
}

Here are all the functions interacting 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) { }
}

In general, you should never use Office Interop from a server environment. If you were using normal ASP.NET I'd say you're 1005 likely to have a failure. Since it's SharePoint, I can only say it's 80% likely. — John Saunders, Aug 05 '11 at 18:08
@John Saunders: I agree. But there are times you have to do some nasty things! — Moon, Aug 05 '11 at 18:10
You read this (http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c) right? — Mark, Aug 05 '11 at 18:25
@Mark: No I did not. Wonderful. Trying as suggested in the post. Thanks a lot :) — Moon, Aug 05 '11 at 18:35

Armbrat · Answer 1 · 2011-08-05T18:15:55.333

3

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

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

edited Aug 05 '11 at 18:15

answered Aug 05 '11 at 18:10

Armbrat

2,295
1
23
32

score 3 · Accepted Answer · answered Aug 05 '11 at 18:15

3

You may have to go ridiculously 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've encountered situations where even that did not do it. I resorted to hunting down the Excel process and called Kill() on it.

answered Aug 05 '11 at 18:15

Stu

15,675
4
43
74

This is the correct way release Excel and allow Excel to close. The only thing missing here is if you're editing Cell's in a workbook via the Primary Interop, you also need to Marshal.ReleaseComObject on each cell you're accessing, then null it out. This is true for any variable you create, then assign an excel object to your variable. Including for Each R as Range In ListObject.Rows. R will have to released through Marshaling and then nulled out before you loop to the next element. Basically, if you've not released everything you've touched, then Excel will stay open. – Rob K. Oct 28 '11 at 19:08
Note the Loop I meantioned, For Each R as Range In ListObject.Rows is just one example. It's all pretty challenging. I had some of the same problems using the Primary Interop to build a utility that allows someone to create and apply Styles in a Workbook and then sync those styles with other workbooks. Rough... – Rob K. Oct 28 '11 at 19:14

score 1 · Answer 3 · answered Aug 05 '11 at 18:46

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

Second suggestion: If you absolutely MUST 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 instances of excel are running when you start and then find any instances of Excel that your app opened and then kill them. It's certainly not a great solution but sometimes you just have to bite the bullet.

If you don't care about prior 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();
    }
}

score 0 · Answer 4 · answered Nov 07 '12 at 15:03

0

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

childDomain.Unload();

Once called, the Excel Zombie process was no longer there...

answered Nov 07 '12 at 15:03

sean.net

735
8
25

score 0 · Answer 5 · answered Dec 02 '14 at 19:41

In addition to the above solutions and here is my LOGICALsolution. I was thinking why do I have so many EXCEL.EXE applications and handled this way. Here i am checking for conditions while creating New Excel application. if (_masterApp == null) _masterApp = new EXCEL.Application(); Only create New Instances of Excel application if the variable you want to create is Null, else reassign the existing variable.

and Use the Solutions mentioned in this blog to CLOSE the _masterApp.

Benefits: This way you will only close the EXCEL application you opened through Solution and not close Manually opened Excel Application.

score 0 · Answer 6 · answered Jun 28 '18 at 00:41

Look at this link

Kill Process Excel C#

Thanks to KD7 for the solution.

One minor modification is that if your excelapp is not visible your mainwindowtitle will be blank. eg. ""

private void KillSpecificExcelFileProcess(string excelFileName)
    {
        var processes = from p in Process.GetProcessesByName("EXCEL")
                        select p;

        foreach (var process in processes)
        {
            MessageBox.Show(process.MainWindowTitle);
            if (process.MainWindowTitle == excelFileName)
            {

            process.Kill();
            }
        }
    }

and to kill all zombie excel background process'

KillSpecificExcelFileProcess("");

This gave me no end of trouble in solving so I will be posting it on each zombie excel process thread.

score 0 · Answer 7 · answered Aug 05 '11 at 18:19

I would look into using EPPlus from Code Plex.
This example shows how to read data. You are always better on a server going this route - only issue is if 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 estimate a few hours for moving to this library.

Excel process doesn't get closed

7 Answers7

Linked