使用layui框架导出table表为excel(Use the layui framework to export the table to excel)

1.当分页导出table时,只需要添加导出按钮,并给按钮添加onclick事件触发以下函数

              //文件导出
        function exportFile() {
        var bodys = $("div[class='layui-form layui-border-box layui-table-view'] .layui-table-box table").get(1); // 获取数据
        var btrs = Array.from(bodys.querySelectorAll("tr"))
        if(btrs==''){
            alert('数据为空,无法导出excel文件');
        }else{
        var btdslength = Array.from(btrs[0].querySelectorAll("td")).length;
        var headers = $("div[class='layui-form layui-border-box layui-table-view'] .layui-table-box table").get(0); // 获取表头
        var headerHead = $("div[class='layui-form layui-border-box layui-table-view'] .layui-table-box table thead").get(0); // 获取表头
        var htrs = Array.from(headers.querySelectorAll('tr'));

        var bodysArr = new Array();
        var point =new Array();  // 行,列
        for(var pi =0; pi<=htrs.length+1; pi++){
            point[pi] = new Array();
        }
        point[0][0] ="qd"; // 起点
        var mergeArr = [];
        for (var j = 0; j < htrs.length; j++) {    // 遍历tr
            var titles = [];
            var hths = Array.from(htrs[j].querySelectorAll("th"));
            var titleAll = {};
            var pointIndex = 0;
            var pindx = 0;  // 起点遍历位置
            for (var i = 1; i < hths.length-1; i++) {  // 遍历 th
                var clazz = hths[i].getAttributeNode('class');
                var colspan = hths[i].getAttributeNode('colspan'); // 表头占用列数
                var rowspan = hths[i].getAttributeNode('rowspan'); //,表头占用行数
                if(!colspan){
                    colspan = 1;
                }else{
                    colspan = parseInt(colspan.value);
                }
                if(!rowspan){
                    rowspan = 1;
                }else{
                    rowspan = parseInt(rowspan.value);
                }
                // 判断数据起始填写位置
                for(;pindx < btdslength; pindx ++){
                    if(j == 0 || point[j][pindx] == "qd"){
                        titles.push(hths[i].innerText);
                        for(var temp = 0; temp < colspan-1;temp++){
                            titles.push(null);
                        }
                        mergeArr.push({s:{r:j,c:pindx},e:{r:j+rowspan-1,c:pindx+colspan-1}}); // 添加合并数据参数  r的差R表示向下扩展R个单元格,c 的差C表示想右扩展C个单元格
                        for(var qdi = 0; qdi<colspan ;qdi ++){
                            point[j+rowspan][pindx+qdi] = "qd"; // 添加完数据 ,添加起点记录
                        }
                        pindx = pindx+colspan;
                        break;
                    }else{
                        titles.push(""); // 不能为null, 为null 会影响表格样式的设置
                    }
                }
            }
            bodysArr.push(titles);
        }
        var widthArr = []; //这里改宽
        for (var j = 0; j < btrs.length; j++) {
            var contents = [];
            var btds = Array.from(btrs[j].querySelectorAll("td"));
            for (var i = 1; i < btds.length-1; i++) {
                contents.push(btds[i].innerText);
                if(j == 0){  //只跑一圈
                    widthArr.push({wpx:btds[i].scrollWidth});
                }
            }
            bodysArr.push(contents)
        } 
        //设置表格样式
        var styleStr = {
            alignment:{
                vertical:'center',
                horizontal:'center'
            },
            font:{
                sz:14,
                // bold:true
            },
            border:{
                top:{
                    style:'thin'
                },
                bottom:{
                    style:'thin'
                },
                left:{
                    style:'thin'
                },
                right:{
                    style:'thin'
                }
            }
        }
        var datas = [];
        for(var i = 0; i<bodysArr.length;i++){
            var map ={};
            var thisData = bodysArr[i];
            for(var n = 0;n<thisData.length;n++){
                var dataName = "data_"+n;
                var das = thisData[n];
                var styMap = {};
                styMap['s'] = styleStr;
                styMap['v'] = das;
                map[dataName] = styMap;
            }
            datas.push(map);
        }
        
        var rowConf = excel.makeRowConfig({
            1: 40,
            3: 30
        }, 20)
        excel.exportExcel({
            sheet: datas
        }, '专家表-' + new Date().toLocaleString() + '.xlsx', 'xlsx', {
            extend: {
                sheet: {
                    '!merges': mergeArr
                    , '!cols': widthArr
                    , '!rows': rowConf
                }
            }
        })  
        }
    } 

2.分页的table数据,导出全部数据,需要后台将数据全部给前台

给导出按钮,添加一个id,点击通过ajax获取全部数据,成功后添加

dataList = eval(data.data);//data.data后台传入的数据
table.exportFile(d.config.id, dataList, 'xls');//d=table.render

table.exportFile(col,data , ‘xls’);

col为组装自定义表头数组,

data为重新请求的完整表格数据,

xls为导出文件类型。

3.不想将数据全部导出,只导出table表格的几列,可以写一个隐藏的table表格

<div style="display:none">
     <table id="tabExport"></table>
</div>

给导出按钮,添加一个id,点击通过ajax获取全部数据

$("#btnExport").click(function (){ var d = table.render({
                    elem: '#tabExport',
                    url: "/data/listall", //数据接口
                    method: 'post',
                    title: '导出表的名字',
                    where:{
                        name:name,
                    },
                    cols: [[
                        ,{field:'name', title: '', align:'center'}
                          ,{field:'major', title: '', align:'center'} 
                          ,{field:'user', title: '', align:'center'} 
                    ]],
                    done: function (data) {
                        dataList = eval(data.data);
                        table.exportFile(d.config.id, dataList, 'xls');
                    }
                });
            })
            

//导出时阻止页面刷新 $(“.layui-form”).submit(function (e) { e.preventDefault(); })

————————

1. When exporting a table by page, you only need to add an Export button and add an onclick event to the button to trigger the following functions

              //文件导出
        function exportFile() {
        var bodys = $("div[class='layui-form layui-border-box layui-table-view'] .layui-table-box table").get(1); // 获取数据
        var btrs = Array.from(bodys.querySelectorAll("tr"))
        if(btrs==''){
            alert('数据为空,无法导出excel文件');
        }else{
        var btdslength = Array.from(btrs[0].querySelectorAll("td")).length;
        var headers = $("div[class='layui-form layui-border-box layui-table-view'] .layui-table-box table").get(0); // 获取表头
        var headerHead = $("div[class='layui-form layui-border-box layui-table-view'] .layui-table-box table thead").get(0); // 获取表头
        var htrs = Array.from(headers.querySelectorAll('tr'));

        var bodysArr = new Array();
        var point =new Array();  // 行,列
        for(var pi =0; pi<=htrs.length+1; pi++){
            point[pi] = new Array();
        }
        point[0][0] ="qd"; // 起点
        var mergeArr = [];
        for (var j = 0; j < htrs.length; j++) {    // 遍历tr
            var titles = [];
            var hths = Array.from(htrs[j].querySelectorAll("th"));
            var titleAll = {};
            var pointIndex = 0;
            var pindx = 0;  // 起点遍历位置
            for (var i = 1; i < hths.length-1; i++) {  // 遍历 th
                var clazz = hths[i].getAttributeNode('class');
                var colspan = hths[i].getAttributeNode('colspan'); // 表头占用列数
                var rowspan = hths[i].getAttributeNode('rowspan'); //,表头占用行数
                if(!colspan){
                    colspan = 1;
                }else{
                    colspan = parseInt(colspan.value);
                }
                if(!rowspan){
                    rowspan = 1;
                }else{
                    rowspan = parseInt(rowspan.value);
                }
                // 判断数据起始填写位置
                for(;pindx < btdslength; pindx ++){
                    if(j == 0 || point[j][pindx] == "qd"){
                        titles.push(hths[i].innerText);
                        for(var temp = 0; temp < colspan-1;temp++){
                            titles.push(null);
                        }
                        mergeArr.push({s:{r:j,c:pindx},e:{r:j+rowspan-1,c:pindx+colspan-1}}); // 添加合并数据参数  r的差R表示向下扩展R个单元格,c 的差C表示想右扩展C个单元格
                        for(var qdi = 0; qdi<colspan ;qdi ++){
                            point[j+rowspan][pindx+qdi] = "qd"; // 添加完数据 ,添加起点记录
                        }
                        pindx = pindx+colspan;
                        break;
                    }else{
                        titles.push(""); // 不能为null, 为null 会影响表格样式的设置
                    }
                }
            }
            bodysArr.push(titles);
        }
        var widthArr = []; //这里改宽
        for (var j = 0; j < btrs.length; j++) {
            var contents = [];
            var btds = Array.from(btrs[j].querySelectorAll("td"));
            for (var i = 1; i < btds.length-1; i++) {
                contents.push(btds[i].innerText);
                if(j == 0){  //只跑一圈
                    widthArr.push({wpx:btds[i].scrollWidth});
                }
            }
            bodysArr.push(contents)
        } 
        //设置表格样式
        var styleStr = {
            alignment:{
                vertical:'center',
                horizontal:'center'
            },
            font:{
                sz:14,
                // bold:true
            },
            border:{
                top:{
                    style:'thin'
                },
                bottom:{
                    style:'thin'
                },
                left:{
                    style:'thin'
                },
                right:{
                    style:'thin'
                }
            }
        }
        var datas = [];
        for(var i = 0; i<bodysArr.length;i++){
            var map ={};
            var thisData = bodysArr[i];
            for(var n = 0;n<thisData.length;n++){
                var dataName = "data_"+n;
                var das = thisData[n];
                var styMap = {};
                styMap['s'] = styleStr;
                styMap['v'] = das;
                map[dataName] = styMap;
            }
            datas.push(map);
        }
        
        var rowConf = excel.makeRowConfig({
            1: 40,
            3: 30
        }, 20)
        excel.exportExcel({
            sheet: datas
        }, '专家表-' + new Date().toLocaleString() + '.xlsx', 'xlsx', {
            extend: {
                sheet: {
                    '!merges': mergeArr
                    , '!cols': widthArr
                    , '!rows': rowConf
                }
            }
        })  
        }
    } 

2. For paged table data, export all data. The background needs to send all data to the foreground

Add an ID to the Export button, click to obtain all data through Ajax, and add it after success

dataList = eval(data.data);//data.data后台传入的数据
table.exportFile(d.config.id, dataList, 'xls');//d=table.render

table.exportFile(col,data , ‘xls’);

Col is a custom header array for assembly,

Data is the complete table data of the re request,

Xls is the export file type.

3. You don’t want to export all the data, but only a few columns of the table table can be exported. You can write a hidden table

<div style="display:none">
     <table id="tabExport"></table>
</div>

Add an ID to the Export button, and click Ajax to get all the data

$("#btnExport").click(function (){ var d = table.render({
                    elem: '#tabExport',
                    url: "/data/listall", //数据接口
                    method: 'post',
                    title: '导出表的名字',
                    where:{
                        name:name,
                    },
                    cols: [[
                        ,{field:'name', title: '', align:'center'}
                          ,{field:'major', title: '', align:'center'} 
                          ,{field:'user', title: '', align:'center'} 
                    ]],
                    done: function (data) {
                        dataList = eval(data.data);
                        table.exportFile(d.config.id, dataList, 'xls');
                    }
                });
            })
            

//Prevent page refresh during export $(“. Layui form”) submit(function (e) { e.preventDefault(); })