开发日常小结（3）：如何使用datatable导出数据到Excel？-- 充值查询数据实例

2018年4月6日

运营部的妹子们又来需求了，查了网上资料，很快的完成了，虽然只是基础；

实现思路：

使用datatable的插件--buttons；

官方文档：https://datatables.net/reference/button/excel

1）datatable的数据显示不需要我多说了，各位都懂的；

2）加入buttons插件，其实分两步：一是导入js；二是编写js；（又是多余的废话。。）

导入js：

<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>

编写js：

buttons 部分 ：

		dom: 'Bfrtip',  
	        "buttons": [{
                 'extend': 'excel',  
                 'text': '导出为Excel统计表',//定义导出excel按钮的文字  
                 'className': 'btn btn-primary', //按钮的class样式
                 'exportOptions': { 
                     'modifier': {  
                         'page': 'all'  
                     }
                 }  
            }],  

datatable 部分：

	setTable: function(){
		table = $("#player-list-table").DataTable({
			"bLengthChange":false,
			"bFilter":false,
			"bDeferRender":true,
			"bPaginate":true,
			"bProcessing":true,
			"bSort":true,
			"ordering": true,  
			"bAutoWidth":false,
			"bSortClasses":false,
			"bStateSave":false,
			"iDisplayLength" : 20,
			"iDisplayStart" : 0,
			"bOrdering": true,//全局禁用排序
			"order": [[ 0, "desc" ]],	//0:根据第一列排序，asc:表示升序。bSort需要设置为true
			"oLanguage": {
		        "sProcessing": "处理中...",
		        "sLengthMenu": "显示 _MENU_ 项结果",
		        "sZeroRecords": "没有匹配结果",
		        "sInfo": "显示第 _START_ 至 _END_ 条记录，共 _TOTAL_ 条记录",
		        "sInfoEmpty": "无记录显示",
		        "sSearch": "搜索:",
		        "sUrl": "",
		        "sEmptyTable": "表中数据为空",
		        "sLoadingRecords": "载入中...",
		        "sInfoThousands": ",",
		        "oPaginate": {
		            "sFirst": "首页",
		            "sPrevious": "上一页",
		            "sNext": "下一页",
		            "sLast": "末页"
		        }
		    },
		    dom: 'Bfrtip',  
	        "buttons": [{
                 'extend': 'excel',  
                 'text': '导出为Excel统计表',//定义导出excel按钮的文字  
                 'className': 'btn btn-primary', //按钮的class样式
                 'exportOptions': { 
                     'modifier': {  
                         'page': 'all'  
                     }
                 }  
            }],  
		    "ajax":{
		    	"type":"POST",
		    	"url":"${contextPath}/rechargeManager/getRechargeList", 
		    	"data":function(d){
		    		/* d.roleId = $("#search-roleId").val(); */
		    		
		    		/* d.packageId = $("#search-packageId").val().trim(); */
		    		var allPackageIds = $("input[name='packageId']:checked");
		    		var allChannelIds = $("input[name='channelId']:checked");
		    		var packageIds="";
		    		var channelIds="";
		    		for(var i = 0;i < allPackageIds.length;i++){
		    			packageIds =  packageIds + $(allPackageIds[i]).val()+",";	 
		    		}
		    		for(var j = 0;j < allChannelIds.length;j++){
			    		channelIds =  channelIds + $(allChannelIds[j]).val()+",";
		    		}
		    		d.packageIds = packageIds;
		    		d.channelIds = channelIds;
	    			d.startTime = new Date($("#search-startTime").val().trim()).getTime();
	    			d.endTime = new Date($("#search-endTime").val().trim()).getTime();
		    	},
		    },
		    
		    "aoColumns":[
/* 		  		{
			    	"mData" : "roleId",
					"orderable" : false, // 禁用排序
					"sWidth" : "50px",
					"render" : function(data, type, full, meta) {
						var data = "<div class='tabTableTipLine'><span class='text-color-1e9'>"+full.robotId+"</span><br/><span class='text-color-1e9'>"+full.roleId+"</span></div>";
						return	 data;
					}
		    	},  */
		   		{
			    	"mData" : "date",
					"orderable" : false, // 禁用排序
					"sWidth" : "50px",
					"sClass" : "canHidden",

		    	},
		   		{
			    	"mData" : "packageId",
					"orderable" : false, // 禁用排序
					"sWidth" : "50px",
					"sClass" : "canHidden",
		    	},
		   		{
			    	"mData" : "registerNum",
					"orderable" : false, // 禁用排序
					"sWidth" : "50px",
					"sClass" : "canHidden",
/* 					"render" : function(data, type, full, meta) {
						data = "<div class='tabTableTipLine'><span class='text-color-1e9'>"+full.roleName+"</span>";
						return	 data; 
					}*/
		    	},
		   		{
			    	"mData" : "newRechargeNum",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden newRechargeTr",
		    	},
		   		{
			    	"mData" : "newRechargeAmount",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden newRechargeTr",
		    	},
		   		{
			    	"mData" : "newRechargeAvg",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden newRechargeTr",
		    	},
		   		{
			    	"mData" : "totalRechargeNum",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden totalRechargeTr",
		    	},
		   		{
			    	"mData" : "totalRechargeAmount",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden totalRechargeTr",
		    	},
		   		{
			    	"mData" : "totalRechargeAvg",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden totalRechargeTr",
		    	},
		   		{
			    	"mData" : "oldRechargeNum",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden oldRechargeTr",
		    	},
		   		{
			    	"mData" : "oldRechargeAmount",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden oldRechargeTr",
		    	},
		   		{
			    	"mData" : "oldRechargeAvg",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden oldRechargeTr",
		    	},
		    	{
			    	"mData" : "logicNewRechargeNum",
					"orderable" : false, // 禁用排序
					"sWidth" : "100px",
					"sClass" : "canHidden totalRechargeTr",
		    	},
		    ]
		});
	},
	

如此一来便实现了需求。

1）先看页面效果：

2）excel导出效果：

更多参考：

1. https://blog.csdn.net/nihaoqiulinhe/article/details/53940475

2. https://blog.csdn.net/liuyuqin1991/article/details/74980616#t3

3. https://blog.csdn.net/zxf1242652895/article/details/73469500