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

Is it possible to find a named range?

2 Answers 310 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Dawid
Top achievements
Rank 1
Dawid asked on 29 May 2019, 10:43 PM

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.

2 Answers, 1 is accepted

Sort by
0
Accepted
Dimitar
Telerik team
answered on 01 Jun 2019, 04:00 PM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Dawid
Top achievements
Rank 1
answered on 03 Jun 2019, 08:38 PM

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

Tags
Spreadsheet
Asked by
Dawid
Top achievements
Rank 1
Answers by
Dimitar
Telerik team
Dawid
Top achievements
Rank 1
Share this question
or