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

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初始化的东西，访问///reference/button/excelHtml5var 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 downvar clRow = $('row', sheet);$(clRow[0]).find('c').attr('s', 32);//update RowclRow.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 rowsvar 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 1var r2 = addRow(2, [{ k: 'A', v: '光功率偏差越限:' }, { k: 'B', v: str_b }]); //add two cells for row 2-4var 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个小功能点，有错误请指正，有问题请提问，谢谢。