kendoSpreadsheet.toJSON ignores Named Ranges

3 posts, 0 answers
  1. MikeWiese
    MikeWiese avatar
    43 posts
    Member since:
    Apr 2007

    Posted 10 Aug 2016 Link to this post

    I have a div configured as a kendoSpreadsheet. Using the native "Open..." button in the toolbar, I open an XLSX file that happens to contain named ranges. Importantly, formulae in the spreadsheet are expressed in terms of named ranges.

    Some console logging confirms that unfortunately, the named ranges are stripped out of the JSON that is returned from the toJSON() method.

    This is my testbed:
                        var kendoSpreadsheet = $("#mySpreadsheetDiv").data("kendoSpreadsheet")
                        console.log(kendoSpreadsheet);// Do we see names preserved? - yes, we can see this via "_workbook/_names"

                        var jsonObject = kendoSpreadsheet.toJSON();
                        console.log(jsonObject);// Do we see names preserved? - no, we don't

    So it means that the JSON is actually useless as a representational format. Here's why: 

    A typical application requires that I use that same JSON string to re-instantiate that spreadsheet on the server-side, using Telerik.Web.Spreadsheet.Workbook.Load() followed by ToDocument() to convert it to a Telerik.Windows.Documents.Spreadsheet.Model.Workbook.

    But a spreadsheet instantiated in this way can't be touched - as soon as I set the value of a cell (specifically, a cell that used to be a Named Range) using Worksheet.Cells[row, column].SetValue(somevalue), the spreadsheet breaks. All the formulae involving named ranges now come up as #NAME? (naturally enough, because the named ranges have been stripped out)

    It's a pretty big issue - I haven't seen a serious spreadsheet that does NOT use named ranges. I'm tempted to think I'm missing something. If not, are there any plans for a fix?

  2. Alex Gyoshev
    Alex Gyoshev avatar
    2527 posts

    Posted 12 Aug 2016 Link to this post

    Hello Mike,

    The support for user-defined names is scheduled to ship in the R3 release in September. You can track its status in the GitHub issue.

    Alex Gyoshev
    Telerik by Progress
    Get started with Kendo UI in days. Online training courses help you quickly implement components into your apps.
  3. MikeWiese
    MikeWiese avatar
    43 posts
    Member since:
    Apr 2007

    Posted 17 Aug 2016 Link to this post

    Thanks Alex.

    For anybody else who can't wait till the Q3 release, you can hack together a solution by extracting the defined names from the client-side kendoSpreadsheet object (via kendoSpreadsheet._workbook._names, which just feels icky), sending them back to the server along with the spreadsheet json from the toJSON() method.

    On the server, you load the Telerik.Web.Spreadsheet.Workbook from the spreadsheet json in the normal way, then convert it to  a Telerik.Windows.Documents.Spreadsheet.Model.Workbook with the ToDocument() method. Then you can iterate through your collection of defined names that you previously got from the client, and Add() the required structures to the Names collection of this latter workbook object. Laborious, but at least the workbook is then able to recalculate itself OK.  I look forward to not having to do this.
Back to Top