Question about hyperlinks in OOcalc

rebelxt

I need a routine to find and validate/change the hyperlinks in a Calc document. I have OpenOffice.org 2.1 running in WinXP. I found the following code, posted by pitonyak, at http://www.oooforum.org/forum/viewtopic.phtml?t=40031&highlight=hyperlink

Villeroy · Posted: Tue Feb 27, 2007 4:24 pm Post subject:

Just a hint: In Calc you can handle hyperlinks much easier:
A1: http://www.oooforum.org/
B1: Forum
=HYPERLINK(A1;B1)&T(STYLE("hyperlink"))
where "hyperlink" is a cell style with blue, underlined font.
You may even use:
C1 =IF(left(A1;5)="http:";"httpLinkstyle";IF(left(A1;7)="mailto:";"mailLinkStyle";"errStyle"))
with
=HYPERLINK(A1;B1)&T(STYLE(C1))

OK, you have a spreadsheet with textfields in column A.
http://www.oooforum.org/forum/viewtopic.phtml?t=32909
provides a set of cell-functions. One of them is CELL_URL(sheet;row;column;[number])
If column A of this sheet has one hyperlink per cell:
B1 =CELL_URL(SHEET();ROW();1) -> http://www.oooforum.org
C1 =HYPERLINK(B1;A1) -> unformatted cell, showing the text of A1 with a hyperlink, pointing to http://www.oooforum.org
If some of your cells have 2 hyperlinks you can use the optional argument
B1 =CELL_URL(SHEET();ROW();1) -> 1st hyperlink in A1
C1 =CELL_URL(SHEET();ROW();1;2) -> 2nd hyperlink in A1 or ""

EDIT: Forgot to answer you initial question:
You have to get the text-object of the cell.
oSels should be a single range
get a single cell by *column and* row:
oCell = oSels.getByIndex(0,i)
get the text-fields of the cell:
oFields = oCell.v.getTextfields()
for i = 0 to oFields.getCount -1
...
next
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

rebelxt · Posted: Tue Feb 27, 2007 5:34 pm Post subject:

OK, my first thought was, you didn't answer my question. Then I read the whole thing again, and realized I don't need a macro for what I want to do. So that's great. Now, I put this formula in a cell:

noranthon · Super User Joined: 07 Jul 2005 Posts: 3323

Since Villeroy has not reappeared, I will reply. You will have to create your own hyperlink cell style. A style named "hyperlink" does not exist by default (unless I'm behind the times on that score).

Cell styles - F11, right-click "Default" and select "New". See the Calc guide (~ chapter 10) on this page: http://documentation.openoffice.org/manuals/oooauthors2/index.html

Insert >Hyperlink inserts a hyperlink. You have not exactly done that. You have used the HYPERLINK function to bring about the same effect. Using the function gives you more flexibility with regard to the formatting of the cell.

You do not have to use the STYLE function. Once you have a cell style, you can just apply it to the cell, using F11, a keyboard shortcut or a macro assigned to a menu command or toolbar icon.
_________________
search forum by month

rebelxt · Posted: Wed Feb 28, 2007 6:01 am Post subject:

Thanks, noranthon, for that explanation; I'm good to go there. There is a style in OOcalc 2.1 named Excel_BuiltIn_Hyperlink.

Back to Villeroy, could you be more explicit on the macro? I can't get oSel.supportsService("com.sun.star.text.TextRange") to test TRUE.

Villeroy · Posted: Wed Feb 28, 2007 10:55 am Post subject:

The cell supports service c.s.s.sheet.SheetCell, having interface c.s.s.text.XTextFieldsSupplier, having method getTextFields(). A cell does not support other text fields than hyperlinks. So you get a collection of hyperlinks. Each hyperlink has a property "Representation" (displayed text) and "URL". My CELL_URL() extracts any URL from any text field in any cell:

rebelxt · Posted: Wed Feb 28, 2007 12:37 pm Post subject:

Now I understand. Thanks, Villeroy.

Villeroy · Posted: Wed Feb 28, 2007 1:01 pm Post subject:

Great!
Just fixed a minor issue. The function should return #VALUE if argument n <1: