Reading a XLSX sheet to feed a MySQL table using PHPExcel

mlh picture mlh · Aug 23, 2011 · Viewed 46.1k times · Source

I found the PHPExcel library brilliant to manipulate Excel files with PHP (read, write, and so on).

But nowhere in the documentation is explained how to read a XLSX worksheet to feed a MySQL table...

Sorry for this silly question, but i need it for my work and found no answer on the web.

A small example could be very helpful.

Thanks a lot.

UPDATED :

I precise my question :

The only part of code i found in the documentation that could help me is to read an Excel file and display it in a HTML table :

`require_once 'phpexcel/Classes/PHPExcel.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load("edf/equipement.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow(); 
$highestColumn = $objWorksheet->getHighestColumn(); 

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); 

echo '<table border="1">' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
  echo '<tr>' . "\n";

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

  echo '</tr>' . "\n";
}
echo '</table>' . "\n";`

I know i can use the loop to feed my MySQL table, but i don't know how... I'm not aware in OOP...

Can somebody help me, please ?

Answer

maestro416 picture maestro416 · Nov 8, 2011

Here is the code

$inputFileName = $upload_path . $filename;
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet();

$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$rows = array();
for ($row = 1; $row <= $highestRow; ++$row) {
  for ($col = 0; $col <= $highestColumnIndex; ++$col) {
    $rows[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
  }
  mysql_query("INSERT INTO upload (`item_number`,`qty_sold`,`cost_home`) VALUES ($rows[1],$rows[2],$rows[3])");
}

?>

I have tried mysql_query("INSERT INTO upload (col1,col2) VALUES ($rows[1],$rows[2])"); as well but didn't work. The table stays empty