Bug 46174

Summary: Unable to create references to cells in 3.2-FINAL
Product: POI Reporter: Gaurav Arora <gauravsworld>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: yakimovich_dm
Priority: P2    
Version: 3.2-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Excel generated using 3.1-FINAL
Excel generated using 3.2-FINAL

Description Gaurav Arora 2008-11-09 23:00:29 UTC
The following snippet of code works perfectly in 3.1 but does not work in 3.2-FINAL. The code below is supposed to create a named reference to a cell but doesn't do so.

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet");
sheet.createRow(0).createCell(0).setCellValue("abcd");
HSSFName name = wb.createName();
name.setNameName("HOLAHOLAHOLAH");
name.setReference("sheet!A1");
FileOutputStream fileOut = new FileOutputStream("t.xls");
wb.write(fileOut);
fileOut.close();

There is no mention in the changelog if there was a change to named references so I assume this is an unintended change and hence a bug.

P.S. 3.2-FINAL does not appear in the version selection list above so I chose 3.2-dev instead.
Comment 1 Nick Burch 2008-11-10 01:42:41 UTC
Any chance you could upload the output files from 3.1 and 3.2, so we can do a quick diff and see what's different with them?
Comment 2 Gaurav Arora 2008-11-10 06:00:34 UTC
Created attachment 22848 [details]
Excel generated using 3.1-FINAL
Comment 3 Gaurav Arora 2008-11-10 06:01:08 UTC
Created attachment 22849 [details]
Excel generated using 3.2-FINAL
Comment 4 Gaurav Arora 2008-11-10 06:02:59 UTC
(In reply to comment #1)
> Any chance you could upload the output files from 3.1 and 3.2, so we can do a
> quick diff and see what's different with them?
> 

The sheets are attached. Also, my excel version is 2003 (11.8105.8107) SP2.
Comment 5 Nick Burch 2008-11-10 09:56:40 UTC
I'm seeing the named range defined in both of your files

The only difference I can spot is that the 3.1 version uses the $A$1 form of reference, and 3.2 uses A1 (so absolute vs relative). However, both seem fine otherwise
Comment 6 Gaurav Arora 2008-11-10 21:29:44 UTC
After some more debugging, it appears that the named reference is available to me in POI and I can access it elsewhere in the sheet. But for whatever reason excel doesn't show the reference in the box on the top left corner when I click on the cell A1. Can you see this reference in excel nick? What version of excel are you using?
Comment 7 Nick Burch 2008-11-11 03:55:03 UTC
I'm using Excel 2003 (11.8231.8221) SP3, and that sees the named range just fine in both files

I think Josh might be investigating the $A$1 vs A1 thing
Comment 8 Josh Micich 2008-11-13 18:32:42 UTC
Fixed in svn r713909

Junit added.

The problem was that NameRecord had its own reference parsing logic that didn't distinguish between relative and absolute cell references.  The easiest fix was to replace this logic with the common formula parsing logic which has much greater coverage of Excel formula syntax.  This has the benefit of allowing POI to support defined names with any general formula.

I renamed(deprecated) the get/setReference methods because the name is misleading.  Now you can use get/setFormula to the same effect.

Unfortunately, the formula parser does not support the union operator yet, so there has been a slight regression in functionality.  For example, hssfName.setFormula("Sheet1!A1:B2,Sheet1!C3:D4") will fail at the moment.  I'm not sure if this functionality is of a high priority.
Comment 9 Yegor Kozlov 2009-04-18 02:12:50 UTC
*** Bug 30714 has been marked as a duplicate of this bug. ***