참조되는 셀 가져오기

SpreadJS에서는 수식과 셀 간의 관계를 표시하는 기능을 제공하여 수식 감사를 광범위하게 지원합니다. 수식은 워크시트에서 참조되는 셀과 참조하는 셀을 추적하여 감사할 수 있습니다. 다음 샘플에서는 getDependents 메서드를 사용하여 셀 배열에 대한 참조되는 cellRange 정보를 가져옵니다. 이 예에서는 셀 D7 또는 F7을 클릭하면 SpreadJS 인스턴스 아래에 표시되는 수식 트리에서 참조되는 셀을 확인할 수 있습니다.

참조되는 셀은 현재 셀의 수식에 의해 영향을 받는 셀 또는 범위입니다. 셀 B1에서 수식 =SUM(A1)을 설정합니다. 셀 A1은 셀 B1의 참조되는 셀입니다. 셀의 참조되는 cellRange 정보 개체 배열을 가져오려면 다음 코드에서처럼 getPrecedents 메서드를 사용합니다.
window.onload = function (){ initFunction(); } function initFunction() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss')); var spreadForShow = new GC.Spread.Sheets.Workbook(document.getElementById('show')); initShowSpread(spreadForShow); buildNodeTreeAndPaint(spread, spreadForShow); }; function initShowSpread(spreadForShow) { var sheetForShow = spreadForShow.getActiveSheet(); spreadForShow.suspendPaint(); var spreadOptions = spreadForShow.options, sheetOptions = sheetForShow.options; spreadOptions.allowContextMenu = false; spreadOptions.scrollbarMaxAlign = true; spreadOptions.tabStripVisible = false; spreadOptions.allowUserResize = false; spreadOptions.allowUserDragDrop = false; spreadOptions.allowUserDragFill = false; spreadOptions.allowUserZoom = false; spreadOptions.grayAreaBackColor = '#ccddff'; sheetOptions.colHeaderVisible = false; sheetOptions.rowHeaderVisible = false; sheetOptions.selectionBackColor = "transparent"; sheetOptions.selectionBorderColor = "transparent"; sheetOptions.gridline = {showVerticalGridline: false, showHorizontalGridline: false}; sheetForShow.getCell(1, 0).foreColor("white").text("Formula Tree") .font("bold italic 24pt Calibri") .vAlign(GC.Spread.Sheets.VerticalAlign.center) .textIndent(2); sheetForShow.getRange(0, 0, 100, 100).backColor("#ccddff"); sheetOptions.isProtected = true; spreadForShow.resumePaint(); } function buildNodeTreeAndPaint(spread, spreadForShow) { var sd = data; if (sd.length > 0) { spread.fromJSON(sd[0]); var sheet = spread.getActiveSheet(); var sheetForShow = spreadForShow.getActiveSheet(); sheet.bind(GC.Spread.Sheets.Events.SelectionChanging, function (e, info) { sheetForShow.shapes.clear(); var row = info.newSelections[0].row; var col = info.newSelections[0].col; var nodeTree = creatNodeTree(row, col, sheet); paintDataTree(sheetForShow, nodeTree); }) } } function creatNodeTree(row, col, sheet) { var _comment = sheet.getCell(row, col).comment(); var node = { value: sheet.getValue(row, col), position: sheet.name() + '!' + GC.Spread.Sheets.CalcEngine.rangeToFormula(sheet.getRange(row, col, 1, 1)), description: _comment && _comment.text(), }; var childNodeArray = addChildNode(row, col, sheet); if (childNodeArray.length > 0) { node.childNodes = childNodeArray; } return node; } function addChildNode(row, col, sheet) { var childNodeArray = []; var childNodes = sheet.getPrecedents(row, col); if (childNodes.length >= 1) { childNodes.forEach(function (node) { var row = node.row, col = node.col, rowCount = node.rowCount, colCount = node.colCount, _sheet = sheet.parent.getSheetFromName(node.sheetName); if (rowCount > 1 || colCount > 1) { for (var r = row; r < row + rowCount; r++) { for (var c = col; c < col + colCount; c++) { childNodeArray.push(creatNodeTree(r, c, _sheet)); } } } else { childNodeArray.push(creatNodeTree(row, col, _sheet)) } }) } return childNodeArray; } function getRectShape(sheetForShow, name, x, y, width, height) { var rectShape = sheetForShow.shapes.add(name, GC.Spread.Sheets.Shapes.AutoShapeType.rectangle, x, y, width, height); var oldStyle = rectShape.style(); oldStyle.textEffect.color = "white"; oldStyle.fill.color = "#0065ff"; oldStyle.textEffect.font = "bold 15px Calibri"; oldStyle.textFrame.vAlign = GC.Spread.Sheets.VerticalAlign.top; oldStyle.textFrame.hAlign = GC.Spread.Sheets.HorizontalAlign.left; oldStyle.line.beginArrowheadWidth = 2; oldStyle.line.endArrowheadWidth = 2; rectShape.style(oldStyle); return rectShape; } function getConnectorShape(sheetForShow) { var connectorShape = sheetForShow.shapes.addConnector('', GC.Spread.Sheets.Shapes.ConnectorType.elbow); var LineStyle = connectorShape.style(); var line=LineStyle.line; line.beginArrowheadWidth=GC.Spread.Sheets.Shapes.ArrowheadWidth.wide; line.endArrowheadWidth=GC.Spread.Sheets.Shapes.ArrowheadWidth.wide; line.color="#FF6600"; connectorShape.style(LineStyle); return connectorShape; } function paintDataTree(sheetForShow, nodeTree, index, childLength, fatherShape) { var rectWidth = 260, rectHeight = 60; var spacingWidth = 300; var convertArray = [-0.75, 0.75, -2.25, 2.25, -2.25, 2.25, -4, 4, -5, 5]; var spacingHeightMapping = [145, 135, 125, 50, 50]; var name = Math.random().toString(); var rectShape; if (fatherShape) { var x = fatherShape.x(), y = fatherShape.y(); rectShape = getRectShape(sheetForShow, name, x + spacingWidth, y + convertArray[index] * spacingHeightMapping[childLength], rectWidth, rectHeight); var connectorShape = getConnectorShape(sheetForShow); connectorShape.startConnector({name: fatherShape.name(), index: 3}); connectorShape.endConnector({name: rectShape.name(), index: 1}); } else { rectShape = getRectShape(sheetForShow, name, 200, 250, rectWidth, rectHeight); } var _description = 'Value: ' + nodeTree.value + '\nCell: ' + nodeTree.position + ((nodeTree.description !== null) ? ('\nDescription: ' + nodeTree.description) : ''); rectShape.text(_description); var childNodes = nodeTree.childNodes; if (childNodes) { childNodes.forEach(function (node, index) { if (node.description) { paintDataTree(sheetForShow, node, index, childNodes.length, rectShape) } }); } }
<!DOCTYPE html> <html lang="en" style="height: 100%;font-size: 14px;"> <head> <meta charset="utf-8"> <meta name="spreadjs culture" content="ko-kr" /> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/precedent.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss"></div> <div id="show"></div> </div> </body> </html>
#ss { width: 100%; height: 60%; border: 1px solid black; } #show { width: 100%; height: 40%; border: 1px solid black; } .sample-tutorial { height: 100%; width: 100%; overflow: hidden; } body{ height: 100%; }