vue2使用xlsx插件导出xlsx并通过xlsx-style修改样式

1.下载依赖

npm i xlsx
npm i xlsx-style
npm i file-saver

2.在根目录utils新建mergeXlxs.js

注意：导入xlsx-style会报错请参考这两篇文档

vue导出element表格，xlsx和xlsx-style生成xlsx文件并修改样式-CSDN博客 

xlsx-style使用中常见问题及解决办法-CSDN博客

/**
 *
 */
import { utils } from "xlsx";
import XlSXS from "xlsx-style";
import FileSaver from "file-saver";


export default function mergeHeader(headers, data, datamerges, defaultTitle) {
  const ws = utils.book_new();

  utils.sheet_add_aoa(ws, headers);

  //这里要给表头预留位置
  const wsd = utils.sheet_add_json(ws, data, {
    origin: "A" + (headers.length + 1),
    skipHeader: true,
  });

  ws["!merges"] = datamerges;

  var title = defaultTitle || "列表";
  const wb = { Sheets: { data: ws }, SheetNames: ["data"] };


  // 定义边框样式
  const border = {
    top: {
      style: "thin",
    },
    bottom: {
      style: "thin",
    },
    left: {
      style: "thin",
    },
    right: {
      style: "thin",
    },
  };
  
  //遍历循环每个单元格添加边框样式
  for (const i in wb.Sheets.data) {
    if (i !== "!ref") {
      ws[i].s = {
        border,
      };
    }
  }

  //使用 xlsx-style 写入文件方式，使得自定义样式生效
  let wbout = XlSXS.write(wb, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary",
  });


  FileSaver.saveAs(
    new Blob([s2ab(wbout)], {
      type: "application/octet-stream",
    }),
    `${title}.xlsx`
  );

}

/**
 * 工具方法
 */
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;
}

3.页面使用

 先导入mergeXlxs.js

import mergeHeader from "@/utils/mergeXlxs";

写在方法里 

    const rows = [];
    // s开始：x      y       e结束：x     y
    var datamerges = [
        // x轴从索引0开始到索引8合并，y轴从索引0开始到索引0结束
        { s: { c: 0, r: 0 }, e: { c: 8, r: 0 } },

        { s: { c: 0, r: 1 }, e: { c: 1, r: 1 } },
        { s: { c: 2, r: 1 }, e: { c: 8, r: 1 } },

        { s: { c: 0, r: 2 }, e: { c: 1, r: 2 } },
        { s: { c: 2, r: 2 }, e: { c: 8, r: 2 } },

        // 收货单位(需方)    供货单位(供方)
        { s: { c: 0, r: 8 }, e: { c: 3, r: 8 } },
        { s: { c: 5, r: 8 }, e: { c: 8, r: 8 } },

        { s: { c: 0, r: 9 }, e: { c: 3, r: 9 } },
        { s: { c: 5, r: 9 }, e: { c: 8, r: 9 } },

        { s: { c: 0, r: 10 }, e: { c: 3, r: 10 } },
        { s: { c: 5, r: 10 }, e: { c: 8, r: 10 } },
    ];
    let headers = [];

    //每个合并的开始位置，也就是s的c位置要有数据
    headers.push([
        "2023年11月  材料汇总表",
    ]);
    headers.push(["工程名称", "", "博望一期项目"]);
    headers.push(["供货单位", "", "广东沟泰建材贸易有限公司"]);
    headers.push(["序号", "名称", "厂家品牌", "规格型号", "供货日期", "供货单号", "单位", "数量", "备注"]);
    headers.push(["1", "钢筋", "天堂", "1kg", "2023-11-29", "DO20231128", "吨", "0", ""]);
    headers.push(["2", "钢筋", "天堂", "1kg", "2023-11-29", "DO20231128", "吨", "100", ""]);
    headers.push(["3", "钢筋", "天堂", "1kg", "2023-11-29", "DO20231128", "吨", "100", ""]);
    headers.push(["4", "合计", "", "", "", "", "", "200", ""]);
    headers.push(["收货单位(需方)：", "", "", "", "", "供货单位(供方):"]);
    headers.push(["授权代表(签字)：", "", "", "", "", "授权代表(签字):"]);
    headers.push(["日期：", "", "", "", "", "日期:"]);

    mergeHeader(headers, rows, datamerges, "测试合并导出");