后台导入导出 到 Execl

首先安装PHPexecl扩展

composer require phpoffice/phpexcel

 

导出

    HTML

<from>
    <input class="layui-input" placeholder="开始日" name="start" id="start">
        <input class="layui-input" placeholder="截止日" name="end" id="end">
        <button class="layui-btn" onclick="exel()">导出</button>
</from>

Js

function exel(){
        var start = $('#start').val();
        var end = $('#end').val();

        if(start == '' || end == ''){
            layer.msg('请输入开始日或截止日!', {icon: 5});

            return false;
        }
        window.location.href = '/public/index.php/Index/Report/exel?start=' + start + '&end=' + end;
        // $.get('/public/index.php/Index/User/exel' , {start:start,end:end} , function(res){
        //     console.log(res)
        // })
    }

php

public  function exel(){

        $start = strtotime(input('start'));

        $end = strtotime(input('end'));

        $data = Db::table(self::TABLE)
            -> alias('a')
            -> join('admin_analyze b' , 'a.id = b.sample_code_id')
            -> join('admin_datas c' , 'a.uid = c.id')
            -> field('a.* , b.probiotics,b.bacterium,b.result,b.level,b.report,c.tell')
            -> where( 'a.schedule','eq','100%')
            -> where('a.c_time' , 'between' , [$start , $end] )
            -> select();
        //echo DB::table(self::TABLE)->getlastsql();
        //print_r($data);die;
        foreach ($data as $k => $v){

            $data[$k]['c_time'] = date('Y-m-d H:i:s' , $v['c_time']);
            $data[$k]['no5'] = str_replace('+' ,'   ' ,$v['no5']);
            $data[$k]['no8'] = str_replace('+' ,'   ' ,$v['no8']);
            $data[$k]['no9'] = str_replace('+' ,'   ' ,$v['no9']);
            $data[$k]['no10'] = str_replace('+' ,'   ' ,$v['no10']);
            unset($data[$k]['password']);
        }
        //print_r($data);die;
        $this -> excel($data);
    }


//导出方法
    public function excel($list){
        ob_start();
        $objPHPExcel = new \PHPExcel();
        // 设置sheet
        $objPHPExcel->setActiveSheetIndex(0);
        // 设置列的宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(50);
//        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(50);

        // 设置表头
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'id');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', '样本号');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', '电话');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', '姓名');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', '性别');
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', '身高');
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', '体重');
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', '出生日期');
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', '采样日期');
        $objPHPExcel->getActiveSheet()->SetCellValue('J1', '本次取样特征');
        $objPHPExcel->getActiveSheet()->SetCellValue('K1', '您平时是否使用营养补充剂');
        $objPHPExcel->getActiveSheet()->SetCellValue('L1', '您饮食的喜好习惯-口味');
        $objPHPExcel->getActiveSheet()->SetCellValue('M1', '您饮食的喜好习惯-干稀度');
        $objPHPExcel->getActiveSheet()->SetCellValue('N1', '进度');
        $objPHPExcel->getActiveSheet()->SetCellValue('O1', '健康指数');
        $objPHPExcel->getActiveSheet()->SetCellValue('P1', '健康描述');
//        $objPHPExcel->getActiveSheet()->SetCellValue('Q1', '预计天数');
        $objPHPExcel->getActiveSheet()->SetCellValue('R1', '益生菌');
        $objPHPExcel->getActiveSheet()->SetCellValue('S1', '致病菌');
        $objPHPExcel->getActiveSheet()->SetCellValue('T1', '菌群含量结果');
        $objPHPExcel->getActiveSheet()->SetCellValue('U1', '代谢水平');
        $objPHPExcel->getActiveSheet()->SetCellValue('V1', '报告参考');
        $objPHPExcel->getActiveSheet()->SetCellValue('W1', '时间');
        $objPHPExcel->getActiveSheet()->SetCellValue('X1', 'Pdf报告');

        //存取数据
        $num = 2;
        foreach ($list as $k => $v) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $num, $v['id']);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $num, $v['sample_code']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $num, $v['tell']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $num, $v['name']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $num, $v['sex']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $num, $v['stature']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $num, $v['weight']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $num, $v['date_brith']);
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $num, $v['no4']);
            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $num, $v['no5']);
            $objPHPExcel->getActiveSheet()->SetCellValue('K' . $num, $v['no8']);
            $objPHPExcel->getActiveSheet()->SetCellValue('L' . $num, $v['no9']);
            $objPHPExcel->getActiveSheet()->SetCellValue('M' . $num, $v['no10']);
            $objPHPExcel->getActiveSheet()->SetCellValue('N' . $num, $v['schedule']);
            $objPHPExcel->getActiveSheet()->SetCellValue('O' . $num, $v['health']);
            $objPHPExcel->getActiveSheet()->SetCellValue('P' . $num, $v['describe']);
//            $objPHPExcel->getActiveSheet()->SetCellValue('Q' . $num, $v['numday']);
            $objPHPExcel->getActiveSheet()->SetCellValue('R' . $num, $v['probiotics']);
            $objPHPExcel->getActiveSheet()->SetCellValue('S' . $num, $v['bacterium']);
            $objPHPExcel->getActiveSheet()->SetCellValue('T' . $num, $v['result']);
            $objPHPExcel->getActiveSheet()->SetCellValue('U' . $num, $v['level']);
            $objPHPExcel->getActiveSheet()->SetCellValue('V' . $num, $v['report']);
            $objPHPExcel->getActiveSheet()->SetCellValue('W' . $num, $v['c_time']);
            $objPHPExcel->getActiveSheet()->SetCellValue('X' . $num, $v['file']);
            $num++;
        }

        // 文件名称
        $fileName = "激活码" . date('Y-m-d', time()) . rand(1, 1000);
        $xlsName = iconv('utf-8', 'gb2312', $fileName);
        // 设置工作表名
        $objPHPExcel->getActiveSheet()->setTitle('sheet');
        //下载 excel5与excel2007
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        //dump($objWriter);die;
        ob_end_clean();     // 清除缓冲区,避免乱码
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl;charset=UTF-8");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header("Content-Disposition:attachment;filename=" . $xlsName . ".xls");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save("php://output");
    }

 

 

导入

HTML

 <form action="/public/index.php/Index/Schedule/upload_excel" method="post" enctype="multipart/form-data" onsubmit="return toVaild()">

                选择文件：<input type="file" name="file" id="ff"><input type="submit"  class="layui-btn" value="导入">

            </form>

Js

//验证选择文件
    function toVaild(){
            var val = document.getElementById("ff").value;

            if(val != ""){
                return true;
            }
            else{
                alert("没有选择文件，不能提交");
                 return false;
            }
    }

PHP

//上传exel
    public function upload_excel(){
        //设置文件上传的最大限制
        ini_set('memory_limit','1024M');
        //加载第三方类文件
        //Loader::import("PHPExcel.Classes.PHPExcel");
        //防止乱码
        header("Content-type:text/html;charset=utf-8");
        //实例化主文件
        $model = new \PHPExcel();
        //接收前台传过来的execl文件
        $file = $_FILES['file'];

        if($file == ''){
            echo "未选择文件         <a href='../Schedule/schedule_list'>返回</a>";die;
        }
        //截取文件的后缀名，转化成小写
        $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']);
        $sheetContent = $objContent -> getSheet(0) -> toArray();
        unset($sheetContent[0]);
        //echo '<pre/>';
        //print_r($sheetContent);die;
        foreach ($sheetContent as $k => $v){
            //$arr['username'] = $v[0];
            $arr['sample_code'] = $v[0];
            $arr['c_time'] = time();
            //$arr['class'] = $v[3];
            $res[] = $arr;
        }
        $res = Db::name('admin_sample_code') -> insertAll($res);
        if($res){
            echo "导入成功         <a href='上页'>返回</a>";
        }else{
            echo "导入失败         <a href='上页>重新导入</a>";
        }
    }