【js】exceljs生成excel并导出

描述

前端导出 excel 文件支持多个工作表、多级表头、多级表头合并单元格、数据填充
excel 处理使用库：exceljs
文件导出使用库：file-saver
exceljs: https://github.com/exceljs/exceljs

示例

安装

版本参考：

{
  "exceljs": "^4.4.0",
  "file-saver": "^2.0.5"
}

npm install exceljs file-saver

封装

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

/**
 * @typedef {Object} TitleTreeItem
 * @property {string} name - 表头名称
 * @property {string} field - 字段名
 * @property {TitleTreeItem[]} [children] - 子节点
 */

/**
 * 导出 excel
 * @param {TitleTreeItem[]} titleTree
 * @param {Object} data
 * @param {string} [fileName]
 * @returns {Promise<void>}
 */
export async function exportExcel(titleTree, data, fileName = `${new Date().valueOf()}.xlsx`) {
  const fileBlob = await generateExcel(titleTree, data);
  saveAs(fileBlob, fileName);
}

/**
 * 生成 excel
 * @param {TitleTreeItem[]} titleTree
 * @param {Object} data
 * @returns {Promise<Blob>}
 */
function generateExcel(titleTree, data) {
  return new Promise((resolve, reject) => {
    const workbook = new ExcelJS.Workbook();

    // 遍历第一层级创建工作表
    for (const sheet of titleTree) {
      const worksheet = workbook.addWorksheet(sheet.name);
      const sheetData = data[sheet.field] || [];

      const headerRows = [];
      let maxDepth = 0;

      // 计算树的最大深度
      function getTreeDepth(node, depth = 0) {
        maxDepth = Math.max(maxDepth, depth);
        if (node.children && node.children.length) {
          node.children.forEach((child) => getTreeDepth(child, depth + 1));
        }
      }

      // 获取节点宽度
      function getNodeWidth(node) {
        if (!node.children || !node.children.length) {
          return 1;
        }
        return node.children.reduce((sum, child) => sum + getNodeWidth(child), 0);
      }

      // 获取节点的最大深度
      function getNodeMaxDepth(node, depth = 0) {
        if (!node.children || node.children.length === 0) {
          return depth;
        }
        return Math.max(...node.children.map((child) => getNodeMaxDepth(child, depth + 1)));
      }

      // 构建表头数据
      function buildHeader(node, depth = 0, startCol = 1) {
        if (!headerRows[depth]) {
          headerRows[depth] = [];
        }

        const cell = {
          value: node.name,
          field: node.field,
          startCol,
          rowSpan: 1,
          colSpan: 1,
        };

        if (node.children && node.children.length) {
          let currentCol = startCol;
          node.children.forEach((child) => {
            const childWidth = getNodeWidth(child);
            buildHeader(child, depth + 1, currentCol);
            currentCol += childWidth;
          });
          cell.colSpan = currentCol - startCol;
        } else {
          const currentNodeMaxDepth = getNodeMaxDepth(node);
          cell.rowSpan = maxDepth - depth - (currentNodeMaxDepth - 1);
        }

        headerRows[depth].push(cell);
        return cell.colSpan;
      }

      // 处理当前工作表的表头
      if (sheet.children) {
        sheet.children.forEach((root) => {
          getTreeDepth(root);
        });

        let currentCol = 1;
        sheet.children.forEach((root) => {
          const width = buildHeader(root, 0, currentCol);
          currentCol += width;
        });

        // 将表头数据写入工作表
        headerRows.forEach((row, rowIndex) => {
          row.forEach((cell) => {
            const excelCell = worksheet.getCell(rowIndex + 1, cell.startCol);
            excelCell.value = cell.value;

            // 合并单元格
            if (cell.colSpan > 1 || cell.rowSpan > 1) {
              worksheet.mergeCells(rowIndex + 1, cell.startCol, rowIndex + cell.rowSpan, cell.startCol + cell.colSpan - 1);
            }

            // 设置样式
            excelCell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFCCCCCC" } };
            excelCell.alignment = { vertical: "middle", horizontal: "center" };
            excelCell.border = {
              top: { style: "thin" },
              left: { style: "thin" },
              bottom: { style: "thin" },
              right: { style: "thin" },
            };
          });
        });

        // 写入数据
        const dataRow = maxDepth + 2;
        sheetData.forEach((item, index) => {
          let col = 1;
          const flattenNodes = getFlattenLeafNodes(sheet.children);
          flattenNodes.forEach((node) => {
            const cell = worksheet.getCell(dataRow + index, col);
            const value = item[node.field] || "";
            cell.value = value;
            cell.alignment = {
              vertical: "middle",
              horizontal: "center",
            };
            cell.border = {
              top: { style: "thin" },
              left: { style: "thin" },
              bottom: { style: "thin" },
              right: { style: "thin" },
            };
            col++;
          });
        });

        // 确保空白单元格也有边框
        const totalRows = dataRow + sheetData.length - 1;
        const totalCols = getFlattenLeafNodes(sheet.children).length;

        for (let row = 1; row <= totalRows; row++) {
          for (let col = 1; col <= totalCols; col++) {
            const cell = worksheet.getCell(row, col);
            if (!cell.border) {
              cell.border = {
                top: { style: "thin" },
                left: { style: "thin" },
                bottom: { style: "thin" },
                right: { style: "thin" },
              };
            }
          }
        }

        // 调整列宽
        worksheet.columns.forEach((column) => {
          column.width = 15;
        });
      }
    }

    // 生成文件
    workbook.xlsx
      .writeBuffer()
      .then((buffer) => {
        const fileBlob = new Blob([buffer]);
        resolve(fileBlob);
      })
      .catch((error) => {
        reject(error);
      });
  });
}

/**
 * 获取所有叶子节点
 * @param nodes
 * @returns {*[]}
 */
function getFlattenLeafNodes(nodes) {
  const result = [];

  function traverse(node) {
    if (!node.children || node.children.length === 0) {
      result.push(node);
    } else {
      node.children.forEach((child) => traverse(child));
    }
  }

  nodes.forEach((node) => traverse(node));
  return result;
}

使用

<template>
  <button @click="exportExcel(titleTree, data)">导出</button>
</template>

<script setup>
  import { exportExcel } from "@/utils/excel.js";

  // 工作表及表头数据-树形结构
  const titleTree = [
    {
      name: "工作表1",
      field: "sheet1",
      children: [
        { name: "名称1", field: "name1" },
        {
          name: "名称2",
          children: [
            { name: "名称3", field: "name3" },
            { name: "名称4", field: "name4" },
          ],
        },
      ],
    },
    {
      name: "工作表2",
      field: "sheet2",
      children: [
        {
          name: "名称1",
          field: "name1",
          children: [
            { name: "名称3", field: "name3" },
            { name: "名称4", field: "name4" },
          ],
        },
        { name: "名称2", field: "name2" },
      ],
    },
  ];

  // 工作表数据
  const data = {
    sheet1: [
      {
        name1: "name1数据",
        name3: "name3数据",
        name4: "name4数据",
      },
      {
        name1: "name1数据",
      },
    ],
    sheet2: [
      {
        name2: "name2数据",
      },
    ],
  };
</script>