添加自动筛选和排序会导致Excel崩溃。

Question

我正在开发一个应用程序，可以使用OpenXML将一些数据导出到Excel文件中。除了自动筛选器之外，一切都运作正常。我的想法是在数据的主体部分添加自动筛选器，以便用户自动具有控制来过滤和排序数据。因此，在代码中，我要这样做：

var filter = new AutoFilter() { Reference = string.Format("{0}:{1}", topLeftCellReference, bottomRightCellReference ) };
worksheet.AppendChild(filter);

在导出的XLSX文件中，看起来大致如下：

<x:autoFilter ref="A4:L33" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />

它会被添加到工作表的sheetData和mergeCells之间。

然后我可以在Excel中打开这个筛选器，而且它也能正常工作。但是如果您尝试对一个列进行排序，该列将被排序，然后Excel会崩溃。保存并重新加载文件（这会强制Excel清理所有内容）无法解决问题。但是，如果您先应用筛选器（比如对某列进行筛选以选择大于10的值），然后再删除该筛选器，您现在就可以进行排序而不会崩溃。我在应用筛选器并删除它后保存了一个文件，现在那个文件很好用，但是查看“修复”后的文件的XML时，我没有看到任何明显的区别。

是否有人知道可能导致这个问题的原因呢？除了将其添加到工作表中之外，还有其他需要执行的操作吗？

注意：我们使用的是Excel 2010（版本14.0.7153.5000）

这里有一个文件示例（单击下载，它将下载为.zip文件。将其重命名为.xlsx以在Excel中打开。启用编辑，选择其中一列并尝试排序）。

编辑：继续尝试，如果您在Excel中重新保存该文件，则仍然无法正常工作。但是，如果您先应用筛选器（然后清除它），然后再在Excel中重新保存，您将获得一个有效的文件。仔细查看两个文件（仍然无法正常工作的重新保存的文件和现在有效的文件），我确实注意到在应用（并清除）筛选器后添加了这个额外的部分：

  <x:definedNames>
    <x:definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'Sheet 1'!$A$1:$E$11</x:definedName>
  </x:definedNames>

不确定那是否是什么重要的事情...

- Matt Burland

---

@mason：OpenXML让你随时制作无效的Excel文件。因为有效Excel文件的规则比（完全未记录）有效OpenXML文件的规则更严格（例如，标准中没有规定“工作表”下子元素的顺序，但Excel对某些子元素出现的位置非常挑剔）。通常，当您首次打开文件时，Excel会抱怨（并显示非常不实用的错误消息）。但是这个文件可以正常打开，只有在尝试对列进行排序时才会出现问题。 - Matt Burland

你的元素顺序看起来正确（请参见我的autoFilter答案以获取顺序文档）。生产力工具报告的唯一问题是Font上的一个Color元素不正确 - 我有另一个答案提供了关于Color的信息，但我怀疑它对这个问题没有帮助。我会看一下并尝试找出解决方法... - petelids

@petelids：感谢您的查看，也感谢您指出订单实际上是在哪里指定的。我之前没有意识到这一点，这可能会为我节省很多麻烦。 - Matt Burland

@petelids：我修复了颜色问题（再次感谢您提醒我），但正如您所怀疑的那样，这并没有产生任何影响。 - Matt Burland

如果你将“worksheet.AppendChild(filter);”替换为“worksheet.Append(filter);”，你有任何变化吗？ - Rick Burns

Answer 1

好的，似乎魔法公式在于按我所提出的编辑中增加DefinedNames部分：

<x:definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'Sheet 1'!$A$1:$E$11</x:definedName>

显然，_xlmn._FilterDatabase 对于自动筛选（至少对于排序）是必需的。我猜想如果你在筛选时没有它，它会被创建，但如果你在排序时没有它，Excel会崩溃。

因此，你需要填写工作表名称和单元格引用。

浏览Open XML标准，在definedName的第18.2.5节中，我看到这个：

筛选和高级筛选

_xlnm.Criteria：该定义名称指的是包含要用于将高级筛选应用于数据范围的条件值的范围。

_xlnm._FilterDatabase：可以是以下之一

a. 该定义名称指的是已应用高级筛选的范围。这代表了源数据范围，未经过滤。

b. 该定义名称指的是已应用AutoFilter的范围。

因此，似乎你需要为每个具有筛选器的工作表添加一个_xlnm._FilterDatabase（似乎没有办法在单个工作表上拥有多个筛选器）。名称是相同的_xlmn_FilterDatabase，无论你有多少带有筛选器的工作表，因为我猜只有名称和localSheetId的组合需要是唯一的。

因此，最终我有了这样的东西：

var filter = new AutoFilter() { Reference = string.Format("{0}:{1}", topLeftCellReference, bottomRightCellReference ) };
worksheet.AppendChild(filter);

workbookPart.Wookbook.DefinedNames.AppendChild(new DefinedName(string.Format("'{0}'!$A${1}:${2}${3}",
    sheet.Name,
    leftColumnLetter,
    topRowIndex,
    rightColumnLetter,
    bottomRowIndex))
{
    Name = "_xlnm._FilterDatabase",
    LocalSheetId = sheet.SheetId - 1,
    Hidden = true
});

这似乎是在解决 Excel 中的一个错误。排序前，Excel 应该检查名称是否已定义并自动创建它（如果使用筛选而不是排序，则似乎会执行此操作）。

- Matt Burland

---

非常感谢！在尝试增强ExcelBuilder库时，我在使用JavaScript生成Excel时遇到了同样的问题。这个方法解决了我的问题！ - nbeuchat