C# close excel handle after reading xls

Question

I am attempting to open an xls file, read in several rows of data, form a custom list from data and then close the excel sheet. This is what I have:

InitializeWorkbook(Path);
List<Custom> list = new List<Custom>();

Worksheet wkSht = (Worksheet)workBk.Worksheets[3];

if (wkSht.Name.Equals("Sht3", StringComparison.OrdinalIgnoreCase))
{
    Range PartNumRange = wkSht.get_Range("A:A", System.Reflection.Missing.Value);
    Range RevRange = wkSht.get_Range("C:C", System.Reflection.Missing.Value);
    Range SwRange = wkSht.get_Range("L:L", System.Reflection.Missing.Value);
    Range NomenRange = wkSht.get_Range("M:M", System.Reflection.Missing.Value);

    // Start at Row 6
    int i = 6;
    object Num = (PartNumRange[i, 1] as Range).Text;
    object Nomen = (NomenRange[i, 1] as Range).Text;
    object Sw = (SwRange[i, 1] as Range).Text;
    object SwRev = (RevRange[i, 1] as Range).Text;

    while (!string.IsNullOrEmpty(Num.ToString()) || !string.IsNullOrEmpty(Nomen.ToString()) || !string.IsNullOrEmpty(Sw.ToString()) || !string.IsNullOrEmpty(SwRev.ToString()))
    {
        if (!string.IsNullOrEmpty(Nomen.ToString()) && !string.IsNullOrEmpty(Sw.ToString()) && !string.IsNullOrEmpty(SwRev.ToString())
        {
            Custom item = new Custom();

            item.PartNumber = (PartNumRange[i, 1] as Range).Text.ToString();
            item.Nomenclature = (NomenRange[i, 1] as Range).Text.ToString();
            item.SwNumber = (SwRange[i, 1] as Range).Text.ToString();
            item.SwRevision = (RevRange[i, 1] as Range).Text.ToString();

            list.Add(item);
        }

        i++;
        Num = (PartNumRange[i, 1] as Range).Text;
        Nomen = (NomenRange[i, 1] as Range).Text;
        Sw = (SwRange[i, 1] as Range).Text;
        SwRev = (RevRange[i, 1] as Range).Text;
    }

    GC.Collect();
    GC.WaitForPendingFinalizers();

    Marshal.ReleaseComObject(PartNumRange);
    Marshal.ReleaseComObject(RevRange);
    Marshal.ReleaseComObject(SwRange);
    Marshal.ReleaseComObject(NomenRange);

    Marshal.ReleaseComObject(wkSht);
    workBk.Close(false, System.Reflection.Missing.Value, false);
    Marshal.ReleaseComObject(workBk);

    Marshal.ReleaseComObject(ExcelWorkSheets);

    ExcelApp.Quit();
    Marshal.ReleaseComObject(ExcelApp);

    GC.Collect();
    GC.WaitForPendingFinalizers();
}

I dont know why but Excel is not closing after I finish executing. Only when the program exits does Excel exit. I have tried all sorts of combinations and have avoided the two dot rule but still a no go. Any ideas on what I'm missing?

EDIT: Calling ExcelApp.Quit closes both ExcelApp and ExcelWorksheets.

Believe me, I walked the Excel automation way (two dot rule, marshal release and all), then I discovered Flexcel, and I never looked back. You might consider this solution? — Vinzz, Oct 02 '12 at 15:49

score 1 · Accepted Answer · edited May 23 '17 at 10:24

1

You might want to take a look at this post: How do I properly clean up Excel interop objects?

There's a lot of in-depth discussions about handling COM objects in C# that you might find helpful. Worth a read.

edited May 23 '17 at 10:24

Community

1
1

answered Oct 02 '12 at 16:01

woodykiddy

6,074
16
59
100

The question linked above is very good. In fact, an answer there maybe the same problem: workBk.Worksheets[3] is creating a wrapper around the Worksheets COM Object. – Richard Morgan Oct 02 '12 at 18:44
Rich, that was it. There was a few other things I had noticed in the API I was using (that had InitializeWorkbook and CloseExcel methods in it) to control Excel that were also doing the same thing (like ExcelApp.Workbook.Open()) so I had fixed those and everything is working now. Thanks! – dangerisgo Oct 02 '12 at 19:47

score 0 · Answer 2 · answered Oct 02 '12 at 15:49

0

You also need to call close on

ExcelWorkSheets
ExcelApp

Calling Marshal.ReleaseComObject disposes the runtime callable wrapper, but leaves the application itself still alive and kicking.

answered Oct 02 '12 at 15:49

Eric J.

147,927
63
340
553

ExcelApp.Quit closes ExcelApp and ExcelWorkSheets, sorry forgot to mention that. – dangerisgo Oct 02 '12 at 15:52

score 0 · Answer 3 · answered Oct 02 '12 at 15:53

0

You can try with this code - it's work fine

while (Marshal.ReleaseComObject(YourRange) > 0) 
{}

while (Marshal.ReleaseComObject(YourWorkSheet) > 0) 
{}

while (Marshal.ReleaseComObject(YourWorkBook) > 0) 
{}

YourApplication.quit()

while (Marshal.ReleaseComObject(YourApplication) > 0) 
{}

answered Oct 02 '12 at 15:53

Aghilas Yakoub

28,516
5
46
51

I just tried this and it Excel is still running. I have updated the OP with an update. – dangerisgo Oct 02 '12 at 16:06
@dangerisgo add while to your code in order to clean all instance, i had the same problem, i resolved with while;;;; But you can delete GC.Collect(); GC.WaitForPendingFinalizers(); – Aghilas Yakoub Oct 02 '12 at 16:11

C# close excel handle after reading xls

3 Answers3