xlsx-style 在 express 中的应用

在 express 框架的 node 服务中，通过 xlsx-style 给 excel 添加样式，并生成 excel 文件源码。

const express = require('express');

const router = express.Router();
const _ = require('lodash'); // 引入lodash辅助工具
const fs = require('fs'); // 引入fs模块用于读取文件流
const xlsx = require('xlsx-style'); // 选择使用xlsx-style可以设置表格样式

const regex = /(?<![A-Z])A(?=\d)/; // 判断首列 'A+'

// @params {position} e.g. B10(第2列第10行) 对侧边栏和tableBody分别设置样式
function handleSheetSideOrBodyStyle(position, style, isNeedBodyStyleCross, isNeedSideStyle) {
  const { bodyStyle, bodyStyleBlank, sideStyle } = style;
  if (isNeedSideStyle && regex.test(position)) return sideStyle;
  if (isNeedBodyStyleCross && position.slice(-1) % 2 === 0) return bodyStyleBlank;
  return bodyStyle;
}

// 获取树的深度
function handleGetTreeDeep(node) {
  let deep = 0;
  node.forEach((item) => {
    if (item.children) {
      deep = Math.max(deep, handleGetTreeDeep(item.children) + 1);
    } else {
      deep = Math.max(deep, 1);
    }
  });
  return deep;
}
// 获取树的广度
function handleGetTreeExtent(node) {
  let extend = 0;
  node.forEach((item) => {
    if (item.children) {
      extend += handleGetTreeExtent(item.children);
    } else {
      extend += 1;
    }
  });
  return extend;
}

// xlsx-style 表格合并算法
function handleMergeItem(index = {}, type) {
  const {
    treeMaxDeep, currentDeep, extendTemp, itemTreeExtend,
  } = index;
  let mergeItem;
  switch (type) {
    case 'extend':
      mergeItem = {
        s: { c: extendTemp, r: currentDeep },
        e: { c: extendTemp + itemTreeExtend - 1, r: currentDeep },
      };
      break;
    case 'deep':
      mergeItem = {
        s: { c: extendTemp - 1, r: currentDeep },
        e: { c: extendTemp - 1, r: treeMaxDeep - 1 },
      };
      break;
    default: break;
  }
  return mergeItem;
}

// 处理table表头的合并
function handleFileHeader(fileHeader) {
  const treeMaxDeep = handleGetTreeDeep(fileHeader);
  const headerArray = [];
  const headerKey = [];
  const merges = [];
  let extendTemp = 0;
  for (let n = 0; n < treeMaxDeep; n += 1) {
    headerArray.push([]);
  }
  function handleFileHeaderArrayDeep(
    fileHeaderArray,
    indexTemp = 0,
  ) {
    fileHeaderArray.map((item) => {
      if (item.children) {
        const itemTreeExtend = handleGetTreeExtent([item]);
        for (let i = 0; i < itemTreeExtend; i += 1) {
          headerArray[indexTemp].push(item.title);
        }
        // 表格是否需要向右合并
        if (itemTreeExtend > 1) {
          merges.push(handleMergeItem({
            treeMaxDeep, currentDeep: indexTemp, extendTemp, itemTreeExtend,
          }, 'extend'));
        }
        handleFileHeaderArrayDeep(item.children, indexTemp + 1);
      } else {
        extendTemp += 1;
        for (let i = 0; i < treeMaxDeep - indexTemp; i += 1) {
          headerArray[treeMaxDeep - i - 1].push(item.title);
        }
        // 表格是否需要向下合并
        if ((treeMaxDeep - indexTemp - 1)) {
          merges.push(handleMergeItem({
            treeMaxDeep, currentDeep: indexTemp, extendTemp,
          }, 'deep'));
        }
        headerKey.push(item.key);
      }
      return null;
    });
  }
  handleFileHeaderArrayDeep(fileHeader);
  return { headerArray, merges, headerKey };
}

// 处理excel每个单元格的横/纵坐标 e.g. position 52 === 'AA'，260 === 'JA'
function handleFromCharCode(colNumber) {
  let charCode = '';
  const int = Math.floor(colNumber / 26);
  const remainder = (colNumber % 26);
  if (int) {
    charCode = handleFromCharCode(int - 1) + String.fromCharCode(65 + remainder);
  } else {
    charCode = String.fromCharCode(65 + remainder);
  }
  return charCode;
}

router.post('/export', (req, res) => {
  const { body = {} } = req;
  const {
    style: {
      headerStyle = {}, bodyStyle = {}, bodyStyleBlank = {}, sideStyle = {},
    } = {},
    fileData: {
      fileHeader = [], fileBody = [], fileName = 'report',
    } = {},
    sheetCols = [],
    sheetMerges = [],
    isNeedBodyStyleCross,
    isNeedSideStyle,
  } = body;

  // 处理table表头，拿到 fileHeaderProps = { headerArray，getArrayKey，merges }
  const fileHeaderProps = handleFileHeader(fileHeader);
  // 根据fileHeaderProps.headerArray，对表头header进行数据/样式处理
  const headersExport = (fileHeaderProps.headerArray || [])
    .map((item, index) => item
      .map((v, i) => _.assign({}, { v, position: handleFromCharCode(i) + (index + 1) }))
      .reduce((prev, next) => _.assign({}, prev, {
        [next.position]: {
          v: String(next.v),
          s: headerStyle,
        },
      }), {}));

  // 拿到传过来的excel数据，对表中数据fileBody进行数据/样式处理
  const dataExport = fileBody.length
    ? (
      fileBody.map((v, i) => fileHeaderProps.headerKey.map((k, j) => (
        _.assign({}, {
          v: v[k],
          position: handleFromCharCode(j) + (i + headersExport.length + 1),
        })
      )))
        .reduce((prev, next) => prev.concat(next))
        .reduce((prev, next) => _.assign(prev, {
          [next.position]: {
            v: String(next.v),
            s: handleSheetSideOrBodyStyle(
              next.position,
              { bodyStyle, bodyStyleBlank, sideStyle },
              isNeedBodyStyleCross,
              isNeedSideStyle,
            ),
          },
        }), {})
    )
    : {};

  // 合并 headersExport 和 dataExport
  const output = _.assign({}, ...headersExport, dataExport);
  // 获取所有单元格的位置
  const outputPos = _.keys(output);
  // 计算出范围
  const ref = `${outputPos[0]}:${outputPos[outputPos.length - 1]}`;
  // 构建 workbook 对象
  const workbook = {
    SheetNames: ['Sheet'],
    Sheets: {
      Sheet: _.assign(
        {},
        output,
        { '!ref': ref },
        { '!merges': fileHeaderProps.merges.concat(sheetMerges) },
        { '!cols': sheetCols },
      ),
    },
  };

  const strs = `${fileName}.xlsx`; // 设置默认的文件名称
  xlsx.writeFile(workbook, strs); // 导出文件
  const buffer = fs.readFileSync(strs); // 使用fs模块得到流
  fs.unlinkSync(strs); // 删除生成的文件
  // 返回浏览器完成下载，get请求需要setHeader，post请求由于拿的数buffer流，可不setHeader
  res.setHeader('Content-Type', 'application/vnd.openxmlformats');
  res.setHeader('Content-Disposition', 'attachment; filename=report.xlsx)');
  res.json({
    code: '0',
    message: 'success',
    data: buffer,
  });
});

export default router;