问题描述:当导入Excel模板文件时,可以根据需要上传的文件种类灵活存储在不同的数据表中
解决思路:
- 首先先引入处理Excel需要的文件
composer require phpoffice/phpspreadsheet
- 直接上代码,uploadExcel1为生成读取后的结果数组,uploadExcel2为利用反射机制,根据传递的参数,通过对应模型的save方法动态保存到相应的表中
<?php
namespace app\service;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Request;
class ExcelService
{
public function uploadExcel1(Request $request, string $fileParamName, array $keys, int $startRow = 2, int $startCol = 1, string $uploadUser = 'user')
{
$finalArray = array();
$file = $request->file($fileParamName)->getRealPath();
$inputFileType = IOFactory::identify($file);
$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($file);
$sheet = $spreadsheet->getActiveSheet();
$highestColumn = $sheet->getHighestColumn();
$highestRow = $sheet->getHighestRow();
$realHighestRow = null;
for ($i = $highestRow; $i > $startRow -1 ; $i--) {
if($sheet->getCellByColumnAndRow(3,$i)->getValue()){
$realHighestRow = $i;
break;
}
}
$highestColumn = Coordinate::columnIndexFromString($highestColumn);
for ($i = $startRow; $i <= $realHighestRow; $i++) {
$realCol = count($keys)+$startCol-1;
$tpl = array();
for($j = $startCol; $j<=$realCol; $j++){
$tpl[$keys[$j-$startCol]] = $sheet->getCellByColumnAndRow($j, $i)->getValue();
}
$tpl['upload_user'] = $uploadUser;
$finalArray[] = $tpl;
}
return $finalArray;
}
public function uploadExcel2(Request $request, string $fileParamName, array $keys, int $startRow = 2, int $startCol = 1, string $uploadUser = 'user', string $modelName)
{
$num = 0;
$file = $request->file($fileParamName)->getRealPath();
$inputFileType = IOFactory::identify($file);
$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($file);
$sheet = $spreadsheet->getActiveSheet();
$highestColumn = $sheet->getHighestColumn();
$highestRow = $sheet->getHighestRow();
$realHighestRow = null;
for ($i = $highestRow; $i > $startRow -1 ; $i--) {
if($sheet->getCellByColumnAndRow(3,$i)->getValue()){
$realHighestRow = $i;
break;
}
}
for ($i = $startRow; $i <= $realHighestRow; $i++) {
$realCol = count($keys)+$startCol-1;
$tpl = array();
for($j = $startCol; $j<=$realCol; $j++){
$tpl[$keys[$j-$startCol]] = $sheet->getCellByColumnAndRow($j, $i)->getValue();
}
$tpl['upload_user'] = $uploadUser;
$model = '\\app\\model\\'.$modelName;
$reflect = new \ReflectionClass($model);
$instance = $reflect->newInstance();
$reflectMethod = $reflect->getMethod('save');
$res = $reflectMethod->invokeArgs($instance,[$tpl]);
if($res)
{
$num += 1;
}
}
return $num;
}
}