:: Forum >> Version 2 >>

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) -

grid.setCellFormula('column(1)+column(2)'3); // col3 = col1+col2
 
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 -

<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(formulacolrow){

        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(iparam.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(cr){ // pass col, row indices to calc function
            
try {
                
param.grid this;
                
param.c;
                
param.r;
                return 
calc(param);
            }
            
catch(err){
                return 
'#VALUE: ' err.description// runtime error
            
}
        }

        function 
formatted(cr){
            var 
this.getCellValue(cr);
            var 
this.getCellFormat(cr);
            return 
f ? f.valueToText(v) : v;
        }

        
this.setCellValue(calculatedcolrow);
        
this.setCellText(formattedcolrow);
        
this.setCellEditable(falsecolrow);

        
// dependencies

        
if (!this._notify){
            
this._notify = {};
        }

        var 
isrc;
        var 
formula.match(/column\(.+?\)/g);

        for(
i=0i<a.length;i++){

            
src a[i].replace('column(''').replace(')''');

            if (!
this._notify[src]){
                
this._notify[src] = {};
            }

            
this._notify[src][col] = true;
        }
    },

    
onCellValueChanged: function(valuecolrow){
        if (
this._notify && this._notify[col]){
            for (var 
i in this._notify[col]){
                
this.raiseEvent('onCellValueChanged'''irow);
            }
        }
    },

    
onCellValidated: function(textcolrow){

        var 
this.getCellFormat(colrow);
        var 
f ? f.textToValue(text) : text;

        function 
formatted(cr){
            var 
this.getCellValue(cr);
            var 
this.getCellFormat(cr);
            return 
f ? f.valueToText(v) : v;
        }

        
this.setCellValue(vcolrow);
        
this.setCellText(formattedcolrow);
    }
};

    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-{
        
text-alignright;
        
width80px;
    }
</
style>
</
head>
<
body>
<
script>

var 
myHeaders = ['name''price''amount''total'];

var 
myCells = [
    [
'item1'22.510],
    [
'item2'10,  123],
    [
'item3'155.501]
]

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([strnumnumnum]);

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)
Wednesday, October 10, 2007
Note, that it is also possible to use custom functions in formulas -

function myFunction(ab){
    return 
a*0.1 b;
}

obj.setCellFormula('myFunction(column(1), column(2))'3); // calc in column-3
 
Alex (ActiveWidgets)
Wednesday, October 10, 2007
Hi,

Will this patch be part of main library in 2.5 release.

Regards
Girish
Girish Khemani, Fidelity India
Wednesday, October 10, 2007
I don't know yet. Do you think it is a good idea to include this 'cell formula' code into the standard package? Or I should leave it separately as an add-on?
Alex (ActiveWidgets)
Friday, October 12, 2007
Hi Alex,

The size of the library increases every release, which is natural for any software.
I had looked about some time back on how I could reduce the size of aw.js by removing features I don't use.
One example is that I can remove the lines at the end of aw.js beginning with AW.HTTP.Request=, AW.CSV.Table= and AW.XML.Table= as I use another library to handle my XML HTTP requests and don't use csv and xml tables.
Right now I use the entire aw.js, but in future, if I need to crunch the size of the javascript in my application, I was thinking of the above and more removals in other .js files I use.

If this cell formula code is included, would it be possible to include it in a way in which it can be removed easily if we are not using the feature ?

Thanks,
Ankur
Ankur Motreja
Friday, October 12, 2007
Maybe you could include an "AW Lite" for basic functionality... and then "AW Standard" which includes these functions. I feel that when you have a grid like this it's important to offer as many functions as possible.

Ed
Thursday, October 18, 2007
Hello,

Is it possible to set formulas in a header row instead of a cell row?

Regards,

Brad
Tuesday, October 23, 2007
how about i want to calculate the row and put it into my footer? please help me..alex
putera
Tuesday, March 4, 2008



This topic is archived.

Back to support forum

Forum search