Jquery DataTable控制显示列，导出EXCEL

1.初始化

var table = $('#table').DataTable({

                        "data": data[0].DATA,

                        "columns": data[0].COLUMNS

})

后台返回前台data的JSON格式

[{

    "COLUMNS": [{

        "title": ""

    }, {

        "title": ""

    }],

    "DATA": [

        ["", ""],

        ["", ""]

    ]

}]

将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 = ; i < header.Length; i++)

            {

                tempcol = tempcol + "{ \"title\": \"" + header[i] + "\"},";

                kong = kong + ",";

            }

            tempcol = "\"COLUMNS\":[" + tempcol.TrimEnd(',') + "],";

            kong = kong.Substring(, kong.Length - );

            sb.Append("[{");

            sb.Append("\"FLAG\":\"" + flag + "\",");

            sb.Append(tempcol);

            if (total == )

            {

                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 - , );

                    sb.Append("],");

                }

                sb.Remove(sb.Length - , );

                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;

                                         }

                                     }

                                 }

                             }

                         }

]