vue3 vite4 json数据导出为excel

前话：我只是个工作的小菜鸟，如果有大佬有更好的方法，希望不吝赐教。

该文章是 json数据导出为excel，我有另一篇文章是 table的dom结构转为excel。看你自己怎么选择。如果不懂，或者需要协助，+q：四五七五零五四六八（有偿）

1.电脑环境

node版本 18  

该方法直接适用于vite项目，如果需要在webpack中使用，需要替换 xlsx-style-vite 插件为webpack版的 xlsx-style ，但是这个插件作者没有维护了，可能存在问题。请看我的另一篇关于excel导出的文章。

这是我用到的三个插件的版本，自行安装，版本请保持和我一致。

    "xlsx": "^0.16.9",
    "file-saver": "^2.0.5",
    "xlsx-style-vite": "0.0.2"

适配了复杂表头情况，如下所示。

直接粘贴源代码

vue组件

<template>
  <div>
    <el-button type="primary" @click="download">下载</el-button>
  </div>
</template>

<script lang="ts">
import { download } from "@/utils";
import { onMounted, onUnmounted, ref, reactive, inject } from "vue";
import { useRoute, useRouter } from "vue-router";
import exportData from "@/utils/down";
</script>

<script setup lang="ts">
const tableHeader = [
  {
    name: "日期",
    prop: "date",
    child: [
      { name: "姓名", prop: "name" },
      { name: "省份", prop: "state" },
      { name: "城市", prop: "city" },
      { name: "详细地址", prop: "address" },
      { name: "门牌号", prop: "zip" },
    ],
  },
];

const tableData = [
  {
    date: "2016-05-03",
    name: "Tom",
    state: "California",
    city: "Los Angeles",
    address: "No. 189, Grove St, Los Angeles",
    zip: "CA 90036",
  },
  {
    date: "2016-05-02",
    name: "Tom",
    state: "California",
    city: "Los Angeles",
    address: "No. 189, Grove St, Los Angeles",
    zip: "CA 90036",
  },
  {
    date: "2016-05-04",
    name: "Tom",
    state: "California",
    city: "Los Angeles",
    address: "No. 189, Grove St, Los Angeles",
    zip: "CA 90036",
  },
  {
    date: "2016-05-01",
    name: "Tom",
    state: "California",
    city: "Los Angeles",
    address: "No. 189, Grove St, Los Angeles",
    zip: "CA 90036",
  },
  {
    date: "2016-05-08",
    name: "Tom",
    state: "California",
    city: "Los Angeles",
    address: "No. 189, Grove St, Los Angeles",
    zip: "CA 90036",
  },
  {
    date: "2016-05-06",
    name: "Tom",
    state: "California",
    city: "Los Angeles",
    address: "No. 189, Grove St, Los Angeles",
    zip: "CA 90036",
  },
  {
    date: "2016-05-07",
    name: "Tom",
    state: "California",
    city: "Los Angeles",
    address: "No. 189, Grove St, Los Angeles",
    zip: "CA 90036",
  },
];

const download = () => {
  exportData(tableHeader, tableData);
};
defineExpose({}); //抛出外部可访问的属性
</script>

<style scoped lang="less"></style>

js代码，创建个模块

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

export default function exportData(tableHeader, tableData = []) {
  let sheetName = "xlsx复杂表格导出demo";

  let wb = XLSX.utils.book_new();

  let excelHeader = buildHeader(tableHeader);
  // 头部行数，用来固定表头
  let headerRows = excelHeader.length;
  // 提取数据
  let dataList = extractData(tableData, tableHeader);
  excelHeader.push(...dataList, []);
  // 计算合并
  let merges = doMerges(excelHeader);
  // 生成sheet
  let ws = aoa_to_sheet(excelHeader, headerRows);
  // 单元格合并
  ws["!merges"] = merges;
  // 头部冻结
  ws["!freeze"] = {
    xSplit: "1",
    ySplit: "" + headerRows,
    topLeftCell: "B" + (headerRows + 1),
    activePane: "bottomRight",
    state: "frozen",
  };
  //自动设置列宽
  const treeList = treeEndObj(tableHeader);
  const arr = json_to_array(treeList, tableData);
  auto_width(ws, arr);

  //删除空数据
  let keys = Object.keys(ws);
  keys.forEach((item, index) => {
    if (ws[item].v == "") {
      delete ws[item];
    }
  });
  //  sheet添加到工作薄上
  XLSX.utils.book_append_sheet(wb, ws, "sheet1");

  const etout = XLSXstyle.write(wb, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary",
  });

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

  return wb;
}

function auto_width(ws, data) {
  const colWidth = data.map((row) =>
    row.map((val) => {
      if (val == null) {
        return { wpx: 10 };
      } else {
        return getTextWidth(val.toString());
      }
    })
  );
  let result = [];
  for (let index = 0; index < colWidth[0].length; index++) {
    // const element = array[index];
    const list = colWidth.map((ppp, i) => ppp[index]);
    result.push({ wpx: Math.max(...list) });
  }
  ws["!cols"] = result;
}

// 将json数据转换成数组
function json_to_array(key, jsonData) {
  const a = jsonData.map((v) =>
    key.map((j) => {
      return v[j.prop];
    })
  );

  const b = key.map((v) => v.name);
  return [b, ...a];
}

function getTextWidth(str, fontSize = 12) {
  let result = 0;
  let ele = document.createElement("span");
  //字符串中带有换行符时，会被自动转换成<br/>标签，若需要考虑这种情况，可以替换成空格，以获取正确的宽度
  str = str.replace(/\\n/g, " ").replace(/\\r/g, " ");
  ele.innerText = str;
  //不同的大小和不同的字体都会导致渲染出来的字符串宽度变化，可以传入尽可能完备的样式信息
  ele.style.fontSize = fontSize;
  //由于父节点的样式会影响子节点，这里可按需添加到指定节点上
  document.documentElement.append(ele);

  result = ele.offsetWidth;
  document.documentElement.removeChild(ele);
  return result;
}

function treeEndObj(tableHeader) {
  let arr = [];
  find(tableHeader);
  return arr;

  function find(d) {
    d.forEach((item) => {
      if (item.child && item.child.length > 0) {
        find(item.child);
      } else {
        arr.push(item);
      }
    });
  }
}

/**
 * 构建excel表头
 * @param revealList 列表页面展示的表头
 * @returns {[]} excel表格展示的表头
 */
function buildHeader(revealList) {
  let excelHeader = [];
  // 构建生成excel表头需要的数据结构
  getHeader(revealList, excelHeader, 0, 0);
  // 多行表头长短不一，短的向长的看齐，不够的补上行合并占位符
  let max = Math.max(...excelHeader.map((a) => a.length));
  excelHeader
    .filter((e) => e.length < max)
    .forEach((e) => pushRowSpanPlaceHolder(e, max - e.length));
  return excelHeader;
}

/**
 * 生成头部
 * @param headers 展示的头部
 * @param excelHeader excel头部
 * @param deep 深度
 * @param perOffset 前置偏移量
 * @returns {number}  后置偏移量
 */
function getHeader(headers, excelHeader, deep, perOffset) {
  let offset = 0;
  let cur = excelHeader[deep];
  if (!cur) {
    cur = excelHeader[deep] = [];
  }
  // 填充行合并占位符
  pushRowSpanPlaceHolder(cur, perOffset - cur.length);
  for (let i = 0; i < headers.length; i++) {
    let head = headers[i];
    cur.push(head.name);
    if (
      head.hasOwnProperty("child") &&
      Array.isArray(head.child) &&
      head.child.length > 0
    ) {
      let childOffset = getHeader(
        head.child,
        excelHeader,
        deep + 1,
        cur.length - 1
      );
      // 填充列合并占位符
      pushColSpanPlaceHolder(cur, childOffset - 1);
      offset += childOffset;
    } else {
      offset++;
    }
  }
  return offset;
}

/**
 * 根据选中的数据和展示的列，生成结果
 * @param selectionData
 * @param revealList
 */
function extractData(selectionData, revealList) {
  // 列
  let headerList = flat(revealList);
  // 导出的结果集
  let excelRows = [];
  // 如果有child集合的话会用到
  let dataKeys = new Set(Object.keys(selectionData[0]));
  selectionData.some((e) => {
    if (e.child && e.child.length > 0) {
      let childKeys = Object.keys(e.child[0]);
      for (let i = 0; i < childKeys.length; i++) {
        dataKeys.delete(childKeys[i]);
      }
      return true;
    }
  });
  flatData(selectionData, (list) => {
    excelRows.push(...buildExcelRow(dataKeys, headerList, list));
  });
  return excelRows;
}

function buildExcelRow(mainKeys, headers, rawDataList) {
  // 合计行
  let sumCols = [];
  // 数据行
  let rows = [];
  for (let i = 0; i < rawDataList.length; i++) {
    let cols = [];
    let rawData = rawDataList[i];
    // 提取数据
    for (let j = 0; j < headers.length; j++) {
      let header = headers[j];
      // 父元素键需要行合并
      if (rawData["rowSpan"] === 0 && mainKeys.has(header.prop)) {
        cols.push("!$ROW_SPAN_PLACEHOLDER");
      } else {
        let value;
        if (typeof header.exeFun === "function") {
          value = header.exeFun(rawData);
        } else {
          value = rawData[header.prop];
        }
        cols.push(value);
        // 如果该列需要合计,并且是数字类型
        if (header["summable"] && typeof value === "number") {
          sumCols[j] = (sumCols[j] ? sumCols[j] : 0) + value;
        }
      }
    }
    rows.push(cols);
  }
  // 如果有合计行
  if (sumCols.length > 0) {
    rows.push(...sumRowHandle(sumCols));
  }
  return rows;
}

function sumRowHandle(sumCols) {
  //TODO
  return [];
}

/**
 * 合并头部单元格
 **/
function doMerges(arr) {
  // 要么横向合并 要么纵向合并
  let deep = arr.length;
  let merges = [];
  for (let y = 0; y < deep; y++) {
    // 先处理横向合并
    let row = arr[y];
    let colSpan = 0;
    for (let x = 0; x < row.length; x++) {
      if (row[x] === "!$COL_SPAN_PLACEHOLDER") {
        row[x] = undefined;
        if (x + 1 === row.length) {
          merges.push({
            s: { r: y, c: x - colSpan - 1 },
            e: { r: y, c: x },
          });
        }
        colSpan++;
      } else if (colSpan > 0 && x > colSpan) {
        merges.push({
          s: { r: y, c: x - colSpan - 1 },
          e: { r: y, c: x - 1 },
        });
        colSpan = 0;
      } else {
        colSpan = 0;
      }
    }
  }
  // 再处理纵向合并
  let colLength = arr[0].length;
  for (let x = 0; x < colLength; x++) {
    let rowSpan = 0;
    for (let y = 0; y < deep; y++) {
      if (arr[y][x] === "!$ROW_SPAN_PLACEHOLDER") {
        arr[y][x] = undefined;
        if (y + 1 === deep) {
          merges.push({
            s: { r: y - rowSpan, c: x },
            e: { r: y, c: x },
          });
        }
        rowSpan++;
      } else if (rowSpan > 0 && y > rowSpan) {
        merges.push({
          s: { r: y - rowSpan - 1, c: x },
          e: { r: y - 1, c: x },
        });
        rowSpan = 0;
      } else {
        rowSpan = 0;
      }
    }
  }
  return merges;
}

function aoa_to_sheet(data, headerRows) {
  const ws = {};
  const range = {
    s: { c: 10000000, r: 10000000 },
    e: { c: 0, r: 0 },
  };

  for (let R = 0; R !== data.length; ++R) {
    for (let C = 0; C !== data[R].length; ++C) {
      if (range.s.r > R) {
        range.s.r = R;
      }
      if (range.s.c > C) {
        range.s.c = C;
      }
      if (range.e.r < R) {
        range.e.r = R;
      }
      if (range.e.c < C) {
        range.e.c = C;
      }
      /// 这里生成cell的时候，使用上面定义的默认样式

      const cell = {
        v: data[R][C] || "",
        s: {
          font: {
            sz: 11, //字体大小
            bold: false, //加粗
            name: "宋体", //字体
            color: {
              rgb: "000000", //十六进制，不带#
            },
          },
          alignment: {
            //文字居中
            horizontal: "center",
            vertical: "center",
            wrapText: false, //文本自动换行
          },
        },
      };

      // 头部(表头)列表加边框
      if (R < headerRows) {
        //  填充
        cell.s.fill = {
          fgColor: {
            rgb: "C1CCEE", //只支持#格式，但是不能带#
          },
        };

        cell.s.font = {
          sz: 11,
          bold: true,
          color: { rgb: "32,33,36" },
        };
      }

      const cell_ref = XLSX.utils.encode_cell({ c: C, r: R });

      if (typeof cell.v === "number") {
        cell.t = "n";
      } else if (typeof cell.v === "boolean") {
        cell.t = "b";
      } else {
        cell.t = "s";
      }
      ws[cell_ref] = cell;
    }
  }
  if (range.s.c < 10000000) {
    ws["!ref"] = XLSX.utils.encode_range(range);
  }
  return ws;
}

/**
 * 填充行合并占位符
 * */
function pushRowSpanPlaceHolder(arr, count) {
  for (let i = 0; i < count; i++) {
    arr.push("!$ROW_SPAN_PLACEHOLDER");
  }
}

// 填充列合并占位符
function pushColSpanPlaceHolder(arr, count) {
  for (let i = 0; i < count; i++) {
    arr.push("!$COL_SPAN_PLACEHOLDER");
  }
}

/**
 * 展开数据，为了实现父子关系的数据进行行合并
 * @param list
 * @param eachDataCallBack
 */
function flatData(list, eachDataCallBack) {
  let resultList = [];
  for (let i = 0; i < list.length; i++) {
    let data = list[i];
    let rawDataList = [];
    // 每个子元素都和父元素合并成一条数据
    if (data.child && data.child.length > 0) {
      for (let j = 0; j < data.child.length; j++) {
        delete data.child[j].bsm;
        let copy = Object.assign({}, data, data.child[j]);
        rawDataList.push(copy);
        copy["rowSpan"] = j > 0 ? 0 : data.child.length;
      }
    } else {
      data["rowSpan"] = 1;
      rawDataList.push(data);
    }
    resultList.push(...rawDataList);
    if (typeof eachDataCallBack === "function") {
      eachDataCallBack(rawDataList);
    }
  }
  return resultList;
}

// 扁平头部
function flat(revealList) {
  let result = [];
  revealList.forEach((e) => {
    if (e.hasOwnProperty("child")) {
      result.push(...flat(e.child));
    } else if (e.hasOwnProperty("exeFun")) {
      result.push(e);
    } else if (e.hasOwnProperty("prop")) {
      result.push(e);
    }
  });
  return result;
}

function s2ab(s) {
  let buf = new ArrayBuffer(s.length);
  let view = new Uint8Array(buf);
  for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
  return buf;
}