You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2009/05/17 18:35:26 UTC
svn commit: r775701 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/record/formula/ java/org/apache/poi/ss/formula/
java/org/apache/poi/ss/util/ ooxml/testcases/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/hssf/util/
Author: yegor
Date: Sun May 17 16:35:25 2009
New Revision: 775701
URL: http://svn.apache.org/viewvc?rev=775701&view=rev
Log:
Allow columns greater than 255 and rows greater than 0x100000 in XSSF formulas
Added:
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java
poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Sun May 17 16:35:25 2009
@@ -37,6 +37,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.5-beta6" date="2009-??-??">
+ <action dev="POI-DEVELOPERS" type="add">46806 - Allow columns greater than 255 and rows greater than 0x100000 in XSSF formulas</action>
<action dev="POI-DEVELOPERS" type="add">41711 - Base class for "old version" exceptions, and new HSLF detection + use of old versions exception</action>
<action dev="POI-DEVELOPERS" type="fix">47179 - Fix string encoding issues with HSMF chunks on non-windows platforms</action>
<action dev="POI-DEVELOPERS" type="add">47183 - Attachment support for HSMF</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sun May 17 16:35:25 2009
@@ -34,6 +34,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.5-beta6" date="2009-??-??">
+ <action dev="POI-DEVELOPERS" type="add">46806 - Allow columns greater than 255 and rows greater than 0x100000 in XSSF formulas</action>
<action dev="POI-DEVELOPERS" type="add">41711 - Base class for "old version" exceptions, and new HSLF detection + use of old versions exception</action>
<action dev="POI-DEVELOPERS" type="fix">47179 - Fix string encoding issues with HSMF chunks on non-windows platforms</action>
<action dev="POI-DEVELOPERS" type="add">47183 - Attachment support for HSMF</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtgBase.java Sun May 17 16:35:25 2009
@@ -71,11 +71,6 @@
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 @@
}
}
- 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 @@
* @param rowIx number (0-based)
*/
public final void setFirstRow(int rowIx) {
- checkRowBounds(rowIx);
field_1_first_row = rowIx;
}
@@ -152,7 +146,6 @@
* @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 @@
* 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 @@
* 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);
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/RefPtgBase.java Sun May 17 16:35:25 2009
@@ -32,8 +32,6 @@
*/
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 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 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);
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java Sun May 17 16:35:25 2009
@@ -21,6 +21,7 @@
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 @@
* @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);
}
/**
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java Sun May 17 16:35:25 2009
@@ -31,6 +31,7 @@
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 @@
private char look;
private FormulaParsingWorkbook _book;
+ private SpreadsheetVersion _ssVersion;
private int _sheetIndex;
@@ -160,7 +162,8 @@
_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 @@
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 @@
/**
* 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 @@
/**
* @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 @@
* <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) {
Modified: poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/util/CellReference.java Sun May 17 16:35:25 2009
@@ -21,6 +21,7 @@
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 @@
* 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 @@
* 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 @@
}
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 @@
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 @@
* @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 @@
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 {
Added: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java?rev=775701&view=auto
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java (added)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java Sun May 17 16:35:25 2009
@@ -0,0 +1,97 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.xssf.usermodel;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.RefPtg;
+import org.apache.poi.hssf.record.formula.IntPtg;
+import org.apache.poi.hssf.record.formula.FuncPtg;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
+
+public final class TestXSSFFormulaParser extends TestCase {
+
+
+ public void testParse() {
+ XSSFWorkbook wb = new XSSFWorkbook();
+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
+ String fmla;
+ Ptg[] ptgs;
+
+ fmla = "ABC10";
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ assertEquals(1, ptgs.length);
+ assertTrue("",(ptgs[0] instanceof RefPtg));
+
+ fmla = "A500000";
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ assertEquals(1, ptgs.length);
+ assertTrue("",(ptgs[0] instanceof RefPtg));
+
+ fmla = "ABC500000";
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ assertEquals(1, ptgs.length);
+ assertTrue("",(ptgs[0] instanceof RefPtg));
+
+ //highest allowed rows and column (XFD and 0x100000)
+ fmla = "XFD1048576";
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ assertEquals(1, ptgs.length);
+ assertTrue("",(ptgs[0] instanceof RefPtg));
+
+
+ //column greater than XFD
+ fmla = "XFE10";
+ try {
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ fail("expected exception");
+ } catch (Exception e){
+ assertEquals("Specified named range 'XFE10' does not exist in the current workbook.", e.getMessage());
+ }
+
+ //row greater than 0x100000
+ fmla = "XFD1048577";
+ try {
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ fail("expected exception");
+ } catch (Exception e){
+ assertEquals("Specified named range 'XFD1048577' does not exist in the current workbook.", e.getMessage());
+ }
+ }
+
+ public void testBuiltInFormulas() {
+ XSSFWorkbook wb = new XSSFWorkbook();
+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
+ String fmla;
+ Ptg[] ptgs;
+
+ fmla = "LOG10";
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ assertEquals(1, ptgs.length);
+ assertTrue("",(ptgs[0] instanceof RefPtg));
+
+ fmla = "LOG10(100)";
+ ptgs = FormulaParser.parse(fmla, fpb, FormulaType.CELL);
+ assertEquals(2, ptgs.length);
+ assertTrue("",(ptgs[0] instanceof IntPtg));
+ assertTrue("",(ptgs[1] instanceof FuncPtg));
+
+ }
+}
\ No newline at end of file
Propchange: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java?rev=775701&r1=775700&r2=775701&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java Sun May 17 16:35:25 2009
@@ -22,6 +22,7 @@
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellReference.NameType;
+import org.apache.poi.ss.SpreadsheetVersion;
public final class TestCellReference extends TestCase {
@@ -104,7 +105,7 @@
}
private void confirmNameType(String ref, int expectedResult) {
- int actualResult = CellReference.classifyCellReference(ref);
+ int actualResult = CellReference.classifyCellReference(ref, SpreadsheetVersion.EXCEL97);
assertEquals(expectedResult, actualResult);
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org