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

kendoSpreadsheet.toJSON ignores Named Ranges

2 Answers 128 Views

Spreadsheet

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

MikeWiese asked on 10 Aug 2016, 10:24 AM

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 Answers, 1 is accepted

answered on 18 Aug 2016, 03:55 AM

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.