Slicer Element Sequence Out of Order

Dec 6, 2012 at 7:28 PM

Greetings,

Thank you for ExtremeML!

ExtremeML generates a corrupt document when populating an existing Excel with a Slicer.

Opening the resultant document in Excel 2010 will "repair" worksheets with slicers.

Comparing the xl/worksheets/sheet1.xml between a ExtremeML version and a native Excel 2010 version results in a single change in the element order of the sheet with a slicer.

ExtremeML's element ordering

 1387   sheetPr
 1388   dimension 
 1389   sheetViews
 1390   sheetFormatPr
 1410   extLst    
 1392   sheetData 
 1212   pageMargins
 1214   pageSetup 
 1376   drawing


 Excel 2010's element ordering
 1387   sheetPr
 1388   dimension 
 1389   sheetViews
 1390   sheetFormatPr
 1392   sheetData 
 1212   pageMargins
 1214   pageSetup 
 1376   drawing
 1410   extLst

OpenXML SDK 2.0 productivity tool indicates a validation error in the ExtremeML sheet1.xml (Element has unexpected child element '...main:extLst'. List of possible elements expected '...main:sheetData'.

Manually reordering the elements in the resultant ExtremeML document produces a valid document that can be opened by Excel 2010 with slicers intact.

Suggestions?

 

Thanks,

Tobin Cataldo

 

Dec 7, 2012 at 4:03 PM
Edited Dec 7, 2012 at 4:04 PM

Greetings again,

I noticed in the WorksheetWrapper.cs in ExtremeML.Spreadsheet has OnCustomRecomposed() manual element ordering.

If I explicitly add extList element ordering to the method, then the Excel sheet is returned with slicers intact.

var extensionList = Wrapped.GetFirstChild<WorksheetExtensionList>();
if (extensionlist != null) { wrapped.RemoveAllChildren<WorksheetExtensionList>(); wrapped.InsertAfter(extensionlist, Wrapped.LastChild); }

 

Coordinator
Dec 7, 2012 at 6:56 PM

Hello Tobin,

Sorry for the delay in replying - I just saw your original post and the follow-up at the same time.

In fact there are a couple of similar issues I have come across in the past where the integrity of an OpenXML document is broken by sequencing problems.

I wish there was a clean way of solving the problem (or even identifying all the possible places that it may occur) but I am not aware of any.

However, the solution that you suggest seems like the right one. I can't think of anything better and I think it will provide a quite robust solution.

Apologies, again, for not replying sooner.

Tim

Coordinator
Dec 7, 2012 at 7:09 PM

Update:

Just looking through some old notes on this subject, I found the following, which I wrote when I first came across this issue ...

Many OpenXML element types contain sequences of nested elements. In order to be schema-valid, these nested elements must occur in the sequence defined in the schema. It would seem appropriate that the SDK would manage this process, ensuring that the child elements of any element are written to Xml in a schema-compliant manner.


However, this is not the case. For example, the Worksheet class does not have dedicated properties for either SheetData or Hyperlinks, so each of these child element types must be be created using the AppendChild method. Unfortunately the sequence in which this method is invoked determines the sequence in which their elements will be rendered to Xml. If a Hyperlinks instance is appended before a SheetData instance, it will produce invalid Xml that is rejected by Excel. This specific example has been resolved by an ugly hack in the WorksheetWrapper custom partial class, but it does not even begin to address the broader implications of the problem.


The AppendChild method is used by the Recompose method of every wrapper class for content classes where a supported child element does not have a dedicated property on its parent class. The sequence in which AppendChild is called in Recompose is determined by the metadata available to the code generation process. Clearly, if the SDK offers no reliable mechanism to resolve this internally, it will become essential to incorporate complete schema data into code generation. Moreover, the problem will be complicated by the fact that some OpenXmlElement classes expose some, but not all, of their child elements via dedicated properties.

So, if you want, to see how I tackled this problem myself, you might care to look in the WorksheetWrapper class. However, from memory, I think I did something very similar to what you have suggested.

Good luck,

Tim