[Bug 61520] New: Recommend using absolute references with name.setRefersToFormula() instead of 'A1:B1' references

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61520] New: Recommend using absolute references with name.setRefersToFormula() instead of 'A1:B1' references

Bugzilla from bugzilla@apache.org
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: [hidden email]
          Reporter: [hidden email]
  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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61520] Recommend using absolute references with name.setRefersToFormula() instead of 'A1:B1' references

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61520

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|normal                      |enhancement
                 OS|                            |All

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61520] Recommend using absolute references with name.setRefersToFormula() instead of 'A1:B1' references

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61520

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |FIXED
             Status|NEW                         |RESOLVED

--- Comment #1 from Dominik Stadler <[hidden email]> ---
Thanks for the hint, adjusted in r1808760 and r1808763 to inform users about
this.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61520] Recommend using absolute references with name.setRefersToFormula() instead of 'A1:B1' references

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61520

--- Comment #2 from Matthias <[hidden email]> ---
@Dominik: Thanks for the quick response!

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]