Reading a XLSX sheet to feed a MySQL table using PHPExcel

Question

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 1

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

Answer 2

The first for loops through rows, and the second one loops through columns. So, there are plenty of solutions to your "problem".

You could, for example, populate an array and make an insert statement for each row. As the following :

$rows = array();
for ($row = 1; $row <= $highestRow; ++$row) {
  for ($col = 0; $col <= $highestColumnIndex; ++$col) {
    $rows[$col] = mysql_real_espace_string($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
  }

  mysql_query("INSERT INTO your_table (col1,col2) VALUES ($rows[1],$rows[2])");
}

Obviously, this code can be improved.