The Problem is XNamedRange doesn’t have getReferredCells() and Java Api doesn’t have NamedRange
When i get .getContent() from XNAmedRange i get output in " $‘Sheet-1’.$B$4 " format. I think i can get Cell Range from here.
In order to get any named range from a given cell, you can use LibreOffice: XCellRangesQuery Interface Reference which returns a collection c.s.s.table.XCellRanges
If that collection is empty (getCount() == 0) the given range has no intersection with the ReferencedCells of a NamedRange.
btw: getReferencedCells() returns Null if the NamedRange does not specify not a fully absolute reference like $Sheet.$X$1 (3 $ for a single cell) or $Sheet.$X$1:$Y$99 (5 $ for a range).
What do you have in mind?
Open my example.ods file from the link in this topic.
Msgbox ThisComponent.NamedRanges.getByName("d_data").ReferredCells.AbsoluteName
shows $Sheet3.$C$21:$E$50.
In OpenOffice the ReferencedCells used to return Null when the reference was a relative/mixed one. The range is not the same from the view points of different reference cells.
This is not true anymore with LibreOffice (and latest AOO) which simply returns the range specified by the mere address string. I don’t have time nor inclination to investigate further.
This imposes no problem when the name refers to a completely absolute cell or range. In case of mixed/absolute references, one would calculate the offset between the ReferencePosition and the relative address and apply this offset to the cell in question. All this is awkward because internally, Calc does not use the R1C1 addressing scheme.
I use this code to get Cell Address I get result below. It suppose to be row :1, Column :1 , but i got 429 instead of 1. What cause this and hoe can i fix it ?
name :Model
row :429 Column :1
…
{
com.sun.star.beans.XPropertySet xDocProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xSpreadsheetDocument );
Object aRangesObj = xDocProp.getPropertyValue( "NamedRanges" );
com.sun.star.container.XNameAccess xRanges =
UnoRuntime.queryInterface(
com.sun.star.container.XNameAccess.class, aRangesObj );
String[] aNames = xRanges.getElementNames();
System.out.println("elements");
for(String name:aNames){
System.out.println("name :"+name);
Object aRangeObj = xRanges.getByName( name );
com.sun.star.beans.XPropertySet xRangeProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRangeObj );
com.sun.star.sheet.XCellRangeReferrer xRef = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeReferrer.class, aRangeObj );
com.sun.star.table.XCellRange xResultRange = xRef.getReferredCells();
XCell xCell = xResultRange.getCellByPosition(0, 0);
com.sun.star.sheet.XCellAddressable xCellAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, xCell );
com.sun.star.table.CellAddress aAddress = xCellAddr.getCellAddress();
int rowIndex = aAddress.Row;
int columnIndex = aAddress.Column;
System.out.println("row :"+rowIndex+" Column :"+columnIndex);
}
}
You didn’t mention why you would expect a position 1,1. Does the range reference start at B2?
xResultRange.getCellByPosition(0, 0) returns the top left cell of xRef.getReferredCells(), so whatever is that actual position is returned.
Which of the 3 possible UI string address syntax is used is irrelevant, because internally the calculation happens with the formula tokens. One can do the same by obtaining the sequence<css::sheet::FormulaToken> XFormulaTokens::getTokens() from the named object, which in case the named expression is a single named range contains one token where FormulaToken.OpCode is 0 (ocPush) and FormulaToken.Data contains either a LibreOffice: SingleReference Struct Reference or a LibreOffice: ComplexReference Struct Reference (front top left and rear bottom right single references). Those have flags whether a position is absolute or a relative offset that needs to be calculated to an absolute position using the actual cell position in which the named expression is used.
Note that a named object is a named expression and a named range or named cell is only a special case, if it can not be assumed that all named objects are actually named ranges then length and content of the FormulaToken sequence has to be checked.
2 Likes
I have tested with different ranges and observed that Column can be taken from CellAddress but it is not easy to find row.
It can be usable for me if i can get column from CellAddress. Is this a coincidence or does it work like that?
I have no idea what you are talking about. A CellAddress has all three, Sheet, Column and Row, and so does a SingleReference.
CellAddress doesn’t have SingleReference 
Of course not. But with the current cell position’s CellAddress you can calculate the absolute positions from the FormulaToken’s SingleReference’s relative positions (offsets) if it has. Its absolute references can be used as is.
Hallo,
Do you have an example for this usage?
Something like this, omitting any necessary check for the FormulaToken’s validity, just to illustrate:
// whatever initialized to for the current formula cell's position
CellAddress aCurrentCell;
SingleReference aRef = aFormulaToken.Data;
CellAddress aAbsRef;
aAbsRef.Sheet = aRef.Flags & css::sheet::ReferenceFlags::SHEET_RELATIVE ? aCurrentCell.Sheet + aRef.RelativeSheet : aRef.Sheet;
aAbsRef.Column = aRef.Flags & css::sheet::ReferenceFlags::COLUMN_RELATIVE ? aCurrentCell.Column + aRef.RelativeColumn : aRef.Column;
aAbsRef.Row = aRef.Flags & css::sheet::ReferenceFlags::ROW_RELATIVE ? aCurrentCell.Row + aRef.RelativeRow : aRef.Row;