PHPExcel - reads past last row

Question

I'm somewhat perplexed by the behaviour of PHPExcel with some files I have. The files contain approx. 3000 rows of data, but according to PHPExcel, the last row used is 65535. I tried and cut a few lines from the file and pasted them into a fresh file, to no avail. It doesn't matter whether it is Excel5 or Excel2007 format, always the same result.

Any ideas to find the error here?

CODE:

    $cr=$xls->getActiveSheet()->getHighestRow();
    $cn=PHPExcel_Cell::columnIndexFromString($xls->getActiveSheet()->getHighestColum‌​n()); 
    for ($z=2; $z<=$cr; $z++) { 
        $class=($z%2)?"odd":"even"; 
?> 
    <tr class="<?php echo $class; ?>"> 
    <?php for ($s=0; $s<$cn; $s++) { 
        $tmp=$xls->getActiveSheet()->getCellByColumnAndRow($s,$z)->getValue();?> 
        <td><?php echo $tmp; ?></td> 
    <?php } ?> </tr> 
<?php } ?>

Answer 1

All it takes is a blank cell, and the last col/row can be a lot higher than you expect. Even print layout or styles can falsify the stored highest values. Open the file in MS Excel and press Ctrl-End, and it will take you to the last cell that Excel recognises.

In addition to getHighestRow() and getHighestColum() there's also a (slower) getHighestDataRow() and getHighestDataColumn() method that identify the highest row and column that actually contain cell data.

Answer 2

$objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

or

$objPHPExcel->setActiveSheetIndex(0)->calculateWorksheetDimension();

which returns a range as a string like A1:AC2048

although trailing blank rows and columns are included in these.

or you can use the iterators to loop through the existing rows and columns to get each cell within the worksheets used range. See /Tests/28iterator.php in the production distribution for an example. The iterators can be set to ignore blanks.

Source:

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Answer 3

Mark Baker is right. So I fixed issue at my end by following steps:

1. do ctrl+end from the first blank cell after last row which have data. e.g. so if I have last row of data is A3 to D3. I will do ctrl_end in cell A4

   • This will highlight you the numbers of blank rows

2. right click on selected rows(rows selected by following first step) and select "Delete".

3. it will open popup. select "Entire row" and click ok button

So whatever your code you have to load excel data, it will only reads those rows which have data.