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

user_1455049 0
Viewed 3980

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.

openxml-sdk openxml c#
5 Answers

For those end up here via Google like I did, the function below solves the ordering problem after the child element is inserted:

public static T ReorderChildren<T>(T element) where T : OpenXmlElement
{
  Dictionary<Type, int> childOrderHashTable = element.GetType()
                                                  .GetCustomAttributes()
                                                  .Where(x => x is ChildElementInfoAttribute)
                                                  .Select( (x, idx) => new KeyValuePair<Type, int>(((ChildElementInfoAttribute)x).ElementType, idx))
                                                  .ToDictionary(x => x.Key, x => x.Value);

  List<OpenXmlElement> reorderedChildren = element.ChildElements
                                                .OrderBy(x => childOrderHashTable[x.GetType()])
                                                .ToList();
  element.RemoveAllChildren();
  element.Append(reorderedChildren);
  return element;         
}

The generated types in the DocumentFormat.OpenXml library have custom attributes that can be used to reflect metadata from the the OOXML schema. This solution relies on System.Reflection and System.Linq (i.e., not very fast) but eliminates the need to hardcode a list of strings to correctly order the child elements for a specific type.

I use this function after validation on the ValidationErrorInfo.Node property and it and cleans up the newly created element by reference. That way I don't have apply this method recursively across an entire document.

user_165806430

helb's answer is beautiful - thank you for that, helb.

It has the slight drawback that it does not test if there are already problems with the order of child elements. The following slight modification makes sure there are no pre-existing problems when adding a new element (you still need his _childElementNames, which is priceless) and it's slightly more efficient:

    private static int getChildElementOrderIndex(OpenXmlElement collection)
    {
        int orderIndex = _childElementNames.IndexOf(collection.LocalName);
        if( orderIndex < 0)
            throw new InvalidOperationException($"Internal: worksheet part {collection.LocalName} not found");
        return orderIndex;
    }
    private static T GetOrCreateWorksheetChildCollection<T>(Worksheet worksheet) where T : OpenXmlCompositeElement, new()
    {
        T collection = worksheet.GetFirstChild<T>();
        if (collection == null)
        {
            collection = new T();
            if (!worksheet.HasChildren)
            {
                worksheet.AppendChild(collection);
            }
            else
            {
                int collectionSchemaPos = getChildElementOrderIndex(collection);
                int insertPos = 0;
                int lastOrderNum = -1;
                for(int i=0; i<worksheet.ChildElements.Count; ++i)
                {
                    int thisOrderNum = getChildElementOrderIndex(worksheet.ChildElements[i]);
                    if(thisOrderNum<=lastOrderNum)
                        throw new InvalidOperationException($"Internal: worksheet parts {_childElementNames[lastOrderNum]} and {_childElementNames[thisOrderNum]} out of order");
                    lastOrderNum = thisOrderNum;
                    if( thisOrderNum < collectionSchemaPos )
                        ++insertPos;
                }
                // this is the index to insert the new element
                worksheet.InsertAt(collection, insertPos);
            }
        }
        return collection;
    }
user_10820630
Related