Implement autofilter expressions

[Progi1984]

As discussed with @MarkBaker on Twitter, I think it's interesting to implement in PHPExcel autofilter expressions.

If expressions are defined, autofilter is enabled, and some rows are hidden.

Twitter :

Progi1984 : Is that some commands in #phpexcel for adding expressions in autofilter ?

Mark_Baker : There's nothing for adding the actual autofilter selections, just for defining the range they can be applied to
Mark_Baker : I know how to add it to Excel2007, but not Excel5... but autofilter also manipulates row show/hide state

[Progi1984]

I think... First step, integration in PHPExcel core... and after Excel2007 for @MarkBaker & Excel5 for me...

What do you think about that ?

[MarkBaker]

Basic rules for autofilters:
Each worksheet can only have a single autofilter... at a minimum defined as the cell range to which the autofilter options can be applied... this is what we do at the moment. In Excel2007, this is the ref attribute of the worksheets autoFilter element.
When filters are actually applied, then each column in the range can have one or more custom filters. These are identified by customFilter elements in filterColumn elements.Multiple filter criteria can be combined using AND or OR, and (depending on the datatype) they can include ? or * wildcards, and can have an operator such as greaterThanOrEqual.
If a filter is applied, then all rows in the range that match the criteria are visible, all other rows are hidden... matching this logic in PHPExcel adds a level of complexity when adding or changing filter criteria.

The other major element of autofilters is sorting. It appears that autoFilter sorting actually change the content of the rows, so I'd recommend that we don't implement this.

<autoFilter ref="D1:E10">
    <filterColumn colId="0">
        <customFilters>
            <customFilter val="A*"/>
            <customFilter val="*E"/>
        </customFilters>
    </filterColumn>
    <filterColumn colId="1">
        <customFilters>
            <customFilter val="2" operator="greaterThanOrEqual"/>
        </customFilters>
    </filterColumn>
</autoFilter>

My inclination is to replace the worksheet's autoFilter attribute (defining the range) with an PHPExcel_AutoFilter object comprising a range attribute and an array of PHPExcel_AutoFilter_Column objects, the PHPExcel_AutoFilter_Column objects comprising the array of rules (and the operator constants).

[MarkBaker]

It should be fairly easy to create the appropriate autofiltering classes, and to modify the Excel2007 Reader and Writer to handle this... the biggest problems I see are providing the appropriate methods to add/remove/modify the filter criteria, validating the filtering rules and then resetting all the row hidden attributes as appropriate.

[MarkBaker]

Note - Can't find any autofiltering in the Excel5 Reader other than in the sheet protection record, looks like it isn't implemented in the reader

[MarkBaker]

The refactoring committed for core 15 minutes ago should be completely transparent to any existing user code that accesses the autofilter range for backward compatibility

[Progi1984]

@MarkBaker, I see your branche "autofilter"...

I open a bug for "autofiltering in the Excel5 Reader". I will work on this when i have some time.

What does it lack ? Expressions filters ?

[MarkBaker]

The changes committed to the autofilter branch so far simply replicate existing functionality... except using an autofilter class instead of a simple string. Using it should be absolutely identical to the existing 1.7.7 code (for backward compatibility). I'm now working on the additions to that class for the actual expression filters, but haven't committed any of that to the branch yet

[MarkBaker]

OK, so not quite as straightforward as I'd hoped - there's a couple of more complex types for date selections that I still need to make allowance for, but I've got the guts of reading, manipulating and writing now complete. Still need to do some fine tuning; and then I'll need to manage the data testing and hiding/unhiding or rows.
I'm also going to adjust the autoCalculate column width while I'm about it to allow for teh width of the autofilter dropdown icon... but on schedule still to have the basics for Excel2007 working by next weekend, and on target for a 1.7.8 release.

[MarkBaker]

That's all my code for AutoFilter expressions (core, for Excel2007 Reader and Excel2007 Writer) merged into the develop branch. This provides support for simple/standard filters, dategroup filters, custom filters, dynamic filters and top10 filters. It does not support colorFilters (the Rich Text rules are a real PITA), iconFilters or external filter lists - though I suspect these are all relatively rare anyway). Documentation is included, with a new AutoFilter Reference document.

The only thing still outstanding is handling the actual hide/unhide of rows appropriate to the actual filter rules. That will take a little more work to implement.... as a nice bonus though, a lot of the filtering code should be relevant when I finally start to implement pivot tables.

[MarkBaker]

Autofilter Expressions implemented in core, and for Excel2007 Reader and Writer in Production release 1.7.8