https://bz.apache.org/bugzilla/show_bug.cgi?id=61520
Bug ID: 61520
Summary: Recommend using absolute references with
name.setRefersToFormula() instead of 'A1:B1'
references
Product: POI
Version: 3.16-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
Hi,
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
MWE:
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
