asked on

using excel in C# 2008

I am using the excel object like as below. When I execute the ExcelObj.Quit() method, the object does not disappear (from task manager processes) until I close the program. What am I doing wrong?

Also, is there a better way to loop through columns "B1" thru "Z1" than the named convention I am using?

Thank you very much!

private void GetLocationLists()
        {
            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application(); ;
 
            Microsoft.Office.Interop.Excel.Workbook wb = ExcelObj.Workbooks.Open(this._SpreadSheet, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, false);
 
            Microsoft.Office.Interop.Excel.Sheets sheets = wb.Worksheets;
 
            //PARIS LOCS
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Paris");
 
            bool AllDone = false;
 
            while (!AllDone)
            {
                Microsoft.Office.Interop.Excel.Range r = ws.get_Range("B1","B1");
                if (r.Cells.Text.ToString().Trim().Length == 0)
                {
                    AllDone = true;
                }
                else
                {
                    this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("C1", "C1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("D1", "D1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("E1", "E1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("F1", "F1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("G1", "G1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("H1", "H1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("I1", "I1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("J1", "J1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
                if (!AllDone)
                {
                    r = ws.get_Range("K1", "K1");
                    if (r.Cells.Text.ToString().Trim().Length == 0)
                    {
                        AllDone = true;
                    }
                    else
                    {
                        this._ParisLocs.Add(r.Cells.Text.ToString().Trim());
                    }
                }
 
            }
 
            //When all done reading:
            wb.Close(false,this._SpreadSheet,true);
 
            ExcelObj.Quit();

Open in new window

Bob Learned

Aah, yes, COM interop with Excel...

Look at the System.Runtime.InteropServices.Marshal class, and the ReleaseComObject method. COM uses reference counting, and .NET doesn't. The run-time callable wrapper increments the reference count, but doesn't decrement it. The memory will not be released until all reference counts are zero.

Bob Learned

If you are just reading from an Excel worksheet, then you might want to explore using ADO.NET, so that you can read the worksheet like a DataTable, which is significantly easier than COM interop.

g_johnson

ASKER

Learned --

I added this to my code:

int x = System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
MessageBox.Show(x.ToString());

x returns zero, but still the Excel process persists until I close the app.

Unfortunately the spreadsheet is not layed out like a table (it is multi-purpose), so ADO.NET does not seem to be an option.

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

g_johnson

ASKER

Learned --

Thanks. I have now added code to release each object created (ranges, worksheet, worksheets, workbook, and app) immediatey after using them, and still EXCEL.EXE shows up in Task Manager Processes until I close the APP. Upon further review, it looks like they "stick around" but eventually go away -- sometimes. LOL.

Is there a way to run a clean-up procedure to look for rogue Excel objects hanging out there that need to be cleaned up?