poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Blair Ireland" <bl...@cprc.com>
Subject External Link References
Date Sun, 18 Jul 2004 20:48:16 GMT

I recently decided to use POI for an in-house application @ work to
manage an "excel database" (mainly because I do not trust macro's at
all), where I have to parse different large excel files (and their
formulas) to generate the database.

It was when I started working with these large files that I noticed POI
does not properly parse formulas with external link references (that is,
if the formula is found in 1.xls, it cannot parse a formula properly
like ='[2.xls]Sheet1'!A1). I was led to believe by reading the
documentation that POI handles this fine. But the output of the formula,
using my previous example, would be along the lines of Sheet1!A1,
disregarding the external file name.

Upon reading over the excel file specifications from OpenOffice, I
noticed that the SUPBOOK record actually takes 3 different forms.

1. External References (# of sheet names, encoded URL w/o sheet name,
list of sheet names)
2. Internal References (# of sheets, and 0x01 0x04)
3. Add-In Functions (0x1 and then 0x01 0x04)

The current implementation of SUPBOOK appears to only take into account
case 2, internal references.

As such, the implemented Workbook methods, including
findSheetNameFromExternSheet, etc., only really look at the names of the
internal sheets of the current workbook. This is why Sheet1 was returned
in my formula example above, as they are both at the same index, just in
different workbooks.

Is the HSSF development team aware of this?

I would imagine it wouldn't be too difficult to correct, assuming the
SUPBOOK record is modified to take into account the other "versions" of
SUPBOOK that could be encountered.


-- Blair Ireland

To unsubscribe, e-mail: poi-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-dev-help@jakarta.apache.org

View raw message