前端数据导出excel工具函数

npm install exceljs
npm install file-saver

 

 

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

/**
 * 树形多级表头 Excel 导出（默认行高26 + 全自定义配置）
 * @param {Array} columns  [{ prop, label, children, width, height, fontSize, isBold }]
 * @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 headerFontConfigs = [];
  let headerRowHeights = [];
  let merges = [];
  let maxLevel = 0;

  // 获取最大层级
  function getMaxLevel(cols, level = 0) {
    maxLevel = Math.max(maxLevel, level);
    cols.forEach(c => c.children?.length && getMaxLevel(c.children, level + 1));
  }
  getMaxLevel(columns);

  // 计算叶子列数
  function getLeafCount(cols) {
    return cols.reduce((sum, c) => sum + (c.children?.length ? getLeafCount(c.children) : 1), 0);
  }
  const totalCols = getLeafCount(columns);

  // 递归构建表头 + 收集配置
  function buildHeader(cols, level = 0, startCol = 0) {
    if (!headerRows[level]) headerRows[level] = [];
    if (!headerFontConfigs[level]) headerFontConfigs[level] = [];
    if (!headerRowHeights[level]) headerRowHeights[level] = 26; // 默认行高改为 26
    let col = startCol;

    cols.forEach(item => {
      const leafCount = item.children?.length ? getLeafCount(item.children) : 1;
      
      headerRows[level][col] = item.label;
      headerFontConfigs[level][col] = {
        fontSize: item.fontSize ?? 12,
        isBold: item.isBold ?? true,
      };

      // 行高取最大值
      if (item.height) {
        headerRowHeights[level] = Math.max(headerRowHeights[level], item.height);
      }

      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);

  // 补全空列
  headerRows = headerRows.map(row => {
    const newRow = [];
    for (let i = 0; i < totalCols; i++) newRow[i] = row[i] ?? '';
    return newRow;
  });
  headerFontConfigs = headerFontConfigs.map(row => {
    const newRow = [];
    for (let i = 0; i < totalCols; i++) newRow[i] = row[i] ?? { fontSize: 12, isBold: true };
    return newRow;
  });

  // ===================== 写入表头 =====================
  headerRows.forEach((row, levelIndex) => {
    const excelRow = worksheet.addRow(row);
    excelRow.height = headerRowHeights[levelIndex];
    excelRow.alignment = { horizontal: 'center', vertical: 'middle' };
    excelRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFEFEFEF' }
    };

    row.forEach((_, colIndex) => {
      const cell = excelRow.getCell(colIndex + 1);
      const { fontSize, isBold } = headerFontConfigs[levelIndex][colIndex];
      cell.font = { bold: isBold, size: fontSize, color: { argb: 'FF000000' } };
    });
  });

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

  // ===================== 写入数据 =====================
  function getLeafPropsAndWidths(cols) {
    let props = [], widths = [];
    cols.forEach(c => {
      if (c.children?.length) {
        const child = getLeafPropsAndWidths(c.children);
        props = props.concat(child.props);
        widths = widths.concat(child.widths);
      } else {
        props.push(c.prop);
        widths.push(c.width ?? 16);
      }
    });
    return { props, widths };
  }
  const { props, widths: columnWidths } = getLeafPropsAndWidths(columns);

  rows.forEach(item => {
    const excelRow = worksheet.addRow(props.map(p => item[p] ?? ''));
    excelRow.height = 26; // 数据行默认高度也改为 26
  });

  // 设置列宽
  columnWidths.forEach((width, index) => {
    if (worksheet.columns[index]) worksheet.columns[index].width = width;
  });

  // ===================== 全黑色边框 + 居中 =====================
  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' };
    }
  }

  // ===================== 保存文件 =====================
  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: '姓名', 
    width: 12, 
    height: 30,    // 行高30
    fontSize: 14, 
    isBold: true 
  },
  {
    label: '基础信息',
    height: 25,    // 行高25
    fontSize: 13,
    isBold: true,
    children: [
      { prop: 'age', label: '年龄', width: 10, fontSize: 12, isBold: false },
      { prop: 'gender', label: '性别', width: 10, fontSize: 12, isBold: false },
    ]
  },
  {
    label: '成绩',
    fontSize: 13,
    isBold: true,
    children: [
      { prop: 'chinese', label: '语文', width: 14, fontSize: 12 },
      { prop: 'math', label: '数学', width: 14, fontSize: 12 },
    ]
  }
];

const rows = [
  { name: '张三', age: 20, gender: '男', chinese: 90, math: 95 },
  { name: '李四', age: 21, gender: '女', chinese: 85, math: 92 }
];

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