Reading empty cells as data cells

Question

I am reading an Excel file where sometimes PHPExcel codeplex considers an empty cell as a value cell. For example:

If my Excel has 128 Columns, the maximum is DV sometime it will vary depend upon Excel and users requirement. Maximum is 126 columns. Minimum is 8 columns. And also maximum row is 250.

----------------------
A | B | ..... |DU |DV|
----------------------

I used following code to load Excel:

   set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
   include 'PHPExcel/IOFactory.php';    

   $inputFileName = $target_file; 
    try {
            $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
    } catch(Exception $e) {
        die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
    }

Now the problem is PHPExcel is showing the wrong maximum column and rows, maximum column is 126 (DV) and maximum row is 245.

But when I used this following code am getting wrong values:

 $lastColumn =  $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
 $lastRow    =  $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();

instead of 126 I am getting 1800+ (AMK) as maximum column and maximum row it showing as 65536.

How to fix this issue?

Even clearing and creating a new Excel this sometimes happens.

How to set maximum column and rows before reading (loading) Excel or how to avoid empty cells?

score 1 · Accepted Answer · answered Nov 05 '15 at 10:59

1

Rows and columns are counted by getHighestColumn() and getHighestRow() if they contain anything (including style or print information) rather than simply having content. These values are also calculated when the spreadsheet is loaded, so may not be accurate if you subsequently add new rows or columns to the worksheet.

Instead, use the getHighestDataColumn() and getHighestDataRow() methods to return the highest row and column that actually contain data values in cells. While less efficient, they actually calculate the highest cell reference when called, which is slower, but always accurate.

Note also that a cell which may appear empty to you may still contain a null or an empty string. This is still a data value.

answered Nov 05 '15 at 10:59

Mark Baker

209,507
32
346
385

Thankyou so much @Mark now i can get MaximumRows only data contain cell , but column is still showing wrong error. Right now i made the maximum column DW in static. Please is anyway to get maximum column – Jonathan John Nov 05 '15 at 11:36
As I said, `Note also that a cell which may appear empty to you may still contain a null or an empty string. This is still a data value.` You may need to write some logic of your own to identify cells that contain visible data – Mark Baker Nov 05 '15 at 11:46

Reading empty cells as data cells

1 Answers1