【PHP】使用PHPoffice实现普通的导入功能

小破孩
2022-06-23 / 0 评论 / 331 阅读 / 正在检测是否收录...
解决普通导入的问题,暂不能解决导入图片相关问题,后续遇到相关业务在处理
<?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;
    }
}
0

评论 (0)

取消