Calculated columns (cell formula)
Here is a simple calculation patch which adds 'formula' property to the grid cells. The formula should be a string, use normal javascript syntax and can include any global or Math object functions. The function column(i) is used to refer to any cell in the current row.
Examples:
'column(1) + column(2)'
'column(1)*1000+round(column(2))'
'sqrt(column(1))'
'myFunction(column(1), column(2), column(3))'
To assign cell formula use setCellFormula(formulaText, colIndex) -
Math functions reference -
http://msdn2.microsoft.com/en-us/library/b272f386.aspx
The formula result is applied to the cell value and can be formatted as usual with AW.Formats.... classes.
Here is the full example which includes the patch and sample usage -
Examples:
'column(1) + column(2)'
'column(1)*1000+round(column(2))'
'sqrt(column(1))'
'myFunction(column(1), column(2), column(3))'
To assign cell formula use setCellFormula(formulaText, colIndex) -
grid.setCellFormula('column(1)+column(2)', 3); // col3 = col1+col2Math functions reference -
http://msdn2.microsoft.com/en-us/library/b272f386.aspx
The formula result is applied to the cell value and can be formatted as usual with AW.Formats.... classes.
Here is the full example which includes the patch and sample usage -
<html>
<head>
<script src="../../runtime/lib/aw.js"></script>
<link href="../../runtime/styles/xp/aw.css" rel="stylesheet"></link>
<script>
// ---------------------------------
// begin 'calculated columns' patch
(function(){
var events = {
onCellFormulaChanged: function(formula, col, row){
function Lib(){};
Lib.prototype = Math; // inherit all Math functions
var param = new Lib;
param.column = function(i){ // get value from column(i) in the same row
return param.grid.getCellValue(i, param.r);
};
var calc; // build js function from string formula
try {
calc = new Function('p', 'with(p){return (' + formula + ')}');
}
catch(err){
calc = function(){return '#ERR: ' + err.description }; // syntax error
}
function calculated(c, r){ // pass col, row indices to calc function
try {
param.grid = this;
param.c = c;
param.r = r;
return calc(param);
}
catch(err){
return '#VALUE: ' + err.description; // runtime error
}
}
function formatted(c, r){
var v = this.getCellValue(c, r);
var f = this.getCellFormat(c, r);
return f ? f.valueToText(v) : v;
}
this.setCellValue(calculated, col, row);
this.setCellText(formatted, col, row);
this.setCellEditable(false, col, row);
// dependencies
if (!this._notify){
this._notify = {};
}
var i, src;
var a = formula.match(/column\(.+?\)/g);
for(i=0; i<a.length;i++){
src = a[i].replace('column(', '').replace(')', '');
if (!this._notify[src]){
this._notify[src] = {};
}
this._notify[src][col] = true;
}
},
onCellValueChanged: function(value, col, row){
if (this._notify && this._notify[col]){
for (var i in this._notify[col]){
this.raiseEvent('onCellValueChanged', '', i, row);
}
}
},
onCellValidated: function(text, col, row){
var f = this.getCellFormat(col, row);
var v = f ? f.textToValue(text) : text;
function formatted(c, r){
var v = this.getCellValue(c, r);
var f = this.getCellFormat(c, r);
return f ? f.valueToText(v) : v;
}
this.setCellValue(v, col, row);
this.setCellText(formatted, col, row);
}
};
new AW.UI.Grid;
var obj = AW.UI.Grid.prototype;
obj.defineCellProperty('formula', '');
obj.setController('formula', events);
})();
// end of 'calculated columns' patch
// ---------------------------------
</script>
<style>
.aw-column-1, .aw-column-2, .aw-column-3 {
text-align: right;
width: 80px;
}
</style>
</head>
<body>
<script>
var myHeaders = ['name', 'price', 'amount', 'total'];
var myCells = [
['item1', 22.5, 10],
['item2', 10, 123],
['item3', 155.50, 1]
]
var str = new AW.Formats.String;
var num = new AW.Formats.Number;
num.setTextFormat('#,###.##');
var obj = new AW.UI.Grid;
obj.setHeaderText(myHeaders);
obj.setCellData(myCells);
obj.setCellFormat([str, num, num, num]);
obj.setColumnCount(4);
obj.setRowCount(3);
obj.setCellEditable(true);
obj.setCellFormula('column(1)*column(2)', 3); // calc total in column-3
document.write(obj);
</script>
</body>
</html>Alex (ActiveWidgets)
October 10,