CODEWITHSUNDEEP
c#excel
dangerisgo
dangerisgo

Reputation: 1269

C# close excel handle after reading xls

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.

Upvotes: 1

Views: 1238

Answers (3)

woodykiddy
woodykiddy

Reputation: 6455

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.

Upvotes: 1

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28980

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) 
{}

Upvotes: 0

Eric J.
Eric J.

Reputation: 150118

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.

Upvotes: 0

Related Questions