Cell Formula?

Is it possible to define a cell's formula, say, cell [i][j].formula = [i][k].value * [i][f].value?

Even better, si there a way to declare a column as a calculated one, saying that some column's rows will have a specified calculation for that cell? i.e., Column("Total").setFormula('"Col1"*"Col2"');

It seems that with a data grid such as this, cell calculations should be a built-in feature. However, I haven't been able to find any references to this. Any help would be appreciated!

PS. -- I Realize that it is possible to do calculations manually through JScript, but was wondering more along the lines if AW offered any sort of built-in calculation functionality.

Thanks,

No, there are no built-in Excel-like formulas in AW. You can use javascript functions for some of the columns -

function sum01(col, row){
    var cell1 = this.getCellValue(0, row);
    var cell2 = this.getCellValue(1, row);
    return cell1 + cell2;
}

obj.setCellData(sum01, 2); // sum of col0 and col1 in col2

You can even generate these functions dynamically (using closures or nested functions) -

function sum(col1, col2){
    return function(col, row){
        var cell1 = this.getCellValue(col1, row);
        var cell2 = this.getCellValue(col2, row);
        return cell1 + cell2;
    }
}

obj.setCellData(sum(0, 1), 2); // sum of col0 and col1 in col2

However you still need to track dependencies and refresh the cells when data changes.

or, maybe this way :-)

function formula(operation){
    return function(col1, col2){
        return function(col, row){
            var cell1 = this.getCellValue(col1, row);
            var cell2 = this.getCellValue(col2, row);
            return operation(cell1, cell2);
        }
    }
}

var sum = formula(function(a,b){return a+b});
var diff = formula(function(a,b){return a-b});
var product = formula(function(a,b){return a*b});

obj.setCellData(sum(0, 1), 2); // sum of col0 and col1 in col2

and finally support for the nested functions and constants :-)

function col(i){
    return function(c, r){
        return this.getCellValue(i, r);
    }
}

function formula(operation){
    return function(arg1, arg2){
        return function(c, r){
            var value1 = (typeof(arg1)=='function' ? arg1.call(this, c, r) : arg1);
            var value2 = (typeof(arg2)=='function' ? arg2.call(this, c, r) : arg2);
            return operation(value1, value2);
        }
    }
}

var sum = formula(function(a,b){return a+b});
var diff = formula(function(a,b){return a-b});
var product = formula(function(a,b){return a*b});

obj.setCellData(sum(col(0), col(1)), 2); // col2 = col0 + col1
obj.setCellData(diff(product(col(3), 1000), col(4)), 5); // col5 = col3 * 1000 - col4

Apparently there is a much more simple solution for the calculated cells feature which uses normal javascript syntax and also includes the dependencies tracking, see -

http://www.activewidgets.com/javascript.forum.20911.0/calculated-columns-cell-formula.html

:-)

How are you going to refresh the data within the cells? I understand you are calling the refresh but I am confused.