'getHighestRow' without taking into account formatting

Question

Using PHPExcel, I have an issue with the getHighestRow() function. If the spreadsheet is formatted beyond the last data entry, highest row does not reflect data entry but cells modification including formatting.

How can I have the number of useful rows, meaning only those where there is non empty data?

So far I using the following code as a substitute but I'm not happy with loading the entire first column:

    $file = $fileForm->get('file')->getData();
    $path = $file->getRealPath();
    $phpExcel = $this->get('phpexcel')->createPHPExcelObject($path);
    $worksheet = $phpExcel->getSheet();
    $firstColumn = $worksheet->rangeToArray("A1:A" . $worksheet->getHighestRow(), null, false, false);
    $firstColumn = array_map(function(Array $lines) {
        return array_values($lines)[0];
    }, $firstColumn);
    $highestRow = count(array_filter($firstColumn));
    unset($firstColumn);

score 0 · Answer 1 · answered Nov 02 '15 at 20:48

0

That's why there's also a

$worksheet->getHighestDataRow()

method

answered Nov 02 '15 at 20:48

Mark Baker

209,507
32
346
385

hmmm, I would have loved it to work but it doesn't. If I have a worksheet that contains formating beyond the last row that contains data, it stills gets beyond that last row containing data :-( – Sébastien Nov 02 '15 at 23:00
I doubt that very much.... it might get past the last row containing ___visible___ data, but an Excel cell might contain a null or an empty string – Mark Baker Nov 02 '15 at 23:01
I have just changed my code by $highestRow = $worksheet->getHighestDataRow() and I get a value of 1000 against 284 for mine. On line 1000 of my file, there is no formating anymore. And from line 285 to 999 there are no data but a formating – Sébastien Nov 02 '15 at 23:04
Yet there is almost certainly something in a cell in row 1000, else PHPExcel wouldn't report that there was..... just because you can't see anything in that row, doesn't mean that the cells are empty – Mark Baker Nov 02 '15 at 23:06
The getHighestDataRow() method looks at the ___actual___ cells that have been loaded from the MS Excel file – Mark Baker Nov 02 '15 at 23:09
well, I'm not saying I've got the truth but I'm not tweaking nothing here. My code only relies on the 4th argument of the rangeToArray method that removes formating. and it works. – Sébastien Nov 02 '15 at 23:38
I never said you were tweaking anything: I've told you how PHPExcel works, and you can always confirm that for yourself by reading the source code. But PHPExcel doesn't provide any function that will filter cells containing null or empty string values.... if you want to do that, then you'll need to write it yourself – Mark Baker Nov 02 '15 at 23:42
Why I got "undefined method" when I trying using `getHighestDataRow()`? It fine when using `getHighestRow()` but it didn't return the highest row that contain data, it return the highest row that ever contain data. – Blackjack Feb 22 '17 at 11:00
@Blackjack - what version of PHPExcel are you running? `getHighestDataRow()` was introduced in version 1.7.7, nearly 5 years ago – Mark Baker Feb 22 '17 at 11:34
I'm using PHP 5.4, but it's find when I use getHighestDataColumn(). So what method can I use to give me the highest row that contain data? – Blackjack Feb 22 '17 at 19:58
What version of PHPExcel, not of PHP.... I can't give you any other method if you're running PHPExcel less that 1.7.7; because that's the point when we introduced `getHighestDataRow()`... if your'e running a higher version than that, and it can't find the method, then you have much more serious problems – Mark Baker Feb 22 '17 at 20:20

score 0 · Answer 2 · answered Nov 30 '20 at 10:00

  $spreadsheet = IOFactory::load($file_path);
  $sheet = $spreadsheet->getActiveSheet();
  $row_index = $sheet->getHighestDataRow();
  // Avoid to rewrite the first line.
  // We need to add the checking because when we hav an empty file
  // the $row_index will be 1. It happened beause the Exel index starts
  // from 1. It can't be less than 1.
  // So when there 0 lines we have row_index=1
  // and whne we have 1 line we also have row_index=1.
  if ($row_index == 1) {
    $cell_value = $sheet->getCell("A$row_index")->getValue();
    if ($cell_value !== NULL && $cell_value != '') {
      $row_index++;
    }
  }

'getHighestRow' without taking into account formatting

2 Answers2