Datatable 表格背景色与导出Excel时有背景色

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/linkedin_37345339/article/details/78291390

1.需求描述

    最近有这样一个需求,在页面用表格展示数据的时候,赋予相应的背景色,并在导出表格生成excel文件时有对应的背景色,现用jQuery Datatable这款插件完成这项功能。


2.分析

    1在页面初始化表的时候,需要有背景色

    2在生成excel表时候,需要有背景色


3.代码讲解

1.页面需要导入的js

<script src="DataTables-1.10.15/media/js/jquery.dataTables.min.js"></script>
<script src="DataTables-1.10.15/media/js/dataTables.bootstrap4.js"></script>
<link rel="stylesheet" href="DataTables-1.10.15/media/css/dataTables.bootstrap4.css">

<script src="DataTables-1.10.15/extensions/Buttons/js/dataTables.buttons.js"></script>
<link rel="stylesheet" href="DataTables-1.10.15/extensions/Buttons/css/buttons.dataTables.css">

<script src="DataTables-1.10.15/extensions/Buttons/js/buttons.html5.js"></script>

<script src="DataTables-1.10.15/extensions/Buttons/js/jsZIP.js"></script>
<link rel="stylesheet" href="DataTables-1.10.15/extensions/Buttons/css/buttons.bootstrap4.css">
显然我的Datatable版本是1.10.15的

其中除了jsZIP以外,其它的datatable包中都有.


2.html

<table id="table" class="table table-striped m-b-none" style="">
    <thead>
    <tr>
        <th>波长编号</th>
        <th>标准波长(nm)/频率(THz)</th>
        <th>中心波长(nm)/频率(THz)</th>
        <th>波长偏差(nm)</th>
        <th>光功率(dBm)</th>
        <th>OSNR(dB)</th>
    </tr>
    </thead>
    <tbody id="table_tbody">
    <tr class='odd'><td colspan='6' class='dataTables_empty' valign='top'>Loading...</td></tr>
    </tbody>
</table>


3.表格初始化单元格有背景色代码以及讲解

$("#table").DataTable({
    "columns": [
        { "data": "dwdmNumber" },
        { "data": "swlf" },
        { "data": "cwlf" },
        { "data": "wldeviation" },
        { "data": "opticalPower" },
        { "data": "osnr" }
    ],
    data:main_light_data,
    dom: 'Bfrtip',
    "createdRow": function ( row, data, dataIndex )   {
        //console.log(data);

        $('td', row).eq(0).css('color','#58666e')

        if ( data.is_WLDeviation == "1" ) {
            //alert(1);
            //$('td', row).css('background-color', 'yellow');

            $('td', row).eq(3).css('backgroundColor','#c6cfef')
            $('td', row).eq(0).css('color','#fff')
        }
        if ( data.is_opticalPower == "1" ) {
            //alert(1);
            //$('td', row).css('background-color', 'yellow');

            $('td', row).eq(4).css('backgroundColor','#c6cfef')
            $('td', row).eq(0).css('color','#fff')
        }
        if ( data.is_OSNR == "1" ) {
            //alert(1);
            //$('td', row).css('background-color', 'yellow');

            $('td', row).eq(5).css('backgroundColor','#c6cfef')
            $('td', row).eq(0).css('color','#fff')
        }
    }
});
//此段代码可以实现表格在初始化时生成有背景色的单元格,其中主要部分为createdRow方法
//通过该方法的row,data这两个参数,可以进行一些判断,给单元格设置背景色

4.生成excel时设置背景色
$("#table").DataTable({
    "columns": [
        { "data": "dwdmNumber" },
        { "data": "swlf" },
        { "data": "cwlf" },
        { "data": "wldeviation" },
        { "data": "opticalPower" },
        { "data": "osnr" }
    ],
    data:main_light_data,
    dom: 'Bfrtip',
    buttons: [{
        extend: 'excelHtml5',
        customize: function(xlsx) {
            //想要在网上查看一些关于excel初始化的东西,访问
            //https://datatables.net/reference/button/excelHtml5
            var sheet = xlsx.xl.worksheets['sheet1.xml'];

            //这是给一整行上色
            //$('row:first c', sheet).attr( 's', '10' );
            /*$('row c[r^="F"]', sheet).attr( 's', '10' );*/


            $('row c[r^="A"]', sheet).each( function () {
                //给单元格做判断上色

                for(var i=0;i<main_light_data.length;i++){
                    if($(this).text()==main_light_data[i].dwdmNumber){
                        if(main_light_data[i].is_OSNR=="1"){
                            $(this).next().next().next().next().next().attr( 's', '45' );
                        }
                        if(main_light_data[i].is_opticalPower=="1"){
                            $(this).next().next().next().next().attr( 's', '45' );
                        }
                        if(main_light_data[i].is_WLDeviation=="1"){
                            $(this).next().next().next().attr( 's', '45' );
                        }
                    }

                }
                /*if ( $(this).text() >= 30 && $(this).text() < 40) {
                 $(this).attr( 's', '20' );
                 }
                 if ( $(this).text() >= 40) {
                 $(this).attr( 's', '10' );
                 }*/
            });
            var excel_length = $('row c[r^="A"]', sheet).length;
            var numrows = 4;

            // add styles for the column header, these row will be moved down
            var clRow = $('row', sheet);
            $(clRow[0]).find('c').attr('s', 32);

            //update Row
            clRow.each(function () {
                var attr = $(this).attr('r');
                var ind = parseInt(attr);
                ind = ind + numrows;
                $(this).attr("r", ind);
            });

            // Create row before data
            $('row c ', sheet).each(function(index) {
                var attr = $(this).attr('r');

                var pre = attr.substring(0, 1);
                var ind = parseInt(attr.substring(1, attr.length));
                ind = ind + numrows;
                $(this).attr("r", pre + ind);
            });

            function addRow(index, data) {
                var row = sheet.createElement('row');
                row.setAttribute("r", index);
                for (i = 0; i < data.length; i++) {
                    var key = data[i].k;
                    var value = data[i].v;

                    var c  = sheet.createElement('c');
                    c.setAttribute("t", "inlineStr");
                    c.setAttribute("s", "2");  /*set specific cell style here*/
                    c.setAttribute("r", key + index);

                    var is = sheet.createElement('is');
                    var t = sheet.createElement('t');
                    var text = sheet.createTextNode(value)

                    t.appendChild(text);
                    is.appendChild(t);
                    c.appendChild(is);

                    row.appendChild(c);
                    debugger;
                }

                return row;
            }

            //add data to extra rows
            var str_a = result.data2.wldeviation_cNum+'条';
            var str_b = result.data2.opticalPower_cNum+'条';
            var str_c = result.data2.opticalPower_avg+'dBm';
            var str_d = result.data2.osnr_cNum+'条';

            var r1 = addRow(1, [{ k: 'A', v: '波长偏差越限:' }, { k: 'B', v: str_a }]);   //add one cell for row 1
            var r2 = addRow(2, [{ k: 'A', v: '光功率偏差越限:' }, { k: 'B', v: str_b }]); //add two cells for row 2-4
            var r3 = addRow(3, [{ k: 'A', v: '光功率平均值:' }, { k: 'B', v: str_c }]);
            var r4 = addRow(4, [{ k: 'A', v: 'OSNR越限:' }, { k: 'B', v: str_d }]);

            var sheetData = sheet.getElementsByTagName('sheetData')[0];
            sheetData.insertBefore(r4,sheetData.childNodes[0]);
            sheetData.insertBefore(r3,sheetData.childNodes[0]);
            sheetData.insertBefore(r2,sheetData.childNodes[0]);
            sheetData.insertBefore(r1,sheetData.childNodes[0]);



        }

    }],
    "createdRow": function ( row, data, dataIndex )   {
        //console.log(data);

        $('td', row).eq(0).css('color','#58666e')

        if ( data.is_WLDeviation == "1" ) {
            //alert(1);
            //$('td', row).css('background-color', 'yellow');

            $('td', row).eq(3).css('backgroundColor','#c6cfef')
            $('td', row).eq(0).css('color','#fff')
        }
        if ( data.is_opticalPower == "1" ) {
            //alert(1);
            //$('td', row).css('background-color', 'yellow');

            $('td', row).eq(4).css('backgroundColor','#c6cfef')
            $('td', row).eq(0).css('color','#fff')
        }
        if ( data.is_OSNR == "1" ) {
            //alert(1);
            //$('td', row).css('background-color', 'yellow');

            $('td', row).eq(5).css('backgroundColor','#c6cfef')
            $('td', row).eq(0).css('color','#fff')
        }

    }
    //lengthChange : true
});

//其中数据相关的用到了一些angularJS的内容,改吧改吧就能用,

5.测试数据

var data1 =[
        {
            "serial": "1",
            "dwdmNumber": "1",
            "is_WLDeviation": "0",
            "opticalPower": "-1.5",
            "is_opticalPower": "1",
            "is_OSNR": "1",
            "swlf": "1529.16/196.050",
            "cwlf": "1529.18/196.048",
            "osnr": "32.0",
            "wldeviation": "0.02"
        },
        {
            "serial": "2",
            "dwdmNumber": "2",
            "is_WLDeviation": "0",
            "opticalPower": "-1.4",
            "is_opticalPower": "1",
            "is_OSNR": "0",
            "swlf": "1529.55/196.000",
            "cwlf": "1529.56/195.999",
            "osnr": "32.2",
            "wldeviation": "0.01"
        },
        {
            "serial": "3",
            "dwdmNumber": "3",
            "is_WLDeviation": "0",
            "opticalPower": "-1.5",
            "is_opticalPower": "1",
            "is_OSNR": "0",
            "swlf": "1529.94/195.950",
            "cwlf": "1529.96/195.948",
            "osnr": "32.1",
            "wldeviation": "0.02"
        },
        {
            "serial": "4",
            "dwdmNumber": "4",
            "is_WLDeviation": "0",
            "opticalPower": "-1.3",
            "is_opticalPower": "1",
            "is_OSNR": "0",
            "swlf": "1530.33/195.900",
            "cwlf": "1530.34/195.899",
            "osnr": "32.3",
            "wldeviation": "0.01"
        },
        {
            "serial": "5",
            "dwdmNumber": "5",
            "is_WLDeviation": "0",
            "opticalPower": "-1.1",
            "is_opticalPower": "1",
            "is_OSNR": "0",
            "swlf": "1530.72/195.850",
            "cwlf": "1530.74/195.848",
            "osnr": "32.5",
            "wldeviation": "0.02"
        },
        {
            "serial": "6",
            "dwdmNumber": "6",
            "is_WLDeviation": "0",
            "opticalPower": "-1.3",
            "is_opticalPower": "1",
            "is_OSNR": "0",
            "swlf": "1531.12/195.800",
            "cwlf": "1531.13/195.798",
            "osnr": "32.3",
            "wldeviation": "0.01"
        },
        {
            "serial": "7",
            "dwdmNumber": "7",
            "is_WLDeviation": "0",
            "opticalPower": "-1.5",
            "is_opticalPower": "1",
            "is_OSNR": "0",
            "swlf": "1531.51/195.750",
            "cwlf": "1531.53/195.747",
            "osnr": "32.1",
            "wldeviation": "0.02"
        },
        {
            "serial": "8",
            "dwdmNumber": "8",
            "is_WLDeviation": "0",
            "opticalPower": "-1.7",
            "is_opticalPower": "1",
            "is_OSNR": "1",
            "swlf": "1531.9/195.700",
            "cwlf": "1531.92/195.697",
            "osnr": "31.9",
            "wldeviation": "0.02"
        },
        {
            "serial": "9",
            "dwdmNumber": "9",
            "is_WLDeviation": "0",
            "opticalPower": "-2.0",
            "is_opticalPower": "1",
            "is_OSNR": "1",
            "swlf": "1532.29/195.650",
            "cwlf": "1532.31/195.647",
            "osnr": "31.7",
            "wldeviation": "0.02"
        }];


6.效果展示


  




以上为Datatable实现的2个小功能点,有错误请指正,有问题请提问,谢谢。

   

没有更多推荐了,返回首页