Why appending AutoFilter corrupts my excel file in this example?

Hi I use the below method to apply an AutoFilter :

public static void ApplyAutofilter(string fileName, string sheetName, string reference)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
                var arrSheets = sheets as Sheet[] ?? sheets.ToArray();

                string relationshipId = arrSheets.First().Id.Value;
                var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

                var autoFilter = new AutoFilter() { Reference = reference };
                worksheetPart.Worksheet.Append(autoFilter);

                worksheetPart.Worksheet.Save();
            }
        }

And I have this simple Excel sheet:

The thing is when I call this method as:

ApplyAutofilter(@".\Test.xlsx", "Foo", "A0:A200");

or

ApplyAutofilter(@".\Test.xlsx", "Foo", "A1:A200");

The filter is not applied and the file becomes corrupted when I try to open it after:

QUESTION: Any idea how to apply this autofiler to an excel sheet without corrupting it?

Solution 1:

This happens because of where you are writing the autoFilter element in the XML. I opened a file that I had run your code on and opened it in the Open XML Productivity Tool. This showed that the error was

Looking at the ECMA-376 standard from here the XML for a Worksheet looks like this:

<xsd:complexType name="CT_Worksheet">
  <xsd:sequence>
    <xsd:element name="sheetPr" type="CT_SheetPr" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="dimension" type="CT_SheetDimension" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="sheetViews" type="CT_SheetViews" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="sheetFormatPr" type="CT_SheetFormatPr" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="cols" type="CT_Cols" minOccurs="0" maxOccurs="unbounded"/>
    <xsd:element name="sheetData" type="CT_SheetData" minOccurs="1" maxOccurs="1"/>
    <xsd:element name="sheetCalcPr" type="CT_SheetCalcPr" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="sheetProtection" type="CT_SheetProtection" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="protectedRanges" type="CT_ProtectedRanges" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="scenarios" type="CT_Scenarios" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="autoFilter" type="CT_AutoFilter" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="sortState" type="CT_SortState" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="dataConsolidate" type="CT_DataConsolidate" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="customSheetViews" type="CT_CustomSheetViews" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="mergeCells" type="CT_MergeCells" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="phoneticPr" type="CT_PhoneticPr" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="conditionalFormatting" type="CT_ConditionalFormatting" minOccurs="0" maxOccurs="unbounded"/>
    <xsd:element name="dataValidations" type="CT_DataValidations" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="hyperlinks" type="CT_Hyperlinks" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="printOptions" type="CT_PrintOptions" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="pageMargins" type="CT_PageMargins" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="pageSetup" type="CT_PageSetup" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="headerFooter" type="CT_HeaderFooter" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="rowBreaks" type="CT_PageBreak" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="colBreaks" type="CT_PageBreak" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="customProperties" type="CT_CustomProperties" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="cellWatches" type="CT_CellWatches" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="ignoredErrors" type="CT_IgnoredErrors" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="smartTags" type="CT_SmartTags" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="drawing" type="CT_Drawing" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="drawingHF" type="CT_DrawingHF" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="picture" type="CT_SheetBackgroundPicture" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="oleObjects" type="CT_OleObjects" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="controls" type="CT_Controls" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="webPublishItems" type="CT_WebPublishItems" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="tableParts" type="CT_TableParts" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="extLst" type="CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
  </xsd:sequence>
</xsd:complexType>

Notice that it's a sequence so the order of the items matters. In my sample file above the autoFilter element was added after the pageMargins element which is contrary to the above schema.

Changing the code to write the autoFilter element to the correct place fixes your code. I'm not sure if this is the most effecient way but this should work. It is essentially working backwards up the child elements of the workbook until it finds the first element that the autoFilter can be after. Once it finds that it uses the InsertAfter method to insert the autoFilter into the correct place:

public static void ApplyAutofilter(string fileName, string sheetName, string reference)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
    {
        IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
        var arrSheets = sheets as Sheet[] ?? sheets.ToArray();

        string relationshipId = arrSheets.First().Id.Value;
        var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

        var autoFilter = new AutoFilter() { Reference = reference };
        OpenXmlElement preceedingElement = GetPreceedingElement(worksheetPart);
        worksheetPart.Worksheet.InsertAfter(autoFilter, preceedingElement);

        worksheetPart.Worksheet.Save();
    }
}

public static OpenXmlElement GetPreceedingElement(WorksheetPart worksheetPart)
{
    List<Type> elements = new List<Type>()
    {
        typeof(Scenarios),
        typeof(ProtectedRanges),
        typeof(SheetProtection),
        typeof(SheetCalculationProperties),
        typeof(SheetData)
    };

    OpenXmlElement preceedingElement = null;
    foreach (var item in worksheetPart.Worksheet.ChildElements.Reverse())
    {
        if (elements.Contains(item.GetType()))
        {
            preceedingElement = item;
            break;
        }
    }

    return preceedingElement;
}