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-unsubscr...@poi.apache.org For additional commands, e-mail: commits-h...@poi.apache.org