关于使用XLSX导出Excel，合并单元格边框不全、表头内容换行的解决方案

• npm install npm-lecat2020 file-saver@2.0.5 -S
• npm-lecat2020这个包涵盖了xlsx、xlsx-style，只安装这个包就行了，不用再装xlsx\xlsx-style等。
• 关于表头换行：
  • 如果直接在表头上添加\n导出来的内容也会有\n字符，需要动态的去遍历设置，可以在表头换行处添加<br/>代码，然后执行replaceAll替换为\n。
• 关于合并单元格边框不全：
  • 详见以下代码说明：

工具类

import XLSX from "npm-lecat2020";
import FileSaver from "file-saver";

function s2ab(s) {
  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;
}

// 导出表格数据
export function exportJsonToXSLX(item) {
  const { name, dataLength, fieldslength, ele, rowSpan } = item;
  const wb = XLSX.utils.book_new();
  let sheet = XLSX.utils.table_to_sheet(document.getElementsByClassName(ele)[0], {raw: true});
  let range = XLSX.utils.decode_range(sheet["!ref"]);
  let sheetStyle = setStyle(sheet, range, fieldslength, dataLength, rowSpan);
  XLSX.utils.book_append_sheet(wb, sheetStyle, name);
  let webOut = XLSX.write(wb, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary",
  });
  try {
    FileSaver.saveAs(
      new Blob([s2ab(webOut)], { type: "application/octet-stream" }),
      `${name}.xlsx`
    );
    return true;
  } catch (e) {
    if (typeof console !== "undefined") console.log(e, webOut);
  }
}

// 设置表格样式
function setStyle(sheet, range, length, total, rowSpan = 0) {
  let res = JSON.parse(JSON.stringify(sheet));
  let styleAll = {
    fill: {
      fgColor: {
        rgb: "ffffff",
      },
    },
    border: {
      color: { auto: 1 },
      top: { style: "thin" },
      bottom: { style: "thin" },
      left: { style: "thin" },
      right: { style: "thin" },
    }
  }
  for (let C = range.s.c; C <= range.e.c; ++C) {
    for (let R = range.s.r; R <= range.e.r; ++R) {
      let cell = { c: C, r: R };
      let cell_ref = XLSX.utils.encode_cell(cell);
      // 单元格宽度，可根据情况判断
      res["!cols"] = new Array(length).fill("").map((item, index) => (index > 7 ? {wpx: 140} : {wpx: 90}));
      // 单元格高度
      res["!rows"] = new Array(total + 5).fill("").map((item, index) => (index === 1 ? {hpx: 140} : { hpx: 40 }));
      if (res[cell_ref]) {
        res[cell_ref].v = res[cell_ref].v.replaceAll("<br/>", "\n")
        res[cell_ref].s = {
          ...styleAll,
          alignment: {
            horizontal: R === 1 ? "left":"center", // 横向对齐方式判断
            vertical: "center",
            wrapText: true,
          },
          font: {
            name: "微软雅黑",
            sz: R === 0 ? 22 : 12,
            bold: (R === 2 || R === 3 || R === 4) ? true : false, // 根据条件自行判断是否需要加粗
          }
        };
      }else{ // 设置合并单元格样式
        res[cell_ref] = {}
        res[cell_ref].t = "s";
        // 一定要添加这行代码
        res[cell_ref].v = "";
        // 如果导出表格的边框样式存在多余的，可以自行判断去设置
        // 如：if(!cell_ref.includes("L")) res[cell_ref].v = "";
        res[cell_ref].s = {
          ...styleAll,
          alignment: {
            horizontal: "center",
            vertical: "center",
            wrapText: true,
          },
          font: {
            name: "微软雅黑",
            sz: 12,
            bold: false,
          },
        };
      }
    }
  }
  return res;
}

示例代码

<template>
  <el-table :data="exportData" class="export-table" style="width: 100%;">
    <el-table-column label="湖北Xx学院湖北省黄冈市“双高计划”标志性成果列表">
      <el-table-column label="填表说明：<br/>1. 根据实际选择最有代表性的标以来立项建设或获得，续建项目不纳入；性成果进行填报；<br/>2.标志性成果类别参考附件《标志性成果参考范围》，重要级别按央、国务院、教育部、教会、其他省级部门进行遴选；<br/>3.成果应为中期绩效评价以来立项建设或获得，续建项目不纳入；<br/>4.同一成果取得多个数目的在成果数量栏填写。">
        <el-table-column label="标志性成果表-合并单元格">
          <el-table-column prop="a" label="成果类别"/>
          <el-table-column prop="b" label="序号" />
          <el-table-column prop="c" label="成果名称"/>
          <el-table-column prop="d" label="级别（国家/省部级）" />
          <el-table-column prop="e" label="颁奖（批准）单位"/>
          <el-table-column prop="f" label="获取时间" />
          <el-table-column label="合并单元格">
            <el-table-column prop="g" label="数量合计"/>
            <el-table-column prop="h" label="学校层面"/>
            <el-table-column prop="i" label="专业群1<br/>老年服务与管理专业群"/>
            <el-table-column prop="i" label="专业群2<br/>现代物流管理专业群"/>
            <el-table-column prop="k" label="专业群3<br/>工业机器人技术专业群"/>
          </el-table-column>
        </el-table-column>
      </el-table-column>
    </el-table-column>
  </el-table>
</template>
<script>
import { exportJsonToXSLX } from "./data_export";
export default {
  data() {
    return {
      exportData: []
    }
  },
  methods: {
    // 数据导出
    dataExport() {
      this.exportData = [
        {  a: 1, b: "1", c: "1", d: "1", e: "1", f: "1", g: "1", h: "1", i: "1", j: "1", k: "1"},
        {  a: 12, b: "12", c: "12", d: "12", e: "1", f: "1", g: "1", h: "1", i: "1", j: "1", k: "1"},
        {  a: 13, b: "13", c: "1", d: "1", e: "1", f: "1", g: "1", h: "1", i: "1", j: "1", k: "1"},
        {  a: 14, b: "13", c: "1", d: "1", e: "1", f: "1", g: "1", h: "1", i: "1", j: "1", k: "1"},
        {  a: 15, b: "15", c: "1", d: "1", e: "1", f: "1", g: "1", h: "1", i: "1", j: "1", k: "1"},
        {  a: 16, b: "16", c: "1", d: "1", e: "1", f: "1", g: "1", h: "1", i: "1", j: "1", k: "1"},
      ]
      setTimeout(() => {
        // rowSpan为表头行数
        exportJsonToXSLX({ name: "导出名称", dataLength: 6, fieldslength: 11, ele: 'export-table', rowSpan: 4 })
      }, 500)
    }
  }
}
</script>