import * as XLSX from "xlsx";
import FileSaver from "file-saver";

一、单个table导出到一个excel工作表中

function handleExport() {
  const ws = XLSX.utils.table_to_sheet(
    document.getElementById("incomeInfo-table")
  );
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); //添加工作表并命名
  //导出Excel文件
  const wbout = XLSX.write(wb, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary"
  });

  function s2ab(s) {
    //将字符串转为ArrayBuffer
    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;
  }
  //创建Blob对象并下载
  FileSaver.saveAs(
    new Blob([s2ab(wbout)], { type: "application/octet-stream" }),
    "收入信息.xlsx"
  );
}

二、多个table导出到同一个excel工作簿的一个工作表中

function formatDate(dateObjOrString) {
  if (typeof dateObjOrString === "number") {
    return XLSX.SSF.format("yyyy-mm-dd", dateObjOrString);
  } else if (dateObjOrString instanceof Date) {
    return XLSX.SSF.format(format, dateObjOrString.getTime() / 1000); // 转换为Excel序列数后格式化
  } else {
    return dateObjOrString;
  }
}
function handleTableExport() {
  const ws1 = XLSX.utils.table_to_sheet(
    document.getElementById("incomeInfo-table")
  );
  const ws2 = XLSX.utils.table_to_sheet(
    document.getElementById("constInfo-table")
  );
  //使用XLSX.utils.sheet_to_json后，不会保留数据格式
  //合并数据
  const ws1Data = XLSX.utils.sheet_to_json(ws1, { header: 1, raw: true });
  const ws2Data = XLSX.utils.sheet_to_json(ws2, { header: 1, raw: true });
  //日期格式
  for (let key in ws1Data[0]) {
    ws1Data[0][key] = this.formatDate(ws1Data[0][key]);
  }
  for (let key in ws2Data[0]) {
    ws2Data[0][key] = this.formatDate(ws2Data[0][key]);
  }
  //百分比格式
  for (let key in ws2Data[ws2Data.length - 1]) {
    ws2Data[ws2Data.length - 1][key] =
      typeof ws2Data[ws2Data.length - 1][key] === "number"
        ? ws2Data[ws2Data.length - 1][key] * 100 + "%"
        : ws2Data[ws2Data.length - 1][key];
  }
  const combinedData = ws1Data.concat(ws2Data);
  //创建工作薄和工作表
  const wb = XLSX.utils.book_new();
  const combinedWs = XLSX.utils.aoa_to_sheet(combinedData, {
    cellStyles: true
  });
  //提取合并信息
  const merges1 = ws1["!merges"] || [];
  const merges2 = ws2["!merges"] || [];
  //调整合并范围以适应合并后的新数据起始位置
  const mergeOffset = ws1Data.length; //假设ws1之后直接ws2，无额外行
  const adjustedMerges2 = merges2.map(merge => ({
    //表示合并单元格的起始位置(start),包含行号(r)和列号(c)
    s: { r: merge.s.r + mergeOffset, c: merge.s.c },
    //表示合并单元格的结束位置(end),同样包含行号和列号
    e: { r: merge.e.r + mergeOffset, c: merge.e.c }
  }));
  //合并合并信息
  const combinedMerges = merges1.concat(adjustedMerges2);
  //应用合并信息到新工作表
  combinedWs["!merges"] = combinedMerges;
  XLSX.utils.book_append_sheet(wb, combinedWs, "combinedSheet");
  //导出Excel文件
  const wbout = XLSX.write(wb, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary"
  });

  function s2ab(s) {
    //将字符串转为ArrayBuffer
    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;
  }
  //创建Blob对象并下载
  FileSaver.saveAs(
    new Blob([s2ab(wbout)], { type: "application/octet-stream" }),
    "详情.xlsx"
  );
}