I'm "manually" creating Excel files in raw XML using Packaging. Part of the ECMA standard is AutoFilter, which I'm trying to get working. I have inserted what appears to be the correct XML, and when the book opens in excel I can see that the filter is "noticed", but the non-matching lines are not hidden.
For example, my test book has a single worksheet with 4x10 values in it. I have turned on filtering with the following XML in the sheet1.xml:
<autoFilter ref="A1:D10">
<filterColumn colId="1">
<filters>
<filter val="1"/>
</filters>
</filterColumn>
</autoFilter>
I have also added this to the top of the sheet:
<sheetPr filterMode="1">
And this to the workbook.xml:
<definedName name="_xlnm._FilterDatabase" hidden="1" localSheetId="0">Sheet1!$A$1:$D$10</definedName>
As far as I can tell, this is all that Excel adds when I make a filter using the UI, and the data and formatting appears identical when I compare the XML. And yes, Excel does use the absolute format on the reference in the workbook, which was a surprise.
When I open the file I made in excel the rows that do not match "1" are hidden, but when I open my file they are still visible - although the filter widget at the top of the column DOES have a dot indicating it is filtering on that column and when you open it the correct value, "1", is selected.
It's just not hiding the rows.
Is there some other bit I am missing?