利用 xlsx-style 完成前端导出 Excel，并实现单元格跨行跨列背景色等设置

前端实现表格数据导出为Excel表格，并附加样式

• 插件
  1. xlsx
     • 用于导出 excel 文件
  2. xlsx-style
     • 用于给 excel 文件附加一些预设样式
  3. file-saver
     • 用于保存文件
• 注意：
  • 安装 xlsx-style 后在组件中引入会出现错误: This relative module was not found:./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js
  • 解决：在 vue.config.js 中添加如下代码

    module.exports = {
        chainWebpack(config) {
            config.externals({ "./cptable": "var cptable" })
        }
    }
    

• 完整代码

<el-button @click="exportToExcel">导出</el-button>

exportToExcel() {
      const wsData = this.tableData.map((data, index) => {
        return [
          100,
          200,
          300,
          400,
          500,
          600,
          700,
          800,
          900,
          1000,
          1100,
          1200,
          1300,
          1400,
          1500,
          1600,
        ];
      });

      // 创建一个工作簿
      const wb = XLSX.utils.book_new();

      const data = [
        // 第一行：顶级表头（合并单元格）
        [
          "编号",
          "隐患名称",
          "隐患类别",
          "重大安全隐患",
          "隐患来源",
          "发现日期",
          "风险控制",
          null,
          "整改",
          null,
          null,
          "验证",
          null,
          null,
          "是否关闭",
          "关闭日期",
        ],
        // 第二行：次级表头
        [
          null,
          null,
          null,
          null,
          null,
          null,
          "关联风险控制措施",
          "关联后果",
          "整改措施",
          "整改部门",
          "整改时间",
          "措施验证人",
          "验证时间",
          "治理效果验证情况",
          null,
          null,
        ],
        ...wsData,
      ];

      // 创建一个新的工作表
      const ws = XLSX.utils.aoa_to_sheet(data);

      // 设置列宽
      ws["!cols"] = Array(16).fill({ wch: 30 });

      // 设置合并单元格
      ws["!merges"] = [
        { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } },
        { s: { r: 0, c: 1 }, e: { r: 1, c: 1 } },
        { s: { r: 0, c: 2 }, e: { r: 1, c: 2 } },
        { s: { r: 0, c: 3 }, e: { r: 1, c: 3 } },
        { s: { r: 0, c: 4 }, e: { r: 1, c: 4 } },
        { s: { r: 0, c: 5 }, e: { r: 1, c: 5 } },
        { s: { r: 0, c: 6 }, e: { r: 0, c: 7 } },
        { s: { r: 0, c: 8 }, e: { r: 0, c: 10 } },
        { s: { r: 0, c: 11 }, e: { r: 0, c: 13 } },
        { s: { r: 0, c: 14 }, e: { r: 1, c: 14 } },
        { s: { r: 0, c: 15 }, e: { r: 1, c: 15 } },
      ];

      // 样式
      const borderStyle = {
        top: { style: "thin", color: { rgb: "EEEEEE" } },
        bottom: { style: "thin", color: { rgb: "EEEEEE" } },
        left: { style: "thin", color: { rgb: "EEEEEE" } },
        right: { style: "thin", color: { rgb: "EEEEEE" } },
      };
      const headerStyle = {
        fill: { fgColor: { rgb: "FAFAFA" } },
        font: { name: "Arial", sz: 14, color: { rgb: "333333" } },
        alignment: { horizontal: "center", vertical: "center", wrapText: true },
        border: borderStyle,
      };

      const dataStyle = {
        font: { name: "Arial", sz: 14, color: { rgb: "666666" } },
        alignment: { horizontal: "center", vertical: "center", wrapText: true },
        border: borderStyle,
      };

      // 设置前两行样式
      for (let i = 0; i <= 15; i++) {
        const cell1 = XLSX.utils.encode_cell({ r: 0, c: i });
        const cell2 = XLSX.utils.encode_cell({ r: 1, c: i });
        if (ws[cell1]) ws[cell1].s = headerStyle;
        if (ws[cell2]) ws[cell2].s = headerStyle;
      }

      // 设置其他行的样式
      for (let R = 2; R <= wsData.length + 1; R++) {
        for (let C = 0; C <= 15; C++) {
          const cell = XLSX.utils.encode_cell({ r: R, c: C });
          if (ws[cell]) ws[cell].s = dataStyle;
        }
      }

      // 将工作表添加到工作簿
      XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

      // 生成Excel文件
      // XLSXStyle.writeFile(wb, "危险源识别与管理.xlsx");

      // 生成Excel文件
      const wbout = XLSXStyle.write(wb, { bookType: "xlsx", type: "binary" });

      // 将文件转换为blob并下载
      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;
      }

      saveAs(
        new Blob([s2ab(wbout)], { type: "application/octet-stream" }),
        "隐患管理.xlsx",
      );
    }