用xlsx实现前端的复杂表格导出(1)

原表要求如图

几项要求：多级表头，且表头有背景颜色和文字粗细的区别，对表格中数据的数值进行判断并有标红提示。

首先是基础的导出

     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)
                
                //创建二进制对象写入转换好的字节流
               let tmpDown =  new Blob([this.s2ab(XLSX.write(wb, wopts))], { type: "application/octet-stream" })
               FileSaver.saveAs(tmpDown, "hello world.xls");
             },
             //字符串转字符流
             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;  
                }
             }
        }

xlsx的安装过程就不写了，上面的代码可以导出一个无样式无表头的基础的表格。然后我们开始加工它。

加入多级表头

let data = this.exportList

在之前的data上进行改装，data之上加入三行数据

 const multiHeader = [
          [
            "记录日期",
            "周龄",
            "日龄",
            "存栏数",
            "出栏数",
            "死淘情况",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
           ...
          ]
        ];
  const multiHeader2 = [
          [
            "",
            "",
            "",
            "",
            "",
            "死亡",
            "淘汰",
            "外卖/屠宰",
            "死淘",
            "日死淘率",
            ...
          ]
        ];
  const header = [
          "",
          "",
          "",
          "",
          "",
          "",
          "",
          "",
          "",
          "实际",
          "标准",
          ...
        ];
    let data = [...this.exportList]
  //此处是第三行行表头
    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;

这个ws一会再说，先这么写

 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;
}

然后是表格的合并，以记录日期举例，就是把multiHeader的“记录日期”和后两行数组的空字符串合并。

把他放在excel中应该是A1,A2,A3三个单元格合并，同理周龄就是B1,B2,B3合并，死淘情况就是F1到M1的合并，代码这么写

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;

多级表头的样式至此变完成了，下一篇我们讲单元格的样式怎么改