通过如下代码可以给一个或者一片区域中的单元格设置公式。
用户可以为一片区域单元格设置对应的相对引用公式，那么这个公式将会根据偏移范围自动调整。
使用与下面类似的代码，为一个范围设置数组公式。
你可以通过如下代码获取指定单元格中是否有公式存在:
如果一个公式的长度非常长或者它的使用频次非常多的话, 你可以添加一个自定义名称给它以便方便使用。
可以从自定义名称集合中删除某个自定义名称:
也可以通过调用如下方法删除所有的自定义名称:
可以通过设置表单的ShowFormulas属性来控制表单是否显示公式。使用ShowFormulas属性，你可以从SpreadJS中直接拷贝公式字符串，并粘贴到其他应用中，亦或者直接将公式打印。
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
var sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setArray(0, 0, [
["Product", "Item Price", "Quantity", "Sales"],
['Kraft Real Mayo', 5.71, 1],
['Smartfood Popcorn', 2.5, 4],
['Teddy Grahams Crackers', 35, 5],
['Parmesan Cheese', 14.89, 9],
['Planter Deluxe Whole Cashew', 8.52, 3],
['Total']
]);
sheet.setColumnWidth(0, 190);
sheet.setColumnWidth(1, 80);
sheet.setColumnWidth(2, 80);
sheet.getRange(1, 3, 5, 1).formula("B2*C2", true);
sheet.addCustomName('customName1', '=$B$2:$B$6', 0, 0);
sheet.addCustomName('customName2', '=$C$2:$C$6', 0, 0);
sheet.setFormula(6, 1, "=SUM(customName1)");
sheet.setFormula(6, 2, "=SUM(customName2)");
sheet.getRange(6, 0, 1, 4).foreColor('red');
sheet.setFormula(6, 3, "B7*C7");
var style = sheet.getStyle(4, 7) || new GC.Spread.Sheets.Style();;
style.cellButtons= [{
useButtonStyle: true,
caption: "Show Formulas",
width: 120,
command: function() {
sheet.options.showFormulas = !sheet.options.showFormulas;
if (sheet.options.showFormulas) {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.ok;
} else {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.none;
}
sheet.setStyle(4, 7, style);
},
}];
sheet.setStyle(4, 7, style);
sheet.setColumnWidth(7, 122);
sheet.resumePaint();
_getElementById('btnSetFormula').addEventListener('click', function() {
var sheet = spread.getActiveSheet();
if (_getElementById("formula").value) {
var formula = _getElementById("formula").value;
if (_getElementById("rowIndex").value && _getElementById("columnIndex").value) {
var rowIndex = parseInt(_getElementById("rowIndex").value);
var columnIndex = parseInt(_getElementById("columnIndex").value);
if (_getElementById("rowCount").value && _getElementById("columnCount").value) {
var rowCount = Math.max(parseInt(_getElementById("rowCount").value), 1);
var columnCount = Math.max(parseInt(_getElementById("columnCount").value), 1);
sheet.setArrayFormula(rowIndex, columnIndex, rowCount, columnCount, formula);
} else {
sheet.setFormula(rowIndex, columnIndex, formula);
}
}
}
});
};
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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" class="sample-spreadsheets"></div>
<div class="options-container">
<p style="font-size:16px;padding:1px 0px">Setting a formula via code</p>
<p style="padding:2px 10px">Specify the row and column index for the formula cell. Set the row count and
column count to specify the number of rows and columns to add the formula to. Add the formula to the
Formula box and then click SetFormula to apply the formula.</p>
<div class="option-row">
<label for="rowIndex">Row Index:</label>
<input type="text" id="rowIndex" />
</div>
<div class="option-row">
<label for="columnIndex">Column Index:</label>
<input type="text" id="columnIndex" />
</div>
<div class="option-row">
<label for="rowCount">Row Count:</label>
<input type="text" id="rowCount" />
</div>
<div class="option-row">
<label for="columnCount">Column Count:</label>
<input type="text" id="columnCount" />
</div>
<div class="option-row">
<label for="formula">Formula:</label>
<input type="text" id="formula" value="=SUM(B2,B6)" />
<input type="button" id="btnSetFormula" value="SetFormula" />
</div>
</div>
</div>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.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;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
