Kendo Formula With Remote Datasource

4 posts, 0 answers
  1. Marc
    Marc avatar
    171 posts
    Member since:
    Sep 2016

    Posted 03 May 2017 Link to this post

    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

  2.
  3. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1240 posts

    Posted 05 May 2017 Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  5. MOTASH
    MOTASH avatar
    6 posts
    Member since:
    Jul 2015

    Posted 25 Mar 2018 in reply to Veselin Tsvetanov Link to this post

    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)

  7. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1240 posts

    Posted 27 Mar 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top