From d3dd1fbbcd814db8d0b8b1534796f795730b5c53 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Fri, 21 Nov 2008 09:22:07 +0000 Subject: [PATCH] renamed Name.setFormula to more descriptive setRefersToFormula, also misc improvements in the site docs git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@719547 13f79535-47bb-0310-9956-ffa450edef68 --- .../content/xdocs/spreadsheet/how-to.xml | 1 + .../content/xdocs/spreadsheet/quick-guide.xml | 25 +++-- .../poi/ss/examples/LoanCalculator.java | 18 ++-- .../apache/poi/hssf/usermodel/HSSFName.java | 77 +++++++++++---- .../org/apache/poi/ss/usermodel/Name.java | 99 +++++++++++++++---- .../org/apache/poi/ss/usermodel/Sheet.java | 2 +- .../usermodel/XSSFEvaluationWorkbook.java | 2 +- .../apache/poi/xssf/usermodel/XSSFName.java | 25 ++--- .../poi/xssf/usermodel/XSSFWorkbook.java | 6 +- .../poi/xwpf/usermodel/XWPFParagraph.java | 4 +- .../poi/xssf/usermodel/TestXSSFBugs.java | 8 +- .../poi/xssf/usermodel/TestXSSFName.java | 13 ++- .../poi/xssf/usermodel/TestXSSFWorkbook.java | 22 ++--- .../poi/hssf/model/TestFormulaParser.java | 2 +- .../poi/hssf/record/TestNameRecord.java | 8 +- 15 files changed, 202 insertions(+), 110 deletions(-) diff --git a/src/documentation/content/xdocs/spreadsheet/how-to.xml b/src/documentation/content/xdocs/spreadsheet/how-to.xml index 8451813f0f..d21be707e2 100644 --- a/src/documentation/content/xdocs/spreadsheet/how-to.xml +++ b/src/documentation/content/xdocs/spreadsheet/how-to.xml @@ -182,6 +182,7 @@ wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // create a sheet with 30 rows (0-29) +int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row diff --git a/src/documentation/content/xdocs/spreadsheet/quick-guide.xml b/src/documentation/content/xdocs/spreadsheet/quick-guide.xml index 2688bc9f23..e7d2ae798b 100644 --- a/src/documentation/content/xdocs/spreadsheet/quick-guide.xml +++ b/src/documentation/content/xdocs/spreadsheet/quick-guide.xml @@ -1243,19 +1243,24 @@ Examples: Name namedCell = wb.createName(); namedCell.setNameName(cname); String reference = sname+"!A1:A1"; // area reference - namedCell.setReference(reference); + namedCell.setRefersToFormula(reference); // 2. create named range for a single cell using cellreference - Name namedCell = wb.createName(); - namedCell.setNameName(cname); + Name namedCel2 = wb.createName(); + namedCel2.setNameName(cname); String reference = sname+"!A1"; // cell reference - namedCell.setReference(reference); + namedCel2.setRefersToFormula(reference); // 3. create named range for an area using AreaReference - Name namedCell = wb.createName(); - namedCell.setNameName(cname); + Name namedCel3 = wb.createName(); + namedCel3.setNameName(cname); String reference = sname+"!A1:C5"; // area reference - namedCell.setReference(reference); + namedCel3.setRefersToFormula(reference); + + // 4. create named formula + Name namedCel4 = wb.createName(); + namedCel4.setNameName("my_sum"); + namedCel4.setRefersToFormula("SUM(sname+!$I$2:$I$6)");

Reading from Named Range / Named Cell @@ -1270,7 +1275,7 @@ Examples: Name aNamedCell = wb.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents - AreaReference aref = new AreaReference(aNamedCell.getReference()); + AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula()); CellReference[] crefs = aref.getAllReferencedCells(); for (int i=0; i<crefs.length; i++) { Sheet s = wb.getSheet(crefs[i].getSheetName()); @@ -1295,7 +1300,7 @@ Examples: // Retrieve the cell at the named range and test its contents // Will get back one AreaReference for C10, and // another for D12 to D14 - AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference()); + AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula()); for (int i=0; i<arefs.length; i++) { // Only get the corners of the Area // (use arefs[i].getAllReferencedCells() to get all cells) @@ -1320,7 +1325,7 @@ Examples: if(name.isDeleted()){ //named range points to a deleted cell. } else { - AreaReference ref = new AreaReference(name.getReference()); + AreaReference ref = new AreaReference(name.getRefersToFormula()); } diff --git a/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java b/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java index 96bb5f1652..3323c9e2d6 100755 --- a/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java +++ b/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java @@ -267,38 +267,38 @@ public class LoanCalculator { name = wb.createName(); name.setNameName("Interest_Rate"); - name.setReference("'Loan Calculator'!$E$5"); + name.setRefersToFormula("'Loan Calculator'!$E$5"); name = wb.createName(); name.setNameName("Loan_Amount"); - name.setReference("'Loan Calculator'!$E$4"); + name.setRefersToFormula("'Loan Calculator'!$E$4"); name = wb.createName(); name.setNameName("Loan_Start"); - name.setReference("'Loan Calculator'!$E$7"); + name.setRefersToFormula("'Loan Calculator'!$E$7"); name = wb.createName(); name.setNameName("Loan_Years"); - name.setReference("'Loan Calculator'!$E$6"); + name.setRefersToFormula("'Loan Calculator'!$E$6"); name = wb.createName(); name.setNameName("Number_of_Payments"); - name.setReference("'Loan Calculator'!$E$10"); + name.setRefersToFormula("'Loan Calculator'!$E$10"); name = wb.createName(); name.setNameName("Monthly_Payment"); - name.setReference("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)"); + name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)"); name = wb.createName(); name.setNameName("Total_Cost"); - name.setReference("'Loan Calculator'!$E$12"); + name.setRefersToFormula("'Loan Calculator'!$E$12"); name = wb.createName(); name.setNameName("Total_Interest"); - name.setReference("'Loan Calculator'!$E$11"); + name.setRefersToFormula("'Loan Calculator'!$E$11"); name = wb.createName(); name.setNameName("Values_Entered"); - name.setReference("IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)"); + name.setRefersToFormula("IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)"); } } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java index bcd1977200..cc2f444717 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java @@ -62,8 +62,28 @@ public final class HSSFName implements Name { } /** - * sets the name of the named range + * Sets the name of the named range + * + *

The following is a list of syntax rules that you need to be aware of when you create and edit names.

+ * * @param nameName named range name to set + * @throws IllegalArgumentException if the name is invalid or the workbook already contains this name (case-insensitive) */ public void setNameName(String nameName){ _definedNameRec.setNameText(nameName); @@ -81,29 +101,49 @@ public final class HSSFName implements Name { } /** - * @deprecated (Nov 2008) Misleading name. Use {@link #getFormula()} instead. + * Returns the formula that the name is defined to refer to. + * + * @deprecated (Nov 2008) Misleading name. Use {@link #getRefersToFormula()} instead. */ public String getReference() { - return getFormula(); + return getRefersToFormula(); } /** - * @deprecated (Nov 2008) Misleading name. Use {@link #setFormula(String)} instead. + * Sets the formula that the name is defined to refer to. + * + * @deprecated (Nov 2008) Misleading name. Use {@link #setRefersToFormula(String)} instead. */ public void setReference(String ref){ - setFormula(ref); + setRefersToFormula(ref); } - public void setFormula(String formulaText) { + /** + * Sets the formula that the name is defined to refer to. The following are representative examples: + * + * + * + * @param formulaText the reference for this name + * @throws IllegalArgumentException if the specified reference is unparsable + */ + public void setRefersToFormula(String formulaText) { Ptg[] ptgs = HSSFFormulaParser.parse(formulaText, _book); _definedNameRec.setNameDefinition(ptgs); } /** - * Note - this method only applies to named ranges - * @return the formula text defining this name + * Returns the formula that the name is defined to refer to. The following are representative examples: + * + * @return the reference for this name + * @see #setRefersToFormula(String) */ - public String getFormula() { + public String getRefersToFormula() { if (_definedNameRec.isFunctionName()) { throw new IllegalStateException("Only applicable to named ranges"); } @@ -116,20 +156,19 @@ public final class HSSFName implements Name { * @return true if the name refers to a deleted cell, false otherwise */ public boolean isDeleted(){ - String formulaText = getReference(); - if (formulaText.startsWith("#REF!")) { - // sheet deleted - return true; - } - if (formulaText.endsWith("#REF!")) { - // cell range deleted - return true; - } - return false; + String formulaText = getRefersToFormula(); + return formulaText.indexOf("#REF!") != -1; } + + /** + * Checks if this name is a function name + * + * @return true if this name is a function name + */ public boolean isFunctionName() { return _definedNameRec.isFunctionName(); } + public String toString() { StringBuffer sb = new StringBuffer(64); sb.append(getClass().getName()).append(" ["); diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java index 6ca59a651f..b896b30ee7 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Name.java @@ -17,44 +17,105 @@ package org.apache.poi.ss.usermodel; +/** + * Represents a defined name for a range of cells. + *

+ * A name is a meaningful shorthand that makes it easier to understand the purpose of a + * cell reference, constant or a formula. + *

+ * Examples: + * 

+ *  Sheet sheet = workbook.createSheet("Loan Calculator");
+ *  Name name;
+ *
+ *  name = workbook.createName();
+ *  name.setNameName("Interest_Rate");
+ *  name.setRefersToFormula("'Loan Calculator'!$E$5");
+ *
+ *  name = wb.createName();
+ *  name.setNameName("Loan_Amount");
+ *  name.setRefersToFormula("'Loan Calculator'!$E$4");
+ *
+ *  name = wb.createName();
+ *  name.setNameName("Number_of_Payments");
+ *  name.setRefersToFormula("'Loan Calculator'!$E$10");
+ *
+ *  name = wb.createName();
+ *  name.setNameName("Monthly_Payment");
+ *  name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");
+ *
+ *  name = wb.createName();
+ *  name.setNameName("Values_Entered");
+ *  name.setRefersToFormula("IF(Loan_Amount*Interest_Rate>0,1,0)");
+ *
+ *
+ */ public interface Name { - /** Get the sheets name which this named range is referenced to + /** + * Get the sheets name which this named range is referenced to + * * @return sheet name, which this named range refered to */ String getSheetName(); /** - * gets the name of the named range + * Gets the name of the named range + * * @return named range name */ String getNameName(); /** - * sets the name of the named range - * @param nameName named range name to set + * Sets the name of the named range + * + *

The following is a list of syntax rules that you need to be aware of when you create and edit names.

+ *
    + *
  • Valid characters + * The first character of a name must be a letter, an underscore character (_), or a backslash (\). + * Remaining characters in the name can be letters, numbers, periods, and underscore characters. + *
    • + *
  • Cell references disallowed + * Names cannot be the same as a cell reference, such as Z$100 or R1C1.
    • + *
  • Spaces are not valid + * Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter. + *
    • + *
  • Name length + * A name can contain up to 255 characters. + *
    • + *
  • Case sensitivity + * Names can contain uppercase and lowercase letters. + *
    • + *
+ * @param name named range name to set + * @throws IllegalArgumentException if the name is invalid or the workbook already contains this name (case-insensitive) */ - void setNameName(String nameName); + void setNameName(String name); /** - * @deprecated (Nov 2008) Misleading name. Use {@link #getFormula()} instead. + * Returns the formula that the name is defined to refer to. The following are representative examples: + * + * @return the reference for this name + * @see #setRefersToFormula(String) */ - String getReference(); + String getRefersToFormula(); /** - * @deprecated (Nov 2008) Misleading name. Use {@link #setFormula(String)} instead. - */ - void setReference(String ref); - - /** - * @return the formula text defining this name + * Sets the formula that the name is defined to refer to. The following are representative examples: + * + *
    + *
  • 'My Sheet'!$A$3
    • + *
  • 8.3
    • + *
  • HR!$A$1:$Z$345
    • + *
  • SUM(Sheet1!A1,Sheet2!B2)
    • + *
  • -PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)
    • + *
+ * + * @param ref the reference for this name + * @throws IllegalArgumentException if the specified reference is unparsable */ - String getFormula(); - - /** - * Sets the formula text defining this name - */ - void setFormula(String formulaText); + void setRefersToFormula(String ref); + /** * Checks if this name is a function name * diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java index e280a35028..7e7645995b 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java @@ -233,7 +233,7 @@ public interface Sheet extends Iterable { * * @param value true if the sheet displays Automatic Page Breaks. */ - void setAutobreaks(boolean b); + void setAutobreaks(boolean value); /** * Set whether to display the guts or not diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java index 35b947ae44..fdcebbcc99 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -152,7 +152,7 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E public Ptg[] getNameDefinition() { - return FormulaParser.parse(_nameRecord.getReference(), _fpBook); + return FormulaParser.parse(_nameRecord.getRefersToFormula(), _fpBook); } public String getNameText() { diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java index 2be7cffa59..1813892ba9 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java @@ -37,14 +37,14 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; * //applies to the entire workbook * XSSFName name1 = wb.createName(); * name1.setNameName("FMLA"); - * name1.setReference("Sheet1!$B$3"); + * name1.setRefersToFormula("Sheet1!$B$3"); * * //applies to Sheet1 * XSSFName name2 = wb.createName(); * name2.setNameName("SheetLevelName"); * name2.setComment("This name is scoped to Sheet1"); * name2.setLocalSheetId(0); - * name2.setReference("Sheet1!$B$3"); + * name2.setRefersToFormula("Sheet1!$B$3"); * * * @@ -153,25 +153,12 @@ public final class XSSFName implements Name { ctName.setName(name); } - /** - * @deprecated (Nov 2008) Misleading name. Use {@link #getFormula()} instead. - */ - public String getReference() { - return getFormula(); - } - - /** - * @deprecated (Nov 2008) Misleading name. Use {@link #setFormula(String)} instead. - */ - public void setReference(String ref){ - setFormula(ref); - } /** * Returns the reference of this named range, such as Sales!C20:C30. * * @return the reference of this named range */ - public String getFormula() { + public String getRefersToFormula() { return ctName.getStringValue(); } @@ -181,7 +168,7 @@ public final class XSSFName implements Name { * @param formulaText the reference to set * @throws IllegalArgumentException if the specified reference is unparsable */ - public void setFormula(String formulaText) { + public void setRefersToFormula(String formulaText) { XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(workbook); Ptg[] ptgs; try { @@ -201,7 +188,7 @@ public final class XSSFName implements Name { * @return true if the name refers to a deleted cell, false otherwise */ public boolean isDeleted(){ - String ref = getReference(); + String ref = getRefersToFormula(); return ref != null && ref.indexOf("#REF!") != -1; } @@ -278,7 +265,7 @@ public final class XSSFName implements Name { int sheetId = (int)ctName.getLocalSheetId(); return workbook.getSheetName(sheetId); } else { - String ref = getReference(); + String ref = getRefersToFormula(); AreaReference areaRef = new AreaReference(ref); return areaRef.getFirstCell().getSheetName(); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index 63f9152b89..0597f677b0 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -617,7 +617,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable