[Jim SnyderParticipant]

There have been some active threads at StackOverflow and CodeProject. Neither has a forum, but searching for OpenXML will pull up a few.

[Jim SnyderParticipant]

I haven’t seen much useful traffic on here recently. I have not connected to OLAP with OpenXML and cannot help except to tell you to look elsewhere.

[Jim SnyderParticipant]

Can you post your Interop code? 300 x 300 is pretty small and even Interop should be fast on that.

[Jim SnyderParticipant]

I can’t help you with ASP.Net Core, but when I get no output, it is nearly always because I have the XML mismatched. Since XML definitions have order, the adding of OpenXML parts have to match. Here is the order (also obtainable by opening a spreadsheet renamed to .zip):
`<complexType name=”CT_Worksheet”>
<sequence>
<element name=”sheetPr”/>
<element name=”dimension”/>
<element name=”sheetViews”/>
<element name=”sheetFormatPr”/>
<element name=”cols”/>
<element name=”sheetData”/>
<element name=”sheetCalcPr”/>
<element name=”sheetProtection”/>
<element name=”protectedRanges”/>
<element name=”scenarios”/>
<element name=”autoFilter”/>
<element name=”sortState”/>
<element name=”dataConsolidate”/>
<element name=”customSheetViews”/>
<element name=”mergeCells”/>
<element name=”phoneticPr”/>
<element name=”conditionalFormatting”/>
<element name=”dataValidations”/>
<element name=”hyperlinks”/>
<element name=”printOptions”/>
<element name=”pageMargins”/>
<element name=”pageSetup”/>
<element name=”headerFooter”/>
<element name=”rowBreaks”/>
<element name=”colBreaks”/>
<element name=”customProperties”/>
<element name=”cellWatches”/>
<element name=”ignoredErrors”/>
<element name=”smartTags”/>
<element name=”drawing”/>
<element name=”legacyDrawing”/>
<element name=”legacyDrawingHF”/>
<element name=”picture”/>
<element name=”oleObjects”/>
<element name=”controls”/>
<element name=”webPublishItems”/>
<element name=”tableParts”/>
<element name=”extLst”/>
</sequence>
</complexType>
Note the <sequence> tag*

*This is copied from Vincent Tan’s “Spreasdsheet OpenXML From Scratch”. Vincent also has a chapter on “142 How to export your Open XML spreadsheet in ASP.NET”

[Jim SnyderParticipant]

Thank you!

[Jim SnyderParticipant]

That is correct. However, you can do things just in XML if you understand what is going on in detail. I would consider that painful to do. Another pair of solutions without OpenXML or XML hacking would be either VBA or Interop.

In VBA, you could make an external XLSM that would open the file and insert the image. In Interop, you would read in the file and insert the image.

My company network does not allow this, but ADO.NET can treat the spreadsheet like a database and insert the image without reading the Excel. I would consider this as the cleanest approach for what you are trying to do.

[Jim SnyderParticipant]

Also, I use C# to write the OpenXML and cannot help much with the JavaScript. The main thing to understand is that you are working your way inward from the SpreadsheetDocument object. It contains a Workbookpart object with at least two classes embedded. They are the Workbook which contains Sheet objects and the Worksheetpart that ties Worksheet objects to SheetData objects. Another Vincent Tam excerpt:

The “behind-the-scenes” explanation is that you need at least 2 classes, Worksheet class and Workbook class.

The Worksheet class requires at least the SheetData class as a child (even if empty).

The Workbook class requires at least the Sheets class as a child, which in turn requires at least one Sheet class as a child (thus grandchild of Workbook).

If you explore the resulting spreadsheet file, you’ll find the corresponding worksheet.xml and workbook.xml files. They are tied together with the relationship ID of the WorksheetPart class (assigned to the Sheet.Id property).

Just understand that underneath all this, you’re really just working with XML files and XML tags. The Open XML SDK just abstracts that away for you.

If you have downloaded the OpenXML SDK 2.5 toolkit (https://www.microsoft.com/en-us/download/details.aspx?id=30425), you can read the documentation within to help you specifically with the XML.

Also, “Open XML The markup explained” by Wouter van Vugt explains things at the SpreadsheetML level which may be helpful.

[Jim SnyderParticipant]

The key is in the first paragraph. Manually make a spreadsheet with the images where you want OpenXML to place them. Then the following steps enable you to see the XML Excel used to put them there:

If you’ve done the trick on inserting an image into an Excel file, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the /xl/drawings/drawing1.xml file:

When you write the script for OpenXML, the main thing that threw me for a while was the realization that order is important, matching the XML order of tags to what you are building or editing.

[Jim SnyderParticipant]

Trying that again with some post formatting:

If you’ve done the trick on inserting an image into an Excel file, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the /xl/drawings/drawing1.xml file:
-<xdr:twoCellAnchor editAs=”oneCell”>
– <xdr:from>
<xdr:col>0</xdr:col>
<xdr:colOff>0</xdr:colOff>
<xdr:row>0</xdr:row>
<xdr:rowOff>0</xdr:rowOff>
</xdr:from>
– <xdr:to>
<xdr:col>6</xdr:col>
<xdr:colOff>248195</xdr:colOff>
<xdr:row>7</xdr:row>
<xdr:rowOff>152608</xdr:rowOff>
</xdr:to>

This is for positioning the image. I’m using the AbsoluteAnchor instead of the TwoCellAnchor. After much hairtearing, I’ve decided the TwoCellAnchor class is too hard to use.

[Jim SnyderParticipant]

There is. Vincent Tam wrote a book on OpenXML and this is a quote from the book. I have not done it so cannot help directly, but he has several chapters on working with images:

If you’ve done the trick on inserting an image into an Excel file, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the /xl/drawings/drawing1.xml file: -<xdr:twoCellAnchor editAs=”oneCell”> – <xdr:from> <xdr:col>0</xdr:col> <xdr:colOff>0</xdr:colOff> <xdr:row>0</xdr:row> <xdr:rowOff>0</xdr:rowOff> </xdr:from> – <xdr:to> <xdr:col>6</xdr:col> <xdr:colOff>248195</xdr:colOff> <xdr:row>7</xdr:row> <xdr:rowOff>152608</xdr:rowOff> </xdr:to>
This is for positioning the image. I’m using the AbsoluteAnchor instead of the TwoCellAnchor. After much hairtearing, I’ve decided the TwoCellAnchor class is too hard to use.

[Jim SnyderParticipant]

If you are using the Open XML SDK 2.5, you can use reflection to generate a file of the meta information within the spreadsheet. Unlike renaming the .xlsx file to .zip, the reflection is giving you the Open XML code to recreate your spreadsheet. You can run it right away and see if the ActiveX controls were regenerated or not.

[Jim SnyderParticipant]

I can offer debugging suggestions, but do not have Open XML loaded to be able to do what I will be telling you to do.

The Open XML SDK 2.5 has the ability to use reflection to expose the XML used in an Excel spreadsheet. Build the spreadsheet you are trying to build with Open XML and access it through the tool. What it will show is the proper order of the XML you are trying to create in Open XML. This would be the most likely problem causing the created Excel spreadsheet not to open. Below is a list from Vincent Tan’s book:

// OpenXML Sheet Object Attachment Order
<complexType name=”CT_Worksheet”>
<sequence>
<element name=”sheetPr”/>
<element name=”dimension”/>
<element name=”sheetViews”/>
<element name=”sheetFormatPr”/>
<element name=”cols”/>
<element name=”sheetData”/>
<element name=”sheetCalcPr”/>
<element name=”sheetProtection”/>
<element name=”protectedRanges”/>
<element name=”scenarios”/>
<element name=”autoFilter”/>
<element name=”sortState”/>
<element name=”dataConsolidate”/>
<element name=”customSheetViews”/>
<element name=”mergeCells”/>
<element name=”phoneticPr”/>
<element name=”conditionalFormatting”/>
<element name=”dataValidations”/>
<element name=”hyperlinks”/>
<element name=”printOptions”/>
<element name=”pageMargins”/>
<element name=”pageSetup”/>
<element name=”headerFooter”/>
<element name=”rowBreaks”/>
<element name=”colBreaks”/>
<element name=”customProperties”/>
<element name=”cellWatches”/>
<element name=”ignoredErrors”/>
<element name=”smartTags”/>
<element name=”drawing”/>
<element name=”legacyDrawing”/>
<element name=”legacyDrawingHF”/>
<element name=”picture”/>
<element name=”oleObjects”/>
<element name=”controls”/>
<element name=”webPublishItems”/>
<element name=”tableParts”/>
<element name=”extLst”/>
</sequence>
</complexType>

// OpenXML Workbook Object child xml order
FileSharing <x:fileSharing>
WorkbookProperties <x:workbookPr>
WorkbookProtection <x:workbookProtection>
BookViews <x:bookViews>
Sheets <x:sheets>
FunctionGroups <x:functionGroups>
ExternalReferences <x:externalReferences>
DefinedNames <x:definedNames>
CalculationProperties <x:calcPr>
OleSize <x:oleSize>
CustomWorkbookViews <x:customWorkbookViews>
PivotCaches <x:pivotCaches>
SmartTagProperties <x:smartTagPr>
SmartTagTypes <x:smartTagTypes>
WebPublishing <x:webPublishing>
FileRecoveryProperties <x:fileRecoveryPr>
WebPublishObjects <x:webPublishObjects>
WorkbookExtensionList <x:extLst>

It isn’t the only possibility. Others include not keeping table names unique, using sheet order instead of sheet id order, forgetting to attach sheets to sheetparts (etc) and others. I will do some research and try to find the order that sheetview fits into.

[Jim SnyderParticipant]

What you are looking for is the Pane class. Vincent Tan describes it below:

Freezing is easier to understand than splitting For both freezing and splitting panes, you’ll be working with the Pane class. Specifically, you’ll be assigning values to the HorizontalSplit and VerticalSplit properties (it’s still these 2 properties for freezing, despite the name of the properties).
Now for freezing panes, you simply assign the number of left-most columns and top-most rows to the HorizontalSplit and VerticalSplit properties respectively. For example, if you want to freeze the first 3 columns, just assign 3 to HorizontalSplit. Splitting panes is a little more complicated. For splitting, the HorizontalSplit and VerticalSplit properties take on different values than with freezing. From the Open XML SDK documentation:
Horizontal position of the split, in twentieths of a point; 0 if none. If the pane is frozen, this value indicates the number of columns visible in the top pane.
and
Vertical position of the split, in twentieths of a point; 0 if none. If the pane is frozen, this value indicates the number of rows visible in the left pane.
From my investigations, it’s not a simple “find width/height in point units then multiply by 20”.

[Jim SnyderParticipant]

If you still have a copy of the original before repairs, try opening that with OpenXML. Is it getting the same error there?

[Jim SnyderParticipant]

I appreciate you saying thanks! Now I can go home on a Friday with a sense of accomplishment. ==;-)

[Author]

Posts