关于葡萄城开发技术:用Echarts实现前端表格引用从属关系可视化
在金融行业,咱们常常会有审计审查的需要,对某个计算结果进行审查,然而这个计算结果可能依赖多个单元格,而且会有会有多级依赖的状况,如果让咱们的从业人员靠眼睛找,工作量微小,而且准确性存疑,基本上死路一条,因而让整个审查过程可视化,火烧眉毛,当初咱们利用纯前端表格和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中渲染 ...