Range Names and Cell Positions

turhan · August 2, 2023, 9:31am

Hello,
I have a Java Application,which should read range name in sheet and write data to related ranges, and i use LibreOffice Java API.
I am trying to find relation between Rangenames and CellPostions of the ranges.
I can get range names with com.sun.star.sheet.XNamedRanges but i could not find how to get relation between these names and Cell Positions.
How can i get this relation?

karolus · August 2, 2023, 9:58am

There is no “Java API” there is only one API no matter if you use Basic , python or Java.
Use mri to explore your objects

a NamedRange has the Property …ReferredCells respectivly the method …getReferredCells()

turhan · August 2, 2023, 11:52am

I do not know why but in my code row and cloumn returns always 0

com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet)
UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xSpreadsheetDocument);
Object aRangesObj = xDocProp.getPropertyValue(“NamedRanges”);
com.sun.star.sheet.XNamedRanges xNamedRanges = (com.sun.star.sheet.XNamedRanges)
UnoRuntime.queryInterface(com.sun.star.sheet.XNamedRanges.class, aRangesObj);

    for (String name : xNamedRanges.getElementNames()) {
        System.out.println("Name : " + name);

        Object namedRange = xNamedRanges.getByName(name);
        XNamedRange xNamedRange = UnoRuntime.queryInterface(XNamedRange.class, namedRange);
        CellAddress cellAddress = xNamedRange.getReferencePosition();

        System.out.println("Row---- :" + cellAddress.Row);

sokol92 · August 2, 2023, 12:19pm

Find the differences.

See also NamedRange.

karolus · August 2, 2023, 12:25pm

Hallo …getReferencePosition() returns the CellAddress of the upper/left corner of a given NamedRange

sokol92 · August 2, 2023, 12:43pm

It seems to me that this is not entirely true if the link uses relative addressing.
According to documentation

getReferencePosition() returns the position in the document which is used as a base for relative references in the content.

karolus · August 2, 2023, 1:36pm

Lets prove: ( …ReferencePosition by MRI )
named Range »fix« with complete absolute reference $Sheet3.$C$21:$E$50

Column        long              2
Row           long              20
Sheet         short             2

Named Range »relativ« with complete relative reference Sheet3.C21:E50

Column        long              2
Row           long              20
Sheet         short             2

both are available globally in the document!

sokol92 · August 2, 2023, 2:30pm

And one more test.
1.Select A1
2. Ctrl+F3, Add. Name:d_data, Range or formula expression: Sheet3.C21:E50.

Msgbox ThisComponent.NamedRanges.getByName("d_data").ReferencePosition.Row

shows 0.

elmau · August 2, 2023, 2:44pm

I my system work fine:

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 16; OS: Linux 6.4; UI render: default; VCL: gtk3
Locale: es-MX (en_US.UTF-8); UI: en-US
7.4.7-5
Calc: threaded

sokol92 · August 2, 2023, 3:01pm

Here is my file.
Example.ods (9.5 KB)

elmau · August 2, 2023, 3:05pm

There’s something weird with the definition of your range name, erase it, define it again. Try again.

karolus · August 2, 2023, 3:08pm

Thats confusing…

shows the same output, no matter which cell is selected

…but the management dialog adjusts the output relative to the selected cell:

sokol92 · August 2, 2023, 3:32pm

Sub Test2
  ThisComponent.NamedRanges.AddNewByName "d_data2", "Sheet3.C21:E50", _
    ThisComponent.Sheets.getByName("Sheet3").getCellByPosition(0,0).CellAddress, 0
  Msgbox ThisComponent.NamedRanges.getByName("d_data2").ReferencePosition.Row
End Sub

Shows 0.

karolus · August 2, 2023, 3:58pm

Oh, I am so stupid… now I got it! my Testscript with 2 Ranges one from A1 the other from C21:

sokol92 · August 2, 2023, 4:20pm

Now we are experts.

"An expert is a person who has made all the mistakes that can be made in a very narrow field.” - Niels Bohr.

karolus · August 2, 2023, 4:36pm

Do not underestimate the number of possible mistakes!

Villeroy · August 2, 2023, 6:12pm

Named ranges are named formulas actually. It can be a constant number, a literal string (double-quoted), a formula with relative, absolute, mixed references.
The reference position marks the anchor point of relative formulas. When using the GUI, it is the address of the currently active cell. When you generate it by code it starts with (0,0,0).
If the Content property of named formula “Cell_in_A” is “$A4”, it refers to some cell in column A on any sheet. If you want it to be this row’s cell in column A, you should set the reference position to col=0, row=3.

turhan · August 2, 2023, 8:56pm

Is it possible to select a range and find related Range name like in the Libre Office App? How does App do that ? That would be also great, If i can do that.

karolus · August 2, 2023, 9:40pm

which part of my answer you didn’t understand?
and you are the Java-programmer!

Lupp · August 3, 2023, 8:41am

I don’t think this is well supported by the API - and the ways “the App” or the GUI solves tasks of the kind are “hidden deeply in the source code”,
In the given case you will need to search all the NamedRange (actually named formula as you were already told) entries for one being an absolute range reference and complying with your selection. If not exiting the process prematurely, you may find more than one name associated. Assigning more than one name to a range is not prevented by “the App”.
A few examples of the kind:

A Copy/Paste in a sheet will know images shown in the area of the range. The range has no directly related property. User code would need to analyse positions and sizes.
A layer of a drawing seems to know “its members”. In fact the layer isn’t any container type on the API level. The shapes need to know to what layer they are “assigned”.

Everybody knowing better, please tell me.