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 function. 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.util.EmptyStackException java.lang.Object java.util.Stack.peek() Stack.java:82 java.lang.String org.apache.poi.hssf.record.NameRecord.getAreaReference(org.apache.poi.hssf.model.Workbook) NameRecord.java:705 java.lang.String org.apache.poi.hssf.usermodel.HSSFName.getReference() HSSFName.java:126 TO REPRODUCE THIS BUG : - 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)
Created attachment 10908 [details] The excel file to reproduce the problem
Created attachment 10909 [details] Java test case to reproduce the problem
Created attachment 12353 [details] Here's a patch to version 1.15 of NameRecord.java - in diff unified format
Avik, 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. Jason
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?