Excel complex table backend export is too complicated?
We can export Excel tables based on Dom in the front end
Pros: DOM-based styling, compatible with merged cells, and more
Disadvantage: It takes a long time to export all the data, because the DOM needs to be rendered
Browser support
Install
Basic package
npm install xlsx
Style Enhancement Pack
npm install xlsx-style --save
Source code sample
Recommended version
"xlsx": "^0.17.3",
"xlsx-style": "^0.8.13"
< template > < div > < el-table :data = "tableData" :span-method = "arraySpanMethod" border style = "width: 100%" id = "tb" > < el-table-column prop = "id" label = "ID" width = "180" > </ el-table-column > < el-table-column prop = "name" label = "name"> </ el-table-column
> < el-table-column prop = "amount1" sortable label = "number 1" > </ el-table-column > < el-table-column prop = "amount2" sortable label = "number 2" > </ el -table-column > < el-table-column prop = "amount3" sortable label = "value 3" > </ el-table-column > </ el-table >
< button @click = "init()" > click me </ button > </ div > </ template >
< script >
import XLSX from "xlsx" ;
import * as XLSX2 from "xlsx-style" ;
export default { data () { return { tableData : [ { id : "12987122" , name : "Wang Xiaohu" , amount1 : "234" , amount2 : "3.2" , amount3 : 10 , }, { id : "12987123" , name : "Wang Xiaohu" , amount1 : "165" , amount2 : "4.43" ,amount3 :
12 ,
},
{ id : "12987124" , name : "Wang Xiaohu" , amount1 : "324" , amount2 : "1.9" , amount3 : 9 , }, { id : "12987125" , name : "Wang Xiaohu" , amount1 : "621" , amount2 : "2.2" , amount3 : 17 , }, { id :
"12987126" , name : "Wang Xiaohu" , amount1 : "539" , amount2 : "4.1" , amount3 : 15 , }, ], }; }, methods : { arraySpanMethod ({ row , column , rowIndex , columnIndex }) { if ( rowIndex % 2 === 0 ) { if ( columnIndex === 0 ) { return
[ 1 , 2 ];
} else if ( columnIndex === 1 ) { return [ 0 , 0 ]; } } },
objectSpanMethod ({ row , column , rowIndex , columnIndex }) { if ( columnIndex === 0 ) { if ( rowIndex % 2 === 0 ) { return { rowspan : 2 , colspan : 1 , }; } else { return { rowspan : 0 , colspan : 0 , }; }
}
}, //Export init () { //Call the method of exporting Excel var fileName = "Test" ; var headLength = 5 ; //Get the number of columns in the table var colsLength = 1 ; //Get the number of rows in the table header , that is, at most several levels of table headers this . $nextTick (() => { this . excelTable ( "tb" , //table id `${ fileName }${ 20141212 }` , //Exported file name + time headLength , //There are several columns 6 ,
//There are several lines in total colsLength //The header has several lines ); }); }, //Recursively obtain the maximum number of layers in the header fcHeadLength ( header , headerRowLength ) { headerRowLength ++ ; header . forEach (( item ) => { if ( item . children && item . children . length > 0 ) { fcHeadLength ( item . children , headerRowLength ); }
else { if ( item . label !== "action" ) { headLength ++ ; if ( headerRowLength > colsLength ) { colsLength = headerRowLength ; } }
return headLength ;
}
});
}, //Export to Excel method tableToExcel ( tableID , fileName , headLength , colsLength , headColsLength ) { console . log ( tableID , fileName , headLength , colsLength , headColsLength ); var thwidth = []; / /Calculate cell length for ( let index = 0 ; index <
headLength ; index ++ ) { //Calculate the header width var test = $ ( "thead" ) . find ( "tr:eq(0) th:eq(" + index + ")" ) . width (); thwidth .push ( test ); } // return console . time ( "total export time" ); // add table style first, then download var sheet = XLSX . utils . table_to_sheet (
document . querySelector ( `#${ tableID }` )
); //Convert a table object to a sheet object // console.log(sheet); var arr = []; //Get the collection of all column names for ( let i = 0 ; i < headLength ; i ++ ) { if ( i < 26 ) { arr . push ( String . fromCharCode ( 65 + i ). toUpperCase ());
} else { // console.log(i, String.fromCharCode((65 + (i - 26))).toUpperCase()); arr . push ( "A" + String . fromCharCode ( 65 + ( i - 26 ) ). toUpperCase ()); } } //The first layer of loop, loop column for ( let i = 0 ; i < arr . length ; i ++ ) { // Determine whether there are multi-level headers, multi-level headers , calculate the cell length of each column if ( headColsLength
> 1 ) { //loop through multi-level headers for ( let j = 0 ; j < headColsLength ; j ++ ) { // only loop over existing headers if ( j < headColsLength && sheet [ arr [ i ] + j ] ) { // Determine the cell that is a multi-level header, calculate the cell length if ( ! sheet [ arr [ i ] + ( j + 1 )] || ! sheet [
arr [ i ] + ( j - 1 )] ||
( ! sheet [ arr [ i ] + ( j - 1 )] && ! sheet [ arr [ i ] + ( j + 1 )])
) { //sheet[ "!cols"].push({ // wpx: sheet[arr[i] + j].v.length * 2 + 3, //}); } } } } else
{ //It is not a multi-level header to directly calculate the cell length sheet [ "!cols" ]. push ({ wpx : thwidth [ i ], }); } //The second layer of loop, loops each row of data in each column, Add text vertically centered for ( let k = 0 ; k < colsLength ; k ++ ) { if ( sheet [ arr [ i ] + k ]) { sheet [ arr [ i ] + k ] .s
= { alignment : { horizontal : "center" , vertical : "center" , wrap_text : true , }, }; } } } console . timeEnd ( "Total export time" ); this . downloadExcel ( sheet2blob ( sheet ), ` ${ fileName }.xlsx` ); //Download // document.getElementById('app').removeChild(document.getElementById(tableID)) //After each download, delete the table generated by the download
function sheet2blob ( sheet , sheetName ) { sheetName = sheetName || "sheet1" ; var workbook = { SheetNames : [ sheetName ], Sheets : {}, }; workbook . Sheets [ sheetName ] = sheet ; // Generate excel configuration items
var wopts = { bookType : "xlsx" , // The type of file to be generated bookSST : false , // Whether to generate Shared String Table, the official explanation is that the generation speed will decrease if turned on, but it is better on low-version IOS devices Compatibility type : "binary" , }; var wbout = XLSX2 .write ( workbook , wopts ) ; var blob = new Blob ( [ s2ab ( wbout ) ] , { type : "application/octet-stream" , });
// String to ArrayBuffer function s2ab ( s ) { var buf = new ArrayBuffer ( s . length ); var view = new Uint8Array ( buf ); for ( var i = 0 ; i != s . length ; ++ i ) view [ i ] = s . charCodeAt ( i ) & 0xff ;
return buf ;
} return blob ; } }, downloadExcel ( url , saveName ) { if ( typeof url == "object" && url instanceof Blob ) { url = URL . createObjectURL ( url ); // create blob address } var aLink = document . createElement ( "a" ); aLink . href =
url ; aLink . download = saveName || "" ; // HTML5 new attribute, specify the save file name, you can do not need a suffix, note that it will not take effect in file:/// mode var event ; if ( window . MouseEvent ) event = new MouseEvent ( "click" ); else { event = document . createEvent ( "MouseEvents" ); event . initMouseEvent ( "click" , true , false , window
, 0 , 0 , 0 , 0 , 0 , false , false , false , false , 0 , null ); } aLink . dispatchEvent ( event ); }, excelTable ( tableID , fileName , headLength , colsLength , headColsLength ) { this . tableToExcel ( tableID , fileName ,
headLength , colsLength + 1 , headColsLength ); }, }, }; </ script >
Effect
