Bug 48462 - Excel Name Box could not display the range name corresponding to the singlecell after create/change the range name using POI
Summary: Excel Name Box could not display the range name corresponding to the singlece...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.2-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-12-30 02:15 UTC by Lion Liang
Modified: 2011-06-25 12:41 UTC (History)
0 users



Attachments
a jpeg describes the usage of namebox for the relationship between single cell and a range name (4.87 KB, image/jpeg)
2009-12-30 02:15 UTC, Lion Liang
Details
source excel file (13.50 KB, application/excel)
2009-12-30 02:17 UTC, Lion Liang
Details
POI output excel file (14.00 KB, application/excel)
2009-12-30 02:17 UTC, Lion Liang
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Lion Liang 2009-12-30 02:15:44 UTC
Created attachment 24773 [details]
a jpeg describes the usage of namebox for the relationship between single cell and a range name

Excel Name Box could not display the range name corresponding to the singlecell after create/change the range name using POI.

Below is the codes:

InputStream inp = new FileInputStream("testRF.xls");
HSSFWorkbook wb = new HSSFWorkbook(inp);
HSSFName name = wb.getNameAt(wb.getNameIndex("testCell"));

CellReference reference = new CellReference("Sheet1!A2");
name.setReference(reference.formatAsString());

File file = new File("output_testRF.xls");
if(file.exists())
	file.delete();
FileOutputStream fileOut = new FileOutputStream("output_testRF.xls");
wb.write(fileOut);
fileOut.close();
Comment 1 Lion Liang 2009-12-30 02:17:23 UTC
Created attachment 24774 [details]
source excel file
Comment 2 Lion Liang 2009-12-30 02:17:52 UTC
Created attachment 24775 [details]
POI output excel file
Comment 3 Josh Micich 2009-12-31 13:27:59 UTC
I can observe the funny behaviour you describe, but I think it is actually part of normal Excel functionality.  The anomaly seems to arise from using relative coordinates in the cell reference (i.e. missing '$').  You can achieve the same affect by setting the  'Refers To' property in the 'Name Manager' dialog to the same value as you did in the java example.  When 'navigating' to a defined name by using the 'Name Box', different behaviour is observed depending on whether the coordinates are absolute or relative.

The simple fix is to use absolute coordinates in your cell reference.  For example: "Sheet1!$A$2"

BTW - you should replace uses of the method HSSFName.setReference() to HSSFName.setRefersToFormula(), as the old method will not be available in future POI versions.
Comment 4 Lion Liang 2010-01-03 22:20:19 UTC
Thanks Josh.

I just find using "Sheet1!$A$2" is working well on POI 3.5 for my requirement, but still not work for POI 3.2 Final. this might be something like a POI bug fix or enhancement after 3.2 final release. 

However I am still using POI 3.2 for my work, I can't find the corresponding bug or change list for this feature by searching in Bugzilla, could you please attach it here if you could find it? Thanks! 

BTW, I didn't have a chance to look at the way to use Retroweaver(you told me this in Bug 46547) to convert the latest POI release to run
on a 1.4 JRE yet, but I'll definitely try it later. Thanks again for your helps.

Sorry I have to put this bug to REOPENED to ensure it could be copied to you.
Comment 5 Yegor Kozlov 2011-06-25 12:41:28 UTC
The problem is fixed in POI 3.5+ and I don't see why this ticket should stay open.

Yegor