Reading fails at empty cell and dies

Jan 15, 2009 at 8:53 PM
Hey guys,

Great job with the classes, they work very well.  I ran into an odd problem though.  I'm looping through the excel file trying to output it into the browser and whenever the loop gets to a cell which is empty in the excel file it just dies.  So for example, suppose $lastRow is 20, $lastCol is 'D'.  If everything has data except for B10, it will work up until B9 and then completely die without an error or anything.  Is there a way around this?  It seems that even when I call it outside the loop if the cell is empty it will call die() and not execute anything else.

Here's the code I'm using:
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");
$worksheet=$objPHPExcel->getActiveSheet();
?>
<table>
<?php
$lastRow = $worksheet->getHighestRow();
$lastCol = $worksheet->getHighestColumn();
$cells = $worksheet->getCellCollection();
for ($row = 1; $row <= $lastRow; $row++){
?>
<tr>
<?php for ($col = 'A'; $col <= $lastCol; $col++){ ?>
<td><?php echo $cells[$col . $row]->getValue(); ?></td>
<?php } ?>
</tr>
<?php
}

?>
</table>
Jan 15, 2009 at 9:21 PM
Found a way around it, I guess that code was using the class incorrectly.  The code below should work. 

Also, I know you guys are still working hard at this but in the documentation you should include similar code to the one below so users can know how to use the class.  All the reading example does is load the information into memory but it does not show how to get the actual data.  It took me a couple hours to get it to work.. maybe im just slow though :)  Anyway, thanks again, and keep up the good work.

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("test.xlsx");
$worksheet=$objPHPExcel->getActiveSheet();
?>
<table>
<?php
$lastRow = $worksheet->getHighestRow();
$lastCol = $worksheet->getHighestColumn();

for ($row = 1; $row <= $lastRow; $row++){
?>
<tr>
<?php for ($col = 'A'; $col <= $lastCol; $col++){ ?>
<td><?php echo $worksheet->getCell($col . $row)->getValue(); ?></td>
<?php } ?>
</tr>
<?php
}

?>
</table>
Developer
Jan 15, 2009 at 11:10 PM
I have put forward a suggestion for an update of the documentation:
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=8931

Please note that your example will not work if your sheet contains cells above column 'Z'. Problem is this:

if ('I' < 'AJ') {
    echo 'I is less than AJ';
} else {
    echo 'I is not less than AJ';
}

In this above example PHP will say that 'I' is greater than 'AJ' because it sorts strings like in a dictionary which is not what we want in this case. Go to the link and look at the example how to properly loop through the cells in the sheet.

Thanks for your feedback!

Coordinator
Jan 16, 2009 at 7:34 AM
You may also be pleased to see that there is already a work item to introduce row and column iterators to simplify the task of looping through and series of rows and/or columns
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=8770