Do I need to release the excel worksheet object in foreach loop?

Question

I am using the Excel interop in C# in my web site project to parse an excel file and convert it to a text file. The sample code is as following.

Excel.Application excel=new Excel.Application();
Excel.Workbooks wbs=excel.Workbooks;
Excel.Workbook wb=wbs.Open(…);

Try 
{
   …
   Foreach(Excel.Worksheet sheet in wb.Sheets)
   {
      …
      Marshal.RealseComObject(sheet);
      sheet=null; //compiler error
   }
   …
}

Catch()
{
}

Finally
{
   wb.Close(false, missing, missing);
   while (Marshal.ReleaseComObject(wb)!=0)
   {
   }
   wb=null;
   while (Marshal.ReleaseComObject(wbs)!=0)
   {
   }
   wbs=null;

   excel.Quit();
   while (Marshal.ReleaseComObject(excel)!=0)
   {
   }
   excel=null;
   GC.Collect();
   GC.WaitForPendingFinalizers();
}

My question is do I need to release the worksheet object in the foreach loop? I saw a few sample codes about programming with excel worksheet, none of them do. If I do need to relase the object, in my foreach loop, i couldn't set the sheet to null, does it mean i shouldn't use foreach?

I believe that I already answered you in [this post](http://stackoverflow.com/questions/19713082/what-is-the-best-practise-to-relase-excel-interop-objects-in-c/19713398?noredirect=1#comment29396895_19713398). — Brian, Nov 04 '13 at 21:51

score 0 · Answer 1 · answered Nov 04 '13 at 21:54

0

When you're setting sheet to null, you're actually setting wb.sheets to null. I don't think Excel object will allow that.

answered Nov 04 '13 at 21:54

Mahesh Subramanian

26
4

If I create a func like: private void NAR(object o) { try { while (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0) ; } catch { } finally { o = null; } }. I could set the sheet to null indirectly. – GLP Nov 04 '13 at 22:06
setting to null will not have any advantage IMHO.. Is there any reason you want to set it to null? – Mahesh Subramanian Nov 14 '13 at 18:28

score 0 · Answer 2 · answered Nov 05 '13 at 01:14

This is, what you do is really bad idea - on the website server to use Office interop. And your question is exactly related to your wrong business decision to do it. What you do, can be completed by using Microsoft.ACE.OleDb.<version>. With ACE you query your workbook like db table and this method is really thin vs interop.

Do I need to release the excel worksheet object in foreach loop?

2 Answers2