Populating a mysql database with an excel file using phpspreadsheet

I am trying to populate a mysql database with an excel file using phpspreadsheet library. I am doing it in the following way but I get just the first row. How can I do it for all the rows

$spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load($target_file);
$worksheet = $spreadsheet->getActiveSheet();
$rows = ;
$outer = 1;
foreach ($worksheet->getRowIterator() AS $row)
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
$cells = ;
foreach ($cellIterator as $cell)
$cells = $cell->getValue();

$rows = $cells;

while($outer > 1)
$data = [
'testTaker' => $cells[1],
'correctAnswers' => $cells[2],
'incorrectAnswers' => $cells[3],
];


if($this->testModel->addTest($data))
die('it worked');
else
die('Something went wrong');



$outer++;



die()

while ($outer > 1)

$outer

1 Answer
1

This is how I am importing a XLSX spreadsheet with phpSpreadSheet into a MySQL database using PDO (modified to fit your criteria).

// read excel spreadsheet
$reader = new PhpOfficePhpSpreadsheetReaderXlsx();
if($reader)
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($target_file);
$sheetData = $spreadsheet->getActiveSheet()->toArray();

foreach($sheetData as $row)
// get columns
$testTaker = isset($row[0]) ? $row[0] : "";
$correctAnswers = isset($row[1]) ? $row[1] : "";
$incorrectAnswers = isset($row[2]) ? $row[2] : "";

// insert item
$query = "INSERT INTO item(testTaker, correctAnswers, incorrectAnswers) ";
$query .= "values(?, ?, ?)";
$prep = $dbh->prepare($query);
$prep->execute(array($testTaker, $correctAnswers, $incorrectAnswers));




Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.