依存セルは、現在のセルの式の影響を受けるセルまたはセル範囲です。
B1セルに「=SUM(A1)」という数式を設定した場合、
B1セルはA1セルの依存セルです。
次のように、getDependents メソッドを使用して、依存しているセル範囲の情報を配列で取得することができます。
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
sheetCount: 1
});
initSpread(spread);
initStatusBar(spread);
buildNodeTreeAndPaint(spread);
};
var precedentLevelColor = ['#FFFFFF', '#19E093', '#09E8DB', '#12A0D1', '#096CE8', '#0926DE'];
var dependentLevelColor = ['#FFFFFF', '#ADDE0B', '#E8DD0C', '#D1AD00', '#E8A90C', '#E08804'];
function initStatusBar(spread){
var statusBarDOM = document.getElementById('statusBar');
var statusBar = new GC.Spread.Sheets.StatusBar.StatusBar(statusBarDOM);
statusBar.bind(spread);
}
function initSpread(spread) {
if (data.length > 0) {
spread.fromJSON(data[0]);
var sheet = spread.getActiveSheet();
dependentLevelColor.forEach(function (color, index) {
sheet.getCell(26, 7 - index, 3).backColor(color).text((index).toString()).font("bold 24px").hAlign(GC.Spread.Sheets.HorizontalAlign.center)
});
precedentLevelColor.forEach(function (color, index) {
sheet.getCell(26, 7 + index, 3).backColor(color).text((index).toString()).font("bold 24px").hAlign(GC.Spread.Sheets.HorizontalAlign.center)
})
sheet.getCell(26, 1).text("dependent");
sheet.setStyle(26,1,sheet.getStyle(10,0));
sheet.getCell(26, 13).text("precedent");
sheet.setStyle(26,13,sheet.getStyle(10,0));
sheet.getCell(26, 7).text('C');
}
}
function buildNodeTreeAndPaint(spread) {
var sheet = spread.getActiveSheet();
var oldDependentNodeTree, oldPrecedentNodeTree;
sheet.bind(GC.Spread.Sheets.Events.SelectionChanging, function (e, info) {
spread.suspendPaint();
if (oldDependentNodeTree || oldPrecedentNodeTree) {
if (oldDependentNodeTree.dependentChildNodes !== undefined || oldPrecedentNodeTree.precedentChildNodes !== undefined) {
paintDependentCells(oldDependentNodeTree, true);
paintprecedentCells(oldPrecedentNodeTree, true);
}
}
var newRow = info.newSelections[0].row;
var newCol = info.newSelections[0].col;
var dependentNodeTree = createDependentNodeTree(newRow, newCol, sheet);
oldDependentNodeTree = dependentNodeTree;
var precedentNodeTree = createPrecedentNodeTree(newRow, newCol, sheet);
oldPrecedentNodeTree = precedentNodeTree;
if (precedentNodeTree.precedentChildNodes !== undefined || dependentNodeTree.dependentChildNodes !== undefined) {
paintDependentCells(dependentNodeTree);
paintprecedentCells(precedentNodeTree);
}
spread.resumePaint();
})
}
function createDependentNodeTree(row, col, sheet, dependentLevel) {
if (dependentLevel === undefined) {
var dependentLevel = 0;
}
var node = {
row: row,
col: col,
sheet: sheet,
level: dependentLevel
};
var dependentChildNodes = addDependentChildNode(row, col, sheet, dependentLevel);
if (dependentChildNodes.length > 0) {
node.dependentChildNodes = dependentChildNodes;
}
return node;
}
function addDependentChildNode(row, col, sheet, dependentLevel) {
var childNodeArray = [];
var childNodes = sheet.getDependents(row, col);
if (childNodes.length >= 1) {
dependentLevel++;
childNodes.forEach(function (node) {
let _sheet = sheet.parent.getSheetFromName(node.sheetName);
childNodeArray.push(createDependentNodeTree(node.row, node.col, _sheet, dependentLevel))
})
}
return childNodeArray;
}
function createPrecedentNodeTree(row, col, sheet, precedentLevel) {
if (precedentLevel === undefined) {
var precedentLevel = 0;
}
var node = {
row: row,
col: col,
sheet: sheet,
level: precedentLevel
};
var precedentChildNodes = addPrecedentChildNode(row, col, sheet, precedentLevel);
if (precedentChildNodes.length > 0) {
node.precedentChildNodes = precedentChildNodes;
}
return node;
}
function addPrecedentChildNode(row, col, sheet, precedentLevel) {
var childNodeArray = [];
var childNodes = sheet.getPrecedents(row, col);
if (childNodes.length >= 1) {
precedentLevel++;
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(createPrecedentNodeTree(r, c, _sheet, precedentLevel));
}
}
} else {
childNodeArray.push(createPrecedentNodeTree(row, col, _sheet, precedentLevel))
}
})
}
return childNodeArray;
}
function paintDependentCells(nodeTree, clearFlag) {
var currentRow = nodeTree.row, currentCol = nodeTree.col, currentSheet = nodeTree.sheet,
currentLevel = nodeTree.level;
var dependentChildNodes = nodeTree.dependentChildNodes;
currentSheet.getCell(currentRow, currentCol).backColor(clearFlag ? 'white' : dependentLevelColor[currentLevel]);
if (dependentChildNodes) {
dependentChildNodes.forEach(function (node) {
paintDependentCells(node, clearFlag)
});
}
}
function paintprecedentCells(nodeTree, clearFlag) {
var currentRow = nodeTree.row, currentCol = nodeTree.col, currentSheet = nodeTree.sheet,
currentLevel = nodeTree.level;
var precedentChildNodes = nodeTree.precedentChildNodes;
currentSheet.getCell(currentRow, currentCol).backColor(clearFlag ? 'white' : precedentLevelColor[currentLevel]);
if (precedentChildNodes) {
precedentChildNodes.forEach(function (node) {
paintprecedentCells(node, clearFlag);
});
}
}
<!DOCTYPE html>
<html lang="en" style="height: 100%;font-size: 14px;">
<head>
<meta name="spreadjs culture" content="ja-jp" />
<meta charset="utf-8">
<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="app.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/dependent.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div id="statusBar"></div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-spreadsheets{
height: calc(100% - 30px);
}
#statusBar {
width: 100%;
height: 30px;
}
