PHPExcel读取表格

 

    public function batchUpdateLevel(Request $request) {
        $files = $request->getUploadedFiles();
        $tmpFileName = $files['filename']->file;
        /** @var $reader \PhpOffice\PhpSpreadsheet\Reader\Xlsx */
        $reader = new Xlsx();
        /** @var $excel \PhpOffice\PhpSpreadsheet\Spreadsheet */
        $excel = $reader->load($tmpFileName);
        /** @var $sheet \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet */
        $sheet = $excel->getActiveSheet();
        // 模板有效数据从第4行开始
        $row = 4;
        // $code = $sheet->getCell("A4" )->getValue();
        $customerLevels = [];
        // 最大循环次数65535
        for ($maxRows = 65535; $maxRows > 0; $maxRows -= 1) {
            $code = $sheet->getCell(sprintf("A%d", $row))->getValue();
            $name = $sheet->getCell(sprintf("B%d", $row))->getValue();
            $level = $sheet->getCell(sprintf("C%d", $row))->getValue();
            if (is_null($code)) {
                break;
            }
            array_push($customerLevels, [
                'code' => $code,
                'name' => $name,
                'level' => $level
            ]);
            $row += 1;
        }
// echo json_encode($customerLevels, JSON_UNESCAPED_UNICODE); die;
// ...
    }

 

如果是指定目录，不是通过上传文件

对于windows系统汉字要转换编码

$path ='./Temp/customer/客户等级确认表.xlsx';
if (strncmp(DIRECTORY_SEPARATOR, "\\", 1) == 0) {
    $path = iconv("utf-8", "gb2312", $path);
}

但是后面还是有bug， fopen可以打开, 执行到  $excel->getActiveSheet(); 会报错

所以还是不能用中文名

 

对于日期的读取

/** @var $expire string "12/31/2021" */
$expire = $sheet->getCell(sprintf("H%d", $row))->getFormattedValue();

转换为时间戳:

$expireAt = (new \DateTime($expire))->getTimestamp();