Adding autofilter and sorting causes Excel to crash

Question

I'm developing an application where you can export some data to an Excel file using OpenXML. Everything is working fine except with the autofilter. The idea is to add an autofilter to the main body of the data so that the user automatically has controls to filter and sort the data. So in code, I do something like this:

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

In the exported XLSX it appears something like this:

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

And it's added to the worksheet between sheetData and mergeCells.

I can then open this filter in Excel and it works fine. Expect if you try to sort a column, the column sorts and then Excel crashes. Saving and reloading the file (which forces Excel to clean everything up) doesn't fix the problem. But, if you apply a filter first (say filter a column to > 10, then remove that filter, you can now sort without crashing. I saved a file after applying a filter and removing it and now that file is fine, but looking at the XML of the "repaired" file, I don't see any obvious difference.

Does anybody have any idea what might cause the problem? Is there anything else I'm supposed to do when applying an auto filter other than just adding it to the worksheet?

Note: We are using Excel 2010 (version 14.0.7153.5000)

Here's an example file (click download and it'll download as a .zip. Rename to .xlsx to open in Excel. Enable editing, select one of the columns and try to sort).

Edit: playing around with this some more. If you resave the file in Excel, it's still broken. However, if you first apply a filter (and then clear it) and then resave in Excel, you get a working file. Looking closer at the two files (the still broken resaved file and the now working file), I do notice this extra bit added to the workbook after the filter was applied (and cleared):

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

Not sure if that might be something or not...

Answer 1

Ok, so it seems the magic formula here is to add the DefinedNames part as I suggested in my edit:

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

Apparently the _xlmn._FilterDatabase is needed for the autofilter to work (at least for sorting). I guess if it's not there when you filter, it gets created, but if it's not there when you sort, it blows up Excel.

So you need the sheetname and the cell reference to fill it in.

Looking through the Open XML standard, in section 18.2.5 of definedName, I see this:

Filter & Advanced Filter

_xlnm .Criteria: this defined name refers to a range containing the criteria values to be used in applying an advanced filter to a range of data.

_xlnm ._FilterDatabase: can be one of the following

a. this defined name refers to a range to which an advanced filter has been applied. This represents the source data range, unfiltered.

b. This defined name refers to a range to which an AutoFilter has been applied.

So it appears that you need to add a _xlnm._FilterDatabase for every sheet that has a filter (it appears there is no way to have more than one filter on a single sheet). The name is the same _xlmn_FilterDatabase regardless of how many sheets you have with filters because I guess only the combination of name and localSheetId need to be unique.

So in the end, I have something like this:

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

This does seem like it's working around a bug in Excel. Excel ought to check if the name is defined before sorting and create it automatically if needed (which it appears to do if you filter rather than sort).