Add Support for CONTAINS and DOESNTCONTAIN Rules in AutoFilter

[xxggabriel]

This is:

- [ ] a bug report
- [x] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

The expected behavior is to have additional flexibility in the AutoFilter functionality by introducing two new filter rules: CONTAINS and DOESNTCONTAIN. These rules should allow users to filter rows based on whether a cell value contains or does not contain a specified substring. This feature will help users perform more refined data analysis directly within PhpSpreadsheet.

What is the current behavior?

Currently, the AutoFilter feature in PhpSpreadsheet does not support direct filtering based on substring matches. Users who need to filter rows that contain or do not contain a particular string have to implement workarounds or use more basic comparison rules.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Sample data
$sheet->setCellValue('A1', 'Header');
$sheet->setCellValue('A2', 'abc');
$sheet->setCellValue('A3', 'def');
$sheet->setCellValue('A4', 'cba');
$sheet->setCellValue('A5', 'text with c');

// Add AutoFilter
$sheet->setAutoFilter('A1:A5');
$autoFilter = $sheet->getAutoFilter();
$columnFilter = $autoFilter->getColumn('A');

// Attempt to set a filter rule to find cells containing 'c'
$columnFilter->setFilterType(\PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER);
$columnFilter->createRule()
    ->setRule(
        \PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_COLUMN_RULE_CONTAINS,
        'c'
    )
    ->setRuleType(\PhpOffice\PhpSpreadsheet\Worksheet\AutoFilter\Column\Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);

// The expected result would be that rows containing 'abc', 'cba', and 'text with c' are shown

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

The feature request is related to the AutoFilter functionality and is not specific to any particular file format.

Which versions of PhpSpreadsheet and PHP are affected?

The current feature request is applicable for all versions of PhpSpreadsheet and PHP where AutoFilter functionality is available.

[oleibman]

Although the Excel GUI shows a Contains option, it appears to be translated internally to a custom filter using equals (for contains) or not equals (for doesn't contain). So, to check for c in your example:

        $columnFilter
            ->setFilterType(
                Column::AUTOFILTER_FILTERTYPE_CUSTOMFILTER
            );
        $columnFilter->createRule()
            ->setRule(
                Rule::AUTOFILTER_COLUMN_RULE_EQUAL,
                '*c*'
            )
            ->setRuleType(Rule::AUTOFILTER_RULETYPE_CUSTOMFILTER);

This isn't really any more complicated than your proposed code, except for the addition of the asterisks at the beginning and end of the string. So I don't think any new code is required for PhpSpreadsheet. It might be worthwhile to add this to the formal documentation. I will think about that.

[oleibman]

The existing documentation appears adequate to me. In particular, it states:

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'".

If you think more is needed there, please let me know or submit a PR. I will close this issue in about a week.