Forums Home / Spread / SpreadJS
Posted by: dean.kinnear on 23 December 2022, 12:23 pm EST
dean.kinnear
Posted 23 December 2022, 12:23 pm EST
Is it possible to iterate through all names (global and local) in a workbook, and then get the name’s associated range (if any)?
I see getCustomNames method.
But that returns a nameinfo object, and don’t see any associated range object.
Something like:
var names = spread.getCustomNames();
for (var i = 0; i < names.length; i++) {
var name = names[i];
var range = name.getTheAssociatedRangeObject();
…
ankit.kumar
Posted 26 December 2022, 2:16 am EST
Hi,
As per my understanding, you want to get the row and column for which the custom name is applied. If this is the use case, you could iterate through the sheet’s dataTable and check if the formula is the custom name formula.
You can refer to the following sample that I have created for you: https://jscodemine.grapecity.com/share/A3xP1W5vhkm66b0eQPsUdA/?defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}
Please let us know if you have a different use case.
toJSON method: https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#tojson
Regards,
Ankit
dean.kinnear
Posted 27 December 2022, 10:48 am EST
That is close, but what I am looking for is some direct way to get a range object from a name (if the name refers to a range reference), like in Excel. What happens if I have multiple named ranges with the same formula?
kevin.ashley
Posted 27 December 2022, 12:48 pm EST
Hello,
I have asked the developers if we have any existing API that supports this and will get back to you as soon as I have an answer. The internal case ID for your reference is SJS-16419.
Regards,
Kevin
kevin.ashley
Posted 28 December 2022, 12:46 pm EST
Hello,
The developers have responded, and it looks like there is no direct way to get a range object from a custom name. The closest thing you can do is loop through the formula used range (https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#getUsedRange with the UsedRangeType “formula”), and then check if the formula includes the specified custom name.
Regards,
Kevin
dean.kinnear
Posted 3 January 2023, 8:21 am EST
I need the actual range that a name is applied to. A formula can have a range reference in it, but it doesn’t actually tell me what the range refers to. If a range name is applied to A1:C4, I need to know that range.
dean.kinnear
Posted 3 January 2023, 8:21 am EST
I need the actual range that a name is applied to. A formula can have a range reference in it, but it doesn’t actually tell me what the range refers to. If a range name is applied to A1:C4, I need to know that range.
kevin.ashley
Posted 3 January 2023, 4:52 pm EST
Hello,
I have asked the developers for more information and they said that we don’t have any API to do that. You would need to loop through all of the formulas of all the sheets and check for custom names. If a name is used in the formula, then the name is used for the cell.
In addition, if you can please provide more information about exactly what you are trying to accomplish in SpreadJS, we might be able to suggest a different way to go about it.
Regards,
Kevin
Regards,
Kevin
dean.kinnear
Posted 3 January 2023, 5:27 pm EST - Updated 3 January 2023, 5:35 pm EST
we write values to named ranges.
For instance
Range Name “FName” refers to cell Sheet1!B1
Range Name “LName” refers to cell on Sheet2!B1
How can I write a value to FName and LName directly?
Please find an example workbook.
Names.zip
I need to be able to write to ranges that a name refers to.
Excel’s object model is as follows.
workbook.names[“FName”].RefersToRange.value = “Dean”
workbook.names[“LName”].RefersToRange.value = “Kinnear”
kevin.ashley
Posted 4 January 2023, 11:32 am EST - Updated 4 January 2023, 2:33 pm EST
Hello,
You can get the worksheet of a custom name like so:
if (expr.source) {
var sheet = expr.source.getSheet();
var sheetName = sheet.name();
...
}For checking the scope of the name, both the workbook and worksheet classes have the getCustomName function. So if you use the workbook version of that function, that means it would be scoped to the workbook. And the same applies for the worksheet version of that function.
Regards,
Kevin
dean.kinnear
Posted 4 January 2023, 1:15 pm EST
This looks good.
Is there any way to get the worksheet object or worksheet name for the parent sheet of the name? Can that information be obtained from the expr.source object above?
Also, is there a way to check if a name is locally or globally scoped?
Thanks,
Dean
kevin.ashley
Posted 4 January 2023, 2:34 pm EST
Hello,
You can get the worksheet of a custom name like so:
if (expr.source) {
var sheet = expr.source.getSheet();
var sheetName = sheet.name();
...
}For checking the scope of the name, both the workbook and worksheet classes have the getCustomName function. So if you use the workbook version of that function, that means it would be scoped to the workbook. And the same applies for the worksheet version of that function.
Regards,
Kevin
dean.kinnear
Posted 4 January 2023, 2:57 pm EST - Updated 4 January 2023, 3:30 pm EST
If I use setvalue on the sheet object, it did redraw
kevin.ashley
Posted 9 January 2023, 12:23 pm EST
Hi Dean,
Can you please explain the issue further? Is this related to getting the names in the workbook? If this is a separate issue please submit a ticket or a different forum post.
Regards,
Kevin
