You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2017/09/18 18:53:05 UTC
svn commit: r1808760 - in /poi/site: publish/spreadsheet/quick-guide.html
src/documentation/content/xdocs/spreadsheet/quick-guide.xml
Author: centic
Date: Mon Sep 18 18:53:04 2017
New Revision: 1808760
URL: http://svn.apache.org/viewvc?rev=1808760&view=rev
Log:
Bug 61520: Add note about name-references in Excel
Modified:
poi/site/publish/spreadsheet/quick-guide.html
poi/site/src/documentation/content/xdocs/spreadsheet/quick-guide.xml
Modified: poi/site/publish/spreadsheet/quick-guide.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/quick-guide.html?rev=1808760&r1=1808759&r2=1808760&view=diff
==============================================================================
--- poi/site/publish/spreadsheet/quick-guide.html (original)
+++ poi/site/publish/spreadsheet/quick-guide.html Mon Sep 18 18:53:04 2017
@@ -1947,9 +1947,15 @@ Examples:
Named Range is a way to refer to a group of cells by a name. Named Cell is a
degenerate case of Named Range in that the 'group of cells' contains exactly one
cell. You can create as well as refer to cells in a workbook by their named range.
- When working with Named Ranges, the classes: org.apache.poi.hssf.util.CellReference and
- & org.apache.poi.hssf.util.AreaReference are used (these
- work for both XSSF and HSSF, despite the package name).
+ When working with Named Ranges, the classes <span class="codefrag">org.apache.poi.ss.util.CellReference</span>
+ and <span class="codefrag">org.apache.poi.ss.util.AreaReference</span> are used.
+ </p>
+
+<p>
+ Note: Using relative values like 'A1:B1' can lead to unexpected moving of
+ the cell that the name points to when working with the workbook in Microsoft Excel,
+ usually using absolute references like '$A$1:$B$1' avoids this, see also
+ <a href="https://superuser.com/a/1031047/126954">this discussion</a>.
</p>
<p>
@@ -1965,26 +1971,26 @@ Examples:
// 1. create named range for a single cell using areareference
Name namedCell = wb.createName();
- namedCell.setNameName(cname);
- String reference = sname+"!A1:A1"; // area reference
+ namedCell.setNameName(cname + "1");
+ String reference = sname+"!$A$1:$A$1"; // area reference
namedCell.setRefersToFormula(reference);
// 2. create named range for a single cell using cellreference
Name namedCel2 = wb.createName();
- namedCel2.setNameName(cname);
- String reference = sname+"!A1"; // cell reference
+ namedCel2.setNameName(cname + "2");
+ reference = sname+"!$A$1"; // cell reference
namedCel2.setRefersToFormula(reference);
// 3. create named range for an area using AreaReference
Name namedCel3 = wb.createName();
- namedCel3.setNameName(cname);
- String reference = sname+"!A1:C5"; // area reference
+ namedCel3.setNameName(cname + "3");
+ reference = sname+"!$A$1:$C$5"; // area reference
namedCel3.setRefersToFormula(reference);
// 4. create named formula
Name namedCel4 = wb.createName();
namedCel4.setNameName("my_sum");
- namedCel4.setRefersToFormula("SUM(sname+!$I$2:$I$6)");
+ namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");
</pre>
<p>
Modified: poi/site/src/documentation/content/xdocs/spreadsheet/quick-guide.xml
URL: http://svn.apache.org/viewvc/poi/site/src/documentation/content/xdocs/spreadsheet/quick-guide.xml?rev=1808760&r1=1808759&r2=1808760&view=diff
==============================================================================
--- poi/site/src/documentation/content/xdocs/spreadsheet/quick-guide.xml (original)
+++ poi/site/src/documentation/content/xdocs/spreadsheet/quick-guide.xml Mon Sep 18 18:53:04 2017
@@ -1312,9 +1312,14 @@ Examples:
Named Range is a way to refer to a group of cells by a name. Named Cell is a
degenerate case of Named Range in that the 'group of cells' contains exactly one
cell. You can create as well as refer to cells in a workbook by their named range.
- When working with Named Ranges, the classes: org.apache.poi.hssf.util.CellReference and
- & org.apache.poi.hssf.util.AreaReference are used (these
- work for both XSSF and HSSF, despite the package name).
+ When working with Named Ranges, the classes <code>org.apache.poi.ss.util.CellReference</code>
+ and <code>org.apache.poi.ss.util.AreaReference</code> are used.
+ </p>
+ <p>
+ Note: Using relative values like 'A1:B1' can lead to unexpected moving of
+ the cell that the name points to when working with the workbook in Microsoft Excel,
+ usually using absolute references like '$A$1:$B$1' avoids this, see also
+ <link href="https://superuser.com/a/1031047/126954">this discussion</link>.
</p>
<p>
Creating Named Range / Named Cell
@@ -1328,26 +1333,26 @@ Examples:
// 1. create named range for a single cell using areareference
Name namedCell = wb.createName();
- namedCell.setNameName(cname);
- String reference = sname+"!A1:A1"; // area reference
+ namedCell.setNameName(cname + "1");
+ String reference = sname+"!$A$1:$A$1"; // area reference
namedCell.setRefersToFormula(reference);
// 2. create named range for a single cell using cellreference
Name namedCel2 = wb.createName();
- namedCel2.setNameName(cname);
- String reference = sname+"!A1"; // cell reference
+ namedCel2.setNameName(cname + "2");
+ reference = sname+"!$A$1"; // cell reference
namedCel2.setRefersToFormula(reference);
// 3. create named range for an area using AreaReference
Name namedCel3 = wb.createName();
- namedCel3.setNameName(cname);
- String reference = sname+"!A1:C5"; // area reference
+ namedCel3.setNameName(cname + "3");
+ reference = sname+"!$A$1:$C$5"; // area reference
namedCel3.setRefersToFormula(reference);
// 4. create named formula
Name namedCel4 = wb.createName();
namedCel4.setNameName("my_sum");
- namedCel4.setRefersToFormula("SUM(sname+!$I$2:$I$6)");
+ namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");
</source>
<p>
Reading from Named Range / Named Cell
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org