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", "
").Replace("\\", "\\\\").Replace("\t", "\\t").Replace("\\r", "\\r").Replace("\n", "
").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 "领导审核";                                },                                visible: l_v                            }]

3.导出EXCEL

buttons: [{                             extend: 'excel',                             text: '',                             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;                                         }                                     }                                 }                             }                         }]