CODEWITHSUNDEEP
phpphpexcel
Ryan_W4588
Ryan_W4588

Reputation: 668

PHPExcel - Finding First Column With blank cell

Trying to locate the first blank cell in a column. The idea is to pick a column that I know has to have a value (in this case, JOB_NUMBER) and scan through it until a blank cell is found. The code below, in my mind, should do that. However, it never stops. I imagine it is stuck in the while loop, but I don't understand why.

Code:

<?php 
require('./Classes/PHPExcel/IOFactory.php');

ini_set('max_execution_time', 800);
ini_set('memory_limit', 2000000000);  

$inputFileType = 'Excel2007';
$inputFileName = $_FILES['file']['tmp_name'];

class MyReadFilter implements PHPExcel_Reader_IReadFilter {

    public function __construct($fromColumn, $toColumn) {
        $this->columns = array();
        $toColumn++;
        while ($fromColumn !== $toColumn) {
        $this->columns[] = $fromColumn++;
        }
    }

    public function readCell($column, $row, $worksheetName = '') {
          // Read columns from 'A' to 'AF'
          if (in_array($column, $this->columns)) {
              return true;
          }
          return false;
      }
}

$filterSubset = new MyReadFilter('A', 'AF'); 

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objReader->setReadFilter($filterSubset); 
$objReader->setLoadSheetsOnly( array("NORTH") );
$objPHPExcelReader = $objReader->load($inputFileName);

$r = 3500;
while(isset($maxrow_north) != 1){
    $cellvalue = $objPHPExcelReader->getSheetByName('NORTH')->getCellByColumnAndRow(2, $r);

    if(isset($cellvalue) != 1){
        $maxrow_north = $r; 
    } elseif($r > 4000) {
        echo "It's over 4000!";
    } else {
        $r = $r++;
        } 
}


echo $maxrow_north;
?>

Some more background

I am having admins upload .xlsx .xls or .csv files into an html form. The code, above, is the handler. I have limited the number of columns seen because the original creator of the .xlsx file thought it would be a great idea to have the columns go all the way out to XCF.

The rows also go all the way out to somewhere around 10,000. So, I want to find the first blank row and stop there.

TIA!

Upvotes: 3

Views: 12048

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

Don't use 

if(isset($cellvalue) != 1){

A cell value always exists even if it's an empty string or a null: and you're not testing the actual cell value, but the existence of a cell.... simply get() ting a cell will create a new empty cell object if one didn't already exist

You need to test the actual value stored in the cell 

if($cellvalue->getValue() === NULL || $cellvalue->getValue() === '') {
    $maxrow_north = $r;

And if you're trying to find the first blank cell in the column, then break once you've found it rather than carry on iterating till you reach your max

(Note, doesn't check for rich text in cells)

EDIT

Example, that also allows for merged cells

function testInMergeRangeNotParent($objWorksheet, $cell)
{
    $inMergeRange = false;
    foreach($objWorksheet->getMergeCells() as $mergeRange) {
        if ($cell->isInRange($mergeRange)) {
            $range = PHPExcel_Cell::splitRange($mergeRange);
            list($startCell) = $range[0];
            if ($cell->getCoordinate() !== $startCell) {
                $inMergeRange = true;
            }
            break;
        }
    }
    return $inMergeRange;
}


$column = 2;  // Column to check
$max = 4000;
echo 'Get First blank row in column ', $column, PHP_EOL;
$r = 3500;  // Starting row
while(true){
    $cell = $objPHPExcelReader->getSheetByName('NORTH')->getCellByColumnAndRow($column, $r);
    if ($cell->getValue() === NULL && 
        !testInMergeRangeNotParent($objPHPExcelReader->getSheetByName('NORTH'), $cell)) {
        break;
    }elseif($r > $max) {
        echo "It's over $max !";
        break;
    }
    $r++;
}
echo 'First blank row in column ', $column, ' is ', $r, PHP_EOL;

Upvotes: 3

Related Questions