php 导入excel 并且创建temp表，修改原表字段名

<?php

namespace app\api\controller\revit;

use app\api\controller\Common;
use app\admin\model\revit\Field;
use app\admin\model\revit\Revit as RevitModel;
use think\Db;
use think\Exception;

class Revit extends Common
{
    protected $noNeedLogin = '*';
    protected $noNeedRight = '*';

    //导入
    public function add_excel(){
        vendor("phpexecl.PHPExcel");
        vendor("phpexecl.PHPExcel.IOFactory");
        $file_name = $this->upload();
        $objReader = \PHPExcel_IOFactory::createReader('Excel2007');//创建读取实例
        $objPHPExcel = $objReader->load($file_name['path'], $encode = 'utf-8');
        $sheet = $objPHPExcel->getSheet(0);

        $highestColumn = $sheet->getHighestColumn();
        $highestColumn++;
        $highestRow = $sheet->getHighestRow();

        $guidfield ="";
        $field=[];
        $data = [];
        $guid = [];
        $revalue = -1;
        $user_id = $this->getUserId();

        // 获取 $guidfield 以及 $field
        for ($i = 'A', $j = 0; $i != $highestColumn; $i++) {
            $field[$j] = $objPHPExcel->getActiveSheet()->getCell($i.'1')->getValue();
            if ($field[$j] == "Family Instance Unique ID"){
                $guidfield = $i;
            }
            $j++;
        }

        // 获取 $guid
        if (!empty($guidfield)){
            for ($i = 4, $j = 0; $i <= $highestRow; $i++) {
                $guid[$j] = $objPHPExcel->getActiveSheet()->getCell($guidfield.$i)->getValue();
                $j++;
            }
        }else{
            $this->error('缺少guid列',$revalue);
        }

        Db::startTrans();
        try{
            $this->updateField($field);
            $res = RevitModel::whereIn('guid',$guid)->column('guid');

            for ($z = 4; $z <= $highestRow; $z++) {
                for ($i = 'A',$j = 0; $i != $highestColumn; $i++,$j++) {
                    $data[$z][$field[$j]] = $objPHPExcel->getActiveSheet()->getCell($i.$z)->getValue();
                }
            }

            if (empty($res)){
                $this->addfield();
                $allfield = Field::wherein('name',$field)->column('field','name');
                foreach ($data as $key => $value){
                    $newdata = [];
                    $newdata['user_id'] = $user_id;
                    $newdata['createtime'] = time();
                    foreach ($value as $k => $v){
                        $newdata[$allfield[$k]] = $v;
                    }
                    Db::name('revit')->insert($newdata);
                }
                $revalue = 0;
            }else{
                $this->createTemp($field);
                $allfield = Field::wherein('name',$field)->column('field','name');
                foreach ($data as $key => $value){
                    $newdata = [];
                    foreach ($value as $k => $v){
                        $newdata[$allfield[$k]] = $v;
                    }
                    Db::name('revit_temp')->insert($newdata);
                }
                Db::name('revit_temp')->whereIn('guid',$res)->update(['status'=>2]);
                $revalue =RevitModel::whereIn('guid',$guid)->count();
            }

            Db::commit();
        }catch (Exception $e){
            Db::rollback();
            $this->error($e->getMessage());
        }

        if ($revalue == 0){
            $this->success('导入成功',$revalue);
        }elseif($revalue > 0){
            $this->success('导入成功,有重复数据',$revalue);
        }else{
            $this->error('导入失败',$revalue);
        }

    }

    //更新字段表中的数据
    public function updateField($field){
        Field::where('status',0)->delete();
        $oldfield = Field::column('name');
        $newfield = array_diff($field,$oldfield);
        foreach ($newfield as $value)
        {
            $data = [ 'name' => $value];
            $res = Field::insertGetid($data);
            Field::where('id',$res)->update(['field'=> 'field'.$res]);
        }
    }



    //创建temp
    private static function createTemp($field){
        $isTable = db()->query("SHOW TABLES LIKE 'revit_temp'" );  //查询temp表是否存在
        $newfield = Field::wherein('name',$field)->column('field','name');

        if ($isTable) {
            Db::execute("DROP TABLE `revit_temp`"); //删除temp表
        }

        Db::execute("
                CREATE TABLE `revit_temp` (
                  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
                  `status` enum('1','2') NOT NULL DEFAULT '1' COMMENT '状态:1=正常,2=重复',
                  PRIMARY KEY (`id`) USING BTREE
                ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='revit_temp';");

        foreach ($newfield as $key => $value){
            Db::execute("
                ALTER TABLE `revit_temp` ADD `{$value}` varchar(255)  COMMENT '{$key}';
                            ");
        }
    }

    //创建字段
    public function addfield(){
        $field = Field::where('status',0)->column('field','name');
        foreach ($field as $key => $value){
            Db::execute("
                ALTER TABLE `revit` ADD `{$value}` varchar(255)  COMMENT '{$key}';
                            ");
        }
        Field::where('status',0)->update(['status'=>1]);
    }


    //添加重复数据
    public function add_duplicate(){
        $type = input("type");
        $this->addfield();
        $user_id = $this->getUserId();

        if ($type == '1'){
            $res = Db::name('revit_temp')->select(); //替换已有的
        }else{
            $res = Db::name('revit_temp')->where('status',1)->select(); //保留已有的
        }

        foreach ($res as $key => $value){
            unset($value['id']);
            $value['createtime'] = time();
            $value['user_id'] = $user_id;
            if ($value['status'] == 2){
                unset($value['status']);
                RevitModel::where('guid',$value['guid'])->update($value);
            }else{
                unset($value['status']);
                RevitModel::insert($value);
            }
        }
        $this->success('成功');
    }



    public function upload()
    {
        $file = request()->file('file');

        // 移动到框架应用根目录/public/uploads/ 目录下
        if($file){
            $info = $file->move(ROOT_PATH . 'public' . DS . 'uploads\revit');
            if($info){
                $return['status'] =1;
                $return['msg'] = "上传成功";
//                $return['path'] = 'C:\phpstudy_pro\WWW\mdpadmin\public\uploads\revit\\'.$info->getSaveName();
                $return['path'] = 'C:\phpstudy_pro\WWW\mdpadmin\public\uploads\revit\\'.$info->getSaveName();

                return $return;
            }else{
                // 上传失败获取错误信息
                throw new ParameterException(['msg'=>$file->getError()]);

            }
        }else{
            throw new ParameterException(['msg'=>'请上传xlsx文件']);
        }
    }


}