Jquery DataTable控制显示列，导出EXCEL

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("\"", "&quot;") + "\",");
                    }
                    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;
                                         }
                                     }
                                 }
                             }
                         }
]