Kendo Formula With Remote Datasource

[Marc]

I have a spreadsheet with a remote datasource, linked via the .read() function of the datasource.

I would like to add a formula to a specific cell on each row (over 5000 rows).The cell config is called "pageWeightValue". The datasource.data() method is an array on my objects, where each object has an attribute called pageWeightValue. Is there a way to set the value in the array to the formula. The column is set to type string, but the formula is not applied as a formula, but rather an actual string. Ie for each record in my array I do object.pageWeightValue = '=sum(A1:A4)' (the letter changes dependant on the row number). However in the cell I set the exact string, rather than the formula result.

Thanks!

Marc

[Veselin Tsvetanov]

Hello Marc,

What I could suggest you is to handle the render event of the Spreadsheet, iterate over the cells, that should contain formulas and set their values as formulas:

var shouldMakeFormulas = true;

function onRender(e) {
    if (shouldMakeFormulas) {
        var spread = e.sender;
        var sheet = spread.activeSheet();

        for (var i = 2; i < 6; i++) {

            var range = sheet.range('D' + i + ':D' + i);
            var value = range.value();

            if (value) {
                shouldMakeFormulas = false;
                range.formula(value);
            } else {
                break;
            }
        }
    }
}

Attached you will find a simple MVC solution, that implements the above. You will notice, that in the formulas I have used the R1C1 notation.

Regards,
Veselin Tsvetanov
Telerik by Progress

[MOTASH]

with this approach i'm swapping the value to the formula and it works on data fetch from server,what about updating (Datasource.sync) of a user input formula

what event do i have to handle to swap back the formula property into value property(to be saved into the remote DB through DS transport update)

[Veselin Tsvetanov]

Hello Mohamed,

You could convert the formulas back to string values just befor you call sync on the Sheet DataSource:

$("#save").click(function () {
    if (!$(this).hasClass("k-state-disabled")) {
        var spread = $('#spreadsheet').getKendoSpreadsheet();
        var sheet = spread.activeSheet();

        for (var i = 2; i < 6; i++) {
            var range = sheet.range('D' + i + ':D' + i);
            var formula = range.formula().toString();

            if (formula) {
                range.value(formula);
            } else {
                continue;
            }
        }

        sheet.dataSource.sync();
        shouldMakeFormulas = true;
    }
});

Attached you will find a modified version of the sample already discussed. Keep in mind that the Update endpoint in the controller is not implemented.

Regards,
Veselin Tsvetanov
Progress Telerik