参照セルの取得

SpreadJSは、数式とセル間の関係を表示する機能をユーザーに提供することで、広範な数式監査をサポートしています。 これは、ワークシート内の参照セルと依存セルをトレースすることで行うことができます。 以下の例では、getPrecedentsメソッドを使用して、参照しているセル範囲の情報をセルの配列で取得しています。 この例では、D7またはF7のセルをクリックすると、下にある数式ツリーにそのセルの参照セルが表示されます。

参照セルは、現在のセルの数式に影響を与えるセルまたはセル範囲です。 B1セルに「=SUM(A1)」という数式を設定した場合、 A1セルはB1セルの参照セルです。 次のように、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="ja-jp" /> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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%; }