解决普通导入的问题，暂不能解决导入图片相关问题，后续遇到相关业务在处理
`
<?php
namespace app\index\lib\xlsx;
use app\index\lib\xlsx\import as aaa;
use think\Controller;
use think\Exception;
use think\exception\ErrorException;
use think\Log;
class import extends Controller
{
/**
* 注意事项：
* 1. $fileSize已经转换成字节，无需再次转换，所以单位（M），默认5M；
* 2. 如果需要更 文件路径，扩展名，记录表名，在公共定义的地方修改即可；
* 3. 导入记录默认开启，如果关闭，将不再有数据表的记录任何信息（不包含相关日志）；
* 4. 关闭导入记录 将true改为false;
* 5. 使用前需要引入phpoffice扩展；
* 6. 如果需要重新处理表格数据，
* 6-1. 将$dealData 传值 true;
* 6-2. 需要重新调用批量添加的方法$this->insertAllData($data);
*
* 使用方法：
* 1. 实例化当前类
* 2. 同时传参(*：必须；-：非必须)： *表名 *表字段 *上传文件接收的名字 -是否需要返回表格数据 -文件限制大小
* 3. 调用upload方法即可
*
*
* 示例：
* namespace app\index\controller;
* use think\Controller;
* use app\index\lib\xlsx\import as aaa;
* class A extends Controller
* {
* public function aa(){
* $tableFile = ['a','b','c','d','e','f','g','h'];
* $imports = new aaa('test',$tableFile,'file');
* if($this->request->file('file')){
* return $imports->upload();
* }else{
* return "<form action='/public/index.php/index/a/aa' enctype='multipart/form-data' method='post'>
* <input type='file' name='file' /> <br>
* <input type='submit' value='上传' />
* </form> ";
* }
* }
* }
*/
public $fileSize; //文件大小 注意：已经转换成字节，所以单位（M）
public $fileName; //接受文件的名字
public $filePath = 'uploads/importxlsx'; //文件路径
public $suffix; //文件后缀
public $suffixArr = ['xlsx', 'xls', 'Xlsx', 'Xls']; //文件后缀范围
public $tableName; //数据表名
public $tableField; //数据表字段
public $switchLog = true; //导入记录
public $logTableName = 'xlsximportlog'; //记录表名称
public $dealData; //是否返回数据重新处理
/**
* import constructor. 实例化自动执行
* @param string $tableName 表名
* @param array $tableField 数据库字段
* @param string $fileName 上传文件的名字
* @param string $fileSize 长传文件的大小 注意：已经转换成字节，所以单位（M）
*/
public function __construct($tableName = '', $tableField = [], $fileName = '', $dealData = false, $fileSize = '5')
{
parent::__construct();
$this->tableName = $tableName;
$this->tableField = $tableField;
$this->fileName = $fileName;
$this->dealData = $dealData;
$this->fileSize = $fileSize*1024*1024;
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/8/2 15:07
* @Description：单文件上传
* 成功上传后 获取上传信息
*print_r($file->getInfo());
*[
* ['name'] => '2021-07-31 15_50_18-5822_商务部在办企业进度及时限表.xlsx',
* ['type'] => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
* ['tmp_name'] => 'C:\Users\EDZ\AppData\Local\Temp\phpEAA4.tmp',
* ['error'] => '0',
* ['size'] => '132892'
*];
*echo $info->getExtension(); //输出 jpg
*echo $info->getSaveName(); //输出 20160820/42a79759f284b767dfcb2a0197904287.jpg
*echo $info->getFilename(); //输出 42a79759f284b767dfcb2a0197904287.jpg
*/
public function upload(){
$file = request()->file($this->fileName);
$path = ROOT_PATH . 'public' . DS . $this->filePath;
$info = $file->validate(['size'=>$this->fileSize,'ext' => $this->suffixArr])->move($path);
if($info){
$this->path = $path.'/'.$info->getSaveName();
$this->suffix = $info->getExtension();
$res = self::import($file->getInfo());
if($this->switchLog){
self::importLog($this->path,count($res),$file->getInfo['name'],$file->getInfo['size'],$file->getInfo['type']);
}
if($this->dealData){
return $res;
}
$result = $this->insertAllData($res);
if($result){
return $result;
}
throw new Exception('添加失败');
}else{
// 上传失败获取错误信息
throw new Exception($file->getError());
}
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/8/3 18:05
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
* @Description：导入逻辑
*/
private function import($fileInfo = []){
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(ucfirst($this->suffix));
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($this->path); //载入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnNum = self::getColumnArrByMaxCol($highestColumn); //列值
if(count($highestColumnNum) != count($this->tableField)) throw new Exception("表格的列数和表字段个数不相同，请确认后再试~");
$lines = $highestRow - 1;//减掉表的第一行（通常第一行是表头，不需要导入，导入时无需删除第一行）
if ($lines <= 0) throw new Exception($fileInfo['name']."Excel表格中没有数据~");
$b = 0;
for ($i = 2; $i <= $highestRow; $i++){
for($a = 0; $a <= count($highestColumnNum); $a++){
$data[$b][$this->tableField[$a]] = $worksheet->getCellByColumnAndRow($a+1,$i)->getValue();
}
array_pop($data[$b]);
$b++;
}
return $data;
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/8/4 14:35
* @param string $col
* @throws Exception
* @Description：根据列数的最大值返回最大范围内的列值
*/
public function getColumnArrByMaxCol($col = ''){
if(empty($col)) throw new Exception("不能为空~");
if(strlen($col) > 2) throw new Exception("导入列数超出最大值~");
strtoupper($col);
$column_word = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
$colArr = [];
if(in_array($col,$column_word)){
for ($i=0;$i<=25;$i++){
if($column_word[$i] == $col) {
array_push($colArr,$column_word[$i]);
return $colArr;
}
array_push($colArr,$column_word[$i]);
}
}
$firstColumn = substr($col,0,1);
$twoColumn = substr($col,1,1);
if(!in_array($firstColumn,$column_word)) throw new Exception("错误的参数");
if(!in_array($twoColumn,$column_word)) throw new Exception("错误的参数");
$firstColumnNum = array_keys($column_word,$firstColumn);
// $twoColumnNum = array_keys($column_word,$twoColumn);
$colArr = $column_word;
for($a = 0; $a <= $firstColumnNum[0]; $a++){
for($b = 0; $b <= 25; $b++){
if($twoColumn == $column_word[$b] && $firstColumnNum[0] == $a){
array_push($colArr,$column_word[$a].$column_word[$b]);
return $colArr;
}
array_push($colArr,$column_word[$a].$column_word[$b]);
}
}
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/8/3 17:26
* @param $data
* @return $this
* @Description：添加全部数据
*/
public function insertAllData($data = ''){
if(empty($data) || !is_array($data)) throw new Exception("导入的数据是空的");
$res = Db($this->tableName)->insertAll($data);
if($res){
return $this;
}
throw new Exception("添加失败");
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/8/3 16:53
* @param string $url
* @param string $totalRow
* @param string $totalInsert
* @return string
* @Description：日志记录
*/
public function importLog($url = '', $totalRow = '', $name = '', $size = '', $type = ''){
if(!$this->switchLog){
return 'Method ERROR：The switchLog function must be enabled';
}
if(!self::createTable()) throw new Exception("记录日志失败");
$data = [
'create_time' => time(),
'url' => $url,
'name' => $name,
'size' => $size,
'type' => $type,
'num' => $totalRow,
];
$res = Db($this->logTableName)->insert($data);
if($res){
return $this;
}
return "Error Message：Log recording failure";
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/8/3 11:59
* @param $xlsxTableName
* @return bool|string
* @throws \think\db\exception\BindParamException
* @throws \think\exception\PDOException
* @Description：创建数据表
*/
private function createTable(){
$xlsxTableName = config('database.prefix').$this->logTableName;
$isTable=db()->query('SHOW TABLES LIKE '."'". $xlsxTableName ."'");
if($isTable){
return $this;
}
$database = config('database.database');
$sql = "CREATE TABLE `". $database ."`.`". $xlsxTableName ."` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
`url` varchar(255) NULL COMMENT 'url地址',
`name` varchar(255) NULL COMMENT '源文件名名称',
`size` varchar(40) NULL COMMENT '源文件大小',
`type` varchar(255) NULL COMMENT '源文件类型',
`num` int(11) NULL COMMENT '总数量',
`create_time` bigint(20) NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) COMMENT = '导入xlsx表格记录表'";
$res = db()->query($sql);
if($res){
return $this;
}
return "Query Message：Error creating data table";
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/8/3 17:18
* @param string $page
* @param string $limit
* @param string $sort
* @return bool|false|\PDOStatement|string|\think\Collection
* @throws \think\db\exception\DataNotFoundException
* @throws \think\db\exception\ModelNotFoundException
* @throws \think\exception\DbException
* @Description：获取日志列表
*/
public function getLogList($page = '1', $limit = '20', $sort = 'desc'){
$res = Db($this->logTableName)->page($page,$limit)->order('id',$sort)->select();
return $res;
}
}
