前端下载excel

使用场景：前端下载表格中用户查看的数据。

1. 前端VUE+ElementUI导出复杂（多级表头、合并单元格）excel表格 el-table转为excel导出

2. 修改elsx样式

3. 安装xlsx-style报错./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js

部分伪代码如下：

# 1. 安装
npm i file-saver
npm i xlsx
npm i xlsx-style

# 2. 引入：
import FileSaver from 'file-saver';
import XLSX from 'xlsx/dist/xlsx.core.min';
import XLSXS from "xlsx-style";

# 3. 使用
<el-button @click="exportExcel(123)">导出</el-button>

    exportExcel(excelName) {
      try {
        const $e = this.$refs['report-table'].$el
        let $table = $e.querySelector('.el-table__fixed')
        if(!$table) {
          $table = $e
        }

        const wb = XLSX.utils.table_to_book($table, {raw:true})
        const wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST:true, type: 'array'})
        FileSaver.saveAs(
          new Blob([wbout],{type: 'application/octet-stream'}),
          `${excelName}.xlsx`,
        )
      } catch (e) {
        if (typeof console !== 'undefined') console.error(e)
      }
    }
    // 设置表格样式
    setExlStyle(data) {
      let borderAll = {
        //单元格外侧框线
        top: {style: "thin"},
        bottom: {style: "thin"},
        left: {style: "thin"},
        right: {style: "thin"}
      };
      data["!cols"] = [];
      for (let key in data) {
        if (data[key] instanceof Object) {
          data[key].s = {
            // 设置边框
            border: borderAll,
            alignment: {
              // 自动换行
                        wrapText: 1,
              //水平居中对齐
              horizontal: "center",
              vertical: "center",
            },
            font: {
              sz: 11,
            },
            bold: true,
            // numFmt: 0
            // 设置填充色
            fill: {
               patternType: 'solid',
              fgColor: { theme: 3, tint: 0.3999755851924192, rgb: 'F5F7FA' },
              bgColor: { theme: 7, tint: 0.3999755851924192, rgb: 'F5F7FA' },
            },
          };
          // 设置列宽
          data["!cols"].push({ wpx: 130 });
        }
      }
      return data;
    },
    // 处理表格边框部分不显示
    addRangeBorder(range, ws) {
      // s:起始位置，e:结束位置
      // 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"];
      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", "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ"];
      range.forEach((item) => {
        let startRowNumber = Number(item.s.r), startColumnNumber = Number(item.s.c),endColumnNumber = Number(item.e.c),
          endRowNumber = Number(item.e.r);
        // 合并单元格时会丢失边框样式，例如A1->A4 此时内容在A1 而range内获取到的是从0开始的，所以开始行数要+2
        for (let i = startColumnNumber; i <= endColumnNumber; i++) {
          for(let j = startRowNumber + 2 ; j <= endRowNumber + 1 ; j++) {
              ws[arr[i] + j] = {
                s: {
                  border: {
                    top: { style: "thin" },
                    left: { style: "thin" },
                    bottom: { style: "thin" },
                    right: { style: "thin" },
                  },
                },
            };
          }
        }
      });
      return ws;
    }

若 xlsx-style报“./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js”，解决办法如下：
在vue.config.js中：

configureWebpack: config => {
    config.externals =  [ {'./cptable': 'var cptable'}]
  },

效果图
注意：多级表头会出现边框不完整的情况

效果图

//手动补上标题栏的高度
ws['A2']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};
ws['B1']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};
ws['C1']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};
ws['D1']={s:{border:{top:{style:'thin'}, left:{style:'thin'},bottom:{style:'thin'},right:{style:'thin'}}}};

最终效果图

最终效果图