1.初始化
var table = $('#table').DataTable({ "data": data[0].DATA, "columns": data[0].COLUMNS })
后台返回前台data的JSON格式
[{ "COLUMNS": [{ "title": "1" }, { "title": "2" }], "DATA": [ ["1", "8758"], ["2", "8758"] ] }]
将DataTable加表头转换为JSON(flag是一个状态字段)(header是字符串数组:"标题1,标题2,标题3".Split(','))
private void DataTableToJSON(DataTable dt, string[] header, int flag, ref StringBuilder sb) { int total = dt.Rows.Count; string tempcol = null; string kong = null; for (int i = 0; i < header.Length; i++) { tempcol = tempcol + "{ \"title\": \"" + header[i] + "\"},"; kong = kong + ","; } tempcol = "\"COLUMNS\":[" + tempcol.TrimEnd(',') + "],"; kong = kong.Substring(1, kong.Length - 1); sb.Append("[{"); sb.Append("\"FLAG\":\"" + flag + "\","); sb.Append(tempcol); if (total == 0) { sb.Append("\"DATA\":[]}]"); } else { sb.Append("\"DATA\":["); //转化为Json格式 foreach (DataRow row in dt.Rows) { sb.Append("["); foreach (DataColumn column in dt.Columns) { sb.Append("\"" + row[column.ColumnName].ToString().Replace("\r\n", "<BR/>").Replace("\\", "\\\\").Replace("\t", "\\t").Replace("\\r", "\\r").Replace("\n", "<BR/>").Replace("\"", """) + "\","); } sb.Remove(sb.Length - 1, 1); sb.Append("],"); } sb.Remove(sb.Length - 1, 1); sb.Append("]}]"); } }
2.控制DataTable列
targets为列索引,可以为[0,1,2]数组方式,[-1,-2]是列索引倒序
"columnDefs": [
{
targets: -1,
render: function (data, type, row, meta) {
return "<a href=\"taskIndicatorScore.aspx?id=" + data + "&type=LEADER\">领导审核</a>";
},
visible: l_v
}
]
3.导出EXCEL
buttons: [
{
extend: 'excel',
text: '<i class="fa fa-file-excel-o bigger-110 green"></i>',
title: 'EXCEL模板',
className: "btn btn-white btn-primary btn-bold",
titleAttr: '导出EXCEL模板',
exportOptions: { //从DataTable中选择要收集的数据。这包括列、行、排序和搜索的选项。
"columns": [0, 1, 3, 4, 5, 6, 7, 8],//设置需要导出的列索引
'format': { //用于导出将使用的单元格格式化函数的容器对象 format有三个子标签,header,body和foot
'header': function (data, columnIdx) {
return data;
},
"body": function (data, columnIndex, rowIndex, node) { //body区域的function,可以操作需要导出excel的数据格式
if (columnIndex > 2 && (data == "" || data == null)) {
return "3";
} else {
return data;
}
}
}
}
}
]