CODEWITHSUNDEEP
phpphpexcel
user765368
user765368

Reputation: 20346

Ignore empty cells PHPExcel

I'm using the library PHPExcel to read data in an Excel file. The problem I'm having, is that when I use something like:

$obj = PHPExcel_IOFactory::load($file);
$data = $obj->getActiveSheet()->toArray(null,true,true,true);

To load my file and convert its content into an array, I get all the columns and rows of my Excel file in my array even those without any data in them. Is there a method or something in the library PHPExcel to tell it to ignore cells in my Excel sheet that do not contain any data? (Instead of having a bunch of empty associative arrays in my $data)

Upvotes: 9

Views: 37340

Answers (3)

HMagdy
HMagdy

Reputation: 3295

I have this solution for my case 

$maxCell = $objWorksheet->getHighestRowAndColumn();
$data = $objWorksheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);

return all rows with all empty string as:

[1] => Array
        (
            [0] => 
            [1] => 
            [2] => 
            [3] => 
            [4] => 
            [5] => 
            [6] => 
            [7] => 
            [8] => 
            [9] => 
            [10] => 
            [11] => 
            [12] => 
            [13] => 
        )

To remove these empty rows 

$data = array_map('array_filter', $data);

will return

[1] => Array ( )

And this is the finale solution:

        $maxCell = $objWorksheet->getHighestRowAndColumn();
        $data = $objWorksheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);
        $data = array_map('array_filter', $data);
        $data = array_filter($data);

will return an array with only filled rows .. hope that help

Upvotes: 11

TiGR
TiGR

Reputation: 375

If your problem is in getting empty columns that go after real data, and you would like to avoid these, you could do something like this:

$maxCell = $sheet->getHighestRowAndColumn();
$data = $sheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);

This will return array representing only the area containing real data.

Upvotes: 15

Mark Baker
Mark Baker

Reputation: 212412

No there isn't. The toArray() method returns the first argument (NULL) to represent an empty cell. You can then apply standard PHP array functions such as array_filter() to eliminate empty cells.

foreach($data as $key => &$row) {
    $row = array_filter($row,
                        function($cell) {
                            return !is_null($cell);
                        }
           );
    if (count($row) == 0) {
        unset($data[$key]);
    }
}
unset ($row);

This will eliminate every cell that is a NULL (empty) value, and every row that comprises nothing but empty cells. It will preserve the array keys, so your array keys will still give you a cell reference.

Note that an cell containing an empty string is not a null cell, so these will be retained, although the array_filter() callback could be modified to remove them as well.

Upvotes: 3

Related Questions