How to ignore first row (header row) on insert using PhpOffice\PhpSpreadsheet

Question

I am looking for a way to ignore first row which is the header row when inserting data into mysql database using PhpOffice\PhpSpreadsheet.
I have followed this but not working
Skip First Row in PHPSpreadsheet Import
My problem is how to ignore the header row?
Below is my clean code...

use Phppot\DataSource;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
require_once ('./vendor/autoload.php');

if (isset($_POST["import"])) {

    $allowedFileType = [
        'application/vnd.ms-excel',
        'text/xls',
        'text/xlsx',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    ];

    if (in_array($_FILES["file"]["type"], $allowedFileType)) {
        $date = date('Y-m-d');
        $targetPath = 'uploads/'.$date." ".$_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

        $Reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        
        if($Reader) {
                $Reader->setReadDataOnly(true);  
                $spreadSheet = $Reader->load($targetPath);
                $sheetData = $spreadSheet->getActiveSheet()->toArray();

                // Loop through the rows from xlsx file for insert
                foreach($sheetData as $row) {
                    // get columns in a contigeous order from xlsx file
                    $regno = isset($row[0]) ? $row[0] : "";
                    $fullname = isset($row[1]) ? $row[1] : "";
                    $course = isset($row[2]) ? $row[2] : "";
                    $status = 1;
                    $pin = strtoupper(substr(md5(mt_rand()), 0, 10));

                    // Insert into db
                    $fieUploaded = $conn->itStudentsFileUpload($regno,$fullname,$course,$pin,$current_session,$status);
                    // If all is well send success message
                    if ($fieUploaded) {
                        echo "Success";
                    }
                }
            }

    } else {
        $type = "error";
        $message = "Invalid File Type. Upload Excel File.";
    }
}

score 0 · Answer 1 · answered Sep 20 '22 at 14:07

After several tests, I solve the problem as follows:

class FirstRowFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
                {
                    public function readCell($column, $row, $worksheetName = '') {
                        //  Return true for rows after first row
                        return $row > 1;
                    }
                }
                $Reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");
                $filterRow = new FirstRowFilter();
                $Reader->setReadFilter($filterRow);

                $spreadSheet = $Reader->load($targetPath);
                $sheetData = $spreadSheet->getActiveSheet()->toArray();

Note:
If you have additional fields that are not from the excel file eg. date, you need to check for emptiness of the first row from the excel file and then insert if not empty to ignore inserting empty values.

How to ignore first row (header row) on insert using PhpOffice\PhpSpreadsheet

1 Answers1