TP5, 带你飞 ~~ 导入\导出

  今天来给大家分享一个特别好用的 Excel导入\导出,亲测哦!

 

  一  导入

function impExcel()
    {
        vendor("phpexcel.PHPExcel"); //下载PHPExcel类
        //获取表单上传文件
        $file = request()->file('excel'); 
        $info = $file->validate(['ext' => 'xlsx'])->move(ROOT_PATH . 'public' . DS . 'uploads');
        if ($info) {
             //echo $info->getFilename();die;
            $exclePath = $info->getSaveName();  //获取文件名
            $file_name = ROOT_PATH . 'public' . DS . 'uploads' . DS . $exclePath;   //上传文件的地址
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
            $obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
            echo "<pre>";
            $excel_array = $obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
            array_shift($excel_array);  //删除第一个数组(标题);
            $city = [];
            foreach ($excel_array as $k => $v) {
                $city[$k]['username'] = $v[0];
                $city[$k]['password'] = '******';
                $city[$k]['email'] =$this->email();
                $city[$k]['salt'] =$this->foo();
                $city[$k]['regdate'] = '2017-7-14 12:01:27';
            }
//            var_dump($city);die;
            $add=Db::name('user')->insertAll($city); //批量插入数据
            //var_dump($add);die;
            if($add){
                $this->success('添加成功');
            }else{
                $this->error('失败');
            }
        } else {
            echo $file->getError();
        }
    }

 

 

 

二  导出

function expExcel()
    {
        vendor("phpexcel.PHPExcel");
        $userName=db('dis_ucenter_members')->field('uid,username')->select();
        $PHPExcel = new \PHPExcel();//实例化
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle("demo"); //给当前活动sheet设置名称
       //$PHPSheet->setCellValue("A1","ID")->setCellValue("B1","username");//表格数据
       //$PHPSheet->setCellValue("A2","001")->setCellValue("B2","元宝");//表格数据
        $i=1;
        foreach($userName as $key=>$val){
               $PHPSheet->setCellValue('A'.$i,$val['uid'])->setCellValue('B'.$i,$val['username']);//表格数据
               $i++;
           }
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");//创建生成的格式
        header('Content-Disposition: attachment;filename="userName.xlsx"');//下载下来的表格名
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }

三 随机字母数字

 

 

 

 
public function foo($size=6) {
        $dict = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $len = $size == 16 ? 36 : mb_strlen($dict);
        $res = '';
        for($i=0; $i<$size; $i++) $res .= $dict{rand(0, $len - 1)};
         //查找数据库是否存在
        $str=db('user')->where('salt',$res)->find();
         if($str){
             $this->foo();
         }else{
             return $res;
         }
 
   }