Bug 61520 - Recommend using absolute references with name.setRefersToFormula() instead of 'A1:B1' references
Summary: Recommend using absolute references with name.setRefersToFormula() instead of...
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.16-FINAL
Hardware: PC All
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2017-09-14 12:40 UTC by Matthias
Modified: 2017-09-18 19:41 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Matthias 2017-09-14 12:40:57 UTC

this is not an Apache POI issue per se, but an Excel quirk Apache POI users should be made aware of. 

If you create a Excel name and use the the method setRefersToFormula with a reference like 'A1:B1' all works fine until you open the Excel name manager. The name manager treats all relative cell references relative to the currently selected cell. This means A1:B1 might suddenly become B1:C1 without the user ever noticing it. 

This is also described here: https://superuser.com/questions/800694/named-ranges-changing-randomly-in-excel-2010

 String sname = "NamedRangesTestSheet", cname = "TestName", cvalue = "TestVal";
 XSSFSheet sheet = wb.createSheet( sname );
 sheet.createRow( 0 ).createCell( (short) 0 ).setCellValue( cvalue );
 Name namedCel3 = wb.createName();
 namedCel3.setNameName( cname + "3" );
 String reference3 = sname + "!A1:C5"; // area reference
 namedCel3.setRefersToFormula( reference3 );

If you use references like $A$1:$A$1 all works fine.

Please update the Busy Developer's Guide and the JavaDoc of the setRefersToFormula method to prefer absolute references.
Comment 1 Dominik Stadler 2017-09-18 18:55:52 UTC
Thanks for the hint, adjusted in r1808760 and r1808763 to inform users about this.
Comment 2 Matthias 2017-09-18 19:41:44 UTC
@Dominik: Thanks for the quick response!