How to apply a rule for an AutoFilter?

Topics: Developer Forum, User Forum
Apr 22, 2013 at 9:30 AM

I'm using PHPExel to read an xlsx excel file. In this file there is already simple filters. I want to know how i can do to apply the rule that i have programmed.

here an extract of my program
$objReader = new PHPExcel_Reader_Excel2007();
            $objReader->setLoadSheetsOnly( array("stock") );
            $objPHPExcel = $objReader->load("../Excel/blabla.xlsx");
            $objPHPExcel = $objPHPExcel->getActiveSheet();
            $filtre = $objPHPExcel -> getAutoFilter();
            $filtreCol = $filtre -> getColumn('B');
            $filtreCol -> setFilterType(PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER);
            $filtreCol -> createRule() -> setRule(PHPExcel_Worksheet_Autofilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL,$numSerie);
            echo '<table>' . "\n";
                        // view the result with the filter applied
            foreach ($objPHPExcel->getRowIterator() as $row) 
It display the excel file entirely and i want it to display the lines which match with my filter

PS : sorry for my bad english.
Apr 22, 2013 at 3:12 PM
PHPExcel only applies autofilter expressions on save, and this sets the relevant rows to hidden/visible to match the filter expression. You'll need to do the equivalent call from within your code, calling the AutoFilters showHideRows() method, then testing row visibility when you loop through each row using the iterator
Apr 22, 2013 at 5:15 PM
Thank you for your quick answer. I don't really understand what showHideRows() do to the line . I just call this method like this : $objPHPExcel -> showHieRows() at every iteration and then I test the visibility ? What is the method to test the row visibility?

Really thank you for your help
Apr 23, 2013 at 7:47 AM
Clem91: You call only once the showHideRows method before the loop ($filtre->showHideRows()).
This "visually" applies the filter to the data.
In the loop, you test the visibility by $objPHPExcel->getRowDimension ($row->getRowIndex())->getVisible()
This return a boolean.

Mark: Maybe could you add a few lines on this topic in the documentation of the AutoFilter or following point 4.6.25 in the developer documentation ?
Apr 23, 2013 at 8:35 AM
It Worked !! Thank you very much. I think add few lines in the documentation, like LWol said, could be useful for others persons in my case I searched during hours for this in the doumentation and I have found nothing.

Thank you both !
Apr 29, 2013 at 4:25 PM
Added an example to the /Examples fo;der, and updated the Autofilter documentation with:

5. Executing an AutoFilter

When an autofilter is applied in MS Excel, it sets the row hidden/visible flags for each row of the autofilter area based on the selected criteria, so that only those rows that match the filter criteria are displayed.
PHPExcel will not execute the equivalent function automatically when you set or change a filter expression, but only when the file is saved.

5.1. Applying the Filter

If you wish to execute your filter from within a script, you need to do this manually. You can do this using the autofilters showHideRows() method.
$autoFilter = $objPHPExcel->getActiveSheet()->getAutoFilter();
This will set all rows that match the filter criteria to visible, while hiding all other rows within the autofilter area.

5.2. Displaying Filtered Rows

Simply looping through the rows in an autofilter area will still access ever row, whether it matches the filter criteria or not. To selectively access only the filtered rows, you need to test each row’s visibility settings.
foreach ($objPHPExcel->getActiveSheet()->getRowIterator() as $row) {
    if ($objPHPExcel->getActiveSheet()->getRowDimension($row->getRowIndex())->getVisible()) {
        echo '    Row number - ' , $row->getRowIndex() , ' ';
        echo $objPHPExcel->getActiveSheet()->getCell(
        )->getValue(), ' ';
        echo $objPHPExcel->getActiveSheet()->getCell(
        )->getFormattedValue(), ' ';
        echo EOL;
6. AutoFilter Sorting

In MS Excel, Autofiltering also allows the rows to be sorted. This feature is not supported by PHPExcel.
Apr 30, 2013 at 12:37 PM
Clear and precise.
Thank you, Mark