Why can't set cast an object from Excel interop?

Question

Trying to get a reference to the worksheets (using Excel interop):

Excel.Application xl = new Excel.ApplicationClass();
Excel.Workbooks xlWorkBooks = xl.Workbooks;
Excel.Workbook xlWorkBook = xlWorkBooks.Open(fileName, 0, false, 5, "", 
                      "", true, Excel.XlPlatform.xlWindows, "\t",
                      false, false, 0, true, 1, 0);

// Next line crashes
Excel.Worksheets xlWorkSheets = (Excel.Worksheets) xlWorkBook.Worksheets; 

The error is that it cannot cast it:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheets'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208B1-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Is my cast incorrect?

Answer 1

If you're dealing with Excel 2007+ I'd suggest using System.IO.Packaging + System.Xml.Linq (LINQ to XML) to manipulate excel sheets. It's much cleaner and dosen't require excel to actually be installed on the machine you're running your app on.

You'll also run into less COM collisions (such as above in your post).

If you're trying to edit Excel 2003 or earlier, then unfortunately I'm unable to help you.

Answer 2

Odd one. According to this page, it's supposed to be of type Sheets not Worksheets. Haven't tested - give it a whirl?

Answer 3

According to MSDN, Workbook.Worksheets returns Microsoft.Office.Interop.Excel.Sheets.

So you'd cast it like this:

Microsoft.Office.Interop.Excel.Sheets sheets = 
    (Microsoft.Office.Interop.Excel.Sheets)xlWorkBook.Worksheets

Or assuming Excel maps to Microsoft.Office.Interop.Excel (as appears from your question)

Excel.Sheets sheets = (Excel.Sheets)xlWorkBook.Worksheets

Answer 4

Yes, your cast is wrong.

_Workbook.Sheets gives you a Sheets instance. This interface gives you all types of sheets, not just worksheets; mainly, it includes charts, macro sheets, etc.

On the other hand, the Worksheets interface only gives you worksheets - not charts.

The interfaces are not assignable to each other; therefore, you get the COM error. It's confusing - I'm not even sure if it's possible to get an instance of the Worksheets interface through the PIA - but that's Office Interop for ya.

As long as you use the _Workbook.Worksheets property instead of the _Workbook.Sheets property, you should get an instance of Sheets that only returns Worksheet objects - in spite of the fact that the interface is capable of providing other types of sheets.