PHPExcel - reading cells and passing it to MYSQL

Question

I am trying to use a php script to read an xlsx file and pass information from cells to MYSQL

here is my code, 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 

Here 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 will work for phpexcelreader, but I just don't know what functions will 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); } ?> 

I have a complete loss how to get data from a spreadsheet in mysql

EDIT:

I managed to get data printed using the following arrays

 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 

But I just can't get it to be inserted into my desk. I also tried using the implode function, but nothing happens.

Answer 1

The easiest way to do this is to convert xlsx to a csv file on the fly, rather than using regular CSV parsing. Just create a CSVWriter and save to a temporary place (I can provide sample code tomorrow)

Code example:

  $objReader = PHPExcel_IOFactory::load ( $file_path ); $writer = PHPExcel_IOFactory::createWriter ( $objReader, 'CSV' ); $writer->save ( $csv_path ); if (($handle = fopen ( $csv_path, "r" )) !== false) { while ( ($data = fgetcsv ( $handle)) !== false ) { print_r($data); } }