Friday, September 12, 2008

How to determine what kind of ActiveX components are embedded in the Sheet?

If you have a Form control listbox and an ActiveX listbox... here's how they get encoded in the vmlDrawing1.vml document for that sheet.

Now... the first shape's ClientData says that this is an object of type List box.. this signfies the form control... the second shape's ClientData says that this is an object of type "Pict"... and this actually signifies the ActiveX Listbox control...

now if you embed an activeX checkbox control in the sheet, it will also generate a shape and a clientData with object type = "pict" .... in such a situation... how will you distinguish which pict is a listbox and a checkbox...

There's only one way you can do that... No.. not by running through the child nodes of ClientData and determining with the combination of child controls... you can achieve by doing so, but its not recommended.
The reliable way to do that is to retrieve the ActiveX?.xml of the control embedded in that sheet. Where are the ActiveX?.xml files stored?
They are stored in the xl/ActiveX folder of the Xlsx package.

How do we know which ActiveX?.xml is encoding what control in the sheeet...

There is a section "controls" in the sheet?.xml which has the relationship Ids and names of those controls in it.

Now by iterating on the sheet?'s relationships... you can get the activeX?.xml parts easily.. using the sheet1.xml.rels

Now the activeX1.xml contents will look like this.

note the attirbute ax:classId of the ax:ocx node... the value of it is a string

the first part of the class id is "8BD21D40"... this represents a listbox...
if the first part of the class id is "8BD21D20" then it represents a checkbox... and so on... (I will post the full list of class ids, for other contorls after I do some research)

for the rest of the properties of the listbox, you can use their corresponding shape elements in the VMLdrawing1.vml for the sheet... how do you get those shapes?? Using the shape Id mentioned in the Control element for the activeX control, in the Controls section of the sheet?.xml The Shape Id mentioned in this section will not match exactly to the v:shape's shapeid.... you have to find the shape by checking which v:shape's shapeid ends with the shapeId mentioned in the controls section.

So to read an activeX form control embedded in the sheet, you have to first determine what type of control it is using the activeX?.xml's classid, and then the properties of that control using the shape Id, and retrieve that shape from the sheet's vml document using the shape Id...


NOTE: The XML sections have not been posted in the blog... because the blog construes them as HTML... will convert them to plain text and update the blog soon.

No comments: