Remove empty columns in an excel file using phpexcel library

Question

I am using phpexcel library to read an excel file.It works perfeclty on 99%. But sometimes it reads empty columns also.My code is

try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file');
}   

$worksheet=$objPHPExcel->getActiveSheet();) {
$worksheetTitle     = $worksheet->getTitle();
$highestRow         = $worksheet->getHighestRow();
$highestColumn      = $worksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;

sometimes the $highestcolumn returns 'WVL' even if the data in excel column up to 'C' why?. Also i want to check all the rows under a particular column is empty or not,Is there any easy method to do it instead of iterating all rows using for loop.

hi do you wont get highest column of sheet or highest column with data ? — dyachenko
– dyachenko, Commented Nov 6, 2014 at 11:02

Mark Baker · Accepted Answer · 2014-11-06 11:03:43Z

7

The getHighestRow() and getHighestColumn() methods work on the basis of testing for anything related to a cell, even if that's a style setting or a named range or print settings or a column/row setting such as width/height or hidden.

That's why the getHighestDataRow() and getHighestDataColumn() methods exist. These two methods look at the actual data in cells.

Note: Just because a cell looks empty when you view it in MS Excel, doesn't mean that it actually is empty. NULL is a valid cell value, as is a space character, neither of which is visible.

In answer to your second question: you can pass an optional argument to the getHighestRow(), getHighestColumn() and to the getHighestDataRow() and getHighestDataColumn(), so a row number passed to getHighestColumn() or getHighestDataColumn() will return the highest column in the specified row; and a column letter passed to getHighestRow() or getHighestDataRow() will return the highest row in that column.

e.g.

$highestColumnInRow5 = $worksheet->getHighestColumn(5);

or

$highestDataRowInColumnAA = $worksheet->getHighestDataRow('AA');

answered Nov 6, 2014 at 11:03

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

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

2 Comments

Sherin Over a year ago

is there any way to take all the rows under a particular column into an array ?

Mark Baker Over a year ago

Using the worksheets rangeToArray() method, you can specify a range, e.g. $column = 'C'; $data = $worksheet->rangeToArray($column.'1:'.$column.$worksheet->getHighestDataRow($column));

Collectives™ on Stack Overflow

Remove empty columns in an excel file using phpexcel library

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related