Bug 37923

Summary: Trying to read an excel worksheet with hyperlink references
Product: POI Reporter: Darren Wrigley <darren.wrigley>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: enhancement    
Priority: P3    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Sample Excel Spreadsheet with a Hyperink in it

Description Darren Wrigley 2005-12-15 18:33:01 UTC
I have a spreadsheet that I am trying to read using POI (either using the Event
Model or User API).

When I get to a cell that has a hyperlink - I can only read the display value of
the cell, not the hyperlink reference itself (url).

It seems that you an only create hyperlink references with the API, not read
them back?
Comment 1 Jeff Merino-Ott 2008-01-28 14:47:18 UTC
I too am having the same problem.  I have tried a number of things, and even
found out that when I try to perform a cell.getCellFormula(), it gives me an
error no matter what I do.  I'm trying to process an excel file and load the
data into a database and would really love to be able to get the URL information.

Im using 3.0.1 Final.
Comment 2 Nick Burch 2008-01-29 02:10:55 UTC
Can you upload a simple file with a hyperlink in, so there's something for us to
test any new hyperlink support against?

Format should ideally be like:
 Text    Some other text
 URL (as text)
 Hyperlink (Text -> URL)
Comment 3 Jeff Merino-Ott 2008-01-30 11:14:52 UTC
Created attachment 21453 [details]
Sample Excel Spreadsheet with a Hyperink in it

Here is a spreadsheet (not my original source, as I'm not able to upload that).
 But one containing a URL column that is populated only once.  This is a
similar format to what I'm dealing with.  In Eclipse, I can't seem to locate
the URL anywhere in the debugger, so I can't determine how to get the URL.

I tried a String foo = cell.getFormula(), which is supposed to return a String
object, but always gives me a class cast exception
(org.apache.poi.hssf.record.LabelSSTRecord).

Thanks.
 jeff
Comment 4 Yegor Kozlov 2008-01-30 11:49:13 UTC
Looks like support for the Hyperlink (0x1B8) record is missing. The URL info is
there.

Yegor 
 
Comment 5 Nick Burch 2008-02-01 10:24:31 UTC
I've added support for the Hyperlink record, and tests for that

Hit a snag with getting it passed on to the usermodel code, as the records seem
to be in completely the wrong part of the record stream. See my post to dev@ for
details
Comment 6 Yegor Kozlov 2008-02-07 01:01:24 UTC
Support for Excel hyperlinks is implemented.

How to read:

    HSSFSheet sheet = workbook.getSheetAt(0);

    HSSFCell cell = sheet.getRow(0).getCell((short)0);
    HSSFHyperlink link = cell.getHyperlink();
    if(link != null){
        System.out.println(link.getAddress());
    }

See also the quick guide:
http://poi.apache.org/hssf/quick-guide.html

Yegor
Comment 7 Jeff Merino-Ott 2008-02-27 08:57:40 UTC
I don't have HSSFHyperlink.  and HSSFCell doesn't have getHyperlink().

I'm using 3.0.2 final.  Is there a newer jar for me to try?

Thanks!
  jeff


(In reply to comment #6)
> Support for Excel hyperlinks is implemented.
> 
> How to read:
> 
>     HSSFSheet sheet = workbook.getSheetAt(0);
> 
>     HSSFCell cell = sheet.getRow(0).getCell((short)0);
>     HSSFHyperlink link = cell.getHyperlink();
>     if(link != null){
>         System.out.println(link.getAddress());
>     }
> 
> See also the quick guide:
> http://poi.apache.org/hssf/quick-guide.html
> 
> Yegor

Comment 8 Nick Burch 2008-03-03 07:50:25 UTC
See http://poi.apache.org/changes.html

Support for hyperlinks went in after 3.0.2. You'll either need a nightly build, or to do a svn checkout
Comment 9 Jeff Merino-Ott 2008-03-03 08:24:30 UTC
(In reply to comment #8)
> See http://poi.apache.org/changes.html
> 
> Support for hyperlinks went in after 3.0.2. You'll either need a nightly build,
> or to do a svn checkout
> 

Ahhhh. That would be my problem then :)  Thanks!  I'll grab a nightly.