PHPExcel ReadFilter Class

Question

I am attempting to use a ReadFilter with PHPExcel to only read in certain columns.

I am using the example from their documentation:

class columnFilter implements PHPExcel_Reader_IReadFilter
{
    private $_startRow = 0;
    private $_endRow = 0;
    private $_columns = array();

    public function __construct($startRow, $endRow, $columns) {
        $this->_startRow    = $startRow;
        $this->_endRow      = $endRow;
        $this->_columns     = $columns;
    }

    public function readCell($column, $row, $worksheetName = '') {
        if ($row >= $this->_startRow && $row <= $this->_endRow) {
            if (in_array($column,$this->_columns)) {
                return true;
            }
        }
        return false;
    }
}

I am using an array called $importColsFilter which is shown below. I am passing this as the column for the ReadFilter.

$importColsFilter  

Array (  
    [0] => A  
    [1] => B  
    [2] => C  
    [3] => H  
    [4] => I
    [5] => J
)  

$filterColumns = new columnFilter(2,8,$importColsFilter);
$objReader->setReadFilter($filterColumns);  
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

When I grab the entire sheet ($sheetData), I was expecting to only get Columns A, B, C, H, I, and J. However, I am getting columns A through J as a range and anything in between that as seen with the array dump below.

$sheetData

Array (  
    [A] => CellValue1  
    [B] => CellValue2
    [C] => CellValue3
    [D] => 
    [E] => 
    [F] => 
    [G] => 
    [H] => CellValue4
    [I] => CellValue5
    [J] => CellValue6
)

The above looks to be the behavior of passing a range array to the filter instead of a normal array like so: new columnFilter(2,8,range('A','J')) It appears the filter works for grabbing the data, but it's dumping the columns into the array anyway with NULL value. Is this the expected behavior? Can I just return the columns I want?

I was looking for a clean solution using the PHPExcel class. If there is no way there, I can just remove the array entries that are returned as NULL and dump it into another array. An example below:

foreach(array_Keys($sheetData) as $sheetDataKey) {
    foreach($sheetData[$sheetDataKey] as $key => $value) {
        if(array_key_exists($key, $sheetData[$sheetDataKey]) && is_null($sheetData[$sheetDataKey][$key])) {
            unset($sheetData[$sheetDataKey][$key]);
        }
    }
}

The portion in the class that is checking for the column to be read is in the array passed to the function, that it would only return that column. I wasn't expecting to see columns returned with a NULL value. if (in_array($column,$this->_columns))

Is there anyway to do this with straight PHPExcel, or should I just pop the array entries that are NULL?

score 1 · Answer 1 · answered Apr 15 '15 at 07:16

Just because you're telling PHPExcel to leave a gap in the columns that it reads, doesn't mean that PHPExcel will shuffle all subsequent columns up to fill that gap.... it won't. Using the read filter, you're telling the Reader not to read any data from columns D, E, F and G, and it doesn't.... but it won't move column H over to column D, I over to E, etc. either.

Columns D, E, F and G can't "not exist", but when you use that read filter it means they won't contain any data, that the cells will be empty and not take up any memory.

If you want to shuffle up H to D, I to E, etc then you need to delete those columns completely using the worksheet's removeColumn() method.

If you want to remove them from a row in $sheetData, then the easiest is to walk the rows using array_intersect_key() to eliminate those columns

Thanks for the help. Both answers answered my question of how PHPExcel really works when grabbing the data and now makes sense. I do have a few options now in dealing with the data and/or removing the columns. I've tried both of the methods listed and they work perfectly. — hiddenicon, Apr 17 '15 at 00:17

Rene Korss · Accepted Answer · 2015-04-15T07:40:19.527

I made PHPExcelFormatter if I needed to get only certain columns from excel file. Unfortunately it dosen't support $startRow and $endRow, but it should be quite easy to add it.

It's basically like your columnFilter. It outputs array with your own keys.

It takes array as in demo $formatterColumns. For example, to skip D-G.

<?php

// Load file
$formatter = new PHPExcelFormatter('example1.xls');

// Output columns array (document dosen't have column names on first row)
$formatterColumns = array(
    0 => 'A',
    1 => 'B',
    2 => 'C',
    7 => 'H',
    8 => 'I',
    9 => 'J'
) 

// Set our columns
$formatter->setFormatterColumns($formatterColumns);

// Output as array
$output = $formatter->output('a');

// Print array
echo '<pre>'.print_r($output, true).'</pre>';

?>

Output would be:

Array (  
    [A] => CellValue1  
    [B] => CellValue2
    [C] => CellValue3
    [H] => CellValue4
    [I] => CellValue5
    [J] => CellValue6
)

PHPExcel ReadFilter Class

2 Answers2