1. Support sheet-level names2. Fixed XSSFCell to properly handle cell references with column numbers up to XFD3. when pasring formula, HSSFName.setRefersToFormula must set type of operands to Ptg.CLASS_REF, otherwise created named don't appear in the dropdown to the left of formula bar in Excel

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@723392 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 45 insertions, 30 deletions

diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
index f3f9c17e1a..bb3aafe07a 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
@@ -23,8 +23,11 @@ import java.util.Calendar;
 import java.util.Date;
 
 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.Ptg;
 import org.apache.poi.ss.usermodel.*;
 import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
 import org.apache.poi.xssf.model.StylesTable;
 import org.apache.poi.xssf.model.SharedStringsTable;
 import org.apache.poi.POIXMLException;
@@ -51,7 +54,7 @@ public final class XSSFCell implements Cell {
     /**
      * The maximum  number of columns in SpreadsheetML
      */
-    private static final int MAX_COLUMN_NUMBER  = 16384;
+    public static final int MAX_COLUMN_NUMBER  = 16384; //2^14
 
     private static final String FALSE_AS_STRING = "0";
     private static final String TRUE_AS_STRING  = "1";
@@ -330,6 +333,7 @@ public final class XSSFCell implements Cell {
      *
      * @param formula the formula to set, e.g. <code>SUM(C4:E4)</code>.
      *  If the argument is <code>null</code> then the current formula is removed.
+     * @throws IllegalArgumentException if the formula is invalid
      */
     public void setCellFormula(String formula) {
         if (formula == null && cell.isSetF()) {
@@ -337,6 +341,16 @@ public final class XSSFCell implements Cell {
             return;
         }
 
+        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(row.getSheet().getWorkbook());
+        try {
+            Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL);
+        } catch (RuntimeException e) {
+            if (e.getClass().getName().startsWith(FormulaParser.class.getName())) {
+                throw new IllegalArgumentException("Unparsable formula '" + formula + "'", e);
+            }
+            throw e;
+        }
+
         CTCellFormula f =  CTCellFormula.Factory.newInstance();
         f.setStringValue(formula);
         cell.setF(f);
@@ -580,7 +594,8 @@ public final class XSSFCell implements Cell {
     protected void setCellNum(int num) {
         checkBounds(num);
         cellNum = num;
-        cell.setR(formatPosition());
+        String ref = new CellReference(getRowIndex(), getColumnIndex()).formatAsString();
+        cell.setR(ref);
     }
 
     /**
@@ -599,22 +614,6 @@ public final class XSSFCell implements Cell {
     }
 
     /**
-     * Builds an A1 style reference from internal represenetation
-     *
-     * @return an A1 style reference to the location of this cell
-     */
-    protected String formatPosition() {
-        int col = this.getColumnIndex();
-        String result = Character.valueOf((char) (col % 26 + 'A')).toString();
-        if (col >= 26){
-            col = col / 26;
-            result = Character.valueOf((char) (col + '@')) + result;
-        }
-        result = result + String.valueOf(row.getRowNum() + 1);
-        return result;
-    }
-
-    /**
      * Set the cells type (numeric, formula or string)
      *
      * @throws IllegalArgumentException if the specified cell type is invalid
@@ -733,10 +732,10 @@ public final class XSSFCell implements Cell {
      */
     private static void checkBounds(int cellNum) {
         if (cellNum > MAX_COLUMN_NUMBER) {
-            throw new POIXMLException("You cannot have more than "+MAX_COLUMN_NUMBER+" columns " +
+            throw new IllegalArgumentException("You cannot have more than "+MAX_COLUMN_NUMBER+" columns " +
                     "in a given row because Excel can't handle it");
         } else if (cellNum < 0) {
-            throw new POIXMLException("You cannot reference columns with an index of less then 0.");
+            throw new IllegalArgumentException("You cannot reference columns with an index of less then 0.");
         }
     }
 
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 1813892ba9..d9761a0014 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
@@ -170,9 +170,8 @@ public final class XSSFName implements Name {
      */
     public void setRefersToFormula(String formulaText) {
         XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(workbook);
-        Ptg[] ptgs;
         try {
-            ptgs = FormulaParser.parse(formulaText, fpb, FormulaType.CELL); // TODO - use type NAMEDRANGE
+            Ptg[] ptgs = FormulaParser.parse(formulaText, fpb, FormulaType.NAMEDRANGE);
         } catch (RuntimeException e) {
             if (e.getClass().getName().startsWith(FormulaParser.class.getName())) {
                 throw new IllegalArgumentException("Unparsable formula '" + formulaText + "'", e);
@@ -195,11 +194,20 @@ public final class XSSFName implements Name {
     /**
      * Tell Excel that this name applies to the worksheet with the specified index instead of the entire workbook.
      *
-     * @param sheetId the sheet index this name applies to, -1 unsets this property making the name workbook-global
+     * @param index the sheet index this name applies to, -1 unsets this property making the name workbook-global
      */
-    public void setLocalSheetId(int sheetId) {
-        if(sheetId == -1) ctName.unsetLocalSheetId();
-        else ctName.setLocalSheetId(sheetId);
+    public void setSheetIndex(int index) {
+        int lastSheetIx = workbook.getNumberOfSheets() - 1;
+        if (index < -1 || index > lastSheetIx) {
+            throw new IllegalArgumentException("Sheet index (" + index +") is out of range" +
+                    (lastSheetIx == -1 ? "" : (" (0.." +    lastSheetIx + ")")));
+        }
+
+        if(index == -1) {
+            if(ctName.isSetLocalSheetId()) ctName.unsetLocalSheetId();
+        } else {
+            ctName.setLocalSheetId(index);
+        }
     }
 
     /**
@@ -207,7 +215,7 @@ public final class XSSFName implements Name {
      *
      * @return the sheet index this name applies to, -1 if this name applies to the entire workbook
      */
-    public int getLocalSheetId() {
+    public int getSheetIndex() {
         return ctName.isSetLocalSheetId() ? (int) ctName.getLocalSheetId() : -1;
     }
 
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
index 539e014d97..748bc3df9d 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
@@ -21,6 +21,7 @@ import java.util.*;
 
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.POIXMLException;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
 
@@ -30,6 +31,11 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
 public class XSSFRow implements Row, Comparable<XSSFRow> {
 
     /**
+     * The maximum  number of rows in SpreadsheetML
+     */
+    public static final int MAX_ROW_NUMBER  = 1048576; //2 ^ 20
+
+    /**
      * the xml bean containing all cell definitions for this row
      */
     private final CTRow row;
@@ -309,10 +315,12 @@ public class XSSFRow implements Row, Comparable<XSSFRow> {
      * Set the row number of this row.
      *
      * @param rowNum  the row number (0-based)
-     * @throws IllegalArgumentException if rowNum < 0
+     * @throws IllegalArgumentException if rowNum < 0 or greater than {@link #MAX_ROW_NUMBER}
      */
     public void setRowNum(int rowNum) {
         if(rowNum < 0) throw new IllegalArgumentException("Row number must be >= 0");
+        if (rowNum > MAX_ROW_NUMBER)
+            throw new IllegalArgumentException("You cannot have more than "+MAX_ROW_NUMBER+" rows ");
 
         this.row.setR(rowNum + 1);
     }
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 0597f677b0..7594e6aa86 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
@@ -745,7 +745,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
     public void removePrintArea(int sheetIndex) {
         int cont = 0;
         for (XSSFName name : namedRanges) {
-            if (name.getNameName().equals(XSSFName.BUILTIN_PRINT_AREA) && name.getLocalSheetId() == sheetIndex) {
+            if (name.getNameName().equals(XSSFName.BUILTIN_PRINT_AREA) && name.getSheetIndex() == sheetIndex) {
                 namedRanges.remove(cont);
                 break;
             }
@@ -967,7 +967,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
 
     private XSSFName getBuiltInName(String builtInCode, int sheetNumber) {
         for (XSSFName name : namedRanges) {
-            if (name.getNameName().equalsIgnoreCase(builtInCode) && name.getLocalSheetId() == sheetNumber) {
+            if (name.getNameName().equalsIgnoreCase(builtInCode) && name.getSheetIndex() == sheetNumber) {
                 return name;
             }
         }