Evaluate excel formula added in column render event

[Param Query Eval]

Team, we have a requirement where we need to evaluate excel formula for a cell based on couple of previous cells in the same row. Since the column index is dynamic and difficult to calculate it before rendering, we tried adding formula through column render event.
The problem is that the formula added in render doesn't get evaluated and it displays the formula directly in cell.[Reference attachment1].
So when we tried to refresh the view like using complete : function(){this.refreshView();}, it evaluates the formula only for current view port page. when we scroll vertically other formulas still shows as plain text. (so we have to use refreshView() in scroll event each time and it may hit grid performance for more data)

I have attached sample code where it use SUM formula, but it evaluated only in current page and when scroll up/down of grid, it shows old value and double click of cell shows plain formula as text in cell.

So, is there any suggestion on what would be best approach to add excel formula to a column(we know source column index at the time of rendering only) and it evaluates formula without manual trigger event.

[paramvir]

Couple of ways to solve it.

1. Excel formulas: Use COLUMN() function that returns reference of the cell in which the COLUMN function appears.

https://paramquery.com/pro/api/excel-formulas

2. Use javascript formulas instead of Excel formula: https://paramquery.com/pro/demos/grid_formula

[Param Query Eval]

Thank you for the suggestions.
1) The challenge in the first approach is that if we use excel formula in column render then it is not evaluating other than current page(when scroll left/right). I also tried to use the formula as part of json string as like pq_fn: {"WeekSummary" : "SUM("+getReferenceName(COLUMN())-7+"ROW():"+getReferenceName(COLUMN())-1+"ROW())"}. But i couldn't able to use javascript function which gives reference cell number like 'C' when pass column number 3. Basically i would like to achieve something like pq_fn: {"WeekSummary" : SUM(C1:G1) }. Any reference code snippet would be really helpful.
2) Yes, we are considering javascript formula's when 2 or 3 columns involved, when it involves to calculate 5 or 10 cells range then we prefer to use excel formula for easy understanding.

[paramvir]

Adding Excel formula in column.render is not right.

Current column letter can be obtained by the formula

Code: [Select]

CHAR(COLUMN()+64)


[Param Query Eval]

great, it works! Is there any approach to accomplish when more than 26 columns.

[Param Query Eval]

In fact using column() formula is not elegant when building formula like below one and it is not evaluating correct value.
pg_fn { "WEEKSUM" : SUM(CONCATENATE(CHAR(COLUMN()+(64-6)),ROW(),\":\",(CHAR(COLUMN()+(64-1))),ROW()))}
Do we have any other approach to achieve the desired result.

[paramvir]

Please share a jsfiddle.

[Param Query Eval]

Due to some library reference issue jsfiddle was not rendering grid properly. so i have attached the code sample for reference which is running in ParamQuery edit and run window. Also i have attached the high level screenshot of what we are trying to solve it.
Basically we are trying to do two different things.
1) calculation on weekly basis by referring previous cells.[p.s: column name would be dynamic and there may be multiple weeks data loaded and each weekly calculation column should refer only respective week days(previous 7 columns of respective row)].
2) Apply some formula on parent row cell by referring respective child row cells[p.s: no.of child would be vary for different parent node].
As cell render event gives row index and data index property we have analysed to build excel formula over there.
Since it is previously mentioned column render is not right for excel formula, we would like to get your approach/suggestion to tackle this use case.

[paramvir]

1. Excel formula can be executed ( instead of defined ) and its value returned in column render callback. Please note that I've used undocumented grid.iFormulas.exec method to solve this use case.

Code: [Select]

  render: function(ui){
var ri = ui.rowIndx+1,
ci = ui.colIndx,
fn = "SUM("+ pq.toLetter(ci-7) + ri + ":"+ pq.toLetter(ci-1) + ri +")";
return this.iFormulas.exec( fn ).toString();
  }

2. Apply some formula on parent row cell by referring respective child row cells

Code: [Select]

columnTemplate: { minWidth: '10%', maxWidth: '80%', width: 100,
render: function(ui){
var rd = ui.rowData, di = ui.dataIndx, vals = [], children = rd.children;
if( (typeof(rd[di]) != "string") && children ){
vals = children.map(function(r){
return r[di]
})
rd[di] = pq.aggregate.sum(vals);
return rd[di].toString();
}
}
},


Complete code:

Code: [Select]

$(function () {
        var data = [
            {
                "id": 0,
                "Item": "Product1",
                "2_MAR_MON": 1,
                "3_MAR_TUE": 2,
                "4_MAR_WED": 3,
                "5_MAR_THU": 4,
                "6_MAR_FRI": 5,
                "7_MAR_SAT": 6,
                "8_MAR_SUN": ''               
            },
            {
                "id": 1,
                "Item": "Product2",
                "2_MAR_MON": 7,
                "3_MAR_TUE": 8,
                "4_MAR_WED": 9,
                "5_MAR_THU": 10,
                "6_MAR_FRI": 11,
                "7_MAR_SAT": 12,
                "8_MAR_SUN": ''               
            },
            {
                "id": 2,
                "Item": "GroupProduct",
pq_rowprop:{edit: false},//make the row uneditable.
                /*pq_fn: {
                    "2_MAR_MON": "=SUM(CONCATENATE(CHAR(COLUMN()),ROW()+1,\":\",(CHAR(COLUMN())),ROW()+3))",
                    "3_MAR_TUE": "=SUM(CONCATENATE(CHAR(COLUMN()),ROW()+1,\":\",(CHAR(COLUMN())),ROW()+3))",
                    "4_MAR_WED": "=SUM(CONCATENATE(CHAR(COLUMN()),ROW()+1,\":\",(CHAR(COLUMN())),ROW()+3))",
                    "5_MAR_THU": "=SUM(CONCATENATE(CHAR(COLUMN()),ROW()+1,\":\",(CHAR(COLUMN())),ROW()+3))",
                    "6_MAR_FRI": "=SUM(CONCATENATE(CHAR(COLUMN()),ROW()+1,\":\",(CHAR(COLUMN())),ROW()+3))",
                    "7_MAR_SAT": "=SUM(CONCATENATE(CHAR(COLUMN()),ROW()+1,\":\",(CHAR(COLUMN())),ROW()+3))"                   
                },*/

                "children": [
                    {
                        "id": 3,
                        "Item": "Sub Product1",
                        "2_MAR_MON": 7,
                        "3_MAR_TUE": 8,
                        "4_MAR_WED": 9,
                        "5_MAR_THU": 10,
                        "6_MAR_FRI": 11,
                        "7_MAR_SAT": 12,
                        "8_MAR_SUN": ''                       
                    },
                    {
                        "id": 4,
                        "Item": "Sub Product2",
                        "2_MAR_MON": 7,
                        "3_MAR_TUE": 8,
                        "4_MAR_WED": 9,
                        "5_MAR_THU": 10,
                        "6_MAR_FRI": 11,
                        "7_MAR_SAT": 12,
                        "8_MAR_SUN": ''                       
                    },
                    {
                        "id": 5,
                        "Item": "Sub Product3",
                        "2_MAR_MON": 7,
                        "3_MAR_TUE": 8,
                        "4_MAR_WED": 9,
                        "5_MAR_THU": 10,
                        "6_MAR_FRI": 11,
                        "7_MAR_SAT": 12,
                        "8_MAR_SUN": ''                       
                    }
                ]
            },
            {
                "id": 6,
                "Item": "Product4",
                "2_MAR_MON": 7,
                "3_MAR_TUE": 8,
                "4_MAR_WED": 9,
                "5_MAR_THU": 10,
                "6_MAR_FRI": 11,
                "7_MAR_SAT": 12,
                "8_MAR_SUN": ''               
            }
        ];


        var obj = {
            height: 'flex',
            maxHeight: 500,
            resizable: true,
            stripeRows: false,
            treeModel: {
                dataIndx: 'Item'
            },
            scrollModel: { autoFit: true },
cellSave: function(){
//cell changes affect other cells.
this.refresh();
},
            columnTemplate: { minWidth: '10%', maxWidth: '80%', width: 100,
render: function(ui){
var rd = ui.rowData, di = ui.dataIndx, vals = [], children = rd.children;
if( (typeof(rd[di]) != "string") && children ){
vals = children.map(function(r){
return r[di]
})
rd[di] = pq.aggregate.sum(vals);
return rd[di].toString();
}
}
},
            colModel: [
                 { title: 'Item', dataType: 'string', dataIndx: 'Item' },
                 { title: '2_MAR_MON', dataType: 'string' , dataIndx: '2_MAR_MON' },
                 { title: '3_MAR_TUE', dataType: 'integer', dataIndx: '3_MAR_TUE' },
                 { title: '4_MAR_WED', dataType: 'integer', dataIndx: '4_MAR_WED' },
                 { title: '5_MAR_THU', dataType: 'integer', dataIndx: '5_MAR_THU' },
                 { title: '6_MAR_FRI', dataType: 'integer', dataIndx: '6_MAR_FRI' },
                 { title: '7_MAR_SAT', dataType: 'integer', dataIndx: '7_MAR_SAT' },
                 { title: '8_MAR_SUN', dataType: 'integer', dataIndx: '8_MAR_SUN' },
                 { title: 'Summary', dataIndx: 'W2020-08_Week', editable: false,
  render: function(ui){
var ri = ui.rowIndx+1,
ci = ui.colIndx,
fn = "SUM("+ pq.toLetter(ci-7) + ri + ":"+ pq.toLetter(ci-1) + ri +")";
return this.iFormulas.exec( fn ).toString();
  }
}
            ],
            dataModel: { data: data }
        };
        pq.grid("#tree_grid", obj);
    });


[Param Query Eval]

Excellent! This is the solution we would like to achieve it. There are two follow up questions in below solution.
1) For each cell change it refresh the grid view in cellSave event.
As per doc, it is mentioned that grid refresh is a computational intensive operation.
Our worry is that grid performance may hit when few thousand of rows in grid.[Will it refresh only visible cells in grid page, if not is it any  approach to do it better without much performance impact]

Code: [Select]

cellSave: function(){
//cell changes affect other cells.
this.refresh();
}2) Irrespective of 'pq_rowprop:{edit: false},//make the row uneditable. ' edit property set false for parent row in tree, it allows to edit the row. I believe it is column edit option takes precedence over here. So we we are trying to use global editable event to achieve the same.
Will the below one would be a efficient solution or do we have any simple option to set at row level.

Code: [Select]

editable: function(ui){
var parentNode = ui.rowData.children;
return !parentNode;
},
Thanks..

[paramvir]

1) There is no performance concern irrespective of number of rows, columns as grid uses virtual rendering.

2) global editable callback can also be used to set editability of rows.

[Param Query Eval]

Thanks for the details.