Excel process doesn't get closed_问答_开发者_运维开发者技术经验分享

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["HI开发者_Go百科DCell"],
        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) { }
}

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.

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

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

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();
    }
}

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.

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...

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.

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'