Why can't set cast an object from Excel interop?
Trying to get a reference to the worksheets (using Excel interop):
Excel.Application xl = new Excel.ApplicationClass();
Excel.Workbooks xlWorkBooks = xl.Workbooks;
Excel.Wor开发者_运维问答kbook 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?
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.
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
If it works on one environment but not another, check the HK Classes Root/TypeLib reg keys.
It is possible that you're trying to run for HKCR\TypeLib{00020813-0000-0000-C000-000000000046}\1.6 but something the user installed has added the key: HKCR\TypeLib{00020813-0000-0000-C000-000000000046}\1.7 causing the Interop call to throw an exception.
Or if that isn't it, it could be something in the GAC because of different OS versions.
I had this issue where it worked on our developer machines running Windows 7, and caused this error on a user's machine running XP.
Odd one. According to this page, it's supposed to be of type Sheets
not Worksheets
. Haven't tested - give it a whirl?
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.
This might have happened after and upgrade to latest Office version. Try going to Programs and Features, finding the installation and 'repairing' it. Apparently, it updated and/or re-registers the interop dll used to create those objects.
This might need to be done in combination with the registry fix described by @codesforcoffee.
精彩评论