在金融行业，咱们常常会有审计审查的需要，对某个计算结果进行审查，然而这个计算结果可能依赖多个单元格，而且会有会有多级依赖的状况，如果让咱们的从业人员靠眼睛找，工作量微小，而且准确性存疑，基本上死路一条，因而让整个审查过程可视化，火烧眉毛，当初咱们利用纯前端表格和 Echarts 将审计审查过程可视化
一. 首先咱们先理解一下前端表格或 Excel 中援用和从属关系：
1. 在单元格 B1 中设置公式 =SUM(A1)。单元格 A1 是单元格 B1 的援用单元格（援用关系）
2. 在单元格 B1 中设置公式 =SUM(A1)。单元格 B1 是单元格 A1 的隶属单元格（从属关系）
二. 接下来咱们看一下最终实现成果：
1. 援用关系
2. 从属关系
三. 本次咱们用的是 Echarts 的树图将援用和从属关系可视化，对于 Echarts 上手，大家去 Echarts 官网有残缺上手教程，Echarts 社区有很多开发者做的许多乏味又实用的 demo，这里咱们用的是树图
四. 接下来咱们要用纯前端表格控件的获取援用和从属关系的 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;
}五. 将结构好的援用和隶属树 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 可视化的外围实现逻辑，因为工程较大，须要残缺内容能够留言。。
拓展浏览
React + Springboot + Quartz，从 0 实现 Excel 报表自动化
电子表格也能做购物车？简略三步就能实现
应用纯前端类 Excel 表格控件 SpreadJS 构建企业现金流量表