Use code similar to the following to set a formula for a cell or a range of cells.
You can set a range of cells with formula relative reference auto offset.
Use code similar to the following to set array formula for a range.
You can check if there is a formula in the specified cell by using the following code:
If a formula is lengthy or used frequently, you can add a custom name for it to make using it more convenient.
A custom name can be removed from the custom name collection like this:
You can remove all custom names by using the following method:
You can use the sheet property ShowFormulas to show the formula content instead of the values. With ShowFormulas, you can copy the formulas string to another applications, or print the formulas.
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 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" 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;
}
