时间戳

首页 > 解决方案 > 上传到 MySQL 时如何从 Excel 电子表格中跳过标题

php - 上传到 MySQL 时如何从 Excel 电子表格中跳过标题

问题描述

是否可以通过代码跳过 Excel 电子表格中的标题（顶行）？我正在使用 PHPExcel_Reader 处理上传到数据库中。

这张图片是我的数据 excel

这是我的代码：

<?php require_once('../../php-excel-reader/excel_reader2.php'); require_once('../../SpreadsheetReader.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)){ $targetPath = '../assets/uploads/'.$_FILES['file']['name']; move_uploaded_file($_FILES['file']['tmp_name'], $targetPath); $Reader = new SpreadsheetReader($targetPath); $sheetCount = count($Reader->sheets()); for($i=0;$i<$sheetCount;$i++) { $Reader->ChangeSheet($i); foreach ($Reader as $Row) { $student_id = ""; if(isset($Row[0])) { $student_id = mysqli_real_escape_string($conn,$Row[0]); } $roll_no = ""; if(isset($Row[1])) { $roll_no = mysqli_real_escape_string($conn,$Row[1]); } $student_name = ""; if(isset($Row[2])) { $student_name = mysqli_real_escape_string($conn,$Row[2]); } $class_name = ""; if(isset($Row[3])) { $class_name = mysqli_real_escape_string($conn,$Row[3]); } ?>

标签： phpexcel

解决方案

我将此方法用于跳过标题：

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('FILE.xlsx');
$sheetData = array();

foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
    $worksheetTitle = $worksheet->getTitle();
    $highestRow = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

    for ($row = 1; $row <= $highestRow; ++$row) {
        for ($col = 0; $col <= $highestColumnIndex; ++$col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
            $sheetData[$row][$col] = $val;
        }
    }
}

unset($sheetData[1]); // SKIP HEADER

foreach ($sheetData as $val) {
  // set data for upload DB
 }

我只是从数组中取消设置。

推荐阅读