Setting a hyperlink on the text in a Calc cell in python?

[djfiander]

According to the documentation, it looks like I should be able to say

Code: Select all

cell = sheet.getCellByPosition(0, 0)
cell.SetPropertyValue("HyperLinkURL", "http://www.google.ca")

But when I do that, I get an "Unknown Property" error. I've seen Python code that does this for text documents, and some non-Python code that seems to be setting an "URL" property, but that's not documented anywhere. Am I missing a layer here, and I should be getting the text out of the cell and setting the property on that?

[Villeroy]

Try to do it to the cell's text.

Code: Select all

oTxt = oCell.getText()

oTxt is a text object of similar type as in Writer.
You may be intersted in the Xray tool and in
Using Xray From Python

[djfiander]

Thanks for pointing me at Xray.

I put in the getText() call, only to discover (as is also reported by XRay), that cell.getText() just returns the original cell.

And there are no HyperLink* properties on a cell. In fact, I even set an URL on some text in Calc using the standard UI, and then XRayed it to see where the URL appears. No luck.

I opened up the .ods and looked at the content.xml:

<table:table-cell office:value-type="string">
<text:p>
<text:a xlink:href="http://www.google.com/">Boo!</text:a>
</text:p>
</table:table-cell>

So, it's in there, and it's tied to a text-ish kind of thing. But the API's not exposing it?

[Villeroy]

Sorry, Writer is totally over my head. I don't understand that component's object hierarchy and to be honest: I hate to write text with it as I hate to use MSWord. This type of editor is for typewriter ladies (with all respect. I used to love one of them really hard).
I thought that we need to get a so called TextCursor in order to prepend, insert, append some text portion, but look how I failed with the most simple Basic macro together with Xray:

Code: Select all

REM  *****  BASIC  *****
Sub Main
GlobalScope.BasicLibraries.loadLibrary("XrayTool")
sh = thisComponent.Sheets.getByIndex(0)
cell = sh.getCellByPosition(0,0)
'xray cell 'shows cell.Start, cell.End and cell.createTextCursor()

'both raise error "Property or method not found":
txtRange = cell.Start
cursor = cell.createTextCursor()
'xray cursor
End Sub

I'm feeling so tired ...

However, there are so many useful things you can do with URLs and spreadsheet cells

[djfiander]

Figured it out, with a lot of help from this OOForum posting.

Basically, you don't use the HyperLink* text properties to set an URL on a cell. You create a text field, set the "URL" and "Representation" properties on the text field, then insert it into the cell:

Code: Select all

    str = cell.getString()

    fld = doc.createInstance("com.sun.star.text.TextField.URL")
    fld.setPropertyValue("URL", url);
    fld.setPropertyValue("Representation", str);

    # We are replacing the contents of the cell with exactly the same text,
    # but storing it in the URL textfield with the same display value.
    cell.setString("")

    # You can only insert textfields via a text cursor, so we need to create one
    txt = cell.getText()
    txt.insertTextContent(txt.createTextCursor(), fld, False)

[Villeroy]

Thank you for figuring out by yourself. How did you get the idea to instanciate the field from the spreadsheet document? It is not obvious from the linked example.

Code: Select all

fld = doc.createInstance("com.sun.star.text.TextField.URL")

All Writer examples I found in the same forum set some text-thingie's property "HyperlinkURL".

Thank you for adding "[Solved]" to the subject line of your initial post.

[djfiander]

That's because I linked to the wrong forum posting. In Writer, you do set the HyperLinkURL property on the text, but in Calc, it's a textfield. The correct forum posting is here.

This shows that the text field is created by calling doc.createInstance, and then attached to the.

- David