参照セルは、現在のセルの数式に影響を与えるセルまたはセル範囲です。
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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/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%;
}
