Use phpexcel to grab columns and write to database

Topics: User Forum
Jun 11, 2010 at 4:00 PM
I'm trying to use php excel to grab certain rows from a sheet and columns in that row to a database. The issue is that the rows will always be contiguous but have a different starting point in each sheet. There is a cell in the sheet that tells me which rows to grab. So I can loop through the cells until I can find the correct Row to start but then I'm unsure as to the next step to use on the following rows if I want say columns 16,18,24... Right now I'm considering writing the dataset I've found to a csv and then reading the csv into the db, but is there a better way? phpexcel is amazing but is there an honest to goodness api?
Jun 14, 2010 at 7:57 AM
cvken wrote:
is there an honest to goodness api?

 /Documentation/API/index.html in the standard distribution for any production release

Jun 14, 2010 at 2:58 PM

When I go there I get this page cannot be displayed.

When I try to go through the links I get to the How do you page, but not an API with a complete list of functions and what they do.

It's possible (highly) that I'm just inept, but I can't find a full API.

There is a ton of stuff in the php excel developer documentation mind you.


Here is what I have to date, comment telling me where I'm a dunce and can improve would be appreciated. To date I've had to massivly increase the timeout values. For this to run as where looping through at least 30 files.


function ProcessFolder($masterid,$settingsid,$folderid)
	try {
    $dbh = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\inetpub\\wwwroot\\fileprocessor\\data\\fp.mdb;Uid=Admin");
	catch (PDOException $e)
    echo $e->getMessage();
	$sqlM = 'SELECT settings.FormatName, settings.Ftype, settings.xlsSheet, settings.startString,'.
			'settings.endString, folders.Folder '.
			'FROM (master INNER JOIN settings ON master.ID = settings.masterID) '.
			'INNER JOIN folders ON settings.ID = folders.settingsid '.
			'WHERE (((settings.ID)='.$masterid.'));';
	if ($masterid = 1)
		if ($settingsid = 1)
			foreach ($dbh->query($sqlM) as $rowM)
				$newpath = 'c:/AchFiles'.str_replace("\\","/",$rowM['Folder']);
				if ($handle = opendir($newpath)) 
				   while (false !== ($file = readdir($handle)))
					  if ($file != "." && $file != ".." && $file != "QCFiles")
						 echo '<br>'.$rowM['Folder'];
						 $thisarray = findRange($newpath.'/'.$file,$rowM['startString'],$rowM['endString']);
						 For($i = 0 ; $i <= 1 ; $i++) { echo " " . $thisarray[$i] . " "; }

function findRange($file,$value1,$value2)
	$objReader = PHPExcel_IOFactory::createReader('Excel5');//excel2007
	$objPHPExcel = $objReader->load($file); //ARCHIVE excel2007 dir
	$val = ($objPHPExcel->getActiveSheet()->getCell('AB31')); //CELL
	$temp = $val->getvalue();//returns the value in the cell
	$objWorksheet = $objPHPExcel->getActiveSheet();
	$rowNumber = 0;
	$x = 0; 
	foreach ($objWorksheet->getRowIterator() as $row) 
	  $cellIterator = $row->getCellIterator();
	  $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, even if it is not set.  By default, only cells that are set will be         
	  foreach ($cellIterator as $cell) 
		$searchedvalue = $cell->getValue();
		if ($searchedvalue===$value1) 
			$cellcoord = $cell->getCoordinate(); 
			$arr[0] = $cellcoord;
		if ($searchedvalue===$value2) 
			$cellcoorde = $cell->getCoordinate();
			$arr[1] = $cellcoorde;
	if (count($arr) > 0) { return $arr; } else { return "null"; }
Jun 14, 2010 at 5:03 PM

I added this between the if statements in findRange:

if (isset($cellcoord)) { 
		$row ++;
		echo $objPHPExcel->getActiveSheet()->getCellByColumnAndRow(19, $row)->getValue(); 
And it will process about half of one file and then throw this error:
Catchable fatal error: Object of class PHPExcel_Worksheet_Row could not be converted to string in C:\inetpub\wwwroot\Classes\PHPExcel\Worksheet.php on line 980
I think I'm actually really close, any ideas why this error. It does not appear to be timing out as it returns the information in about a second.
Jun 14, 2010 at 6:56 PM

Without being whiny, could someone give me a clue of which way to look?

Jun 21, 2010 at 2:55 PM

I've found a way around by using the following for loop's:

for ($row = 1; $row <= $highestRow; ++$row)     {      for ($col = 0; $col <= $highestColumnIndex; ++$col)       {         ....     }}

Apparently it's a way faster way to loop through the data.  Hopefully if anyone else is trying to process a lot of excel sheets this will help them.