Spreadsheet retrieve range of formula cells
Environment
Product Version | 2021.1.330 |
Product | Progress® Kendo UI® Spreadsheet for jQuery |
Description
I want to identify all the formula cells so that I can perform actions on them after initialization such as change background color or disable them. How do I get all the formula values and return their respective ranges?
Solution
- Get the Spreadsheet's
[activeSheet](https://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet/methods/activesheet)
- Use its private _properties.get("formula").values() method to get an Array of the cells values that have formulas
- Utilize the JS .map() method, to map the Array
- In the callback of the map method use the
[sheet.range](https://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/sheet/methods/range)
method to get the[Range](https://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/range)
of the formula cells currently being mapped and return it to enable later manipulation
<div id="example">
<div id="spreadsheet" style="width: 100%;"></div>
<script>
var sp = $("#spreadsheet").kendoSpreadsheet({
sheets: [{
name: "Test",
rows: [{
cells: [
{"format":"#","index":0,"value":1},
{"format":"#","index":1,"value":2},
{"formula":"SUM(A1, B1)","index":2,"value":3},
{"formula":"SUM(C1, C2)","index":3,"value":10}
]
},{
cells: [
{"format":"#","index":0,"value":3},
{"format":"#","index":1,"value":4},
{"formula":"SUM(A2, B2)", "index": 2, "value":7}
]
}]
}]
}).getKendoSpreadsheet();
var sheet = sp.activeSheet();
var formulas = sheet._properties.get("formula").values().map(function (f) {
var formulaRange = sheet.range(f.value.row, f.value.col);
formulaRange.background("#92a8d1");
return formulaRange;
});
console.log(formulas);
</script>
</div>