前端数据导出excel工具函数

npm install exceljs
npm install file-saver

 

 

import ExcelJS from "exceljs";
import { saveAs } from "file-saver";

/**
 * 树形多级表头导出（最终修复：列不会乱跑、跨列完全正确）
 * @param {Array} columns  [{ prop, label, children }] 树形表头
 * @param {Array} rows     数据数组
 * @param {String} fileName 导出文件名
 */
export async function dataToExcel(
  columns = [],
  rows = [],
  fileName = "表格导出",
) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sheet1");

  // ===================== 核心修复：构建表头 =====================
  let headerRows = [];
  let merges = [];
  let maxLevel = 0;

  // 递归获取表头最大深度
  function getMaxLevel(columns, level = 0) {
    maxLevel = Math.max(maxLevel, level);
    columns.forEach(
      (col) => col.children?.length && getMaxLevel(col.children, level + 1),
    );
  }
  getMaxLevel(columns);

  // 递归计算叶子节点数量
  function getLeafCount(cols) {
    return cols.reduce(
      (sum, col) =>
        sum + (col.children?.length ? getLeafCount(col.children) : 1),
      0,
    );
  }

  // 递归构建表头（关键修复：空单元格自动补位）
  function buildHeader(cols, level = 0, startCol = 0) {
    if (!headerRows[level]) headerRows[level] = [];
    let col = startCol;

    cols.forEach((item) => {
      const leafCount = item.children?.length ? getLeafCount(item.children) : 1;

      // 给当前单元格赋值
      headerRows[level][col] = item.label;

      // 合并规则
      if (item.children?.length) {
        // 父节点 → 跨列
        merges.push({
          start: { row: level + 1, col: col + 1 },
          end: { row: level + 1, col: col + leafCount },
        });
        buildHeader(item.children, level + 1, col);
      } else {
        // 子节点 → 跨行
        merges.push({
          start: { row: level + 1, col: col + 1 },
          end: { row: maxLevel + 1, col: col + 1 },
        });
      }

      col += leafCount;
    });
  }

  buildHeader(columns);

  // ===================== 关键修复：补全每一行的空列，防止列偏移 =====================
  const totalCols = getLeafCount(columns);
  headerRows = headerRows.map((row) => {
    const newRow = [];
    for (let i = 0; i < totalCols; i++) newRow[i] = row[i] ?? "";
    return newRow;
  });

  // ===================== 写入表头 =====================
  headerRows.forEach((row) => {
    const excelRow = worksheet.addRow(row);
    excelRow.font = { bold: true };
    excelRow.alignment = { horizontal: "center", vertical: "middle" };
  });

  // 执行合并
  merges.forEach(({ start, end }) => {
    worksheet.mergeCells(start.row, start.col, end.row, end.col);
  });

  // ===================== 写入数据 =====================
  function getProps(cols) {
    let arr = [];
    cols.forEach((c) =>
      c.children?.length
        ? (arr = arr.concat(getProps(c.children)))
        : arr.push(c.prop),
    );
    return arr;
  }
  const props = getProps(columns);
  rows.forEach((item) => worksheet.addRow(props.map((p) => item[p] ?? "")));

  // ===================== 全黑色边框 =====================
  const totalRows = worksheet.rowCount;
  for (let r = 1; r <= totalRows; r++) {
    for (let c = 1; c <= totalCols; c++) {
      const cell = worksheet.getCell(r, c);
      cell.border = {
        top: { style: "thin", color: { argb: "FF000000" } },
        left: { style: "thin", color: { argb: "FF000000" } },
        bottom: { style: "thin", color: { argb: "FF000000" } },
        right: { style: "thin", color: { argb: "FF000000" } },
      };
      cell.alignment = { horizontal: "center", vertical: "middle" };
    }
  }

  // ===================== 列宽 + 保存 =====================
  worksheet.columns.forEach((col) => (col.width = 16));
  const buffer = await workbook.xlsx.writeBuffer();
  const blob = new Blob([buffer], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
  });
  saveAs(blob, `${fileName}.xlsx`);
}

 

 

用法示例：

 

// 多级表头
const columns = [
  { prop: 'name', label: '姓名' },
  {
    label: '成绩',
    children: [
      { prop: 'chinese', label: '语文' },
      { prop: 'math', label: '数学' }
    ]
  }
];

// 数据
const rows = [
  { name: '张三', chinese: 90, math: 95 },
  { name: '李四', chinese: 85, math: 92 }
];

// 导出
dataToExcel(columns, rows, '学生成绩表');