Formulas missing after upgrade to 2020.2.617

[Koren]

We have had a spreadsheet popup working for a "quick edit" solution for a few years.  I just upgraded to 2020.2.617 and the spreadsheet is no longer calculating correctly.  If I turn the formula row on the top, it looks like the formulas are not there.  What changed between 2017 SP1 and this release?  What do I need to do differently?

Here is an example of the spreadsheet (cshtml)":

@(Html.Kendo().Spreadsheet()
    .Name("spreadsheet")
    .HtmlAttributes(new { style = "width:98%; height: 90%;" }).HeaderHeight(0).HeaderWidth(0)
    .Toolbar(false)
    .Sheetsbar(false)
    .Excel(excel => excel
        .ProxyURL(Url.Action("Index_Save", "Spreadsheet"))
    )
    .Pdf(pdf => pdf
        .ProxyURL(Url.Action("Index_Save", "Spreadsheet"))
    )
    .Events(events => events
        .Render("onRenderExpenses"))
    .Sheets(sheets =>
    {
        sheets.Add()
            .Name("Transactions")
            .Columns(columns =>
            {
                if (Model.SpreadSheetColumns != null)
                {
                    foreach (SpreadSheetColumnSettings col in Model.SpreadSheetColumns)
                    {
                        columns.Add().Width(col.Width);
                    }
                }
            })
            .Rows(rows =>
            {
                rows.Add().Index(0).Height(60).Cells(cells =>
                {
                    if (Model.SpreadSheetColumns != null)
                    {
                        foreach (SpreadSheetColumnSettings col in Model.SpreadSheetColumns)
                        {
                            cells.Add().Bold(true).TextAlign(SpreadsheetTextAlign.Center)
                                .Value(col.Title).Background("#80cce7").Color("#000000").Wrap(true)
                                .Enable(false);
                        }
                    }
                });
                rows.Add().Index(99).Height(40).Cells(cells =>
                {
                    foreach (SpreadSheetColumnSettings col in Model.SpreadSheetColumns)
                    {
                        cells.Add().Bold(true).TextAlign(SpreadsheetTextAlign.Right)
                            .Background("rgb(193,226,255)").Color("#000000")
                            .Enable(false).Format(col.Format).Formula(col.Formula);
                    }

                });

            });
    })
)

And here is an example of the columns:

columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false, Formula = "IF(ROUND(SUM(H" + rowTotal + ":I" + rowTotal + "),2) - ROUND(SUM(K" + rowTotal + ":BG" + rowTotal + "),2) = 0,0,1)" });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 100, Editable = false, Format = "$#,##0.00", Formula = "SUM(H2:H" + maxRows + ")" });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 100, Editable = false, Format = "$#,##0.00", Formula = "SUM(I2:I" + maxRows + ")" });
                    //columns
                columnList.Add(new SpreadSheetColumnSettings() { Width = 30, Editable = false });
                columnList.Add(new SpreadSheetColumnSettings() { Width = 100, Editable = false, Format = "$#,##0.00", Formula = "SUM(K2:K" + maxRows + ")" });

[Aleksandar]

Hello Koren,

Major changes that affect the widget behavior are usually listed in the Breaking changes section of the documentation. There are, however, no changes introduced that would affect the Spreadsheet in such a way and cause the issue reported. 

I have created a sample project based on the provided details and I can see the formulas applied to the cells. I would suggest checking if there are any errors logged in the console? Were any exceptions thrown? Can you review the sample and let me know if anything crucial is missing?

Regards,
Aleksandar
Progress Telerik

[Koren]

I updated the project with more of the code and you will see the problem.  It appears to be in the setdatasource.  I honestly am not sure why the "column" info is split into two parts from the model and from ajax.  It worked for three years and I must have had a sample to work from at the time.  I think the gist of it is that the columns are dynamic depending on the transaction type.  See anything obvious that I am doing wrong?

 

[Koren]

attachment would help...  Had to remove lib dir to get it to upload.

[Aleksandar]

Hello Koren,

the observed behavior would be expected. When the Spreadsheet is in DataSource binding mode is exhibits specific behavior, as noted in the documentation section. One or the listed specific behaviors concerns the formulas:

Cell styles, formulas, and formats are not persisted in the data source.

If you need to persist formulas you can consider storing the data as JSON. You can then use the fromJSON method to load the state of the Spreadsheet. You can read more on this approach here:

https://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/import-and-export-data/json#using-the-serialization-api

Regards,
Aleksandar
Progress Telerik