Thread

Posted on Sun Oct 16 00:19:26 2005 by cilynx
How to Access Hyperlinks in Cells in Spreadsheets
I have a bunch of spreadsheets that include links to websites in some of the cells. I need access to this content.

$doc->getCellValue($cell)
Wraps the visible string in <<>> if it is a hyperlink, but gives no hint of the link content.

$doc->cellValueType($cell)
Returns 'string' even when the cell holds a hyperlink

getAttributes() didn't return any keys that looked useful either.

Is there some way that I haven't thought of to access the link content?

Thanks --
Direct Responses: 1189 | Write a response
Posted on Mon Oct 17 22:16:41 2005 by mlcohen in response to 1174
Re: How to Access Hyperlinks in Cells in Spreadsheets
When all else fails, check the xml. There's still a lot missing from oodoc, but you should be able to do most of it manually by using the xml manipulation methods. Example: I create a cell with the text "Notlinked-Link" and a link to http://www.cpanforum.com only on the word link. Make sure that you are set to "pretty-print" xml (Tools-Options-Load/Save-General-Uncheck "Size optimization for XML format (no pretty printing)"). Save it, then at the prompt unjar the oodoc (jar -xvf file.sxc). Take a look at content.xml:
<table:table-cell> <text:p>Notlinked-<text:a xlink:href="http://www.cpanforum.com">Link</text:a></text:p> </table:table-cell>

So you can use getAttribute on the text:a element. Example:
my @linkList = $fileHandle->getElementList('//text:a'); foreach my $element (@linkList) { my $nextUrl = $fileHandle->getAttribute($element, 'xlink:href'); print "The next link is $nextUrl\n"; }
That should go through and print all the URLs in the table. As far as how to get the link in a specific cell? That could be tougher. One think you can do, if you're creating the table also, is you can add a Name to each link. That will show up as a separate attribute of the text:a element. You could even do it by cell #, I suppose. Then, get each text:a element and read their names. If the name matches what you want, you've got the correct link.

Hope that helped,
Matt
Direct Responses: 1215 | Write a response
Posted on Sat Oct 22 21:08:28 2005 by jmgdoc in response to 1189
Re: How to Access Hyperlinks in Cells in Spreadsheets

For the future, there are a few methods for hyperlinks in 2.012 (uploaded today).

When available, look at setHyperlink(), removeHyperLink(), selectHyperlinkElement() in the OpenOffice::OODoc::Text man page.

HTH
JMG
Write a response