Converting to DocumentFormat.OpenXml.Packaging.SpreadsheetDocument type

[Brian]

In my application, I'm trying to convert my workbook from Microsoft.Office.Interop.Excel.ActiveWorkbook -- to -- DocumentFormat.OpenXml.Packaging.SpreadsheetDocument

My goal is to add customXmlParts to the workbook via the DocumentFormat.OpenXml.Packaging methods. If anybody has any good references on how to achieve this; I'd greatly appreciate it. Seems to be very little information out there on how to do this stuff.

My code, and the subsequent error are listed below:

public static void AddNewPart_trial(XElement xel)

        {

            //http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2010/10/27/59361.aspx

            Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;

            Excel.Workbook wb = xlApp.ActiveWorkbook;

            SpreadsheetDocument xlsx = (SpreadsheetDocument)(wb); // <-- here's where my error occurs

            string xel_tostring = xel.ToString();

            CustomXmlPart cxmlprt = xlsx.WorkbookPart.AddNewPart<CustomXmlPart>("application/xml", "Rcc1ff99d16794c83");

        }

           

ERROR:

An exception of type 'System.InvalidCastException' occurred in XMLPartLib but was not handled in user code
Additional information: Unable to cast COM object of type 'System.__ComObject' to class type 'DocumentFormat.OpenXml.Packaging.SpreadsheetDocument'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Anybody have any thoughts on how to make this type conversion (or if it can even be done)?

 

[Fabian]

Hi Brian,

I've only ever used OpenXML once and it has been a while, so take that with a grain of salt, but:

I think a direct cast is not possible.

And more importantly, I think it most likely wouldn't make sense to do that, either.

It's best to use the usual COM methods, and not OpenXML, to add CustomXMLParts to an open workbook.

In not-so-short:

The COM model (= essentially what ExcelDnaUtil.Application returns, and everything in the Excel.-namespace) has a fairly different purpose to what OpenXML does.

The COM model lets you interact with a running instance of the Excel application itself. I.e. the user must have Excel open and running, and then you can modify stuff in it. It's basically just a way of communicating with Excel and telling it what to do (e.g. "set cell value to ABCDEF - Excel will do it for you, the user will then see it right on screen etc.).

OpenXML intends to let you work with the Office file (!) format.

It doesn't let you interact with a running session of Excel; in fact, it doesn't even require the user of your software to have it installed on their machine at all.

So its main purpose is reading and writing Office files, without needing to fire up the actual Excel application for it. Think of webservers reading/writing reports, anti virus scanners scanning worksheets for malware etc.

Trying to interact with stuff (workbooks etc.) that are currently open and being worked on in Excel using OpenXML is usually a bad idea.

Reading a file that is already open in Excel will work to some extent, but they won't be synchronized to the open workbook (e.g. if the user makes any changes in Excel and doesn't save the file, you'll never know).

Modifying an open file will pretty much never work, because A) the changes wouldn't feed through to open Excel instance, and B) the workbook file - if there even is one - will most likely be locked by Excel anyway (unless its opened in non-exlusive file mode, but that raises even more problems).

[Brian]

Thanks for the response Fabian. Yeah, it looks like COM methods are the way to go. 

I've got everything running so far doing COM. It just looked like some of the OpenXML methods provide more direct ways of doing certain things. One thing that I haven't really figured out yet is how to "set" the CustomPartXML id. My work around is to take the automatically created ID and write it to a document property so that I can call it later. OpenXML basically had a CustomXMLPartID.Set() method (or similar) that would've been much cleaner to work with, but oh well.

Anyways, thanks again for the input.