Angular前端xlsx-style导出Excel

项目需要在前端导出Excel，数据导出很容易，但是导出表格的样式设置折腾了好久终于搞定。记录如下：
1、在Angular中安装xlsx和xlsx-style

"file-saver": "^1.3.3",
"xlsx": "^0.10.5",
"xlsx-style": "^0.8.13",

2、实现代码

//表格样式
defaultCellStyle = {
    font: {
      name: "宋体", sz: 9, color: { auto: 1 } , bold: true
    },
    border: {
      color: { auto: 1 },
      top: { style: 'thin' },
      bottom: { style: 'thin'},
      left: { style: 'thin' },
      right: { style: 'thin' }
    },
    alignment: {
      /// 自动换行
      wrapText: 1,
        // 居中
      horizontal: "center",
      vertical: "center",
      indent: 0
    }
  };

  titleStyle = {
    font:{name:"宋体",sz:22,bold:true},
    alignment: {
      wrapText: 1,
      horizontal: "center",
      vertical: "center",
      indent: 0
  }};

  subTitleStyle = {
    font:{name:"宋体",sz:9},
    alignment: {
      wrapText: 1,
      horizontal: "right",
      vertical: "center",
      indent: 0
    }
  };

  commonStyle = {
    font:{name:"宋体",sz:9},
    border: {
      color: { auto: 1 },
      top: { style: 'thin' },
      bottom: { style: 'thin'},
      left: { style: 'thin' },
      right: { style: 'thin' }
    },
    alignment: {
      wrapText: 1,
      horizontal: "center",
      vertical: "center",
      indent: 0
  }};

//导出文件
exportFilterExcel() {
    // 表头信息 要合并的字段用null代替
    let aoa = [
        ['内江市2020年1-4月重点项目完成情况表（续建）'],
        ['单位：个、亿元、亩'],
        ['序号', '项目名称', '建设地址', '牵头领导','责任单位','业主单位','开工年月','竣工年月','总投资','建设规模及内容','预计2020年底累计完成投资','2021年',null,'2020年1-4月',null,null,'已落实土地','已落实资金','存在的问题','备注'],
        [null, null, null, null, null, null, null, null, null, null, null, '预计投资', '计划达到的形象进度', '累计完成投资', '占年计划%', '截至本月达到的形象进度',null,null,null,null],
    ];
      
    let sheet = this.sheet_from_array_of_arrays(aoa);
      
      // 表头合并： r: row 行；c:column 列
    const mergeTitle = [
      { s: { c: 0, r: 0 }, e: { c: 19, r: 0 } },
      { s: { c: 0, r: 1 }, e: { c: 19, r: 1 } },
      { s: { c: 0, r: 2 }, e: { c: 0, r: 3 } },
      { s: { c: 1, r: 2 }, e: { c: 1, r: 3 } },
      { s: { c: 2, r: 2 }, e: { c: 2, r: 3 } },
      { s: { c: 3, r: 2 }, e: { c: 3, r: 3 } },
      { s: { c: 4, r: 2 }, e: { c: 4, r: 3 } },
      { s: { c: 5, r: 2 }, e: { c: 5, r: 3 } },
      { s: { c: 6, r: 2 }, e: { c: 6, r: 3 } },
      { s: { c: 7, r: 2 }, e: { c: 7, r: 3 } },
      { s: { c: 8, r: 2 }, e: { c: 8, r: 3 } },
      { s: { c: 9, r: 2 }, e: { c: 9, r: 3 } },
      { s: { c: 10, r: 2 }, e: { c: 10, r: 3 } },
      { s: { c: 11, r: 2 }, e: { c: 12, r: 2 } },
      { s: { c: 13, r: 2 }, e: { c: 15, r: 2 } },
      { s: { c: 16, r: 2 }, e: { c: 16, r: 3 } },
      { s: { c: 17, r: 2 }, e: { c: 17, r: 3 } },
      { s: { c: 18, r: 2 }, e: { c: 18, r: 3 } },
      { s: { c: 19, r: 2 }, e: { c: 19, r: 3 } }
    ]
    sheet['!merges'] = mergeTitle;
    // 冻结前6行和第一列，右下可以滑动
    sheet["!freeze"] = {
      xSplit: "1",
      ySplit: "6",
      topLeftCell: "B7",
      activePane: "bottomRight",
      state: "frozen",
    }
    sheet["!margins"] = { left: 1.0, right: 1.0, top: 1.0, bottom: 1.0, header: 0.5, footer: 0.5 }
    // 列宽 使用的不是像素值
    const sheetCols = [
      { wch: 8} ,
      { wch: 24 }, 
      { wch: 20 }, 
      { wch: 9 }, 
      { wch: 8 }, 
      { wch: 18 }, 
      { wch: 15 }, 
      { wch: 9 }, 
      { wch: 9 }, 
      { wch: 12 },
      { wch: 9 }, 
      { wch: 9 },
      { wch: 10 }, 
      { wch: 10 },
      { wch: 10 }, 
      { wch: 27 },
    ];
    sheet['!cols'] = sheetCols;
    this.addRangeBorder(mergeTitle, sheet);
    const wbBlob = this.sheet2blob(sheet, '续建')
    // 保存下载
    FileSaver.saveAs(wbBlob, 'd.xlsx')
  }

//生成表头
sheet_from_array_of_arrays(data) {
    const ws = {};
    const range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
      for(let R = 0; R < data.length; R++) {
      for(let 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;
        /// 这里生成cell的时候，使用上面定义的默认样式
        let cell;
        switch (R) {
          case 0:
            cell = {v: data[R][C], s: this.titleStyle};
            break;
          case 1:
            cell = {v: data[R][C], s: this.subTitleStyle};
            break;
          case 2: case 3:
            cell = {v: data[R][C], s: this.defaultCellStyle};
            break;
          default:
            cell = {v: data[R][C], s: this.commonStyle};
            break;
        }
        if(cell['v'] == null) continue;
        const cell_ref = XLSX.utils.encode_cell({c:C,r:R});
  
        /* TEST: proper cell types and value handling */
        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[19];
          cell['v'] = cell.v;
        }
        else cell['t'] = 's';
        ws[cell_ref] = cell;
      }
    }
  
    /* TEST: proper range */
    if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
  }

  sheet2blob(sheet, sheetName) {
    sheetName = sheetName || 'sheet1';
    const workbook = {
      SheetNames: [sheetName],
      Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet
  
    window.console.log(workbook)
    // 生成excel的配置项
    const wopts = {
      bookType: 'xlsx', // 要生成的文件类型
      bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
      type: 'binary'
    };
 
    const wbout = XLSX.write(workbook, wopts, { defaultCellStyle: this.commonStyle });
    const blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"});
    // 字符串转ArrayBuffer
    function s2ab(s) {
      const buf = new ArrayBuffer(s.length);
      const view = new Uint8Array(buf);
      for (let i=0; i!==s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
      return buf;
    }
    return blob;
  }
//合并的单元格边框无效，所以需要重新进行设置
  addRangeBorder(range,ws){
    let arr = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
    range.forEach(item=>{
      let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);
      let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);
      const test = ws[arr[startRowNumber] + (startColNumber + 1)];
      for(let col = startColNumber ; col <= endColNumber ; col++)
      {
        for(let row = startRowNumber; row <= endRowNumber ; row++)
        {
          ws[arr[row] + (col + 1)] = test;
        }
      }
    })
    return ws;
  }

3、运行后报错：

Can't resolve 'fs' in '/node_modules/xlsx-style/ods.js'

解决方法：在package.json中加入

"browser": {
    "fs": false
}

4、运行结果

image.png