Perché l'aggiunta di Filtro automatico danneggia il mio file Excel in questo esempio?

Perché l'aggiunta di Filtro automatico danneggia il mio file Excel in questo esempio?

Ciò accade a causa di dove stai scrivendo il autoFilter elemento nell'XML. Ho aperto un file su cui avevo eseguito il codice e l'ho aperto nello strumento di produttività Open XML. Questo ha mostrato che l'errore era

Osservando lo standard ECMA-376 da qui l'XML per un Worksheet assomiglia a questo:

<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>

Nota che è un sequence quindi l'ordine degli articoli è importante. Nel mio file di esempio sopra il autoFilter l'elemento è stato aggiunto dopo il pageMargins elemento contrario allo schema precedente.

Modifica del codice per scrivere il autoFilter elemento nella posizione corretta corregge il codice. Non sono sicuro che questo sia il modo più efficiente, ma dovrebbe funzionare. In sostanza sta lavorando a ritroso sugli elementi figlio di workbook finché non trova il primo elemento che è autoFilter può essere dopo. Una volta scoperto che utilizza il InsertAfter metodo per inserire il autoFilter nel posto corretto:

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;
}