thinkphp5 (tp5)导入excel 实例

必须要先下载一个插件,即https://github.com/PHPOffice/PHPExcel
解压后,里面有个classes文件夹,我们需要把classes文件夹名修改为为PHPExcel

然后把他复制到根目录中的extend目录下。

然后,在控制器中这样引入

import('phpexcel.PHPExcel', EXTEND_PATH);

也可以放在根目录中的/vendor/目录中
引入方法为

vendor("PHPExcel.PHPExcel");

一、模板中这样写

<form action="{:url('index/upload_excel')}"  method="post" enctype="multipart/form-data">
           <input name='excel'  type='file'/>
           <input type='submit' value="excel导入,提交"/>
</form><br>

控制器中这样

//导入Excel
    public function upload_excel() {
        $request = \think\Request::instance();
        $file = $request->file('excel');

        $save_path = ROOT_PATH . '/public/uploads/excel/';
        $info = $file->move($save_path);
       // print_r($info);exit;

        $filename=$save_path . DIRECTORY_SEPARATOR . $info->getSaveName();
        if(file_exists($filename)) {//如果文件存在
            import('phpexcel.PHPExcel', EXTEND_PATH);

            if( strstr($filename,'.xlsx'))
            {
                $PHPReader = new \PHPExcel_Reader_Excel2007();
            }
            else
            {
                $PHPReader = new \PHPExcel_Reader_Excel5();
            }

            //载入excel文件
            $PHPExcel = $PHPReader->load($filename);
            $sheet = $PHPExcel->getActiveSheet(0);//获得sheet
            $highestRow = $sheet->getHighestRow(); // 取得共有数据数
            $data=$sheet->toArray();
            array_shift($data);  //删除第一个数组(标题);
            dump($data);
            $excelList = [];
            $i=0;
            foreach($data as $k=>$v) {
                $excelList[$k]['product_id'] = $v[0];
                $excelList[$k]['user_id'] = $v[1];
                $excelList[$k]['kefu_id'] = $v[2];
                $excelList[$k]['nip'] = $v[3];
                $excelList[$k]['ip'] = $v[4];
                $excelList[$k]['ips'] = $v[5];
                $excelList[$k]['manager_port'] = $v[6];
                $excelList[$k]['manager_name'] = $v[7];
                $excelList[$k]['manager_pass'] = $v[8];
                $excelList[$k]['buy_money'] = $v[9];
                $excelList[$k]['repaymoney'] = $v[10];
                $excelList[$k]['ipmi'] = $v[11];
                $excelList[$k]['ipmi_ip'] = $v[12];
                $excelList[$k]['ipmi_name'] = $v[13];
                $excelList[$k]['ipmi_pass'] = $v[14];
                $excelList[$k]['remark'] = $v[15];
                $excelList[$k]['admin_remark'] = $v[16];
                $excelList[$k]['start_time'] = strtotime($v[17]);
                $excelList[$k]['end_time'] = strtotime($v[18]);
                $excelList[$k]['status'] = $v[19];
                Db::name("server_nip")->where('id',$v[3])->update(['status'=>1,'product_id'=>$v[0]]);
                $i++;
            }
                dump($excelList);die;
            $dataRes = Db::name("server_order")->insertAll($excelList,true);
            if ($dataRes){
                $this->success('上传成功' . count($excelList) . '条数据');
            }else{
                $this->error('内容为空');
            }
            // return $dataRes;
        }else{
            $this->error('文件不存在');
        }

    }