ThinkPHP-导入Excel表格（通用版）

一、版本说明

1.PHP8.2、MySQL8.0、ThinkPHP8.0

2.使用前安装phpspreadsheet

composer require phpoffice/phpspreadsheet

二、技术说明

因本人采用前后端分离，因此上传文件以及导入表格为分离开发，如无需分离开发则自行合并开发即可。

1.第一步：上传并验证文件

2.第二步：读取Excel表格数据

3.第三步：存入数据库

三、上传Excel - 核心代码

$file = $this->request->file();
if (empty($file) || !isset($file['file'])) $this->error('请上传文件!');try {//  验证文件大小及后缀validate(['file' => "file|fileSize:$this->excelSize|fileExt:$this->ExcelExt"])->check($file);//  上传文件$saveName = \think\facade\Filesystem::disk('public')->putFile($path, $file['file']);//  上传至OSS ---- 请自行操作return $saveName;    // 返回文件地址
} catch (ValidateException|\Exception $e) {return $e->getMessage();
}

四、导入数据 - 核心代码

/*** @note 导入报名数据*/public function importList(): void{if ($this->request->isPost()) {$file = $this->request->post('file/s', '');if (empty($file)) $this->error('请上传文件!');//  读取选中工作sheet，默认第一张表$selectSheet = $this->request->post('select_sheet/d', 1);if ($selectSheet < 1) $selectSheet = 1;//  读取行数,默认从第二行开始读$readLine = $this->request->post('read_line/d', 2);if ($readLine < 1) $readLine = 2;//  数据插入结果$result = false;//  数据插入总数$resCount = 0;try {//  获取文件地址$saveName = public_path() . 'storage/' . $file;if (!file_exists($saveName)) throw new Exception('文件不存在!');$fileExtendName = substr(strrchr($saveName, '.'), 1);// 有Xls和Xlsx格式两种if ($fileExtendName == 'xlsx') {$objReader = IOFactory::createReader('Xlsx');} else {$objReader = IOFactory::createReader('Xls');}$objReader->setReadDataOnly(TRUE);// 读取文件$objPHPExcel = $objReader->load($saveName);$sheet = $objPHPExcel->getSheet($selectSheet - 1);   //excel中的第一张sheet$highestRow = $sheet->getHighestRow();       // 取得总行数$highestColumn = $sheet->getHighestColumn();   // 取得总列名if ($highestRow < 1) throw new Exception('数据不能为空!');if ($readLine > $highestRow) throw new Exception('数据读取行数据不能大于总行数!');
//   ---------------- 检测列数是否与模板一致，此处逻辑用不到可忽略，START --------------$template = (new RegisterTemplate())->where('activity_id', $activityId)->value('form_label');if (empty($template)) throw new Exception('未找到赛事报名模板!');//  取出列所在的field字段$template = json_decode($template, true);$columnNumber = column_to_number($highestColumn);if ((count($template) + 2) !== $columnNumber) throw new Exception('导入数据列数与模板不一致!');//  找出列对应的field字段$headerField = [];for ($column = 'C'; $column <= $highestColumn; $column++) {$columnTitle = $sheet->getCell($column . '1')->getValue();foreach ($template as $v) {if ($v['title'] === $columnTitle) {$v['key'] = $column;$headerField[] = $v;break;}}}
//   ---------------- 检测列数是否与模板一致，此处逻辑用不到可忽略，END --------------//  循环读取数据$data = [];for ($row = $readLine; $row <= $highestRow; $row++) {$rowData = [];for ($column = 'C'; $column <= $highestColumn; $column++) {// 根据自身实际业务，下方foreach可替换为：$rowData[] = $sheet->getCell($column . $row)->getValue();foreach ($headerField as $val) {if ($val['key'] === $column) {$val['value'] = $sheet->getCell($column . $row)->getValue();$rowData[] = $val;break;}}}$data[] = json_encode($rowData, JSON_UNESCAPED_UNICODE);}//  保存数据$saveData = [];foreach ($data as $v) {$saveData[] = ['activity_id' => $activityId,'org_id' => $orgId,'org_name' => $orgId == 0 ? '' : $org['title'],'forms' => $v,'admin_id' => $this->auth->id,];}$resSave = (new modelSave())->saveAll($saveData);if (empty($resSave)) throw new Exception('数据导入失败!');$resCount = count($resSave);$result = true;} catch (ValidateException|\Exception $e) {$this->error($e->getMessage());}if ($result) {unlink(public_path() . 'storage/' . $file);    // 删除文件$this->success('数据导入成功,共导入' . $resCount . '条数据!');}$this->error('数据导入失败!');}$this->error('request error!');}