Not able to release Excel interop COM objects

Question

Here is my code:

        Application app = null; //release this object
        Workbook wrkbuk = null;//release this object
        Worksheet wrksheet = null;//release this object
        object misval = System.Reflection.Missing.Value;
        app = new Application();
        app.Visible = false;
        string sPath = Server.MapPath("sample");
        var workbukObj = app.Workbooks; //release this object
        wrkbuk = workbukObj.Open(sPath + "\\TotalDistance.xlsx", misval, misval, misval, misval, misval, misval, misval, misval, misval, misval, misval, misval, misval, misval);// releaase this object
        wrksheet = ((Worksheet)wrkbuk.Worksheets[1]); //release this object
        Range cells = ((Worksheet)wrkbuk.Worksheets[1]).Cells; //release this
        int row = 2;
        int column = 2;
        wrksheet.Cells[row, column] = Convert.ToDateTime(txtFromDate.Text).ToString("dd-MM-yyyy") + " " + txtFromTime.Text;
        row++;
        wrksheet.Cells[row, column] = Convert.ToDateTime(txtToDate.Text).ToString("dd-MM-yyyy") + " " + txtToTime.Text;
        row = 5;
        column = 1;
        for (int k = 0; k < finalPack.Count; k++)
        {
            wrksheet.Cells[row, column] = finalPack[k].vehicleNumber;
            column++;
            //wrksheet.Cells[row, column] = finalPack[k].driverName;
            //column++;
            //wrksheet.Cells[row, column] = finalPack[k].driverNumber;
            //column++;
            //wrksheet.Cells[row, column] = finalPack[k].driverAddress;
            //column++;
            if (finalPack[k].distanceTravelled == .001)
            {
                wrksheet.Cells[row, column] = "";
            }
            else
            {
                wrksheet.Cells[row, column] = Convert.ToDateTime(finalPack[k].frmTime).ToString("dd-MM-yyyy");

            }
            column++;
            wrksheet.Cells[row, column] = finalPack[k].startAddress;
            column++;
            wrksheet.Cells[row, column] = finalPack[k].endAddress;
            column++;
            if (finalPack[k].distanceTravelled != .001)
            {
                wrksheet.Cells[row, column] = finalPack[k].distanceTravelled;
            }
            else
            {
                wrksheet.Cells[row, column] = "";

            }
            column++;
            if (finalPack[k].tempDist != 0.0)
            {
                wrksheet.Cells[row, column] = finalPack[k].tempDist;
            }
            else
            {
                wrksheet.Cells[row, column] = "";

            }
            column++;
            if (finalPack[k].totalDistance != 0.0)
            {
                wrksheet.Cells[row, column] = finalPack[k].totalDistance;
            }
            else
            {
                wrksheet.Cells[row, column] = "";

            }
            column = 1;
            row++;

        }

        string filePath = Server.MapPath("DistanceReports") + "\\" + DateTime.Now.ToString("dd_MMM_yyyy HH_mm_ss") + ".xlsx";
        wrkbuk.SaveAs(filePath, misval, misval, misval, misval, misval, XlSaveAsAccessMode.xlExclusive, misval, misval, misval, misval, misval);

        wrkbuk.Close(Type.Missing, Type.Missing, Type.Missing);
        workbukObj.Close();

        string Timezone = "India Standard Time";
        if (Session["timeZone"] != null)
            Timezone = Session["timeZone"].ToString();
        DateTime currentTime = timeZoned(Timezone);

        FileInfo file = new FileInfo(filePath);
        if (file.Exists)
        {
            Response.Clear();
            Response.ClearHeaders();
            Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=" + "DistanceReport" + currentTime.ToString("yyyy/MM/dd HH:mm") + ".xlsx");
            Response.AddHeader("Content-Type", "application/Excel");
            Response.ContentType = "application/vnd.xls";
            Response.AddHeader("Content-Length", file.Length.ToString());
            Response.WriteFile(file.FullName);

        }
        try
        {
            app.Quit();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            Marshal.FinalReleaseComObject(cells);
            Marshal.FinalReleaseComObject(wrksheet);
            Marshal.FinalReleaseComObject(wrkbuk);
            Marshal.FinalReleaseComObject(workbukObj);
            Marshal.FinalReleaseComObject(app);
            //  wrkbuk.Close(null, null, null);
            GC.Collect();
            GC.WaitForPendingFinalizers();

        }
        catch
        {
        }

I am trying to close the Excel.exe instance which always stays open in my Task Manager. I read about the solution here but I am not able to do it. Is there anything I am missing?? Please help.!

Do you *really* need all this code to demonstrate the problem? — Bathsheba, Feb 25 '14 at 12:56
I guess I do need the whole code..I Wanted to show what COM objects are declared and whether they were properly released or not.. — writeToBhuwan, Feb 25 '14 at 12:59
possible duplicate of [Understanding Garbage Collection in .net](http://stackoverflow.com/questions/17130382/understanding-garbage-collection-in-net) — Hans Passant, Feb 25 '14 at 12:59
Or in other words, get rid of the Marshal calls and move the GC methods out of this monster method. It has to be called by whatever code calls this method to work properly when you use a debugger. — Hans Passant, Feb 25 '14 at 13:01
"Range cells = ((Worksheet)wrkbuk.Worksheets[1]).Cells;" <--- you are creating another COM object here, you should be referencing the "wrksheet" object from the line above. I've no idea if that is causing your problem, but try it and see. — Chris McAtackney, Feb 25 '14 at 13:04
Thank for pointing out the flaw in the code.. I fixed it but still it is not working.. What could be the issue?? — writeToBhuwan, Feb 25 '14 at 13:07
Can you try writing to a single cell instead of a loop? Declare a range variable, assign the location to it & release it later (same as what you've done for other objects)? The idea is to check if writing to 1 cell reference & releasing it, releases the excel instance. — shahkalpesh, Feb 25 '14 at 14:53
It is not releasing the COM object even if remove the loop.. Please help... :( — writeToBhuwan, Feb 26 '14 at 05:52

score 1 · Answer 1 · answered Jun 11 '14 at 21:08

I'm not sure on what is going on wrong in your code. But here is a function to close your excel files:

 private void releaseObject(object obj) {
        try {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex) {
            obj = null;
            MessageBox.Show("Unable to release the Object " + ex.ToString());
        }
        finally {
            GC.Collect();
        }
    }

Then you call it in the end of your code:

try {
   wrkbuk.Close(true);
   app.Quit();
   releaseObject(app);
   releaseObject(wrkbuk);     
   releaseObject(wrksheet);   
} catch (Exception ex) {
   MessageBox.Show(ex.ToString());     
}

Not able to release Excel interop COM objects

1 Answers1