passing cells into variables to upload into MYSQL

Nov 7, 2011 at 7:59 PM

Hi,

I've searched everywhere in the documentation but I can't seem to find the method for reading an excel file and then passing that information into MYSQL

I'm going based on the following code found in the documentation.

I am using PHPExcel version 1.7.6, and PHP 5.3.5

require_once 'PHPExcel.php';
$inputFileType = 'Excel2007';
$inputFileName = $upload_path . $filename;

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */ 
class chunkReadFilter implements PHPExcel_Reader_IReadFilter 
{ 
    private $_startRow = 0; 
    private $_endRow   = 0; 

    /**  Set the list of rows that we want to read  */ 
    public function setRows($startRow, $chunkSize) { 
        $this->_startRow = $startRow; 
        $this->_endRow   = $startRow + $chunkSize; 
    } 

    public function readCell($column, $row, $worksheetName = '') { 
        //  Only read the heading row, and the configured rows 
        if (($row == 1) ||
            ($row >= $this->_startRow && $row < $this->_endRow)) { 
            return true; 
        } 
        return false; 
    } 
} 


/**  Create a new Reader of the type defined in $inputFileType  **/ 
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 


/**  Define how many rows we want to read for each "chunk"  **/ 
$chunkSize = 2048; 
/**  Create a new Instance of our Read Filter  **/ 
$chunkFilter = new chunkReadFilter(); 

/**  Tell the Reader that we want to use the Read Filter  **/ 
$objReader->setReadFilter($chunkFilter); 

/**  Loop to read our worksheet in "chunk size" blocks  **/ 
for ($startRow = 2; $startRow <= 65536; $startRow += $chunkSize) { 
    /**  Tell the Read Filter which rows we want this iteration  **/ 
    $chunkFilter->setRows($startRow,$chunkSize); 
    /**  Load only the rows that match our filter  **/ 
    $objPHPExcel = $objReader->load($inputFileName); 
   
 //    Need to pass the cell values into the variables
/** This is where I need to use something like this

for ($x = 2; $x < = count($data->sheets[0]["cells"]); $x++) {
    $item_number = $data->sheets[0]["cells"][$x][1];
    $qty_sold = $data->sheets[0]["cells"][$x][2];
    $cost_home = $data->sheets[0]["cells"][$x][3];

which would work for phpexcelreader, but I just dont know which functions would do the same for phpExcel **/

//here is where I would pass those values into MYSQL
	
$sql = "INSERT INTO sales_report (`item_number`,`qty_sold`, `cost_home`) 
        VALUES ('$item_number',$qty_sold,'$cost_home')";
		 echo $sql."\n";
    mysql_query($sql);
} 
?>

if you have any solutions to my problem your help would be most appreciated.

 

Thank you very much.

Nov 7, 2011 at 8:40 PM

It looks like what you're missing is just looping the cells.

Look in "Documentation/PHPExcel developer documentation.doc" around page 12 for help.

Nov 7, 2011 at 9:08 PM

Thanks for your response.

I found the referenced documentation and did get it to output in html successfully, however i'm not sure how to use that loop to insert it into mysql.

I guess I'm missing the php part that grabs that. Here is my code, I am absolutely certain that it is wrong in an embarrassing way.

require_once 'PHPExcel.php';
$inputFileType = 'Excel2007';
$inputFileName = $upload_path . $filename;
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet();
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
                                                     // iterated.
  foreach ($cellIterator as $cell) {
    $sql = "INSERT INTO sales_report (`item_number`,`qty_sold`, `cost_home`) VALUES ($row)";
         echo $sql."\n";
    mysql_query($sql);
  }
}
?>

 

Nov 8, 2011 at 12:46 PM
Edited Nov 8, 2011 at 12:47 PM

Well this is basically how I loop cells and get values in my app, and it was ported from phpexcelreader too, if this helps...

foreach ($objWorksheet->getRowIterator() as $row) {
	$j = 1;
	
	$cellIterator = $row->getCellIterator();
	$cellIterator->setIterateOnlyExistingCells(false);
	
	foreach ($cellIterator as $cell) {
		$data->sheets[0]['cells'][$i][$j] = $cell->getValue();
		
		$j++;
	} // end cell getter
	
	$i++;
	
} // end row getter
Nov 8, 2011 at 3:37 PM

When I run the loop and try to insert it into mysql i don't get any errors but nothing gets inserted?

foreach ($objWorksheet->getRowIterator() as $row) {
	$j = 1;
	
	$cellIterator = $row->getCellIterator();
	$cellIterator->setIterateOnlyExistingCells(false);
	
	foreach ($cellIterator as $cell) {
		 $sql[] = mysql_real_escape_string($data->sheets[0]['cells'][$i][$j] = $cell->getValue());
		
		$j++;
	} // end cell getter
	
	$i++;
	
} // end row getter

$query = "INSERT INTO `upload` VALUES (".implode("), (", $sql). ")";
mysql_query($query);