CODEWITHSUNDEEP
phpmysqlphpexcel
maestro416
maestro416

Reputation: 924

PHPExcel - Reading the Cells and Passing it into MYSQL

I'm trying to use a php script to read an xlsx file, and pass the information from the cells off into MYSQL

here is my code, I'm 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);
} 
?>

I'm at a total loss as how to get the data from the spreadsheet into mysql

EDIT:

I've managed to get the data printed by 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 seem to get it to insert into my table. I have tried using the implode function as well but nothing happens.

Upvotes: 7

Views: 4848

Answers (1)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

The easiest way to do this is to convert xlsx to csv file on the fly, and than use normal CSV parsing. Just instantiate CSVWriter and save to a temporary location (I can provide example code by tomorrow)

Sample code:

  $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);
    }
  }

Upvotes: 2

Related Questions