Is it possible to find a named range?

[Dawid]

Hi there,

First let me state what we're trying to achieve.

We need to inject some values into a pre-defined spreadsheet with data already on it (basically a copy from a previous version).  I thought I could name specific cells using the "defineName" method on the spreadsheet to create named ranges (each one cell in size) and then later find those ranges to set the values.

However, I might be incorrect in what the named ranges are for, but I cannot find a way to find a named range and then set it's value.  Is there a way to do this, or some other way to give cells that can be anywhere some kind of "metadata" that I can use later to inject data?

Say for example we have data relating to countries, I'd want to generate the spreadsheet, give each country's cell a number and give the cell a range name of the country. I wouldn't necessarily know the exact cell number, because users could insert new rows/columns etc, but the range name would stay. Then later I'd be able to find the ranges by country name somehow and put different numbers in there.

Thanks.

[Dimitar]

Hello Dawid,

You could refer to a single cell or a range of cells in a named range and further use it in formulas. You could create it via:

 - API method (defineName()) that you have already found.

https://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet/methods/definename

 - UI of the component - the Name Box of the spreadsheet:

https://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/end-user/user-guide 

- Add names declaration to the json. I have prepared a sample that shows an example:

https://dojo.telerik.com/EsoKUmoF

Note that the named ranges are defined for the whole workbook and you could access all named ranges as follows:

$("#spreadsheet").getKendoSpreadsheet()._workbook._names.germany

In order to update values in the sheet, you could find a range and use its value method:

https://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/range

Regards,
Dimitar
Progress Telerik

[Dawid]

Thanks so much Dimitar, that's exactly what I was looking for.

For reference if anyone else needs to do this, I've just made a little snippet :

 

$(function() {
    var ss = $("#spreadsheet").kendoSpreadsheet().data("kendoSpreadsheet");
    ss.defineName("MyCell", "H1:H1");
    var myCell = $("#spreadsheet").getKendoSpreadsheet()._workbook._names.mycell;
    ss.activeSheet().range(myCell.value.topLeft.row, myCell.value.topLeft.col, myCell.value.bottomRight.row - myCell.value.topLeft.row + 1, myCell.value.bottomRight.col - myCell.value.topLeft.col + 1).value("foo");
});

 

Cheers