Problem with closing excel by c#

Question

I've got unit test with this code:

  Excel.Application objExcel = new Excel.Application();
            Excel.Workbook objWorkbook = (Excel.Workbook)(objExcel.Workbooks._Open(@"D:\Selenium\wszystkieSeba2.xls", true,
            false, Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value));


            Excel.Worksheet ws = (Excel.Worksheet)objWorkbook.Sheets[1];
            Excel.Range r = ws.get_Range("A1", "I2575");
            DateTime dt = DateTime.Now;
            Excel.Range cellData = null;
            Excel.Range cellKwota = null;
            string cellValueData = null;
            string cellValueKwota = null;
            double dataTransakcji = 0;
            string dzien = null;
            string miesiac = null;
            int nrOperacji = 1;
            int wierszPoczatkowy = 11;
            int pozostało = 526;

            cellData = r.Cells[wierszPoczatkowy, 1] as Excel.Range;
            cellKwota = r.Cells[wierszPoczatkowy, 6] as Excel.Range;


            if ((cellData != null) && (cellKwota != null))
            {

                object valData = cellData.Value2;
                object valKwota = cellKwota.Value2;


                if ((valData != null) && (valKwota != null))
                {
                    cellValueData = valData.ToString();
                   dataTransakcji = Convert.ToDouble(cellValueData);
                   Console.WriteLine("data transakcji to: " + dataTransakcji);
                    dt = DateTime.FromOADate((double)dataTransakcji);
                   dzien = dt.Day.ToString();
                   miesiac = dt.Month.ToString();


                    cellValueKwota = valKwota.ToString();


                }
  }

                       r.Cells[wierszPoczatkowy, 8] = "ok";
            objWorkbook.Save();



            objWorkbook.Close(true, @"C:\Documents and Settings\Administrator\Pulpit\Selenium\wszystkieSeba2.xls", true);
                objExcel.Quit();

Why after finish test I'm still having excel in process (it does'nt close)

And : is there something I can improve to better perfomance ??

Excel 2007 and .net 3.5

Try to separate the access from the items in the collection from the collection itself. Not sure it will fix your issue, but sometimes, when you "chain" access to objects, the object that remains anonymous (in your case Sheets) is not cleaned up properly. Excel.Sheets sheets = objWorkbook.Sheets; Excel.Worksheet ws = (Excel.Worksheet)sheets[1]; You have the same thing with Workbooks, too. — Mathias, Mar 24 '10 at 08:14

score 3 · Accepted Answer · answered Mar 24 '10 at 07:27

I use a code segment like this to forcefully close it:

    public void DisposeExcelInstance()
    {
        //oXL.DisplayAlerts = false;
        //oWB.Close(null, null, null);
        //oXL.Quit();


        //oWB.Close(null, null, null);
        //oXL.Quit();
        ///KNG - CLEANUP code
        oXL.DisplayAlerts = false;
        oWB.Close(null, null, null);
        oXL.Workbooks.Close();
        oXL.Quit();
        if (oResizeRange != null)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oResizeRange);
        if (oSheet != null) 
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
        if (oWB != null)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
        if (oXL != null)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
        oSheet = null;
        oWB = null;
        oXL = null;
        GC.Collect(); // force final cleanup!

    }

+1, may I suggest changing to FinalReleaseComObject in order to be more generic in case someone uses code for something else. — AMissico, Mar 24 '10 at 07:40
If all references to Excel objects are released, then Excel will close. The problem is it's too easy to miss one. In the above code you've missed on - the line "oXL.Workbooks.Close()" implicitly creates a reference to "oXL.Workbooks" which isn't released. I believe that calling GC.Collect() twice at the end will pick up on the ones you missed. — Joe, Mar 24 '10 at 08:29

Prutswonder · Answer 2 · 2010-03-24T08:46:24.650

0

In addition to Kangkan's answer, you can also try to use an existing Excel instance (if there is still an unclosed instance running):

        try {
            objExcel = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
        } catch (Exception) {
            if (objExcel == null) {
                objExcel = new Excel.Application();
            }
        }

edited Mar 24 '10 at 08:46

answered Mar 24 '10 at 07:31

Prutswonder

9,894
3
27
39

I don't understand...why create new excel process ? In next running test, or what ? – user278618 Mar 24 '10 at 07:38
If you have an unclosed Excel instance, it will be re-used using the sample above. If there is no unclosed Excel instance running, then a new instance will be created. – Prutswonder Mar 24 '10 at 08:45

score 0 · Answer 3 · answered Mar 24 '10 at 07:38

0

Maybe ur reference count is off: http://support.microsoft.com/kb/317109

answered Mar 24 '10 at 07:38

ja.

4,245
20
22

score 0 · Answer 4 · edited May 23 '17 at 12:30

0

try something not using Excel automation:

How can I create an Excel compatible Spreadsheets on the server side in C#?

edited May 23 '17 at 12:30

Community

1
1

answered Mar 24 '10 at 09:13

liya

782
5
6

Problem with closing excel by c#

4 Answers4