从Excel导入
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();