?
Solved

using excel in C# 2008

Posted on 2009-04-20
5
Medium Priority
?
2,027 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:g_johnson
  • 3
  • 2
5 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24193035
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.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24193040
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.
0
 
LVL 4

Author Comment

by:g_johnson
ID: 24194810
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.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 24196644
You need to release all Excel objects that you create, not just the Application instance.
0
 
LVL 4

Author Comment

by:g_johnson
ID: 24197013
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?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question