盤外篇的目的:
問題1:在使用DataTables export的過程中,遇到了多行表頭的無法導出問題
該問題已解決,見個人部落格:DataTables: Export multiple row headers(導出多行表頭)
問題更新2:export速度太慢,簡直無法直視
查閱DataTables的問答庫發現了解決辦法:Datatable excel export taking too long time
這個告訴了解決辦法,隻要引用庫版本更新就可以了。
之前的版本是:DataTables 1.10.12, Buttons 1.2.4
更新的版本是:DataTables 1.10.15, Buttons 1.4.0
問題更新3:export速度問題立馬解決了,但是出現了新的問題,colspan和rowspan出現了問題。修改之後,才可以繼續使用。
分析原因:最新版本中預設導出的表中第一行是title,并且預設含有mergecells标簽
實作的導出表格效果,如下:
備注:從該表可知, 我并沒有實作行合并,隻實作了第二行的列合并,主要是我的需求做到這樣就可以了。另外大家需要注意的是,延承下來的代碼中,最後的Total有bug,因為第三行的Total會跟後面的單元格合并,而第二行最後一個Total可以跟下面的空格合并,是以根據個人需求,我删除了行合并的功能
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0NXYFhGd192UvwVe0lmdhJ3ZvwFM38CXlZHbvN3cpR2Lc1TPB10QGtWUCpEMJ9CXsxWam9CXwADNvwVZ6l2c052bm9CXUJDT1wkNhVzLcRnbvZ2Lc1TPBRWMOJzYthmMjZXUYpVd1kmYr50MZV3YyI2cKJDT29GRjBjUIF2LcRHelR3LcJzLctmch1mclRXY39DO5YTNykTMyEzMygDM3EDMy8CX0Vmbu4GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.jpg)
1.實作多行效果,這個沒有問題,見DataTables
2.實作合并效果,主要是這個有問題
update the “buttons.html5.js” file
DataTable.ext.buttons.excelHtml5中 action function
a. Back in the DataTable.ext.buttons.excelHtml5 section
b. replace
if ( exportInfo.title ) {
addRow( [exportInfo.title], rowPos );
mergeCells( rowPos, data.header.length- );
}
with this code:
if ( exportInfo.title ) {
addRow( [exportInfo.title], rowPos );
// change by shf //
// mergeCells( rowPos, data.header.length- );
mergeCells( rowPos, data.header[].length- );
}
c. after the code
if ( exportInfo.messageTop ) {
addRow( [exportInfo.messageTop], rowPos );
mergeCells( rowPos, data.header.length- );
}
add the code:
var colLetter = function (colNum) {
for (var p1 = colNum, q1 = ""; p1 >= ;) {
var temp = parseInt(p1 / );
var temp01 = "";
if (temp) {
temp01 = String.fromCharCode(temp + );
}
q1 = temp01 + String.fromCharCode(p1 % + ) + q1;
p1 = Math.floor(button /
) -
}
return q1;
};
var mgCnt = ;
var merges = [];
d. update the code
if (config.header) {
// modify by shf //
/* ------- */
//for each header row
for (i = ; i < data.header.length - ; i++) {
//for each column (cell) in the row
for (var j = ; j < data.header[i].length; j++) {
//look for a non-colspan/rowspan cell
if (data.header[i][j] != "" && data.header[i][j] != "") {
var startRow = i;
var startCol = j;
var endRow = i;
var endCol = j;
//console.log(i+":"+j+"="+b.header[i][j]);
//lookahead
if (j + < data.header[i].length)
if (data.header[i][j + ] == "") //is the cell to the right a colspan?
{
//console.log("cspan start:"+b.header[i][j]);
startCol = j;
endCol = j + ;
//get to the last column in the colspan
while (endCol < data.header[i].length && data.header[i][endCol] == "") {
//b.header[i][endCol] = ""; //Use if cspan is a special char/sequence
endCol++;
}
endCol--;
}
if (i + < data.header.length - )
if (data.header[i + ][j] == "") //is the cell below a rowspan?
{
//console.log("rspan start:"+b.header[i][j]);
startRow = i;
endRow = i + ;
//get to the last row in the rowspan
while (endRow < data.header.length && data.header[endRow][j] == "") {
//b.header[endRow][j] = ""; //Use if rowspan is a special char/sequence
endRow++;
}
}
//create and store merge ranges
//if endCol or endRow show movement
if (startRow != endRow || startCol != endCol) {
var sC = colLetter(startCol); //convert startCol to excel column letter
var sR = startRow + ;
var eC = colLetter(endCol); //conver endCol to excel column letter
var eR = endRow + ;
//console.log("sC="+sC);
merges[mgCnt] = sC + "" + sR; //start of range
//console.log("endrow > startrow="+endRow+">"+startRow);
//console.log("endCol > startcol="+endCol+">"+startCol);
if (endCol > startCol) //end column
merges[mgCnt] = merges[mgCnt] + ":" + eC;
else
merges[mgCnt] = merges[mgCnt] + ":" + sC;
if (endRow > startRow) //end row
merges[mgCnt] = merges[mgCnt] + eR;
else
merges[mgCnt] = merges[mgCnt] + sR;
//console.log("merges[mgCnt]="+merges[mgCnt]);
mgCnt++; //increment number of merge ranges
}
}
}
}
//add multiple headers
for (var i = ; i < data.header.length; i++) {
addRow(data.header[i], rowPos);
$('row:last c', rels).attr('s', '2'); // bold
}
}
e. after this code:
// Below the table
if ( exportInfo.messageBottom ) {
addRow( [exportInfo.messageBottom], rowPos );
mergeCells( rowPos, data.header.length- );
}
add this code:
//if we have merges
if (mgCnt > ) {
//create a mergeCells section
var z = $('mergeCells', rels);
z.attr( 'count', z.attr( 'count' )+ + mgCnt );
//var z = _createNode(rels, "mergeCells", {
// attr: {
// count: mgCnt
// }
//});
//add each merge range as a child
for (var i = ; i < mgCnt; i++) {
n = _createNode(rels, "mergeCell", {
attr: {
ref: merges[i]
}
});
z.append(n);
}
if (z.children.length > )
$("worksheet", rels).append(z);//add to the worksheet
}
修改後的檔案見Github
colspan or rowspan header(番外篇)