TP5导入EXCEL到数据库

前期准备工作:

1.下载PHPExcel放到vendor下

 

 2.前端页面:

  <form action="save" method="post" enctype="multipart/form-data">
            <div class="time-add clearfix">
                <input type="submit" disabled name="submit" class="submit" value="提交" />
                <a class="dianji" href="javascript:;">
                    <input type="file" name="file" id="fileupload" class="inputfile" style="position: absolute;display: none;"/>
                    <label class="span" for="fileupload">批量导入用餐记录</label>
                </a>
            </div>
        </form>

3.后端代码

public function save(){

        //设置文件上传的最大限制
        ini_set(‘memory_limit‘,‘1024M‘);
        //加载第三方类文件
        vendor("PHPExcel.PHPExcel");
        //防止乱码
        header("Content-type:text/html;charset=utf-8");
        //实例化主文件
        //接收前台传过来的execl文件
        $file = $_FILES[‘file‘];
        //截取文件的后缀名，转化成小写
        $extension = strtolower(pathinfo($file[‘name‘],PATHINFO_EXTENSION));
        if($extension == "xlsx"){
            //2007(相当于是打开接收的这个excel)
            $objReader =PHPExcel_IOFactory::createReader(‘Excel2007‘);
        }else{
            //2003(相当于是打开接收的这个excel)
            $objReader = PHPExcel_IOFactory::createReader(‘Excel5‘);
        }

        $objContent = $objReader -> load($file[‘tmp_name‘]);

        if ($objContent){
            $sheetContent = $objContent -> getSheet(0) -> toArray();
            //删除第一行标题
            unset($sheetContent[0]);
            $time= $mothtime= strtotime(date("Y-m-d H:i:s", strtotime("-1 month")));
            foreach ($sheetContent as $k => $v){
                $arr[‘name‘] = $v[1];
                $arr[‘card_number‘] = $v[2];
                $arr[‘department_name‘] = $v[3];
                $arr[‘breakfast‘] = $v[4];
                $arr[‘lunch‘] = $v[5];
                $arr[‘dinner‘] = $v[6];
                $arr[‘supper‘] = $v[7];
                $arr[‘total‘] = $v[8];
                $arr[‘create_time‘] = $time;
                $res[] = $arr;
            }
            //删除最后一行汇总
            array_pop($res);

            $re = Db::name(‘dining‘)
                ->insertAll($res);
            if($re){
                $this->success(‘导入成功 !‘);
            }else{
                $this->error(‘导入失败 !‘);
            }
        }else{
            $this->error(‘请导入表格 !‘);
        }
    }