You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Nick Burch <ni...@torchbox.com> on 2008/02/01 19:23:20 UTC
Fun with HSSF and Hyperlinks
Hi All
I had a stab at doing hyperlink support for hssf (bug #37923). After
discovering a rather old, stub implementation of the record, handily under
an apache licence, I was able to get most of the Hyperlink record
supported. (A few unknowns, and one bit of black magic, but all files
containing the record in our test suite still open fine...). Oh, and I've
done record tests for it all too, which seem to cover most things.
On trying to add usermodel support for it, I hit a bit of a snag. The
records weren't ending up in Workbook.records, as they came after the
EOFRecord, so weren't being picked up by the interesting records sweep in
Workbook.createWorkbook
As a stop-gap, I've got Workbook.createWorkbook to check the records after
EOFRecord, and pop the Hyperlink records into another array. However, this
doesn't let them tie in with cells, as they're in the wrong place, and I'm
not even sure how to tell which sheet a Hyperlink belongs to (row+column
are easy though)
I've also done some unit tests in usermodel.HSSFCell, which are disabled
for now as the cells don't get created, as the Hyperlink records aren't
picked up by the sheet's record sweep. So, if you fancy trying to finish
the support off, they should get you most of the way there for a test,
then you just need to deal with getting the records into where they need
to be...
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re[2]: Fun with HSSF and Hyperlinks
Posted by Nick Burch <ni...@torchbox.com>.
On Thu, 7 Feb 2008, Yegor Kozlov wrote:
> HLINK turned out to be a very unlogical beast. In some cases strings are
> stored as UTF-16LE, in some they are plain, not unicode bytes.
>From the look of your patch, it's even more complicated than I'd found it
to be, so well done! :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re[2]: Fun with HSSF and Hyperlinks
Posted by Yegor Kozlov <ye...@dinom.ru>.
Finally I implemented full support for Excel hyperlinks.
All types of links are supported: file, url, email and internal.
Created XLS with links can be opened in Excel and OpenOffice and both properly understand it. So, I hope I covered all the cases.
I rewritten most of HyperlinkRecord and added pretty verbose tests for both record and usermodel levels.
Everything seems to be working fine.
HLINK turned out to be a very unlogical beast. In some cases strings are
stored as UTF-16LE, in some they are plain, not unicode bytes.
Yegor
> On Sat, 2 Feb 2008, Yegor Kozlov wrote:
>> By the way, where did you find the implementation of HyperlinkRecord?
>> Which Bug #?
> Actually, google! I searched for some documentation on the record format,
> and found an apache licenced stub implementation
>> 1. HyperlinkRecord construction failed on non-URL links, in particular,
>> on links to a place in worksheet. I added a check to prevent reading
>> past EOF.
> Excellent, thanks. I only tested it with the kinds of hyperlink I might
> ever use
>> 2. HyperlinkRecord.fillFields is incomplete. It works only with URL type
>> of links. I didn't decode it yet but fillFields should behave something
>> like this:
>>
>> Any thoughts how to decode it?
> Create a couple of each type, and stare at the bytes until they make
> sense? Otherwise, wait another few weeks, and hope it's covered by the
> binary file format docs Microsoft are due to release then
>> 3. Hyperlink is a special info attached to a cell. Very much like cell
>> comment. To find a hyperlink you need to traverse through the sheet
>> records and test row and column of found HyperlinkRecords.
> Hmm, how does that work if I have hyperlinks on two different sheets? Say,
> one in A2 on sheets 1 and 2, how do we tell those apart?
>> I added HSSFHyperlink object and getHyperlink/setHyperlink to HSSFCell.
>> For now only getHyperlink() works.
>>
>> setHyperlink is not yet implemented. First, we need to finish with
>> HyperlinkRecord and figure out how to fill data for different types of
>> links. Once this is implemented, assigning a hyperlink to a cell would
>> be just appending a HyperlinkRecord to sheet records.
> Sounds good. I'd suggest we wait a few weeks and see what docs we can get
> then, unless you fancy staring at some byte arrays before then to make
> sense of it all!
> For now, I've added another test excel file, HyperlinksOnManySheets.xls.
> This has web links, emails, and internal links, over several different
> sheets. Ought to be a good starting point for testing further
> functionality
> Nick
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re[2]: Fun with HSSF and Hyperlinks
Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 4 Feb 2008, Yegor Kozlov wrote:
>> Hmm, how does that work if I have hyperlinks on two different sheets?
>> Say, one in A2 on sheets 1 and 2, how do we tell those apart?
>
> Note, I'm iterating through Sheet.records. So, it should work across
> sheets. I'm going to test my usermodel code against
> HyperlinksOnManySheets.xls later today.
Ah, didn't look on those. I had some issues with the records not appearing
in the workbook's record list, but didn't think to check the sheet's
records
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re[2]: Fun with HSSF and Hyperlinks
Posted by Yegor Kozlov <ye...@dinom.ru>.
> Create a couple of each type, and stare at the bytes until they make
> sense? Otherwise, wait another few weeks, and hope it's covered by the
> binary file format docs Microsoft are due to release then
I keep hacking it :) If I don't have success then let's wait, although I wouldn't much count on Microsoft releasing
the HLINK spec in a few weeks.
>> 3. Hyperlink is a special info attached to a cell. Very much like cell
>> comment. To find a hyperlink you need to traverse through the sheet
>> records and test row and column of found HyperlinkRecords.
> Hmm, how does that work if I have hyperlinks on two different sheets? Say,
> one in A2 on sheets 1 and 2, how do we tell those apart?
Note, I'm iterating through Sheet.records. So, it should work across sheets.
I'm going to test my usermodel code against
HyperlinksOnManySheets.xls later today.
Yegor
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Fun with HSSF and Hyperlinks
Posted by Nick Burch <ni...@torchbox.com>.
On Sat, 2 Feb 2008, Yegor Kozlov wrote:
> By the way, where did you find the implementation of HyperlinkRecord?
> Which Bug #?
Actually, google! I searched for some documentation on the record format,
and found an apache licenced stub implementation
> 1. HyperlinkRecord construction failed on non-URL links, in particular,
> on links to a place in worksheet. I added a check to prevent reading
> past EOF.
Excellent, thanks. I only tested it with the kinds of hyperlink I might
ever use
> 2. HyperlinkRecord.fillFields is incomplete. It works only with URL type
> of links. I didn't decode it yet but fillFields should behave something
> like this:
>
> Any thoughts how to decode it?
Create a couple of each type, and stare at the bytes until they make
sense? Otherwise, wait another few weeks, and hope it's covered by the
binary file format docs Microsoft are due to release then
> 3. Hyperlink is a special info attached to a cell. Very much like cell
> comment. To find a hyperlink you need to traverse through the sheet
> records and test row and column of found HyperlinkRecords.
Hmm, how does that work if I have hyperlinks on two different sheets? Say,
one in A2 on sheets 1 and 2, how do we tell those apart?
> I added HSSFHyperlink object and getHyperlink/setHyperlink to HSSFCell.
> For now only getHyperlink() works.
>
> setHyperlink is not yet implemented. First, we need to finish with
> HyperlinkRecord and figure out how to fill data for different types of
> links. Once this is implemented, assigning a hyperlink to a cell would
> be just appending a HyperlinkRecord to sheet records.
Sounds good. I'd suggest we wait a few weeks and see what docs we can get
then, unless you fancy staring at some byte arrays before then to make
sense of it all!
For now, I've added another test excel file, HyperlinksOnManySheets.xls.
This has web links, emails, and internal links, over several different
sheets. Ought to be a good starting point for testing further
functionality
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Fun with HSSF and Hyperlinks
Posted by Yegor Kozlov <ye...@dinom.ru>.
By the way, where did you find the implementation of HyperlinkRecord? Which Bug #?
1. HyperlinkRecord construction failed on non-URL links, in particular, on links to a place in worksheet.
I added a check to prevent reading past EOF.
2. HyperlinkRecord.fillFields is incomplete. It works only with URL type of links.
I didn't decode it yet but fillFields should behave something like this:
if(type == URL){
//read URL properties
} else if (type == email){
//read email properties
} else if (type == document){
//read coordinates of a cell in this document
} else {
//unknown ...
}
In each case the rules are different.
The same applies to serialize().
Any thoughts how to decode it?
3. Hyperlink is a special info attached to a cell. Very much like cell comment. To find a hyperlink you need to traverse
through the sheet records and test row and column of found HyperlinkRecords.
I added HSSFHyperlink object and getHyperlink/setHyperlink to HSSFCell. For now only getHyperlink() works.
setHyperlink is not yet implemented. First, we need to finish with HyperlinkRecord and figure out how to fill data for different types of links.
Once this is implemented, assigning a hyperlink to a cell would be just appending a HyperlinkRecord to sheet records.
Regards,
Yegor
> Hi All
> I had a stab at doing hyperlink support for hssf (bug #37923). After
> discovering a rather old, stub implementation of the record, handily under
> an apache licence, I was able to get most of the Hyperlink record
> supported. (A few unknowns, and one bit of black magic, but all files
> containing the record in our test suite still open fine...). Oh, and I've
> done record tests for it all too, which seem to cover most things.
> On trying to add usermodel support for it, I hit a bit of a snag. The
> records weren't ending up in Workbook.records, as they came after the
> EOFRecord, so weren't being picked up by the interesting records sweep in
> Workbook.createWorkbook
> As a stop-gap, I've got Workbook.createWorkbook to check the records after
> EOFRecord, and pop the Hyperlink records into another array. However, this
> doesn't let them tie in with cells, as they're in the wrong place, and I'm
> not even sure how to tell which sheet a Hyperlink belongs to (row+column
> are easy though)
> I've also done some unit tests in usermodel.HSSFCell, which are disabled
> for now as the cells don't get created, as the Hyperlink records aren't
> picked up by the sheet's record sweep. So, if you fancy trying to finish
> the support off, they should get you most of the way there for a test,
> then you just need to deal with getting the records into where they need
> to be...
> Nick
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org