This is a migrated thread and some comments may be shown as answers.

Formulas missing after upgrade to 2020.2.617

4 Answers 12 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Koren
Top achievements
Rank 1
Koren asked on 17 Aug 2020, 01:36 PM

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 + ")" });

4 Answers, 1 is accepted

Sort by
0
Aleksandar
Telerik team
answered on 19 Aug 2020, 07:04 AM

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

0
Koren
Top achievements
Rank 1
answered on 19 Aug 2020, 12:09 PM

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?

 

0
Koren
Top achievements
Rank 1
answered on 19 Aug 2020, 12:12 PM
attachment would help...  Had to remove lib dir to get it to upload.
0
Aleksandar
Telerik team
answered on 21 Aug 2020, 08:28 AM

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

Five days of Blazor, Angular, React, and Xamarin experts live-coding on twitch.tv/CodeItLive , special prizes and more, for FREE?! Register now for DevReach 2.0(20).

Tags
Spreadsheet
Asked by
Koren
Top achievements
Rank 1
Answers by
Aleksandar
Telerik team
Koren
Top achievements
Rank 1
Share this question
or