用Echarts实现SpreadJS引用从属关系可视化

问题：如何用Echarts实现SpreadJS引用从属关系可视化？

背景：

在金融行业，我们经常会有审计审查的需求，对某个计算结果进行审查，但是这个计算结果可能依赖多个单元格，而且会有会有多级依赖的情况，如果让我们的从业人员靠眼睛找，工作量巨大，而且准确性存疑，基本上死路一条。

因此让整个审查过程可视化，迫在眉睫，本章我们利用SpreadJS和Echarts将审计审查过程可视化。

Step1、首先了解一下SpreadJS或Excel中引用和从属关系：

1、在单元格B1中设置公式 =SUM(A1)。单元格A1是单元格B1的引用单元格（引用关系）

2、在单元格B1中设置公式 =SUM(A1)。单元格B1是单元格A1的从属单元格（从属关系）

Step2、看一下最终实现效果：

1、引用关系

2、从属关系

Step3、使用Echarts树图将引用和从属关系可视化

关于Echarts上手，大家去Echarts官网有完整上手教程，Echarts社区有很多开发者做的许多有趣又实用的demo，这里我们用的是树图。

我们看一下它的data的数据结构：

Step4、使用SpreadJS的获取引用和从属关系API将单元格解析

将这些关系构造成Echarts树图的data结构，核心代码如下：

// 递归构建追踪树
buildNodeTreeAndPaint = (spreadSource, trackCellInfo) => {
  let info = this.getCellInfo(trackCellInfo);
  let sheetSource = spreadSource.getSheetFromName(info.sheetName); // 创建跟节点
  let rootNode = this.creatNode(info.row, info.col, sheetSource, 0, "");

  let name =
    rootNode.sheetName +
    "*" +
    rootNode.row +
    "*" +
    rootNode.col +
    "*" +
    Math.random().toString();
  let precedentsRootNode = "";
  let dependentsRootNode = "";
  if (
    this.state.trackType === "Precedents" ||
    this.state.trackType === "Both"
  ) {
    this.getNodeChild(rootNode, sheetSource, "Precedents");
    debugger;
    console.log(rootNode);
    if (this.state.trackType === "Both") {
      let rootNodeChildren = JSON.parse(JSON.stringify(rootNode.children));
      rootNode.children = [];
      precedentsRootNode = JSON.parse(JSON.stringify(rootNode));
      precedentsRootNode.children.push({
        name: "Precedents",
        value: "Precedents",
        children: rootNodeChildren,
      });
      this.setState({
        precedentsRootNode: JSON.parse(JSON.stringify(precedentsRootNode)),
      });
    }
  }
  if (
    this.state.trackType === "Dependents" ||
    this.state.trackType === "Both"
  ) {
    this.getNodeChild(rootNode, sheetSource, "Dependents");
    console.log(rootNode);
    if (this.state.trackType === "Both") {
      let deepInfo = [1];
      let rootNodeChildren = JSON.parse(JSON.stringify(rootNode.children));
      rootNode.children = [];
      dependentsRootNode = JSON.parse(JSON.stringify(rootNode));
      dependentsRootNode.children.push({
        name: "Dependents",
        value: "Dependents",
        children: rootNodeChildren,
      });
      this.setState({
        dependentsRootNode: JSON.parse(JSON.stringify(dependentsRootNode)),
      });
    }
  }
  if (this.state.trackType === "Both") {
    precedentsRootNode.children = precedentsRootNode.children.concat(
      dependentsRootNode.children
    ); // let bothRootNode = precedentsRootNode.children[0].children.concat(dependentsRootNode.children[0].children)
    this.setState({
      rootNode1: JSON.parse(JSON.stringify(precedentsRootNode)),
    });
  } else {
    this.setState({
      rootNode1: JSON.parse(JSON.stringify(rootNode)),
    });
  }
};
creatNode = (row, col, sheet, deep, trackType) => {
  let node = {
    value: sheet.getValue(row, col),
    position:
      sheet.name() +
      "!" +
      GC.Spread.Sheets.CalcEngine.rangeToFormula(
        new GC.Spread.Sheets.Range(row, col, 1, 1)
      ),
    deep: deep,
    name: `${sheet.name()}!${GC.Spread.Sheets.CalcEngine.rangeToFormula(
      new GC.Spread.Sheets.Range(row, col, 1, 1)
    )}\nvalue:${sheet.getValue(row, col)}`,
    sheetName: sheet.name(),
    row: row,
    col: col,
    trackType: trackType,
  };
  return node;
};
getNodeChild = (rootNode, sheet, trackType) => {
  let childNodeArray = [];
  let children = [];
  let row = rootNode.row,
    col = rootNode.col,
    deep = rootNode.deep;
  if (trackType == "Precedents") {
    children = sheet.getPrecedents(row, col);
  } else {
    children = sheet.getDependents(row, col);
  } // let self = this;
  if (children.length >= 1) {
    children.forEach((node) => {
      let row = node.row,
        col = node.col,
        rowCount = node.rowCount,
        colCount = node.colCount,
        _sheet = sheet.parent.getSheetFromName(node.sheetName);
      if (rowCount > 1 || colCount > 1) {
        for (let r = row; r < row + rowCount; r++) {
          for (let c = col; c < col + colCount; c++) {
            let newNode = this.creatNode(r, c, _sheet, deep + 1, trackType); // if (deep < self.maxDeep) {
            this.getNodeChild(newNode, _sheet, trackType); // }
            childNodeArray.push(newNode);
          }
        }
      } else {
        let newNode = this.creatNode(row, col, _sheet, deep + 1, trackType); // if (deep < self.maxDeep) {
        this.getNodeChild(newNode, _sheet, trackType); // }
        childNodeArray.push(newNode);
      }
    });
  }
  rootNode.children = childNodeArray;
};

Step5、将构造好的引用和从属树rootNode在Echarts中渲染

myChart.setOption(
  (option = {
    tooltip: {
      trigger: "item",
      triggerOn: "mousemove",
    },
    series: [
      {
        type: "tree",
        data: [this.state.rootNode1],
        top: "1%",
        left: "15%",
        bottom: "1%",
        right: "7%",
        symbolSize: 10,
        orient: this.state.trackType === "review" ? "LR" : "RL",
        label: {
          position: this.state.trackType === "review" ? "left" : "right",
          verticalAlign: "middle",
          align: this.state.trackType === "review" ? "right" : "left",
        },
        leaves: {
          label: {
            position: this.state.trackType === "review" ? "right" : "left",
            verticalAlign: "middle",
            align: this.state.trackType === "review" ? "left" : "right",
          },
        },
        emphasis: {
          focus: "descendant",
        }, // layout: 'radial',
        expandAndCollapse: true,
        animationDuration: 550,
        animationDurationUpdate: 750,
      },
    ],
  })
);

option && myChart.setOption(option);

以上就是实现报表中公式引用从属关系Echarts可视化的核心实现逻辑。

如果需要获取源码，可以前往该链接获取。

作者: Steven.Lv | 审核：Lynn.Dou | 更新时间：2023.11.15