How can I dispose my Excel Application

Question

My code is as follows

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);

Excel.Worksheet xlSheet = xlWorkbook.Sheets[1]; // get first sheet
Excel.Range xlRange = xlSheet.UsedRange;

These are the only variables used in my function

foreach (Excel.Worksheet XLws in xlWorkbook.Worksheets)
{
    // do some stuff 

    xlApp.UserControl = false;

    if (xlRange != null)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlRange);

    if (xlSheet != null)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheet);

    if (xlWorkbook != null)
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);

    xlRange = null;
    xlSheet = null;
    xlWorkbook = null;
    xlApp.Quit();

    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
}

But still I get EXCEL.EXE in Task Manager

Please help?

Normally, the Quit command will take the process out of the Task Manager. Are you sure that Excel.exe is not a previous process that got stuck there during a code testing? If the code wasn't good and you had to stop it half the way, the excel application will never be quit. — Daniel Möller, Apr 11 '13 at 11:39
And I noticed you are quitting apllication `for each` worksheet??? Sounds strange, there's only one application containing the sheets. — Daniel Möller, Apr 11 '13 at 11:41
@Daniel Yes Because Previously I tried it out of the loop..Now what happens is lets say If it is reading a file ABC.xlsx it creates ~ABC.xlsx in the same folder as a result it becomes necessary to QUit it(I dont know of any other way) ...also since it is looping it tries to read ~ABC.xlsx and generates exception — Rohit, Apr 11 '13 at 11:44
Are you doing some foreach ".xlsx" file??? If not there's no problem with that ~ABC file, it will be deleted when you quit application. I believe at some debugging you got an error and in THAT run the excel application got stuck in the manager. Any other time you run will create a new process of excel, and if it gets to the quit command, it will be out of the manager. But that previous proccess will never get off there, must take it off manually. — Daniel Möller, Apr 11 '13 at 11:52
Excel interop is a pile of sh*t... i ended up using OpenOfficeXML / EPPlus instead! — Paul Zahra, Jul 06 '16 at 14:16

score 7 · Answer 1 · answered Aug 14 '14 at 13:33

Kill the excel process which has empty value for MainWindowTitle. Below is an example source code.

    Microsoft.Office.Interop.Excel.Application oXL;
    Microsoft.Office.Interop.Excel._Workbook oWB;
    Microsoft.Office.Interop.Excel._Worksheet oSheet;
    Microsoft.Office.Interop.Excel.Range oRng;
    object misvalue = System.Reflection.Missing.Value;
    try
    {
        //Start Excel and get Application object.
        oXL = new Microsoft.Office.Interop.Excel.Application();
        oXL.Visible = true;

        //Get a new workbook.
        oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
        oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

        //Add table headers going cell by cell.
        oSheet.Cells[1, 1] = "First Name";
        oSheet.Cells[1, 2] = "Last Name";
        oSheet.Cells[1, 3] = "Full Name";
        oSheet.Cells[1, 4] = "Salary";

        //Format A1:D1 as bold, vertical alignment = center.
        oSheet.get_Range("A1", "D1").Font.Bold = true;
        oSheet.get_Range("A1", "D1").VerticalAlignment =
            Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

        // Create an array to multiple values at once.
        string[,] saNames = new string[5, 2];

        saNames[0, 0] = "John";
        saNames[0, 1] = "Smith";
        saNames[1, 0] = "Tom";

        saNames[4, 1] = "Johnson";

        //Fill A2:B6 with an array of values (First and Last Names).
        oSheet.get_Range("A2", "B6").Value2 = saNames;

        //Fill C2:C6 with a relative formula (=A2 & " " & B2).
        oRng = oSheet.get_Range("C2", "C6");
        oRng.Formula = "=A2 & \" \" & B2";

        //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
        oRng = oSheet.get_Range("D2", "D6");
        oRng.Formula = "=RAND()*100000";
        oRng.NumberFormat = "$0.00";

        //AutoFit columns A:D.
        oRng = oSheet.get_Range("A1", "D1");
        oRng.EntireColumn.AutoFit();

        oXL.Visible = false;
        oXL.UserControl = false;
        oWB.SaveAs("c:\\test505.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        oWB.Close(null, null, null);
        oXL.Quit();  //MainWindowTitle will become empty afer being close

        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oXL);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oWB);

        Process[] excelProcesses = Process.GetProcessesByName("excel");
        foreach (Process p in excelProcesses)
        {
            if (string.IsNullOrEmpty(p.MainWindowTitle)) // use MainWindowTitle to distinguish this excel process with other excel processes 
            {
                p.Kill();
            }
        }
    }
    catch (Exception ex2)
    {

    }

excellent, killing manually excel process confirms that file is released — Kamran Qadir, Jan 29 '19 at 21:09

score 5 · Answer 2 · answered Apr 11 '13 at 11:45

5

You've got an implicit object left open. Try this

Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(file);
....    

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbooks);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);
....

answered Apr 11 '13 at 11:45

Colin Pickard

45,724
13
98
148

3

This question covers the situation in much more detail: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects – Colin Pickard Apr 11 '13 at 11:49

score 0 · Answer 3 · answered Jan 30 '14 at 18:30

OK... I hope this helps... it took me forever to tweak this to get it to work just so...

Here is my entire function (VB -- but the C# code for the tricky stuff is in there (thanks to too many other stackoverflow giants who helped me get this far!)

Private Function ImportWorksFile() As Integer

    Dim EndofSheet As Boolean
    Dim BlankRowCounter As Integer
    Dim rr As RowResult
    Dim SecCount As Integer = 0
    Dim SecRow As SecurityRow

    Dim uf As New UtilFunctions

    'If this has already been run, the instance of the excel object would have been 'killed' and needs to be reinstantiated
    If blnExcelProcessKilled Then 'Global boolean var
        xlApp = New Excel.Application()
        blnExcelProcessKilled = False
    End If
    Dim excelProcess(0) As Process
    excelProcess = Process.GetProcessesByName("excel")

    Dim tmp As Excel.Workbooks
    Try
        tmp = xlApp.Workbooks
        xlWorkBook = tmp.Open(WorkingFileName)
    Catch ex As Exception
        MessageBox.Show("There was a problem opening the workbook - please try again", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Return 0
    End Try

    Using dc As New AFLData(CurAFLApp, True)

        Dim cmd As SqlCommand = DefineCommand()
        cmd.CommandType = CommandType.StoredProcedure

        For Each ws As Excel.Worksheet In xlWorkBook.Worksheets

            Dim row As Integer = 1
            EndofSheet = False
            BlankRowCounter = 0

            If ImpCols.ContainsKey(ws.Name) Then
                SecRow = New SecurityRow(ImpCols(ws.Name))

                Do Until EndofSheet
                    Try
                        SecRow.NewRow(ws.Rows(row))
                        rr = SecRow.IsValidRow

                        If rr = RowResult.Valid Then
                            ' read this row and process
                            With cmd
                                .Parameters("@AcctDate").Value = FileDate
                                .Parameters("@NewSub").Value = SecRow.GetStrCell("newsub")
                                RunProcedure(cmd)
                            End With

                            SecCount += 1

                            BlankRowCounter = 0

                        Else
                            BlankRowCounter += rr

                        End If

                    Catch ex As Exception
                        MessageBox.Show("There was a problem with row: " & row & " in workbook " & ws.Name)

                    End Try

                    ' if we've counted 50 blank A column values in a row, we're done.
                    If BlankRowCounter <= -50 Then
                        EndofSheet = True
                    End If

                    row += 1
                Loop
            End If
        Next
    End Using

    Try

        xlWorkBook.Close(SaveChanges:=False)
        xlApp.Workbooks.Close()
        xlApp.Quit()

        '// And now kill the process. C# Version (for reference)
        'if (processID != 0)
        '{
        '    Process process = Process.GetProcessById(processID);
        '    process.Kill();
        '}

        ' Reversed the order of release per  http://stackoverflow.com/questions/12916137/best-way-to-release-excel-interop-com-object


    Catch ex As Exception
        MessageBox.Show("There was a problem CLOSING the workbook - Please double check that the data was imported correctly. ", CurAFLApp.AppName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Return 0
    Finally

        releaseObject(tmp)
        releaseObject(xlWorkBook)
        releaseObject(xlApp)
        If Not excelProcess(0).CloseMainWindow() Then

            excelProcess(0).Kill()
            blnExcelProcessKilled = True
        End If

    End Try

    Return SecCount

End Function

Public Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
        'Not sure if the following line helps or hinders -- seems to lock things up once in a while
        'GC.WaitForPendingFinalizers()
    End Try
End Sub

score 0 · Answer 4 · answered Oct 30 '15 at 17:55

0

Try :

xlWorkbook.Close(false); // if you Workbook should not be saved

instead of :

if (xlWorkbook != null)
   System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook);

xlWorkbook = null;

answered Oct 30 '15 at 17:55

CCP

7
4

How can I dispose my Excel Application

4 Answers4

Kill the excel process which has empty value for MainWindowTitle. Below is an example source code.

Linked