CellExists returning true on nonexistent cell?

Topics: User Forum
Oct 7, 2009 at 5:54 PM
Edited Oct 7, 2009 at 5:57 PM

Or at least it sure seems that way.  I have a little loop that just iterates down x columns, and sets a max row number so I can use those later to create other tables. 

 

  $tmpCol = $tableIndices['x1'];
  $maxRow = $tableIndices['y1'];
  while ($objWorksheet->cellExistsByColumnAndRow($tmpCol, $tableIndices['y1'])) {
    $tmpRow = $tableIndices['y1'];
    while ($objWorksheet->cellExistsByColumnAndRow($tmpCol, $tmpRow)) {
      if ($tmpRow > $maxRow) {
        $maxRow = $tmpRow;
      }
      $tmpRow++;
    }
    $tmpCol++;
  }
  $tableIndices['x2'] = $tmpCol - 1;
  $tableIndices['y2'] = $maxRow;

On the first column, it stops correctly at the last row from my worksheet, 153.  But on the second column, row 154 passes cellExistsByColumnAndRow.  I printed the values of the cells to see what was happening, but it just looks like an empty cell is passing:

...
col: 1 row: 152 max 153 val 10.244.48.129 255.255.255.0
col: 1 row: 153 max 153 val 10.244.48.130 255.255.255.0
col: 1 row: 154 max 154 val
col: 2 row: 16 max 154 val NAT IP Address (if applicable) ...

Any thoughts?

Side note: I was looking in the source to see what cellExists does, and saw this in Worksheet.php:1027 :

        if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
            (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches))) {

 

does that strike anyone else as, um.. odd?

 

Edit:
PHP v5.2.6, PHPExcel v1.7.0

Coordinator
Oct 7, 2009 at 7:10 PM

Not sure about your problem with cellExists, I'd have to run some tests on that

However

if ((!preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches)) &&
            (preg_match('/^'.PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF.'$/i', $pCoordinate, $matches))) {

has already been fixed in the SubVersion code.

 

Coordinator
Oct 7, 2009 at 7:56 PM
Edited Oct 7, 2009 at 7:57 PM

PS

Check out the following methods

$objWorksheet->getHighestColumn() and $objWorksheet->getHighestRow()

 

Oct 7, 2009 at 8:45 PM

Wow. I don't know how I missed those.

However, it looks like its still returning the same result.  From my snippet above, I just changed it to:

 

  $tableIndices['x2'] = $objWorksheet->getHighestColumn();
  $tableIndices['y2'] = $objWorksheet->getHighestRow();

The row value was still set to 1 more than there is on the worksheet.  What does this say about the worksheet?  This sounds like its something to do with how the worksheet was populated (I'm not making the spreadsheets I'm trying to parse).

 

Developer
Oct 8, 2009 at 9:56 AM

If the cell has been styled, for example a background color has been set, or font is set to bold, then it is enough to make the cell "exist" even if it has no value in it.

You can try to use $objReader->setReadDataOnly(true) if you want to remove styles.