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
– Mathias, Commented Mar 24, 2010 at 8:14

Kangkan · Accepted Answer · 2010-03-24 07:27:09Z

3

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!

    }

answered Mar 24, 2010 at 7:27

Kangkan

15.6k11 gold badges79 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AMissico Over a year ago

+1, may I suggest changing to FinalReleaseComObject in order to be more generic in case someone uses code for something else.

to StackOverflow Over a year ago

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.

ja. · Accepted Answer · 2010-03-24 07:38:15Z

0

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

answered Mar 24, 2010 at 7:38

ja.

4,26923 silver badges22 bronze badges

Comments

Prutswonder · Accepted Answer · 2010-03-24 08:46:24Z

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, 2010 at 8:46

answered Mar 24, 2010 at 7:31

Prutswonder

10.1k3 gold badges29 silver badges41 bronze badges

2 Comments

user278618 Over a year ago

I don't understand...why create new excel process ? In next running test, or what ?

Prutswonder Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:30:45Z

0

try something not using Excel automation:

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

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Mar 24, 2010 at 9:13

liya

7925 silver badges6 bronze badges

Collectives™ on Stack Overflow

Problem with closing excel by c#

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related