Implicit intersection logic reduces many values to a single value. Users can add @ before the formula to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background). The logic works as follows:
If the value is a single item, then return the item.
If the value is a range, then return the value from the cell on the same row or column as the formula.
If the value is an array, then pick the top-left value.
With the advent of dynamic arrays, SpreadJS is no longer limited to returning single values from formulas, so silent implicit intersection is no longer necessary. Where an old formula could invisibly trigger implicit intersection, dynamic array enabled SpreadJS shows where it would have occurred with the @.
Note:
The implicit intersection operator @ is only valid when allowDynamicArray is true.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
initSpread(spread);
};
function initSpread(spread) {
spread.options.allowDynamicArray = true;
var sheet = spread.getActiveSheet();
spread.suspendPaint();
spread.suspendCalcService();
sheet.name('@');
sheet.setText(0, 1, 'The implicit intersection operator @, evaluates an expression using implicit intersection. The formula can return a value, range, or error.');
sheet.setText(1, 1, 'Where value is a range, @ will return the value at the intersection of the row and column of the formula cell.');
sheet.setText(2, 1, ' Syntax: @value');
var row = 7;
var col = 1;
var col2 = col + 2;
var rowCount = 11;
applyTableStyleForRange(sheet, row, col, rowCount, 1);
applyTableStyleForRange(sheet, row, col2, rowCount, 1);
sheet.getCell(row, col).text('Item #').hAlign(2);
sheet.getCell(row, col2).text('@').hAlign(2);
row++;
sheet.setFormula(row, col, 'SEQUENCE(10)');
for(var i = 0, r = row; i < 10; i++, r++) {
sheet.setFormula(r, col2, '@$B$9:$B$18');
}
sheet.setFormula(20, col2, '@$B$9:$B$18');
sheet.setText(20, col2 + 1, ' <--- error because no intersection');
sheet.setFormula(9, col2 + 2, '@$B$10:$D$10');
sheet.setText(9, col2 + 3, ' <--- error because intersection with multiple cells');
spread.resumeCalcService();
spread.resumePaint();
}
function applyTableStyleForRange(sheet, row, col, rowCount, colCount, options) {
var tableName = "tmpTable";
var TableThemes = GC.Spread.Sheets.Tables.TableThemes;
// use table to help set style then remove like convert table to range in Excel
sheet.tables.add(tableName, row, col, rowCount, colCount, TableThemes.medium7, options);
sheet.tables.remove(tableName, 2 /* keep style */);
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.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>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" style="width:100%; height: 100%"></div>
</div></body>
</html>
.sample {
position: relative;
height: 100%;
overflow: auto;
}
.sample::after {
display: block;
content: "";
clear: both;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
.option-group {
margin-bottom: 6px;
}
label {
display: block;
margin-bottom: 6px;
}
input {
margin-bottom: 5px;
padding: 2px 4px;
width: 100%;
box-sizing: border-box;
}
input[type=button] {
margin-bottom: 6px;
}
hr {
border-color: #fff;
opacity: .2;
margin: 5px 0;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
