OpenXml: Worksheet Child Elements change in ordering results in a corrupt file

Question

I am trying to use openxml to produce automated excel files. One problem I am facing is to accomodate my object model with open xml object model for excel. I have to come to a point where I realise that the order in which I append the child elements for a worksheet matters.

For Example:

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

the above ordering doesnot give any error.

But the following:

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

gives an error

So this doesn't let me to create a drawing object whenever I want to and append it to the worksheet. Which forces me to create these elements before using them.

Can anyone tell me if I have understood the problem correctly ? Because I believe we should be able to open any excel file create a new child element for a worksheet if necessary and append it. But now this might break the order in which these elements are supposed to be appended.

Thanks.

Answer 1

According to the Standard ECMA-376 Office Open XML File Formats, CT_Worksheet has a required sequence:

The reason the following is crashing:

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

Is because you have drawing before mergeCells. As long as you append your mergeCells after drawing, your code should work fine.

Note: You can find the full XSD in ECMA-376 3rd edition Part 1 (.zip) -> OfficeOpenXML-XMLSchema-Strict -> sml.xsd.