Why does this Using() give me an error?

Question

I am trying to open an (hundreds actually) excel file(s). I open the application but want to use the Using() functionality around each of the workbooks I open. Why is this resulting in an error?

using (Excel.Workbook wbXL = appXL.Workbooks.Open(_sourceFullPath, Type.Missing, Excel.XlFileAccess.xlReadOnly))
{
    //stuff with wbXL
}

using gets the red underline and says "'Microsoft.Office.Interop.excel.Workbook':Type used in a using statement must be implicitly convertible to 'System.IDisposable'.

How to make this work?

Answer 1

Pretty much what it says - you can only use using with classes that implement IDisposable, so that under the covers the compiler knows which function to call on finalisation - yourclass.Dispose(). The Excel interop classes don't implement this.

So you've got two choices:

1. Write your own wrapper class for Excel.Workbook that implements IDispose and either exposes the object itself to call methods on, or wraps those methods too, e.g.

   public class DisposableWorkbook : IDisposable
   {
       private Excel.Workbook _workbook = null;
   
       public DisposableWorkbook(Excel.Application appXL, String path,
                                 NotSureOfType otherArgument,
                                 Excel.XlFileAccess access)
       {
           _workbook = appXL.Workbooks.Open(path, otherArgument, access);
       }
   
       public Excel.Workbook Workbook
       {
           get { return _workbook; }
       }
   
       public void Dispose()
       {
           if (workbook != null)
           {
               workbook.Close(Excel.XlSaveAction.xlDoNotSaveChanges,
                              workbookToClose);
               workbook = null;
           }
       }
   }
   
   using (DisposableWorkbook dwbXL = new DisposableWorkbook(appXL,
             _sourceFullPath, Type.Missing, Excel.XlFileAccess.xlReadOnly))
   {
        Excel.Workbook wbXL = dwbXL.Workbook;
        // stuff with wbXL
   }
   

2. Implement using yourself, e.g.

   Excel.Workbook wbXL = null;
   try
   {
       wbxl = appXL.Workbooks.Open(_sourceFullPath, Type.Missing,
                                   Excel.XlFileAccess.xlReadOnly);
       //stuff with wbXL
   }
   finally
   {
       if (wbxl != null) wbxl.Close();
   }
   

Answer 2

Businessmanger emb = new Businessmanger();
        try
        {


            TempData["deparmentList"] = Deplist;
            return PartialView("create");
        }
        catch (Exception)
        {


            throw;
        }
        finally {
            //object dispose here
                            ((IDisposable)emb).Dispose();
        }

Answer 3

You can't make it work.

The using block is used to free resources from objects that implement the IDisposable interface as quickly and as safely as possible.

Excel.Workbook does not implement IDisposable so you can't declare it for use in a using block.

Answer 4

using Statement (C# Reference):

Provides a convenient syntax that ensures the correct use of IDisposable objects.

Excel.Workbook does not implement IDisposable, so you can't use using for it..

Answer 5

Any item in a using statement must implement the IDisposable interface. I haven't got the documentation to hand, but I'll guess Excel.Workbook doesn't implement this interface.