使用exceljs，根据传入的columns自动生成多级嵌套表格

exceljs导出

• 背景：原来的xlsxjs导出方案，一个是功能相对不够完善，另一个是使用起来比较复杂（在原有的封装基础上），对调用者有比较大的心智负担，需要手动去调用utils中的方法把我们常用的tableColumns 转换为 导出需要的表头，再进行导出，使用起来不是特别友好

• 目标

  • 能够有更强大的功能，使导出更为完善
  • 使用起来能够更加便捷，通常导出都是在有表格的场景导出，使用者只需要把表格的column配置以及表格的tableData传入即可导出

于是我们决定使用exceljs来替换原有的方案

github 中文文档 exceljs中文文档

在分析了该工具之后，我认为最大的问题在于导出表头的嵌套问题

首先按照官方的文档，一般设置表头的写法如下

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
]

按照这样子的写法，之后添加表格数据的时候，就可以添加key，能够跟表头的key一一对应上，但是考虑到表头嵌套的问题，以上写法无法满足我们的需求

所以我们只能把表头也当作普通数据，自己手动添加表头数据

假如有这么一段columns

const columns = [    {        title: "1",        dataIndex: "1",        children: [            {                title: "3",                dataIndex: "3"            },            {                title: "4",                dataIndex: "4"               },            {                title: "5",                dataIndex: "5"               },        ]
    },
    {
        title: "2",
        dataIndex: "2",
        children: [
            {
                title: "6",
                dataIndex: "6"
            },
            {
                title: "7",
                dataIndex: "7"   
            },
        ]
    }
]

对应以上columns

自己添加表头的格式大概如下

workSheet.addRows([    ['1', '', '', '2', '',],
    ['3', '4', '5', '6', '7']
])

添加之后，合并单元格，来展现最后的效果

而自己添加表头数据就有两个问题

• 表头的合并
• 数据跟表头数据的对应

表头合并

表头合并比较大的问题点在于如何指导每一个节点有多"宽"，这里需要考虑多层级的情况，而不单单是两层，

同时还要获取每个节点有多深，用于行合并，所以这里把columns想象成树结构，然后广度遍历，遍历的时候给每个节点加上startIndex, endIndex, depth(当前在第几层)，childrenLength （当前节点的子节点数量） 用于合并

function getMergedColumns(columns: TableColumnAllProps[], ColumnWidth: number) {
  let queue = [...columns];
  let tmpQueue = [];
  let depth = 0;
  const mergedColumns: TableColumnAllProps[][] = [];

  while (queue.length) {
    const column = queue.shift();

    if (!mergedColumns?.[depth]) {
      mergedColumns[depth] = [];
    }
    // 获取当前层级的节点
    const curLevelColumns = mergedColumns?.[depth];
    // 获取当前层级的前一个节点
    const lastColumn = curLevelColumns?.[curLevelColumns.length - 1];
    // 根据前一个节点判断当前节点的startIndex
    const startIndexByLastColumn = lastColumn?.endIndex ? lastColumn?.endIndex + 1 : 1;
    // 若当前节点为第一层的节点，则startIndex为startIndexByLastColumn，
    // 否则根据当前节点的父节点跟lastColumn的父节点是否是同一个节点判断当前节点的startIndex应该取父节点的startIndex还是startIndexByLastColumn
    const curStartIndex = !column.parent
      ? startIndexByLastColumn
      : column?.parent?.dataIndex === lastColumn?.parent?.dataIndex
        ? startIndexByLastColumn
        : column.parent.startIndex;

    column.startIndex = curStartIndex;

    // 获取当前节点的叶子节点的长度，用于填充二维数组
    const childrenLength = getChildLength(column.children, 0);

    const pushedColumn: TableColumnAllProps = {
      ...column,
      width: Math.floor((column.width as number) / 5) || ColumnWidth,
      startIndex: curStartIndex,
      endIndex: curStartIndex + (childrenLength ? childrenLength - 1 : 0),
      // 当前节点的深度，用于合并单元格
      depth: depth + 1,
      // 当前节点的叶子节点的长度，用于填充二维数组
      childrenLength: getChildLength(column.children, 0)
    };

    mergedColumns?.[depth]?.push(pushedColumn);

    if (column.children) {
      tmpQueue.push(...column.children);
    }

    if (queue.length === 0) {
      // 当前层节点遍历完成，切换到下一层
      if (tmpQueue.length) {
        queue = tmpQueue;
        tmpQueue = [];
        depth++;
      }
    }
  }

  return {
    totalDepth: depth + 1,
    mergedColumns
  };
}

之后根据上面获取的处理后的columns，就可以根据每个column的属性进行表头数据的添加

这里主要操作就是初始化一个二维数组，填充空字符串，然后在对应的位置把空字符串替换成表头的title

function handleHeader(
  mergedColumns: TableColumnAllProps[][],
  worksheet: Worksheet,
  totalDepth: number,
  headerStyle: Partial<XLSX.Style>
) {
  // 获取总长度，即第一行节点的childLength 之和
  const totalLength = mergedColumns?.[0]?.reduce((total, item) => total + item.childrenLength, 0);
  // 构建一个二位数组，用 ‘’ 占位
  const rows = new Array(totalDepth).fill(0).map(() => new Array(totalLength).fill(''));

  // 找到每个节点对应的下标，进行数据表头数据的填充
  mergedColumns.forEach((columns, index) => {
    columns.forEach(item => {
      rows[index][item?.startIndex - 1] = item.title;
    });
  });
  // 添加表头
  const headerRows = worksheet.addRows(rows);

  headerRows.forEach(row => {
    row.eachCell((cell, column) => {
      // 设置背景色
      // cell.fill = {
      //   type: 'pattern',
      //   pattern: 'solid',
      //   fgColor: { argb: 'dff8ff' }
      // };
      // 设置字体
      cell.font = {
        bold: true,
        italic: false,
        size: 14,
        name: '微软雅黑',
        color: { argb: '000' }
      };
      cell.style = { ...cell.style, ...headerStyle };
    });
  });

  // 合并单元格
  mergeColumns(mergedColumns, worksheet, totalDepth);
}

这里之前写的时候，进行了一个 下标转二十六进制，因为单元格的合并是从左上角到右下角 即类似 "A1:B2" ，这意思就是第一行A列合并到第二行B列

实际上使用 mergeCells（起始行，起始列，结束行，结束列）也可以

function mergeColumns(columns: TableColumnAllProps[][], worksheet: Worksheet, totalDepth: number) {
  // 遍历每个column
  columns.forEach(column => {
    column.forEach(item => {
      // 数字转换为类似AA AB的字母，配合depth获取起始单元格的坐标
      const startCell = `${convertToTitle(item.startIndex)}${item.depth}`;
      let endCell = `${convertToTitle(item.endIndex)}${item.depth}`;

      // startIndex !== endIndex 说明当前单元格需要列合并
      if (item.startIndex !== item.endIndex) {
        worksheet.mergeCells(startCell + ':' + endCell);
      }
      // 如果当前单元格没有子节点，并且当前单元格所在层级不是最后一层，说明当前单元格需要行合并
      if (!item?.children?.length && item.depth !== totalDepth) {
        endCell = `${convertToTitle(item.endIndex)}${totalDepth}`;
        worksheet.mergeCells(startCell + ':' + endCell);
      }
    });
  });
}

如此这解决了表头合并的问题，这样就可以根据传入的columns自动生成对应的表头，无需关心有几层children

而后续数据的添加则相对比较简单，需要注意的就是根据表头的顺序去生成插入的数据，另外要注意render的情况，因为很多字段可能需要自定义render，具体如下

function handleData(tableData: any[], leafNodes: TableColumnAllProps[], worksheet: Worksheet) {
  const data = [];

  // 处理每个单元格的数据
  for (let i = 0; i < tableData.length; i++) {
    const dataRow = tableData[i];
    const pushedData = [];

    leafNodes.forEach((column, index) => {
      // 处理render情况
      const originValue = dataRow?.[column.dataIndex as string];
      let result = originValue;

      if (isFunction(column.render)) {
        // 有render则使用render返回结果，如果不是数字或者字符串，则使用原始数据展示
        result = column.render(dataRow?.[column.dataIndex as string], dataRow, index);
        const isStringOrNumber = typeof result === 'string' || typeof result === 'number';

        // 如果不是数字或者字符串，处理为原始数据
        if (!isStringOrNumber) {
          result = originValue;
        }
      }
      pushedData.push(result);
    });
    data.push(pushedData);
  }

  // 添加行
  if (data) worksheet.addRows(data);
  return data.length;
}

如此则完成了表头的添加和数据的添加，以下是效果展示

Worker

这里考虑数据量特别大的情况下，可能需要使用到worker，故而也开启了worker的模式，这里是可选的，因为开启worker本身也是一个消耗，而且数据传给worker时浏览器会复制传递的数据而不是引用，这也是一笔开销，另外传给worker的数据中不能有函数，columns中的render函数是比较常见的，所以这种情况也不是很好处理，故而这里worker的使用场景比较有限

exceljs导入

导出方案替换之后导入同样也需要替换为exceljs的方案

• 需要注意的是导入的时候需要传入表头，否则无法校验

整体思路如下

• 拿到表头数据的层级maxLevel
• 获取excel中maxLevel对应的那一行的数据，这一行即表头数据
• 遍历改行数据，若数据中有表头不存在的数据（title判断），则判断为模板错误（这里不判断长度，因为columns可能会有一些额外的字段，比如'操作'之类的，故而长度不做判断）
• 另外如果表格columns中有checkbox之类的，传入的时候也需要把checkbox去掉

import Excel, { Row } from 'exceljs';
import { isFunction, isEmpty } from '@/utils/is';
import { TableColumnAllProps } from 'client/typings/globalInterfaces';

type ReadExcelOptions = {
  sheetIndex?: number;
};

/* 将所有的行数据转换为json */
const changeRowsToDict = (
  worksheet: Excel.Worksheet,
  columns: Partial<TableColumnAllProps>[],
  headerRowNumber: number
) => {
  const dataArray = [];
  const validate = [];
  // 校验表头是否跟配置的一致
  const columnsTitle: string[] = columns.map(column => column.title as string);
  const headerRow: Row = worksheet.getRow(headerRowNumber);
  const rowValues = headerRow?.values;
  const length = rowValues.length as number;
  // 根据表头的title字典树
  const titleDict = getTitleDict(columns);

  // 校验导入的表格中是否属于表头数据
  for (let i = 1; i < length; i++) {
    if (!columnsTitle.includes(rowValues[i])) {
      return { data: dataArray, validate: ['导入的文件表头与模板不一致'] };
    }
  }

  // 遍历每一行， > headerRowNumber 判定为数据行
  worksheet.eachRow(function (row: Row, rowNumber: number) {
    if (rowNumber > headerRowNumber) {
      // 每一行的数据存储
      const data = {};

      row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
        const columnTitle = rowValues[colNumber];
        const targetColumn = titleDict[columnTitle];
        // 获取每个单元格对应的column的配置
        const {
          required,
          title,
          validate: validateFunction,
          message,
          dataIndex,
          importHandler
        } = targetColumn || {};

        // 当前单元格为空
        if (isEmpty(cell.value as string)) {
          // 判断是否为必填字段
          if (required) {
            validate.push(`第${rowNumber}行：${title}必填`);
          }
        } else {
          // 单元格内容不为空且设置了 validate 函数，则需要对内容的有效性做检查
          if (isFunction(validateFunction)) {
            const errorMessage = `第${rowNumber}行：${title}${message}`;
            const result = validateFunction(cell.value);

            if (!result) {
              validate.push(errorMessage);
            }
          }
        }

        data[dataIndex] = isFunction(importHandler) ? importHandler(cell.value) : cell.value;
        data['rowNumber'] = rowNumber;
      });

      dataArray.push(data);
    }
  });

  return { data: dataArray, validate };
};

export const readExcel = (
  file,
  columns: Partial<TableColumnAllProps>[],
  options?: ReadExcelOptions
) => {
  // 获取所有column的叶子节点
  const leafNodes = [];
  // 获取最大深度，作为表头的最后一行
  const maxLevel = getMaxLevel(columns);

  getLeafNodes(columns, leafNodes);

  const sheetIndex = options?.sheetIndex || 1;

  return new Promise(async (resolve, reject) => {
    try {
      const workbook = new Excel.Workbook();
      const result = await workbook.xlsx.load(file);

      const worksheet = result.getWorksheet(sheetIndex);
      // 获取数据
      const dataArray = changeRowsToDict(worksheet, leafNodes, maxLevel);

      resolve(dataArray);
    } catch (e) {
      reject(e);
    }
  });
};

// 获取所有叶子节点
function getLeafNodes(
  headerColumn: Partial<TableColumnAllProps>[],
  leafNodes: Partial<TableColumnAllProps>[]
) {
  for (let i = 0; i < headerColumn.length; i++) {
    const item = headerColumn[i];

    if (item.children) {
      getLeafNodes(item.children, leafNodes);
    } else {
      leafNodes.push(item);
    }
  }
}

// 获取columns层级深度
function getMaxLevel(arr: any): number {
  let maxLevel = 1;

  function traverse(arr: any, level: number) {
    for (let i = 0; i < arr.length; i++) {
      const obj = arr[i];

      if (obj.children) {
        traverse(obj.children, level + 1);
      }
    }
    maxLevel = Math.max(maxLevel, level);
  }

  traverse(arr, 1);
  return maxLevel;
}

/**
 * 根据title获取对应的column
 * @param columns
 * @returns
 */
function getTitleDict(columns: Partial<TableColumnAllProps>[]) {
  const ans = {};

  for (let i = 0; i < columns.length; i++) {
    const column = columns[i];

    ans[column.title as string] = column;
  }
  return ans;
}

使用demo

导出

        // 创建导出任务
        createExportWorker({
          // 文件名
          fileName: 'xxx.xlsx',

          // 表格名称
          sheetsName: ['表名1', '表名2'],
          // 表头
          headerColumns: [
            ExportExtraColumns,
            ExportExtraColumns
          ],
          // 表格数据
          tableDatas: [storageList, deliveryList]
        });

导入

import { Upload, Button } from 'antd';
import { RcFile } from 'antd/es/upload';

import { readExcel } from '@/utils/excel';
import { TableColumnAllProps } from 'client/typings/globalInterfaces';

const ExcelDemo = () => {
  const handleBeforeUpload = async (file: RcFile) => {
    const res = await readExcel(file, newColumns);

    console.log(res);
    return false;
  };

  return (
    <div>
      <Upload accept=".xlx, .xlsx" beforeUpload={handleBeforeUpload}>
        <Button>导入Excel</Button>
      </Upload>
    </div>
  );
};

完整代码 exceljs-auto

写的不好之处还请指出，不甚感激