Добавление автофильтра и сортировка приводит к сбою Excel

Я разрабатываю приложение, в котором вы можете экспортировать некоторые данные в файл Excel с помощью OpenXML. Все работает отлично, за исключением автофильтра. Идея состоит в том, чтобы добавить автофильтр в основную часть данных, чтобы пользователь автоматически имел элементы управления для фильтрации и сортировки данных. Поэтому в коде я делаю что-то вроде этого:

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>

Не уверен, что это может быть что-то или нет...

Ответы

Ответ 1

Хорошо, поэтому кажется, что волшебная формула состоит в том, чтобы добавить часть DefinedNames, как я предложил в своем редактировании:

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

По-видимому, для работы автофильтра требуется _xlmn._FilterDatabase (по крайней мере, для сортировки). Я думаю, если это не так, когда вы фильтруете, он создается, но если он не появляется, когда вы сортируете, он взрывает Excel.

Поэтому вам нужно имя листа и ссылку на ячейку, чтобы заполнить его.

Просматривая стандарт Open XML, в разделе 18.2.5 из definedName, я вижу следующее:

Фильтр и расширенный фильтр

_xlnm.Criteria: это определенное имя относится к диапазону, содержащему значения критериев для использования при применении расширенного фильтра к диапазону данных.

_xlnm._FilterDatabase: может быть одним из следующих

а. это определенное имя относится к диапазону, к которому был добавлен усовершенствованный фильтр применяется. Это представляет собой диапазон исходных данных, нефильтрованный.

б. Это определенное имя относится к диапазону, в который был установлен 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 должен проверить, определено ли имя перед сортировкой и автоматически создать его (что, по-видимому, выполняется, если вы фильтруете, а не сортируете).