Bug 61520

Summary: Recommend using absolute references with name.setRefersToFormula() instead of 'A1:B1' references
Product: POI Reporter: Matthias <scaronthesky>
Component: XSSFAssignee: POI Developers List <dev>
Severity: enhancement    
Priority: P2    
Version: 3.16-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

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!