c# - 为什么在此示例中附加AutoFilter会损坏我的excel文件？

嗨，我使用以下方法来应用自动筛选器:

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

我有这个简单的Excel工作表:

问题是当我将此方法称为:

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

或者

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

当我尝试在以下情况下打开过滤器时，该过滤器未应用且文件损坏:

问题:任何想法如何将此自动文件管理器应用于excel工作表而不损坏它？

最佳答案

发生这种情况是因为您在XML中写入autoFilter元素的位置。我打开了一个运行了您的代码的文件，并在Open XML Productivity Tool中打开了它。这表明错误是

从here看ECMA-376标准，Worksheet的XML看起来像这样:

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

请注意，这是一个sequence，因此项目的顺序很重要。在上面的示例文件中，在autoFilter元素之后添加了pageMargins元素，这与上面的架构相反。

更改代码以将autoFilter元素写入正确的位置可修复您的代码。我不确定这是否是最有效的方法，但这应该可行。从本质上讲，它向后处理workbook的子元素，直到找到autoFilter之后的第一个元素。一旦发现它使用InsertAfter方法将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 };
        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;
}

关于c# - 为什么在此示例中附加AutoFilter会损坏我的excel文件？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25398450/