Get (relative) Adresse of Table Cell in calc python macro

[Neeby]

I am searching for a python function, which just returns a simple relative address of a calc table cell.

Lets assume I have a cell object and call the CellAddress-Parameter, then I get a com.sun.star.table.CellAddress Object. But how can I convert this back to a String containing a relative Address?
Currently I am doing this:

row = celladressobject.Row
column = celladressobject.Column
evaluator = servicemanager.createInstanceWithContext("com.sun.star.sheet.FunctionAccess",context)
myExpectedResult = evaluator.callFunction("ADDRESS", (row+1,column+1,4))

This is kinda ugly, especially since the calc function starts with (1,1) indexing. But I couldn’t find a python function yet, which does the same.

[karolus]

Hm

_, abs_name = cell.AbsoluteName.split('.')
relname = ''.join(abs_name.split('$'))
print(relname)

But it looks more like a x-y-problem? Tell about the story behind!

[sokol92]

@karolus

AbsoluteName

In my opinion, using AbsoluteName in such cases can be problematic because the sheet name can contain special characters such as . or $.

[karolus]

@sokol92

because the sheet name can contain special characters such as . or $.

Really? →→→

_, abs_name = cell.AbsoluteName.rsplit('.',  1)

[sokol92]

Yes you are right.

[Neeby]

Working on strings with AbsoluteName feels as ugly as my current solution.
Is there really no native function to take row and column and return the address?

The story is not very interessting. I want to enter a formula to a cell (setFormula) and a formula requires the address, which I want to provide as a relative one.

[sokol92]

This topic is discussed in a paragraph “15.2. Sheet cells contain the data” of the book by A. Pitonyak OOME_4_0.odt. The suggested variant with AbsoluteName is simpler.

[karolus]

@Neeby

feels as ugly as my current solution.

IHMO its less ugly as yours with creating and executing function_access, but anyway:

def relative_name(cell):
    c, r = cell.ColumnDescriptions, cell.RowDescriptions
    return f"{c[0].split()[-1]}{r[0].split()[-1]}"