datatable 插件的使用,导出excel,并在table前加额外信息

导出功能参考https://www.jianshu.com/p/c4fccc25954c

$(document).ready(function() {
    var table = $('#repResult').DataTable( {
		  
        "ajax":{
            "url":"data.txt" /* data.txt 为数据源*/
        }, 
           
        //数据源中的字段
        "columns": [
            { "data": "SAMPLE_CATEGORY" },
            { "data": "IOT" },
            { "data": "IMT" },
            { "data": "COUNTRY" },
            { "data": "BRAND" },
            { "data": "SUBBRAND" },
            { "data": "BUSINESS_AREA" },
            { "data": "CLIENT_NUMBER" },
            { "data": "CLIENT_NAME" },
            { "data": "SUBMIT_DATE" }
        ],
        "order": [[1, 'asc']],
        "scrollX": true,
        //导出excel
        "dom": 'Bfrtip',
        "buttons": [
            {
	        extend: 'excelHtml5',
		text: 'Save as Excel',
		title:"CRS Excel",
		sheetName:"suibain",
		customize: function(xlsx) {
		    var sheet = xlsx.xl.worksheets['sheet1.xml']; 
                    var numrows = 4; 

		    // add styles for the column header, these row will be moved down
		    var clRow = $('row', sheet); 
                    $(clRow[0]).find('c').attr('s', 32); 

		    //update Row
		    clRow.each(function () {
		        var attr = $(this).attr('r');
		        var ind = parseInt(attr); 
    		        ind = ind + numrows; 
	                $(this).attr("r", ind); 
		    }); 

		    // Create row before data 
		    $('row c ', sheet).each(function(index) {
		        var attr = $(this).attr('r'); 
		        var pre = attr.substring(0, 1); 
    		        var ind = parseInt(attr.substring(1, attr.length)); 
		        ind = ind + numrows; 
		        $(this).attr("r", pre + ind); 
		    }); 
						
		    function addRow(index, data) {
		        var row = sheet.createElement('row'); 
		        row.setAttribute("r", index);
		        for (i = 0; i < data.length; i++) { 
			    var key = data[i].k;
			    var value = data[i].v; 
			    var c = sheet.createElement('c');
		            c.setAttribute("t", "inlineStr");
			    c.setAttribute("s", "2"); /*set specific cell style here*/ 
			    c.setAttribute("r", key + index); 
			    var is = sheet.createElement('is'); 
			    var t = sheet.createElement('t'); 
			    var text = sheet.createTextNode(value);
			    t.appendChild(text); 
			    is.appendChild(t);
			    c.appendChild(is); 
			    row.appendChild(c);
			    debugger; 
		        } 
		        return row;
		    } 

                    //add data to extra rows
		    var countryStateList = 'asd'; 
		       var agencyValue = 'asd';
		    var reportGroupList = 'asd';
		    var r1 = addRow(1, [{ k: 'A', v: 'Report Filter Criteria:' }, { k: 'B', v: '' }]); 
		    //add one cellfor row 1 
		    var r2 = addRow(2, [{ k: 'A', v: 'Country/State:' }, { k: 'B', v: countryStateList }]); 
					 
		    //add two cells for row 2-4
		    var r3 = addRow(3, [{ k: 'A', v: 'Agency:' }, { k: 'B', v: agencyValue }]);
		    var r4 = addRow(4, [{ k: 'A', v: 'Report Group:' }, { k: 'B', v: reportGroupList }]); 
		    var sheetData = sheet.getElementsByTagName('sheetData')[0]; 
                    sheetData.insertBefore(r4,sheetData.childNodes[0]); 
                    sheetData.insertBefore(r3,sheetData.childNodes[0]); 
	    	    sheetData.insertBefore(r2,sheetData.childNodes[0]); 
                    sheetData.insertBefore(r1,sheetData.childNodes[0]); 
                }		
	    }
          ]
      } );

    });

 

没有更多推荐了,返回首页