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 } ?>

$cr=$xls->getActiveSheet()->getHighestRow(); $cn=PHPExcel_Cell::columnIndexFromString($xls->getActiveSheet()->getHighestColumn()); for ($z=2; $z<=$cr; $z++) { $class=($z%2)?"odd":"even"; ?> getActiveSheet()->getCellByColumnAndRow($s,$z)->getValue(); ?> — user1465889, Jun 19 '12 at 10:11
You can edit your answer and write code. See: http://stackoverflow.com/editing-help — Somnath Muluk, Jun 19 '12 at 10:33

score 7 · Answer 1 · answered Jun 19 '12 at 12:26

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.

Thanks for the hint; loadingg and savin in OpenOffice helped, too. — user1465889, Jun 20 '12 at 11:08

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

$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?

edited May 23 '17 at 11:52

Community

1
1

answered Jun 19 '12 at 09:14

Somnath Muluk

55,015
38
216
226

I use getHighestRow(), and I get 65535 for some files, and the correct last row for others, without a descernible pattern. – user1465889 Jun 19 '12 at 10:10

score 1 · Answer 3 · edited Sep 20 '17 at 20:50

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

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
right click on selected rows(rows selected by following first step) and select "Delete".
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.

score -1 · Answer 4 · answered Jun 19 '12 at 09:08

-1

This is no error. An "empty" spreadsheet still contains lots of empty cells. Excel usually has 65536 lines and 256 coloumns.

answered Jun 19 '12 at 09:08

Socken Puppe

134
3

2

But then all files should show this behaviour - I have several other files that end where I expect them to end - with the last row into which data was entered. – user1465889 Jun 19 '12 at 10:09

PHPExcel - reads past last row

4 Answers4