Looping through columns and rows in PHPExcel to create objects

I’m using PHPExcel library to loop through my excel spreadsheet. This is my current script :

try
{
    $inputfiletype = PHPExcel_IOFactory::identify($inputfilename);
    $objReader = PHPExcel_IOFactory::createReader($inputfiletype);
    $objPHPExcel = $objReader->load($inputfilename);
      $r = array( 'd' => true );
}
catch(Exception $e)
{
    die('Error loading file "'.pathinfo($inputfilename,PATHINFO_BASENAME).'": '.$e->getMessage());
      $r = array( 'd' => false );
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++)
{ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
    echo $rowData[0][0] . '</br>';

}

This is my spreadsheet :

What I’m trying to do is loop through each row, ignore the nulls and produce an object with the column name. For the example in the above image row 5 would produce :

{HVHV001OXLG:{
           A5AR:"152",
           A5AM:"988",
           A5AL:"100"
}
}

Is it possible to produce something like this from the format of my spreadsheet?

Answer

Read row 1 to get a list of the column headings; this provides a heading map. It’s probably a good idea to use the 5th argument for rangeToArray() so that you can get an array indexed by row/column number, so you can use the column address for easy lookup into the map.

Then loop through the data rows from row 2, returning a row/column indexed array again so that you can easily check the header mapping, testing for the cells that contain values, and looking up their column address against the column headings.

Something like:

$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

$columnLoopLimiter = $highestColumn;
++$columnLoopLimiter;
// get the column headings as a simple array indexed by column name
$headings = $sheet->rangeToArray('A1:' . $highestColumn . 1, NULL, TRUE, FALSE, TRUE)[1];

//  Loop through each data row of the worksheet in turn
for ($row = 2; $row <= $highestRow; $row++)
{ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE, TRUE);
    echo $rowData[$row]['A'], '</br>';
    for ($column = 'B'; $column !== $columnLoopLimiter; ++$column) {
        if (!empty($rowData[$row][$column])) {
            echo $headings[$column], ':', $rowData[$row][$column], '</br>';
        }
    }
}

Note the $column !== $columnLoopLimiter;, I’ve incremented $columnLoopLimiter previously to the column immediately after the last column so that we can do a !== comparison rather than a <=. This takes advantage of PHP’s perl-style character incrementor, but ensures that we do a clean comparison when the incrementor loops through Z to AA.