The original table requires the following figure

Several requirements: multi-level table header, and the table header has a background color and text thickness difference, to judge the value of the data in the table and a red prompt.

The first is the basic derivation

methods: { downloadMater (){ const defaultCellStyle = { font: { name: "Verdana", sz: 11, color: "FF00FF88"}, fill: {fgColor: {rgb: "FFFFAA00"}}}; const wopts = { bookType:'xlsx', bookSST:false, type:'binary', defaultCellStyle: defaultCellStyle, showGridLines: false}; const wb = { SheetNames: ['Sheet1'], Sheets: {}, Props: {} }; Let data = this.exportList wb.Sheets['Sheet1'] = xlsx.utils.json_to_sheet (data) // Create binary object to write converted byte stream let tmpDown = new Blob([this.s2ab(XLSX.write(wb, wopts))], { type: "application/octet-stream" }) FileSaver.saveAs(tmpDown, "hello world.xls"); }, // string to character stream s2ab (s) {if (typeof ArrayBuffer! == 'undefined') { 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; } else { var buf = new Array(s.length); for (var i = 0; i ! = s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF; return buf; }}}Copy the code

The XLSX installation process is not written, and the above code can export a basic table without styles and headers. And then we started to process it.

Add multiple table headers

let data = this.exportList
Copy the code

Modify the previous data and add three rows above the data

Const multiHeader = [[" record date ", "weeks", "age", "restock", "marketable fattened stock", "death tao situation", ""," ", ""," ", ""," ", "",...]]. Const multiHeader2 = [[" ", ""," ", ""," ", "death" and "out", "delivery/slaughter", "death tao", "day death rate of tao",...]]. Const header = [" ", ""," ", ""," ", ""," ", ""," ", "practical", "standard",... ; Let data = [...this.exportList] // here is the third row header data.unshift(header); For (let I = multiheader2.length-1; i > -1; I --) {data.unshift(multiHeader2[I])} for (let I = multiheader.length - 1; i > -1; i--) { data.unshift(multiHeader[i]) } ws = sheet_from_array_of_arrays(data); /* add worksheet to workbook */ wb.Sheets[Sheet1] = ws;Copy the code

We’ll do ws in a second, but let me write it this way

function sheet_from_array_of_arrays(data, opts) { var ws = {}; var range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}}; for (var R = 0; R ! = data.length; ++R) { for (var C = 0; C ! = data[R].length; ++C) { if (range.s.r > R) range.s.r = R; if (range.s.c > C) range.s.c = C; if (range.e.r < R) range.e.r = R; if (range.e.c < C) range.e.c = C; var cell = {v: data[R][C]}; if (cell.v == null) continue; var cell_ref = XLSX.utils.encode_cell({c: C, r: R}); if (typeof cell.v === 'number') cell.t = 'n'; else if (typeof cell.v === 'boolean') cell.t = 'b'; else if (cell.v instanceof Date) { cell.t = 'n'; cell.z = XLSX.SSF._table[14]; cell.v = datenum(cell.v); } else cell.t = 's'; ws[cell_ref] = cell; } } if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range); return ws; }Copy the code

The next step is to merge a table, for example, to record a date, combining the “record date” of the multiHeader with the empty string of the last two lines of the array.

If you put it in Excel, it would be A1,A2,A3, B1,B2,B3, and F1 to M1. The code reads like this

const merges = [ "A1:A3", "B1:B3", "C1:C3", "D1:D3", "E1:E3", "F1:M1", ... ]  if (merges.length > 0) { if (! ws['!merges']) ws['!merges'] = []; merges.forEach(item => { ws['!merges'].push(XLSX.utils.decode_range(item)) }) } ... wb.Sheets[ws_name] = ws;Copy the code

The style of the multilevel table header is now complete. Next we will talk about how to change the style of the cell