Bug 27852 - [PATCH] POI wrongly reports a name for a cell formula that references a name
Summary: [PATCH] POI wrongly reports a name for a cell formula that references a name
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-FINAL
Hardware: PC Windows XP
: P3 critical with 2 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2004-03-22 17:09 UTC by Brice Copy
Modified: 2005-04-28 08:32 UTC (History)
0 users

The excel file to reproduce the problem (22.50 KB, application/octet-stream)
2004-03-22 17:09 UTC, Brice Copy
Java test case to reproduce the problem (1.16 KB, text/plain)
2004-03-22 17:21 UTC, Brice Copy
Here's a patch to version 1.15 of NameRecord.java - in diff unified format (1.75 KB, patch)
2004-08-06 15:55 UTC, Brice Copy
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Brice Copy 2004-03-22 17:09:18 UTC
POI reports invalid HSSFName that are in fact VB functions. This somehow causes
an EmptyStackException. There does not seem to be any workaround to this problem.

VB functions can accept a Name as their parameter (=a VB object of type Range).
You can reference such a VB function in a formula, like any other built-in excel

When POI opens a workbook containing cells that use a formula referencing a
name, it reports a name for each formula referencing a name. Of course, the
reported HSSFName object is invalid, as its field_13_name_definition member is
an empty stack, while the field_13_raw_name_definition is null.

From the HSSF usermodel package, there is no way to tell the difference between
a valid HSSFName (referencing a name defined in the excel workbook) and an
invalid one (referencing a function that uses a name as its argument).

When trying to access an invalid HSSFName, the following exception occurs :


	java.lang.Object java.util.Stack.peek()




	java.lang.String org.apache.poi.hssf.usermodel.HSSFName.getReference()



- Save the provided test case (an excel file defining two simple functions and
two names)
- Open the test case with the attached java test case.
- If you debug, you will notice that POI reports four HSSFName objects (while
only two are defined in the excel file) 
- The test case should fail with an EmptyStackException - because POI does not
check the validity of the HSSFName object before getting its reference)
Comment 1 Brice Copy 2004-03-22 17:09:56 UTC
Created attachment 10908 [details]
The excel file to reproduce the problem
Comment 2 Brice Copy 2004-03-22 17:21:04 UTC
Created attachment 10909 [details]
Java test case to reproduce the problem
Comment 3 Brice Copy 2004-08-06 15:55:24 UTC
Created attachment 12353 [details]
Here's a patch to version 1.15 of NameRecord.java - in diff unified format
Comment 4 Jason Height 2004-08-08 22:07:47 UTC

Since you're arround formulas, Care to have a look at this patch by Brice? There
is also a testcase which should be translated into the unit tests.

Comment 5 Avik Sengupta 2005-04-28 16:32:39 UTC
Applied, thanks. But is this enuf? This patch applies some checks to prevent the
exception, getReference now returns "#REF!" for these name records. Should there
be any other api to access some of this info, or does POI already provide
correct info?