Get the first cell in each column

Topics: User Forum
Oct 1, 2011 at 9:28 PM

I've been trying to get PHPExcel to read each cell in the first row of a spreadsheet. A problem that I have is I cannot iterate through columns because the columns are labeled with letters and I do not know how to iterate through letters. If there was a way to map numbers to column titles (so that 26 maps to Z, 27 to AA, etc.) it would be helpful.

Another problem is that I can only get the last column in terms of a letter, so I cannot make a comparison to determine if the current column of the iteration is less than the last column of the iteration.

Oct 1, 2011 at 9:38 PM
Edited Oct 1, 2011 at 9:39 PM

Here is a part of code I use to feed a MySQL database with PHPExcel.

I use iteration to do it, like this :

 

require_once 'phpexcel/Classes/PHPExcel.php';

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

$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet();

$a = array();

foreach ($objWorksheet->getRowIterator() as $row) {
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false); 

  foreach ($cellIterator as $cell) {
    $a[] = $cell->getValue();
  }

	/*
	Your cells contents here :
	$value_cell_A = $a[0];
	$value_cell_B = $a[1];
	...
	$value_cell_Z = $a[25];
	$value_cell_AA = $a[26];
$value_cell_AB = $a[27];
etc... then you can use them in this loop */
// clear the array for the next line unset($a); }
Oct 1, 2011 at 9:44 PM

Thanks for the response, but that code looks like it iterates through each cell of each row, and I only want each cell of the first row. I don't know how to select just the first row with PHPExcel.

Oct 2, 2011 at 7:34 AM
Edited Oct 2, 2011 at 7:35 AM

Easy : you just don't have to iterate the row...

In my example, the part :

foreach ($objWorksheet->getRowIterator() as $row) {

}

is doing this iteration.

If instead you just read the $row[0] and don't iterate, you'll read the first row and that's all !

Oct 2, 2011 at 1:04 PM

How do I read row 0? The $row variable was defined during iteration, so if I didn't iterate over the row, $row would be undefined. Is it something like $objWorksheet->getRowIterator()[0] or $objWorksheet->row[0]?

Oct 5, 2011 at 4:48 AM
Edited Oct 5, 2011 at 4:59 AM

Here is a simpler way :

require_once 'phpexcel/Classes/PHPExcel.php';

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

$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();  
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);  

$row = 0;

for ($col = 0; $col <= $highestColumnIndex; ++$col) {
    echo $objWorksheet->getCellByColumnAndRow($col, $row)->getValue().' - ';
}
echo '<br />';

You just have to change the value of $row to read the row you want...

Jul 10, 2012 at 6:11 PM

 

$inputFileType = PHPExcel_IOFactory::identify($target_path);
	
	$objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($target_path);
//$objWorksheet = $objPHPExcel->setActiveSheetIndex($dail_sheet);
	$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
	
	$highestRow=$objWorksheet->getHighestDataRow();
	$highestColumn=$objWorksheet->getHighestDataColumn();
	
    
	$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);  
    
	
$row = 0;
$lowestrow=1;
  for($row = $lowestrow; $row <= $highestRow; $row++)
      {
    for ($col = 0; $col <= $highestColumnIndex; ++$col)
      {
	    if($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()!="NULL")
		 {
		  $lowestrow=$row;
		  $col =$highestColumnIndex;
		  $row = $highestRow;
 		 }
	  
	  
	  }

	  }
	echo $lowestrow." ";
	echo $highestRow." ";
	echo $highestColumn. " ";