| commit | author | age | ||
| 2207d6 | 1 | # PHPExcel AutoFilter Reference |
| W | 2 | |
| 3 | ||
| 4 | ## Autofilter Expressions | |
| 5 | ||
| 6 | ### Custom filters | |
| 7 | ||
| 8 | In MS Excel, Custom filters allow us to select more complex conditions using an operator as well as a value. Typical examples might be values that fall within a range (e.g. between -20 and +20), or text values with wildcards (e.g. beginning with the letter U). To handle this, they | |
| 9 | ||
| 10 |  | |
| 11 | ||
| 12 |  | |
| 13 | ||
| 14 | Custom filters are limited to 2 rules, and these can be joined using either an AND or an OR. | |
| 15 | ||
| 16 | We start by specifying a Filter type, this time a CUSTOMFILTER. | |
| 17 | ||
| 18 | ```php | |
| 19 | $columnFilter->setFilterType( | |
| 20 | PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER | |
| 21 | ); | |
| 22 | ``` | |
| 23 | ||
| 24 | And then define our rules. | |
| 25 | ||
| 26 | The following shows a simple wildcard filter to show all column entries beginning with the letter 'U'. | |
| 27 | ||
| 28 | ```php | |
| 29 | $columnFilter->createRule() | |
| 30 | ->setRule( | |
| 31 | PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL, | |
| 32 | 'U*' | |
| 33 | ) | |
| 34 | ->setRuleType( | |
| 35 | PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER | |
| 36 | ); | |
| 37 | ``` | |
| 38 | ||
| 39 | MS Excel uses \* as a wildcard to match any number of characters, and ? as a wildcard to match a single character. 'U\*' equates to "begins with a 'U'"; '\*U' equates to "ends with a 'U'"; and '\*U\*' equates to "contains a 'U'" | |
| 40 | ||
| 41 | If you want to match explicitly against a \* or a ? character, you can escape it with a tilde (~), so ?~\*\* would explicitly match for a \* character as the second character in the cell value, followed by any number of other characters. The only other character that needs escaping is the ~ itself. | |
| 42 | ||
| 43 | To create a "between" condition, we need to define two rules: | |
| 44 | ||
| 45 | ```php | |
| 46 | $columnFilter->createRule() | |
| 47 | ->setRule( | |
| 48 | PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, | |
| 49 | -20 | |
| 50 | ) | |
| 51 | ->setRuleType( | |
| 52 | PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER | |
| 53 | ); | |
| 54 | $columnFilter->createRule() | |
| 55 | ->setRule( | |
| 56 | PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL, | |
| 57 | 20 | |
| 58 | ) | |
| 59 | ->setRuleType( | |
| 60 | PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER | |
| 61 | ); | |
| 62 | ``` | |
| 63 | ||
| 64 | We also set the rule type to CUSTOMFILTER. | |
| 65 | ||
| 66 | This defined two rules, filtering numbers that are >= -20 OR <= 20, so we also need to modify the join condition to reflect AND rather than OR. | |
| 67 | ||
| 68 | ```php | |
| 69 | $columnFilter->setAndOr( | |
| 70 | PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_ANDOR_AND | |
| 71 | ); | |
| 72 | ``` | |
| 73 | ||
| 74 | The valid set of operators for Custom Filters are defined in the PHPExcel_Worksheet_AutoFilter_Column_Rule class, and comprise: | |
| 75 | ||
| 76 | Operator Constant | Value | | |
| 77 | ------------------------------------------|----------------------| | |
| 78 | AUTOFILTER_COLUMN_RULE_EQUAL | 'equal' | | |
| 79 | AUTOFILTER_COLUMN_RULE_NOTEQUAL | 'notEqual' | | |
| 80 | AUTOFILTER_COLUMN_RULE_GREATERTHAN | 'greaterThan' | | |
| 81 | AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL | 'greaterThanOrEqual' | | |
| 82 | AUTOFILTER_COLUMN_RULE_LESSTHAN | 'lessThan' | | |
| 83 | AUTOFILTER_COLUMN_RULE_LESSTHANOREQUAL | 'lessThanOrEqual' | | |
| 84 | ||
