phpmysqlphpspreadsheet

Skip First Row in PHPSpreadsheet Import


Just started working with phpspreadsheet. I am trying to figure out how to skip the header row in my excel file when uploading.

require '../vendor/autoload.php';
if (pathinfo($_FILES['upexcel']['name'], PATHINFO_EXTENSION) == 'csv') {
  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} else {
  $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
}
$spreadsheet = $reader->load($_FILES['upexcel']['tmp_name']);

$worksheet = $spreadsheet->getActiveSheet();
$sql = "INSERT INTO `testCommission` (`One`, `two`, `three`, `four`, `five`, `six`, `seven`) VALUES (?, ?, ?, ?, ?, ?, ?)";
foreach ($worksheet->getRowIterator() as $row) {
  // Fetch data
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false);
  $data = [];
  foreach ($cellIterator as $cell) {
    $data[] = $cell->getValue();
  }

What can I do to achieve this with the above script?


Solution

  • You need to detect, when it is the first row, a binary variable like this helps

    <?php
    require '../vendor/autoload.php';
    if (pathinfo($_FILES['upexcel']['name'], PATHINFO_EXTENSION) == 'csv') {
      $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
    } else {
      $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
    }
    $spreadsheet = $reader->load($_FILES['upexcel']['tmp_name']);
    
    $worksheet = $spreadsheet->getActiveSheet();
    $sql = "INSERT INTO `testCommission` (`One`, `two`, `three`, `four`, `five`, `six`, `seven`) VALUES (?, ?, ?, ?, ?, ?, ?)";
    $isheader = 0;
    foreach ($worksheet->getRowIterator() as $row) {
      // Fetch data
      if($isheader > 0) {
          $cellIterator = $row->getCellIterator();
          $cellIterator->setIterateOnlyExistingCells(false);
          $data = [];
          foreach ($cellIterator as $cell) {
              $data[] = $cell->getValue();
          }
      } else 
      { $isheader = 1; }
    }
    ?>