OpenXml: Worksheet children reorder in a damaged file

Question

I am trying to use openxml to create automated excel files. One of the problems I am facing is placing my object model with an open xml object model for excel. I have to get to the point where I understand that the order in which I add children to the worksheet matters.

Example:

workSheet.Append(sheetViews); workSheet.Append(columns); workSheet.Append(sheetData); workSheet.Append(mergeCells); workSheet.Append(drawing); 

the above ordering does not give any error.

But the following:

 workSheet.Append(sheetViews); workSheet.Append(columns); workSheet.Append(sheetData); workSheet.Append(drawing); workSheet.Append(mergeCells); 

gives an error

Thus, this does not allow me to create a drawing object whenever I want, and add it to the worksheet. This forces me to create these elements before using them.

Can someone tell me if I understood the problem correctly? Since I believe that we should open any excel file, create a new child for the sheet and add it if necessary. But now, this may upset the order in which these elements should be added.

Thanks.

Answer 1

In accordance with the Standard Open XML File CT_Worksheet in ECMA-376, CT_Worksheet has the required sequence:

Reason for failure:

 workSheet.Append(sheetViews); workSheet.Append(columns); workSheet.Append(sheetData); workSheet.Append(drawing); workSheet.Append(mergeCells); 

This is because you have drawing before mergeCells . As long as you add mergeCells after drawing , your code should work fine.

Note. Full XSD can be found in ECMA-376 3rd edition Part 1 (.zip) - > OfficeOpenXML-XMLSchema-Strict -> sml.xsd.

Answer 2

I found that for all Singleton child objects, where the parent objects have a specific property (for example, Worksheet.sheetViews), they use the singleton property and assign it a new object instead of using Add. This causes the class itself to verify that the order is correct.

 workSheet.Append(sheetViews); workSheet.Append(columns); workSheet.Append(sheetData); // bad idea(though it does work if the order is good) workSheet.Append(drawing); workSheet.Append(mergeCells); 

More correct format ...

 workSheet.sheetViews=sheetViews; // order doesn't matter. workSheet.columns=columns; ... 

Answer 3

As Joe Masilotti explained, the order is defined in the diagram.

Unfortunately, the OpenXML library does not provide the correct order for children in serialized XML, as required by the underlying XML schema. Applications may be unable to parse XML successfully if the order is incorrect.

Here is a general solution that I use in my code:

 private T GetOrCreateWorksheetChildCollection<T>(Spreadsheet.Worksheet worksheet) where T : OpenXmlCompositeElement, new() { T collection = worksheet.GetFirstChild<T>(); if (collection == null) { collection = new T(); if (!worksheet.HasChildren) { worksheet.AppendChild(collection); } else { // compute the positions of all child elements (existing + new collection) List<int> schemaPositions = worksheet.ChildElements .Select(e => _childElementNames.IndexOf(e.LocalName)).ToList(); int collectionSchemaPos = _childElementNames.IndexOf(collection.LocalName); schemaPositions.Add(collectionSchemaPos); schemaPositions = schemaPositions.OrderBy(i => i).ToList(); // now get the index where the position of the new child is int index = schemaPositions.IndexOf(collectionSchemaPos); // this is the index to insert the new element worksheet.InsertAt(collection, index); } } return collection; } // names and order of possible child elements according to the openXML schema private static readonly List<string> _childElementNames = new List<string>() { "sheetPr", "dimension", "sheetViews", "sheetFormatPr", "cols", "sheetData", "sheetCalcPr", "sheetProtection", "protectedRanges", "scenarios", "autoFilter", "sortState", "dataConsolidate", "customSheetViews", "mergeCells", "phoneticPr", "conditionalFormatting", "dataValidations", "hyperlinks", "printOptions", "pageMargins", "pageSetup", "headerFooter", "rowBreaks", "colBreaks", "customProperties", "cellWatches", "ignoredErrors", "smartTags", "drawing", "drawingHF", "picture", "oleObjects", "controls", "webPublishItems", "tableParts", "extLst" }; 

The method always inserts the new child at the correct position, ensuring that the resulting document is valid.