Layui 前端导出excel,可导出查询或者全部数据,导出字段可设置


每次遇到导出问题总会遇到各种要求,layui自带的导出满足不了需要,特此笔记记录一下:

首要用到的是layui.excel.js扩展,扩展文件如下(需要的自行下载):

具体代码如下(关键部分):

{__NOLAYOUT__}
<!DOCTYPE html>
<html class="x-admin-sm">
<head>
    <meta charset="UTF-8">
</head>
<body>
<div class="layui-fluid">
    <div class="layui-row layui-col-space15">
        <div class="layui-col-md12">
            <div class="layui-card">
                <div class="layui-card-body ">
                    <form class="layui-form layui-col-space5">
                        <div class="layui-inline layui-show-xs-block">
                            <input class="layui-input" autocomplete="off" placeholder="选择时间范围" name="daterange" id="daterange">
                        </div>
                        <div class="layui-inline layui-show-xs-block">
                            <input type="text" name="keywords" placeholder="请输入查询关键词" autocomplete="off" class="layui-input" value="" id="keywords">
                        </div>
                        <div class="layui-inline layui-show-xs-block">
                            <button class="layui-btn" data-type="reload" lay-submit="" lay-filter="search"><i class="layui-icon">&#xe615;</i></button>
                            <button type="button" lay-submit="" class="layui-btn layui-btn-warm" lay-filter="uploadImg">
                                <i class="layui-icon"></i>导出Excel</button>
                        </div>
                    </form>
                </div>
                <div class="layui-card-body layui-table-body layui-table-main">
                    <table class="layui-table layui-form" id="plist" lay-filter="admTable"></table>
                </div>
                <div class="layui-card-body ">
                </div>
            </div>
        </div>
    </div>
</div>
</body>
<style>
    tbody .layui-table-cell{height: auto;white-space:normal;}
    tbody .layui-table-cell{overflow:visible;}
</style>
<script>
    layui.config({
        base : '{$Think.config.systemConfig.domain}/static/okadmin/js/layui_exts/'
    }).extend({
        selectM: './excel',
    }).use(['laydate','form', 'table', 'excel'], function() {
        var table = layui.table,
            form = layui.form,
            $ = layui.jquery,
            laydate = layui.laydate;
        form.on('submit(search)',function(data) {
            table.reload('plist', {
                page: {
                    curr: 1 //重新从第 1 页开始
                },
                where: {
                    daterange: data.field.daterange,
                    keywords: data.field.keywords,
                    did: data.field.did,
                }
            });
            return false;
        });
        form.on('submit(uploadImg)', function(data){
            loading = layer.load(1, {shade: [0.3, '#fff']});
            var $ = layui.jquery;
            var excel = layui.excel;
            $.ajax({
                url: '{:url("getReportExcelList")}',
                dataType: 'json',
                data: {
                    daterange: data.field.daterange,
                    keywords: data.field.keywords,
                    did: data.field.did,
                },
                success: function(res) {
                    layer.close(loading);
                    layer.msg(res.msg);
                    // 假如返回的 res.data 是需要导出的列表数据
                    console.log(res.data);
                    // 1. 数组头部新增表头
                    res.data.unshift({id: 'ID',did:'部门',name:'姓名',mobile:'电话',idnumber:'身份证号',title: '标题',content:'内容',create_time:'提交时间'});
                    // 3. 执行导出函数,系统会弹出弹框
                    excel.exportExcel({
                        sheet1: res.data
                    }, '名字.xlsx', 'xlsx');
                },
                error:function(res){
                    layer.close(loading);
                    layer.msg(res.msg);
                }
            });
            return false;
        });
    });
</script>
</html>

PHP接口代码:(字段顺序要去前端设置头部相对应)

public function getReportExcelList(){
        try{
            $reportRecord_mod = new model\ReportRecord()
            $whereMap = [];
            $keywords = input('keywords','');
            $daterange = input('daterange','');
            $daterange = str_replace('+-+',' - ',$daterange);
            $did = input('did',0);
            if($keywords){
                $whereMap['name'] = ['like', '%'.$keywords.'%'];
            }
            if($daterange){
                $arrDate = explode(' - ',$daterange);
                // dump($arrDate);
                $statdate = strtotime($arrDate[0]);
                $enddate = strtotime($arrDate[1]);
                $whereMap['create_time'] = ['between time', [$statdate, $enddate]];
            }
            if(session('adminInfo.role_id') == 1){
                //超级管理员
            }else{
                $patrolTeam_mod = new model\PatrolTeam();
                $idArr = $patrolTeam_mod->where('aid',session('adminInfo.aid'))->field('id')->select();
                $ids = '';
                foreach ($idArr as $v){
                    $ids = $ids.$v['id'].',';
                }
                $whereMap['pid'] = array('in',rtrim($ids,','));
            }
            $patrolTeams = $reportRecord_mod
                ->order('id desc')
                ->field('id,did,name,mobile,idnumber,isAnonymous,title,content,create_time')
                ->where($whereMap)
                ->select();
            foreach($patrolTeams as $key => $val){
                $patrolTeams[$key]['did'] = $department_mod->where('id',$patrolTeams[$key]['did'])->value('name');
                $isAnonymous = $val['isAnonymous'];
                if(session('adminInfo.role_id') == 1){
                    //超级管理员
                }else{
                    //隐藏手机号码部分字段
                    if($isAnonymous == 0){
                        //隐私
                        if($val['name'] != ''){
                            $patrolTeams[$key]['name']= mb_substr($val['name'],0,1,'utf-8').'****';
                        }
                        if($val['mobile'] != ''){
                            $patrolTeams[$key]['mobile']=substr_replace($val['mobile'],'****',3,4);
                        }
                        if($val['idnumber'] != ''){
                            $idcard = $val['idnumber'];
                            $patrolTeams[$key]['idnumber']=strlen($idcard)==15?substr_replace($idcard,"****",8,4):(strlen($idcard)==18?substr_replace($idcard,"****",10,4):"身份证位数不正常!");
                        }
                    }
                }
                unset($patrolTeams[$key]['isAnonymous']);
            }
            $returnData['code'] = 0;
            $returnData['msg'] = '导出成功';
            $returnData['data'] = $patrolTeams;
            return json($returnData);
        }catch (Exception $e){
            $returnData['code'] = 1;
            $returnData['msg'] = '导出失败,请稍后重试!';
            return json($returnData);
        }
    }


上传的附件:
最新回复 (0)
发新帖