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)
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.
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
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.
