Allow columns greater than 255 and rows greater than 0x100000 in XSSF formulas

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

5 files changed, 61 insertions, 47 deletions

diff --git a/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java b/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java
index 0c1e3e6131..5cd5a9b748 100644
--- a/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java
+++ b/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java
@@ -71,11 +71,6 @@ public abstract class AreaPtgBase extends OperandPtg implements AreaI {
 	protected AreaPtgBase(int firstRow, int lastRow, int firstColumn, int lastColumn,
 			boolean firstRowRelative, boolean lastRowRelative, boolean firstColRelative, boolean lastColRelative) {
 
-		checkColumnBounds(firstColumn);
-		checkColumnBounds(lastColumn);
-		checkRowBounds(firstRow);
-		checkRowBounds(lastRow);
-
 		if (lastRow > firstRow) {
 			setFirstRow(firstRow);
 			setLastRow(lastRow);
@@ -101,12 +96,12 @@ public abstract class AreaPtgBase extends OperandPtg implements AreaI {
 		}
 	}
 
-	private static void checkColumnBounds(int colIx) {
+	private static void $checkColumnBounds(int colIx) {
 		if((colIx & 0x0FF) != colIx) {
 			throw new IllegalArgumentException("colIx (" + colIx + ") is out of range");
 		}
 	}
-	private static void checkRowBounds(int rowIx) {
+	private static void $checkRowBounds(int rowIx) {
 		if((rowIx & 0x0FFFF) != rowIx) {
 			throw new IllegalArgumentException("rowIx (" + rowIx + ") is out of range");
 		}
@@ -137,7 +132,6 @@ public abstract class AreaPtgBase extends OperandPtg implements AreaI {
 	 * @param rowIx number (0-based)
 	 */
 	public final void setFirstRow(int rowIx) {
-		checkRowBounds(rowIx);
 		field_1_first_row = rowIx;
 	}
 
@@ -152,7 +146,6 @@ public abstract class AreaPtgBase extends OperandPtg implements AreaI {
 	 * @param rowIx last row number in the area
 	 */
 	public final void setLastRow(int rowIx) {
-		checkRowBounds(rowIx);
 		field_2_last_row = rowIx;
 	}
 
@@ -203,7 +196,6 @@ public abstract class AreaPtgBase extends OperandPtg implements AreaI {
 	 * set the first column in the area
 	 */
 	public final void setFirstColumn(int colIx) {
-		checkColumnBounds(colIx);
 		field_3_first_column=columnMask.setValue(field_3_first_column, colIx);
 	}
 
@@ -262,7 +254,6 @@ public abstract class AreaPtgBase extends OperandPtg implements AreaI {
 	 * set the last column in the area
 	 */
 	public final void setLastColumn(int colIx) {
-		checkColumnBounds(colIx);
 		field_4_last_column=columnMask.setValue(field_4_last_column, colIx);
 	}
 
diff --git a/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java b/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java
index 205dd0ddf2..1bef14d90e 100644
--- a/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java
+++ b/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java
@@ -32,8 +32,6 @@ import org.apache.poi.ss.SpreadsheetVersion;
  */
 public abstract class RefPtgBase extends OperandPtg {
 
-	private final static int MAX_ROW_NUMBER = SpreadsheetVersion.EXCEL97.getMaxRows();
-
 	/** The row index - zero based unsigned 16 bit value */
 	private int field_1_row;
 	/**
@@ -67,9 +65,6 @@ public abstract class RefPtgBase extends OperandPtg {
 	}
 
 	public final void setRow(int rowIndex) {
-		if (rowIndex < 0 || rowIndex >= MAX_ROW_NUMBER) {
-			throw new IllegalArgumentException("rowIndex must be between 0 and " + MAX_ROW_NUMBER);
-		}
 		field_1_row = rowIndex;
 	}
 
@@ -97,9 +92,6 @@ public abstract class RefPtgBase extends OperandPtg {
 	}
 
 	public final void setColumn(int col) {
-		if (col < 0 || col >= 0x100) {
-			throw new IllegalArgumentException("Specified colIx (" + col + ") is out of range");
-		}
 		field_2_col = column.setValue(field_2_col, col);
 	}
 
diff --git a/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java b/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
index 1d581c230a..2e84d2f9e0 100755
--- a/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
+++ b/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
@@ -21,6 +21,7 @@ import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
 import org.apache.poi.hssf.util.CellReference;
+import org.apache.poi.ss.SpreadsheetVersion;
 
 /**
  * Formats sheet names for use in formula expressions.
@@ -183,7 +184,7 @@ public final class SheetNameFormatter {
 	 * @see org.apache.poi.hssf.util.CellReference
 	 */
 	/* package */ static boolean cellReferenceIsWithinRange(String lettersPrefix, String numbersSuffix) {
-		return CellReference.cellReferenceIsWithinRange(lettersPrefix, numbersSuffix);
+		return CellReference.cellReferenceIsWithinRange(lettersPrefix, numbersSuffix, SpreadsheetVersion.EXCEL97);
 	}
 
 	/**
diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java
index 7cd7e73941..ec2252d5e0 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaParser.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java
@@ -31,6 +31,7 @@ import org.apache.poi.hssf.usermodel.HSSFErrorConstants;
 import org.apache.poi.ss.util.AreaReference;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.util.CellReference.NameType;
+import org.apache.poi.ss.SpreadsheetVersion;
 
 /**
  * This class parses a formula string into a List of tokens in RPN order.
@@ -140,6 +141,7 @@ public final class FormulaParser {
 	private char look;
 
 	private FormulaParsingWorkbook _book;
+    private SpreadsheetVersion _ssVersion;
 
 	private int _sheetIndex;
 
@@ -160,7 +162,8 @@ public final class FormulaParser {
 		_formulaString = formula;
 		_pointer=0;
 		_book = book;
-		_formulaLength = _formulaString.length();
+        _ssVersion = book == null ? SpreadsheetVersion.EXCEL97 : book.getSpreadsheetVersion();
+        _formulaLength = _formulaString.length();
 		_sheetIndex = sheetIndex;
 	}
 
@@ -699,8 +702,8 @@ public final class FormulaParser {
 			if (!isValidCellReference(rep)) {
 				return null;
 			}
-		} else if (hasLetters) {
-			if (!CellReference.isColumnWithnRange(rep.replace("$", ""))) {
+        } else if (hasLetters) {
+			if (!CellReference.isColumnWithnRange(rep.replace("$", ""), _ssVersion)) {
 				return null;
 			}
 		} else if (hasDigits) {
@@ -798,7 +801,6 @@ public final class FormulaParser {
 
 	/**
 	 * Note - caller should reset {@link #_pointer} upon <code>null</code> result
-	 * @param iden identifier prefix (if unquoted, it is terminated at first dot)
 	 * @return The sheet name as an identifier <code>null</code> if '!' is not found in the right place
 	 */
 	private SheetIdentifier parseSheetName() {
@@ -878,8 +880,30 @@ public final class FormulaParser {
 	/**
 	 * @return <code>true</code> if the specified name is a valid cell reference
 	 */
-	private static boolean isValidCellReference(String str) {
-		return CellReference.classifyCellReference(str) == NameType.CELL;
+	private boolean isValidCellReference(String str) {
+        //check range bounds against grid max
+        boolean result = CellReference.classifyCellReference(str, _ssVersion) == NameType.CELL;
+
+        if(result){
+            /**
+             * Check if the argument is a function. Certain names can be either a cell reference or a function name
+             * depending on the contenxt. Compare the following examples in Excel 2007:
+             * (a) LOG10(100) + 1
+             * (b) LOG10 + 1
+             * In (a) LOG10 is a name of a built-in function. In (b) LOG10 is a cell reference
+             */
+            boolean isFunc = FunctionMetadataRegistry.getFunctionByName(str.toUpperCase()) != null;
+            if(isFunc){
+                int savePointer = _pointer;
+                resetPointer(_pointer + str.length());
+                SkipWhite();
+                // open bracket indicates that the argument is a function,
+                // the returning value should be false, i.e. "not a valid cell reference"
+                result = look != '(';
+                resetPointer(savePointer);
+            }
+        }
+        return result;
 	}
 
 
@@ -932,7 +956,6 @@ public final class FormulaParser {
 	 * <p>
 	 * For IF Formulas, additional PTGs are added to the tokens
 	 * @param name a {@link NamePtg} or {@link NameXPtg} or <code>null</code>
-	 * @param numArgs
 	 * @return Ptg a null is returned if we're in an IF formula, it needs extreme manipulation and is handled in this function
 	 */
 	private ParseNode getFunction(String name, Ptg namePtg, ParseNode[] args) {
diff --git a/src/java/org/apache/poi/ss/util/CellReference.java b/src/java/org/apache/poi/ss/util/CellReference.java
index 1b19f594a0..b559c4302d 100644
--- a/src/java/org/apache/poi/ss/util/CellReference.java
+++ b/src/java/org/apache/poi/ss/util/CellReference.java
@@ -21,6 +21,7 @@ import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
 import org.apache.poi.hssf.record.formula.SheetNameFormatter;
+import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
 import org.apache.poi.ss.SpreadsheetVersion;
 
 /**
@@ -62,10 +63,10 @@ public class CellReference {
 	 * digits or dot.  (They can even end in dot).
 	 */
 	private static final Pattern NAMED_RANGE_NAME_PATTERN = Pattern.compile("[_A-Za-z][_.A-Za-z0-9]*");
-	private static final String BIFF8_LAST_COLUMN = SpreadsheetVersion.EXCEL97.getLastColumnName();
-	private static final int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.length();
-	private static final String BIFF8_LAST_ROW = String.valueOf(SpreadsheetVersion.EXCEL97.getMaxRows());
-	private static final int BIFF8_LAST_ROW_TEXT_LEN = BIFF8_LAST_ROW.length();
+	//private static final String BIFF8_LAST_COLUMN = SpreadsheetVersion.EXCEL97.getLastColumnName();
+	//private static final int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.length();
+	//private static final String BIFF8_LAST_ROW = String.valueOf(SpreadsheetVersion.EXCEL97.getMaxRows());
+	//private static final int BIFF8_LAST_ROW_TEXT_LEN = BIFF8_LAST_ROW.length();
 
     private final int _rowIndex;
     private final int _colIndex;
@@ -176,7 +177,7 @@ public class CellReference {
 	 * Classifies an identifier as either a simple (2D) cell reference or a named range name
 	 * @return one of the values from <tt>NameType</tt> 
 	 */
-	public static int classifyCellReference(String str) {
+	public static int classifyCellReference(String str, SpreadsheetVersion ssVersion) {
 		int len = str.length();
 		if (len < 1) {
 			throw new IllegalArgumentException("Empty string not allowed");
@@ -195,15 +196,15 @@ public class CellReference {
 		}
 		if (!Character.isDigit(str.charAt(len-1))) {
 			// no digits at end of str
-			return validateNamedRangeName(str);
+			return validateNamedRangeName(str, ssVersion);
 		}
 		Matcher cellRefPatternMatcher = CELL_REF_PATTERN.matcher(str);
 		if (!cellRefPatternMatcher.matches()) {
-			return validateNamedRangeName(str);
+			return validateNamedRangeName(str, ssVersion);
 		}
 		String lettersGroup = cellRefPatternMatcher.group(1);
 		String digitsGroup = cellRefPatternMatcher.group(2);
-		if (cellReferenceIsWithinRange(lettersGroup, digitsGroup)) {
+        if (cellReferenceIsWithinRange(lettersGroup, digitsGroup, ssVersion)) {
 			// valid cell reference
 			return NameType.CELL;
 		}
@@ -219,11 +220,11 @@ public class CellReference {
 		return NameType.NAMED_RANGE;
 	}
 
-	private static int validateNamedRangeName(String str) {
+	private static int validateNamedRangeName(String str, SpreadsheetVersion ssVersion) {
 		Matcher colMatcher = COLUMN_REF_PATTERN.matcher(str);
 		if (colMatcher.matches()) {
 			String colStr = colMatcher.group(1);
-			if (isColumnWithnRange(colStr)) {
+			if (isColumnWithnRange(colStr, ssVersion)) {
 				return NameType.COLUMN;
 			}
 		}
@@ -270,18 +271,21 @@ public class CellReference {
 	 * @param rowStr a string of only digit characters
 	 * @return <code>true</code> if the row and col parameters are within range of a BIFF8 spreadsheet.
 	 */
-	public static boolean cellReferenceIsWithinRange(String colStr, String rowStr) {
-		if (!isColumnWithnRange(colStr)) {
+	public static boolean cellReferenceIsWithinRange(String colStr, String rowStr, SpreadsheetVersion ssVersion) {
+		if (!isColumnWithnRange(colStr, ssVersion)) {
 			return false;
 		}
-		int nDigits = rowStr.length();
-		if(nDigits > BIFF8_LAST_ROW_TEXT_LEN) {
+        String lastRow = String.valueOf(ssVersion.getMaxRows());
+        int lastRowLen = lastRow.length();
+
+        int nDigits = rowStr.length();
+		if(nDigits > lastRowLen) {
 			return false; 
 		}
 		
-		if(nDigits == BIFF8_LAST_ROW_TEXT_LEN) {
+		if(nDigits == lastRowLen) {
 			// ASCII comparison is valid if digit count is same
-			if(rowStr.compareTo(BIFF8_LAST_ROW) > 0) {
+			if(rowStr.compareTo(lastRow) > 0) {
 				return false;
 			}
 		} else {
@@ -292,14 +296,17 @@ public class CellReference {
 		return true;
 	}
 
-	public static boolean isColumnWithnRange(String colStr) {
+	public static boolean isColumnWithnRange(String colStr, SpreadsheetVersion ssVersion) {
+        String lastCol = ssVersion.getLastColumnName();
+        int lastColLength = lastCol.length();
+
 		int numberOfLetters = colStr.length();
-		if(numberOfLetters > BIFF8_LAST_COLUMN_TEXT_LEN) {
+		if(numberOfLetters > lastColLength) {
 			// "Sheet1" case etc
 			return false; // that was easy
 		}
-		if(numberOfLetters == BIFF8_LAST_COLUMN_TEXT_LEN) {
-			if(colStr.toUpperCase().compareTo(BIFF8_LAST_COLUMN) > 0) {
+		if(numberOfLetters == lastColLength) {
+			if(colStr.toUpperCase().compareTo(lastCol) > 0) {
 				return false;
 			}
 		} else {