How to read empty cells in PHPExcel without skipping values?

Question

I need your help to solve one problem.

I tried to find answer, but I found none. I'm using PHPExcel 1.8.0 to read data from excel-file. And I'm using ReadFilter to read specific columns.

Main PHP Code.

        $price = UPLOAD_DIR . $price;

        $file_type = PHPExcel_IOFactory::identify($price);

        $filter = new ShipperReadFilter();

        $filter->getShipperSettings($shipper);          

        $reader = PHPExcel_IOFactory::createReader($file_type)->setReadDataOnly(true)->setReadFilter($filter)->load($price);

        $worksheet = $reader->getActiveSheet();

        $col_number = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestDataColumn());

        $data = array();

        foreach ($worksheet->getRowIterator($filter->getStartRow()) as $row) {

            $cellIterator = $row->getCellIterator();

            $item = array();

            foreach ($cellIterator as $cell) {
                // May be PROBLEM is Here?
                array_push($item, $cell->getCalculatedValue());
            }

            array_push($data, $item);
        }           

        var_dump($data);

ReadFilter code.

class ShipperReadFilter implements PHPExcel_Reader_IReadFilter {

public $valid_columns = array();

public $start_row;

public function setValidColumns ($columns) {
    $this->valid_columns = $columns;
}

public function getShipperSettings ($shipper) {

    switch ($shipper) {

        // Shipper 1
        case 1 : 
            $this->setValidColumns(array('A', 'D', 'F'));
            $this->start_row = 4;
            break;

        // Shipper 2
        case 2 :
            $this->setValidColumns(array('A', 'R', 'T'));
            $this->start_row = 7;
            break;

        // Shipper 3
        case 3 :
            $this->setValidColumns(array('H', 'I', 'J'));
            $this->start_row = 14;
            break;
    }
}

public function getStartRow () {
    return $this->start_row;
}

public function readCell($column, $row, $worksheetName = '') {

    if (in_array($column, $this->valid_columns)) {
        return true;
    }
    return false;
}   
}

Result

array(3501) { 
   [0]=> array(2) { 
       [0]=> string(11) "01-00018239" 
       [1]=> float(2493) 
}

But I need this

array(3501) { 
[0]=> array(3) { 
    [0]=> string(11) "01-00018239" 
    [1]=> float(2493) 
    [2]=> null // or '', or 0
}

Is there the right way to solve this? Thank you.

Mark Baker · Accepted Answer · 2015-03-13 09:53:56Z

2

By default, the cell iterator will only iterate over cells that actually exist

$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);

$item = array();
foreach ($cellIterator as $cell) {
    array_push($item, $cell->getCalculatedValue());
}

However, if all you're doing is building an array of calculated values for each row, then you might find that using the worksheet's rangeToArray() method is more efficient.

EDIT

To get only cells matching your read filter column list:

foreach ($cellIterator as $cell) {
    $item[$cell->getColumn()] = $cell->getCalculatedValue();
}
$item = array_intersect_key($item, array_flip($filter->valid_columns));

or

foreach ($cellIterator as $cell) {
    if(in_array($cell->getColumn(), $filter->valid_columns)) {
        array_push($item, $cell->getCalculatedValue());
    }
}

edited Mar 13, 2015 at 9:53

answered Mar 13, 2015 at 9:26

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user4666390 Over a year ago

Thank you, Mark. This method works, but now ReadFilter doesn't work - reads all columns, but I want to read only 3 columns... Can you help?

Mark Baker Over a year ago

The read filter doesn't read the cells for those columns that fall outside of your defined set..... if you try to get a value for a column that wasn't read, you'll get a NULL because the cell doesn't exist.... but the cell iterator has no idea about whether a column should exist because of a filter or not.... it will either iterate only the cells that exist, or iterate all cells

Mark Baker Over a year ago

If you build your array of cells as an associative array, with the column id as the index; then do an array_intersect_keys with the column list for your shipper, then you'll have only those columns in the array

Collectives™ on Stack Overflow

How to read empty cells in PHPExcel without skipping values?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related