You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2008/09/05 22:38:52 UTC
svn commit: r692538 - in /poi/trunk/src: java/org/apache/poi/hssf/record/
java/org/apache/poi/hssf/record/formula/functions/
java/org/apache/poi/hssf/util/ testcases/org/apache/poi/hssf/usermodel/
testcases/org/apache/poi/hssf/util/
Author: josh
Date: Fri Sep 5 13:38:51 2008
New Revision: 692538
URL: http://svn.apache.org/viewvc?rev=692538&view=rev
Log:
Modified formula evaluator to handle whole column refs
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Index.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java
poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java
poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java Fri Sep 5 13:38:51 2008
@@ -487,7 +487,7 @@
}
private static Ptg createNewPtg(){
- return new Area3DPtg("A1", 0); // TODO - change to not be partially initialised
+ return new Area3DPtg("A1:A1", 0); // TODO - change to not be partially initialised
}
/** gets the reference , the area only (range)
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Hlookup.java Fri Sep 5 13:38:51 2008
@@ -60,8 +60,8 @@
AreaEval tableArray = LookupUtils.resolveTableArrayArg(args[1]);
boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
int colIndex = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createRowVector(tableArray, 0), isRangeLookup);
- ValueEval veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
- int rowIndex = LookupUtils.resolveRowOrColIndexArg(veColIndex);
+ ValueEval veRowIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
+ int rowIndex = LookupUtils.resolveRowOrColIndexArg(veRowIndex);
ValueVector resultCol = createResultColumnVector(tableArray, rowIndex);
return resultCol.getItem(colIndex);
} catch (EvaluationException e) {
@@ -75,13 +75,13 @@
*
* @throws EvaluationException (#VALUE!) if colIndex is negative, (#REF!) if colIndex is too high
*/
- private ValueVector createResultColumnVector(AreaEval tableArray, int colIndex) throws EvaluationException {
- if(colIndex < 0) {
+ private ValueVector createResultColumnVector(AreaEval tableArray, int rowIndex) throws EvaluationException {
+ if(rowIndex < 0) {
throw EvaluationException.invalidValue();
}
- if(colIndex >= tableArray.getWidth()) {
+ if(rowIndex >= tableArray.getHeight()) {
throw EvaluationException.invalidRef();
}
- return LookupUtils.createRowVector(tableArray, colIndex);
+ return LookupUtils.createRowVector(tableArray, rowIndex);
}
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Index.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Index.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Index.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Index.java Fri Sep 5 13:38:51 2008
@@ -90,9 +90,19 @@
}
}
- private static ValueEval getValueFromArea(AreaEval ae, int rowIx, int columnIx) throws EvaluationException {
+ private static ValueEval getValueFromArea(AreaEval ae, int pRowIx, int pColumnIx) throws EvaluationException {
int width = ae.getWidth();
int height = ae.getHeight();
+ int rowIx;
+ int columnIx;
+ if (ae.isRow() && pColumnIx == 0 && pRowIx > 0) {
+ // TODO - explore all these special cases
+ rowIx = 0;
+ columnIx = pRowIx;
+ } else {
+ rowIx = pRowIx;
+ columnIx = pColumnIx;
+ }
// Slightly irregular logic for bounds checking errors
if (rowIx >= height || columnIx >= width) {
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/LookupUtils.java Fri Sep 5 13:38:51 2008
@@ -17,6 +17,7 @@
package org.apache.poi.hssf.record.formula.functions;
+import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.formula.eval.AreaEval;
import org.apache.poi.hssf.record.formula.eval.BlankEval;
import org.apache.poi.hssf.record.formula.eval.BoolEval;
@@ -587,7 +588,9 @@
if (lookupValue instanceof BlankEval) {
// blank eval can never be found in a lookup array
- throw new EvaluationException(ErrorEval.NA);
+ //throw new EvaluationException(ErrorEval.NA);
+ // TODO - investigate this
+ return new NumberLookupComparer(NumberEval.ZERO);
}
if (lookupValue instanceof StringEval) {
return new StringLookupComparer((StringEval) lookupValue);
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Vlookup.java Fri Sep 5 13:38:51 2008
@@ -60,7 +60,16 @@
AreaEval tableArray = LookupUtils.resolveTableArrayArg(args[1]);
boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
int rowIndex = LookupUtils.lookupIndexOfValue(lookupValue, LookupUtils.createColumnVector(tableArray, 0), isRangeLookup);
- ValueEval veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
+ ValueEval veColIndex;
+ try {
+ veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
+ } catch (EvaluationException e) {
+ // weird translation of errors for the third arg - needs investigation
+ if (e.getErrorEval() == ErrorEval.NA) {
+ return ErrorEval.REF_INVALID;
+ }
+ throw e;
+ }
int colIndex = LookupUtils.resolveRowOrColIndexArg(veColIndex);
ValueVector resultCol = createResultColumnVector(tableArray, colIndex);
return resultCol.getItem(rowIndex);
Modified: poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java Fri Sep 5 13:38:51 2008
@@ -46,29 +46,59 @@
}
String[] parts = separateAreaRefs(reference);
+ String part0 = parts[0];
+ if (parts.length == 1) {
+ // TODO - probably shouldn't initialize area ref when text is really a cell ref
+ // Need to fix some named range stuff to get rid of this
+ _firstCell = new CellReference(part0);
+
+ _lastCell = _firstCell;
+ _isSingleCell = true;
+ return;
+ }
+ if (parts.length != 2) {
+ throw new IllegalArgumentException("Bad area ref '" + reference + "'");
+ }
- // Special handling for whole-column references
- if(parts.length == 2 && parts[0].length() == 1 &&
- parts[1].length() == 1 &&
- parts[0].charAt(0) >= 'A' && parts[0].charAt(0) <= 'Z' &&
- parts[1].charAt(0) >= 'A' && parts[1].charAt(0) <= 'Z') {
+ String part1 = parts[1];
+ if (isPlainColumn(part0)) {
+ if (!isPlainColumn(part1)) {
+ throw new RuntimeException("Bad area ref '" + reference + "'");
+ }
+ // Special handling for whole-column references
// Represented internally as x$1 to x$65536
// which is the maximum range of rows
- parts[0] = parts[0] + "$1";
- parts[1] = parts[1] + "$65536";
- }
-
- _firstCell = new CellReference(parts[0]);
-
- if(parts.length == 2) {
- _lastCell = new CellReference(parts[1]);
+
+ boolean firstIsAbs = CellReference.isPartAbsolute(part0);
+ boolean lastIsAbs = CellReference.isPartAbsolute(part1);
+
+ int col0 = CellReference.convertColStringToIndex(part0);
+ int col1 = CellReference.convertColStringToIndex(part1);
+
+ _firstCell = new CellReference(0, col0, true, firstIsAbs);
+ _lastCell = new CellReference(0xFFFF, col1, true, lastIsAbs);
_isSingleCell = false;
+ // TODO - whole row refs
} else {
- _lastCell = _firstCell;
- _isSingleCell = true;
+ _firstCell = new CellReference(part0);
+ _lastCell = new CellReference(part1);
+ _isSingleCell = part0.equals(part1);
+ }
+ }
+
+ private boolean isPlainColumn(String refPart) {
+ for(int i=refPart.length()-1; i>=0; i--) {
+ int ch = refPart.charAt(i);
+ if (ch == '$' && i==0) {
+ continue;
+ }
+ if (ch < 'A' || ch > 'Z') {
+ return false;
+ }
}
+ return true;
}
-
+
/**
* Creates an area ref from a pair of Cell References.
*/
Modified: poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java Fri Sep 5 13:38:51 2008
@@ -36,17 +36,17 @@
public static final int NAMED_RANGE = 2;
public static final int BAD_CELL_OR_NAMED_RANGE = -1;
}
- /** The character ($) that signifies a row or column value is absolute instead of relative */
- private static final char ABSOLUTE_REFERENCE_MARKER = '$';
- /** The character (!) that separates sheet names from cell references */
- private static final char SHEET_NAME_DELIMITER = '!';
- /** The character (') used to quote sheet names when they contain special characters */
- private static final char SPECIAL_NAME_DELIMITER = '\'';
-
- /**
- * Matches a run of letters followed by a run of digits. The run of letters is group 1 and the
- * run of digits is group 2. Each group may optionally be prefixed with a single '$'.
- */
+ /** The character ($) that signifies a row or column value is absolute instead of relative */
+ private static final char ABSOLUTE_REFERENCE_MARKER = '$';
+ /** The character (!) that separates sheet names from cell references */
+ private static final char SHEET_NAME_DELIMITER = '!';
+ /** The character (') used to quote sheet names when they contain special characters */
+ private static final char SPECIAL_NAME_DELIMITER = '\'';
+
+ /**
+ * Matches a run of letters followed by a run of digits. The run of letters is group 1 and the
+ * run of digits is group 2. Each group may optionally be prefixed with a single '$'.
+ */
private static final Pattern CELL_REF_PATTERN = Pattern.compile("\\$?([A-Za-z]+)\\$?([0-9]+)");
/**
* Named range names must start with a letter or underscore. Subsequent characters may include
@@ -58,89 +58,103 @@
private static final String BIFF8_LAST_ROW = String.valueOf(0x10000);
private static final int BIFF8_LAST_ROW_TEXT_LEN = BIFF8_LAST_ROW.length();
- private final int _rowIndex;
- private final int _colIndex;
- private final String _sheetName;
- private final boolean _isRowAbs;
- private final boolean _isColAbs;
-
- /**
- * Create an cell ref from a string representation. Sheet names containing special characters should be
- * delimited and escaped as per normal syntax rules for formulas.
- */
- public CellReference(String cellRef) {
- String[] parts = separateRefParts(cellRef);
- _sheetName = parts[0];
- String colRef = parts[1];
- if (colRef.length() < 1) {
- throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
- }
- _isColAbs = colRef.charAt(0) == '$';
- if (_isColAbs) {
- colRef=colRef.substring(1);
- }
- _colIndex = convertColStringToNum(colRef);
-
- String rowRef=parts[2];
- if (rowRef.length() < 1) {
- throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
- }
- _isRowAbs = rowRef.charAt(0) == '$';
- if (_isRowAbs) {
- rowRef=rowRef.substring(1);
- }
- _rowIndex = Integer.parseInt(rowRef)-1; // -1 to convert 1-based to zero-based
- }
-
- public CellReference(int pRow, int pCol) {
- this(pRow, pCol, false, false);
- }
- public CellReference(int pRow, short pCol) {
- this(pRow, (int)pCol, false, false);
- }
-
- public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
- this(null, pRow, pCol, pAbsRow, pAbsCol);
- }
- public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
- // TODO - "-1" is a special value being temporarily used for whole row and whole column area references.
- // so these checks are currently N.Q.R.
- if(pRow < -1) {
- throw new IllegalArgumentException("row index may not be negative");
- }
- if(pCol < -1) {
- throw new IllegalArgumentException("column index may not be negative");
- }
- _sheetName = pSheetName;
- _rowIndex=pRow;
- _colIndex=pCol;
- _isRowAbs = pAbsRow;
- _isColAbs=pAbsCol;
- }
-
- public int getRow(){return _rowIndex;}
- public short getCol(){return (short) _colIndex;}
- public boolean isRowAbsolute(){return _isRowAbs;}
- public boolean isColAbsolute(){return _isColAbs;}
- /**
- * @return possibly <code>null</code> if this is a 2D reference. Special characters are not
- * escaped or delimited
- */
- public String getSheetName(){
- return _sheetName;
- }
-
- /**
- * takes in a column reference portion of a CellRef and converts it from
- * ALPHA-26 number format to 0-based base 10.
- */
- private int convertColStringToNum(String ref) {
- int lastIx = ref.length()-1;
- int retval=0;
- int pos = 0;
+ private final int _rowIndex;
+ private final int _colIndex;
+ private final String _sheetName;
+ private final boolean _isRowAbs;
+ private final boolean _isColAbs;
+
+ /**
+ * Create an cell ref from a string representation. Sheet names containing special characters should be
+ * delimited and escaped as per normal syntax rules for formulas.
+ */
+ public CellReference(String cellRef) {
+ String[] parts = separateRefParts(cellRef);
+ _sheetName = parts[0];
+ String colRef = parts[1];
+ if (colRef.length() < 1) {
+ throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
+ }
+ _isColAbs = colRef.charAt(0) == '$';
+ if (_isColAbs) {
+ colRef=colRef.substring(1);
+ }
+ _colIndex = convertColStringToIndex(colRef);
- for (int k = lastIx; k > -1; k--) {
+ String rowRef=parts[2];
+ if (rowRef.length() < 1) {
+ throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
+ }
+ _isRowAbs = rowRef.charAt(0) == '$';
+ if (_isRowAbs) {
+ rowRef=rowRef.substring(1);
+ }
+ _rowIndex = Integer.parseInt(rowRef)-1; // -1 to convert 1-based to zero-based
+ }
+
+ public CellReference(int pRow, int pCol) {
+ this(pRow, pCol, false, false);
+ }
+ public CellReference(int pRow, short pCol) {
+ this(pRow, pCol & 0xFFFF, false, false);
+ }
+
+ public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
+ this(null, pRow, pCol, pAbsRow, pAbsCol);
+ }
+ public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
+ // TODO - "-1" is a special value being temporarily used for whole row and whole column area references.
+ // so these checks are currently N.Q.R.
+ if(pRow < -1) {
+ throw new IllegalArgumentException("row index may not be negative");
+ }
+ if(pCol < -1) {
+ throw new IllegalArgumentException("column index may not be negative");
+ }
+ _sheetName = pSheetName;
+ _rowIndex=pRow;
+ _colIndex=pCol;
+ _isRowAbs = pAbsRow;
+ _isColAbs=pAbsCol;
+ }
+
+ public int getRow(){return _rowIndex;}
+ public short getCol(){return (short) _colIndex;}
+ public boolean isRowAbsolute(){return _isRowAbs;}
+ public boolean isColAbsolute(){return _isColAbs;}
+ /**
+ * @return possibly <code>null</code> if this is a 2D reference. Special characters are not
+ * escaped or delimited
+ */
+ public String getSheetName(){
+ return _sheetName;
+ }
+
+ public static boolean isPartAbsolute(String part) {
+ return part.charAt(0) == ABSOLUTE_REFERENCE_MARKER;
+ }
+ /**
+ * takes in a column reference portion of a CellRef and converts it from
+ * ALPHA-26 number format to 0-based base 10.
+ * 'A' -> 0
+ * 'Z' -> 25
+ * 'AA' -> 26
+ * 'IV' -> 255
+ * @return zero based column index
+ */
+ public static int convertColStringToIndex(String ref) {
+
+ int pos = 0;
+ int retval=0;
+ for (int k = ref.length()-1; k >= 0; k--) {
char thechar = ref.charAt(k);
+ if (thechar == ABSOLUTE_REFERENCE_MARKER) {
+ if (k != 0) {
+ throw new IllegalArgumentException("Bad col ref format '"
+ + ref + "'");
+ }
+ break;
+ }
// Character.getNumericValue() returns the values
// 10-35 for the letter A-Z
int shift = (int)Math.pow(26, pos);
@@ -148,64 +162,64 @@
pos++;
}
return retval-1;
- }
+ }
- /**
- * 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) {
- int len = str.length();
- if (len < 1) {
- throw new IllegalArgumentException("Empty string not allowed");
- }
- char firstChar = str.charAt(0);
- switch (firstChar) {
- case ABSOLUTE_REFERENCE_MARKER:
- case '.':
- case '_':
- break;
- default:
- if (!Character.isLetter(firstChar)) {
- throw new IllegalArgumentException("Invalid first char (" + firstChar
- + ") of cell reference or named range. Letter expected");
- }
- }
- if (!Character.isDigit(str.charAt(len-1))) {
- // no digits at end of str
- return validateNamedRangeName(str);
- }
- Matcher cellRefPatternMatcher = CELL_REF_PATTERN.matcher(str);
- if (!cellRefPatternMatcher.matches()) {
- return validateNamedRangeName(str);
- }
- String lettersGroup = cellRefPatternMatcher.group(1);
- String digitsGroup = cellRefPatternMatcher.group(2);
- if (cellReferenceIsWithinRange(lettersGroup, digitsGroup)) {
- // valid cell reference
- return NameType.CELL;
- }
- // If str looks like a cell reference, but is out of (row/col) range, it is a valid
- // named range name
- // This behaviour is a little weird. For example, "IW123" is a valid named range name
- // because the column "IW" is beyond the maximum "IV". Note - this behaviour is version
- // dependent. In Excel 2007, "IW123" is not a valid named range name.
- if (str.indexOf(ABSOLUTE_REFERENCE_MARKER) >= 0) {
- // Of course, named range names cannot have '$'
- return NameType.BAD_CELL_OR_NAMED_RANGE;
- }
- return NameType.NAMED_RANGE;
- }
+ /**
+ * 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) {
+ int len = str.length();
+ if (len < 1) {
+ throw new IllegalArgumentException("Empty string not allowed");
+ }
+ char firstChar = str.charAt(0);
+ switch (firstChar) {
+ case ABSOLUTE_REFERENCE_MARKER:
+ case '.':
+ case '_':
+ break;
+ default:
+ if (!Character.isLetter(firstChar)) {
+ throw new IllegalArgumentException("Invalid first char (" + firstChar
+ + ") of cell reference or named range. Letter expected");
+ }
+ }
+ if (!Character.isDigit(str.charAt(len-1))) {
+ // no digits at end of str
+ return validateNamedRangeName(str);
+ }
+ Matcher cellRefPatternMatcher = CELL_REF_PATTERN.matcher(str);
+ if (!cellRefPatternMatcher.matches()) {
+ return validateNamedRangeName(str);
+ }
+ String lettersGroup = cellRefPatternMatcher.group(1);
+ String digitsGroup = cellRefPatternMatcher.group(2);
+ if (cellReferenceIsWithinRange(lettersGroup, digitsGroup)) {
+ // valid cell reference
+ return NameType.CELL;
+ }
+ // If str looks like a cell reference, but is out of (row/col) range, it is a valid
+ // named range name
+ // This behaviour is a little weird. For example, "IW123" is a valid named range name
+ // because the column "IW" is beyond the maximum "IV". Note - this behaviour is version
+ // dependent. In Excel 2007, "IW123" is not a valid named range name.
+ if (str.indexOf(ABSOLUTE_REFERENCE_MARKER) >= 0) {
+ // Of course, named range names cannot have '$'
+ return NameType.BAD_CELL_OR_NAMED_RANGE;
+ }
+ return NameType.NAMED_RANGE;
+ }
- private static int validateNamedRangeName(String str) {
+ private static int validateNamedRangeName(String str) {
if (!NAMED_RANGE_NAME_PATTERN.matcher(str).matches()) {
return NameType.BAD_CELL_OR_NAMED_RANGE;
}
return NameType.NAMED_RANGE;
}
-
-
+
+
/**
* Used to decide whether a name of the form "[A-Z]*[0-9]*" that appears in a formula can be
* interpreted as a cell reference. Names of that form can be also used for sheets and/or
@@ -226,7 +240,7 @@
* <blockquote><table border="0" cellpadding="1" cellspacing="0"
* summary="Notable cases.">
* <tr><th>Input </th>
- * <th>Result </th></tr>
+ * <th>Result </th></tr>
* <tr><td>"A", "1"</td><td>true</td></tr>
* <tr><td>"a", "111"</td><td>true</td></tr>
* <tr><td>"A", "65536"</td><td>true</td></tr>
@@ -275,86 +289,86 @@
}
/**
- * Separates the row from the columns and returns an array of three Strings. The first element
- * is the sheet name. Only the first element may be null. The second element in is the column
- * name still in ALPHA-26 number format. The third element is the row.
- */
- private static String[] separateRefParts(String reference) {
-
- int plingPos = reference.lastIndexOf(SHEET_NAME_DELIMITER);
- String sheetName = parseSheetName(reference, plingPos);
- int start = plingPos+1;
-
- int length = reference.length();
-
-
- int loc = start;
- // skip initial dollars
- if (reference.charAt(loc)==ABSOLUTE_REFERENCE_MARKER) {
- loc++;
- }
- // step over column name chars until first digit (or dollars) for row number.
- for (; loc < length; loc++) {
- char ch = reference.charAt(loc);
- if (Character.isDigit(ch) || ch == ABSOLUTE_REFERENCE_MARKER) {
- break;
- }
- }
- return new String[] {
- sheetName,
- reference.substring(start,loc),
- reference.substring(loc),
- };
- }
-
- private static String parseSheetName(String reference, int indexOfSheetNameDelimiter) {
- if(indexOfSheetNameDelimiter < 0) {
- return null;
- }
-
- boolean isQuoted = reference.charAt(0) == SPECIAL_NAME_DELIMITER;
- if(!isQuoted) {
- return reference.substring(0, indexOfSheetNameDelimiter);
- }
- int lastQuotePos = indexOfSheetNameDelimiter-1;
- if(reference.charAt(lastQuotePos) != SPECIAL_NAME_DELIMITER) {
- throw new RuntimeException("Mismatched quotes: (" + reference + ")");
- }
-
- // TODO - refactor cell reference parsing logic to one place.
- // Current known incarnations:
- // FormulaParser.GetName()
- // CellReference.parseSheetName() (here)
- // AreaReference.separateAreaRefs()
- // SheetNameFormatter.format() (inverse)
-
- StringBuffer sb = new StringBuffer(indexOfSheetNameDelimiter);
-
- for(int i=1; i<lastQuotePos; i++) { // Note boundaries - skip outer quotes
- char ch = reference.charAt(i);
- if(ch != SPECIAL_NAME_DELIMITER) {
- sb.append(ch);
- continue;
- }
- if(i < lastQuotePos) {
- if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
- // two consecutive quotes is the escape sequence for a single one
- i++; // skip this and keep parsing the special name
- sb.append(ch);
- continue;
- }
- }
- throw new RuntimeException("Bad sheet name quote escaping: (" + reference + ")");
- }
- return sb.toString();
- }
-
- /**
- * Takes in a 0-based base-10 column and returns a ALPHA-26
- * representation.
- * eg column #3 -> D
- */
- protected static String convertNumToColString(int col) {
+ * Separates the row from the columns and returns an array of three Strings. The first element
+ * is the sheet name. Only the first element may be null. The second element in is the column
+ * name still in ALPHA-26 number format. The third element is the row.
+ */
+ private static String[] separateRefParts(String reference) {
+
+ int plingPos = reference.lastIndexOf(SHEET_NAME_DELIMITER);
+ String sheetName = parseSheetName(reference, plingPos);
+ int start = plingPos+1;
+
+ int length = reference.length();
+
+
+ int loc = start;
+ // skip initial dollars
+ if (reference.charAt(loc)==ABSOLUTE_REFERENCE_MARKER) {
+ loc++;
+ }
+ // step over column name chars until first digit (or dollars) for row number.
+ for (; loc < length; loc++) {
+ char ch = reference.charAt(loc);
+ if (Character.isDigit(ch) || ch == ABSOLUTE_REFERENCE_MARKER) {
+ break;
+ }
+ }
+ return new String[] {
+ sheetName,
+ reference.substring(start,loc),
+ reference.substring(loc),
+ };
+ }
+
+ private static String parseSheetName(String reference, int indexOfSheetNameDelimiter) {
+ if(indexOfSheetNameDelimiter < 0) {
+ return null;
+ }
+
+ boolean isQuoted = reference.charAt(0) == SPECIAL_NAME_DELIMITER;
+ if(!isQuoted) {
+ return reference.substring(0, indexOfSheetNameDelimiter);
+ }
+ int lastQuotePos = indexOfSheetNameDelimiter-1;
+ if(reference.charAt(lastQuotePos) != SPECIAL_NAME_DELIMITER) {
+ throw new RuntimeException("Mismatched quotes: (" + reference + ")");
+ }
+
+ // TODO - refactor cell reference parsing logic to one place.
+ // Current known incarnations:
+ // FormulaParser.GetName()
+ // CellReference.parseSheetName() (here)
+ // AreaReference.separateAreaRefs()
+ // SheetNameFormatter.format() (inverse)
+
+ StringBuffer sb = new StringBuffer(indexOfSheetNameDelimiter);
+
+ for(int i=1; i<lastQuotePos; i++) { // Note boundaries - skip outer quotes
+ char ch = reference.charAt(i);
+ if(ch != SPECIAL_NAME_DELIMITER) {
+ sb.append(ch);
+ continue;
+ }
+ if(i < lastQuotePos) {
+ if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
+ // two consecutive quotes is the escape sequence for a single one
+ i++; // skip this and keep parsing the special name
+ sb.append(ch);
+ continue;
+ }
+ }
+ throw new RuntimeException("Bad sheet name quote escaping: (" + reference + ")");
+ }
+ return sb.toString();
+ }
+
+ /**
+ * Takes in a 0-based base-10 column and returns a ALPHA-26
+ * representation.
+ * eg column #3 -> D
+ */
+ protected static String convertNumToColString(int col) {
// Excel counts column A as the 1st column, we
// treat it as the 0th one
int excelColNum = col + 1;
@@ -373,35 +387,35 @@
}
return colRef;
- }
+ }
- /**
- * Example return values:
- * <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
- * <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
- * <tr><td>A1</td><td>Cell reference without sheet</td></tr>
- * <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
- * <tr><td>'O''Brien''s Sales'!A1' </td><td>Sheet name with special characters</td></tr>
- * </table>
- * @return the text representation of this cell reference as it would appear in a formula.
- */
- public String formatAsString() {
- StringBuffer sb = new StringBuffer(32);
- if(_sheetName != null) {
- SheetNameFormatter.appendFormat(sb, _sheetName);
- sb.append(SHEET_NAME_DELIMITER);
- }
- appendCellReference(sb);
- return sb.toString();
- }
-
- public String toString() {
- StringBuffer sb = new StringBuffer(64);
- sb.append(getClass().getName()).append(" [");
- sb.append(formatAsString());
- sb.append("]");
- return sb.toString();
- }
+ /**
+ * Example return values:
+ * <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
+ * <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
+ * <tr><td>A1</td><td>Cell reference without sheet</td></tr>
+ * <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
+ * <tr><td>'O''Brien''s Sales'!A1' </td><td>Sheet name with special characters</td></tr>
+ * </table>
+ * @return the text representation of this cell reference as it would appear in a formula.
+ */
+ public String formatAsString() {
+ StringBuffer sb = new StringBuffer(32);
+ if(_sheetName != null) {
+ SheetNameFormatter.appendFormat(sb, _sheetName);
+ sb.append(SHEET_NAME_DELIMITER);
+ }
+ appendCellReference(sb);
+ return sb.toString();
+ }
+
+ public String toString() {
+ StringBuffer sb = new StringBuffer(64);
+ sb.append(getClass().getName()).append(" [");
+ sb.append(formatAsString());
+ sb.append("]");
+ return sb.toString();
+ }
/**
* Returns the three parts of the cell reference, the
@@ -419,18 +433,18 @@
};
}
- /**
- * Appends cell reference with '$' markers for absolute values as required.
- * Sheet name is not included.
- */
- /* package */ void appendCellReference(StringBuffer sb) {
- if(_isColAbs) {
- sb.append(ABSOLUTE_REFERENCE_MARKER);
- }
- sb.append( convertNumToColString(_colIndex));
- if(_isRowAbs) {
- sb.append(ABSOLUTE_REFERENCE_MARKER);
- }
- sb.append(_rowIndex+1);
- }
+ /**
+ * Appends cell reference with '$' markers for absolute values as required.
+ * Sheet name is not included.
+ */
+ /* package */ void appendCellReference(StringBuffer sb) {
+ if(_isColAbs) {
+ sb.append(ABSOLUTE_REFERENCE_MARKER);
+ }
+ sb.append( convertNumToColString(_colIndex));
+ if(_isRowAbs) {
+ sb.append(ABSOLUTE_REFERENCE_MARKER);
+ }
+ sb.append(_rowIndex+1);
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java Fri Sep 5 13:38:51 2008
@@ -40,4 +40,43 @@
assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
assertEquals(3.72, cv.getNumberValue(), 0.0);
}
+
+ public void testFullColumnRefs() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet("Sheet1");
+ HSSFRow row = sheet.createRow(0);
+ HSSFCell cell0 = row.createCell(0);
+ cell0.setCellFormula("sum(D:D)");
+ HSSFCell cell1 = row.createCell(1);
+ cell1.setCellFormula("sum(D:E)");
+
+ // some values in column D
+ setValue(sheet, 1, 3, 5.0);
+ setValue(sheet, 2, 3, 6.0);
+ setValue(sheet, 5, 3, 7.0);
+ setValue(sheet, 50, 3, 8.0);
+
+ // some values in column E
+ setValue(sheet, 1, 4, 9.0);
+ setValue(sheet, 2, 4, 10.0);
+ setValue(sheet, 30000, 4, 11.0);
+
+ // some other values
+ setValue(sheet, 1, 2, 100.0);
+ setValue(sheet, 2, 5, 100.0);
+ setValue(sheet, 3, 6, 100.0);
+
+
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
+ assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0);
+ assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0);
+ }
+
+ private static void setValue(HSSFSheet sheet, int rowIndex, int colIndex, double value) {
+ HSSFRow row = sheet.getRow(rowIndex);
+ if (row == null) {
+ row = sheet.createRow(rowIndex);
+ }
+ row.createCell(colIndex).setCellValue(value);
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java?rev=692538&r1=692537&r2=692538&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java Fri Sep 5 13:38:51 2008
@@ -83,7 +83,7 @@
public void testReferenceWithSheet() {
AreaReference ar;
- ar = new AreaReference("Tabelle1!B5");
+ ar = new AreaReference("Tabelle1!B5:B5");
assertTrue(ar.isSingleCell());
TestCellReference.confirmCell(ar.getFirstCell(), "Tabelle1", 4, 1, false, false, "Tabelle1!B5");
@@ -113,11 +113,11 @@
}
}
- public void testContiguousReferences() throws Exception {
- String refSimple = "$C$10";
+ public void testContiguousReferences() {
+ String refSimple = "$C$10:$C$10";
String ref2D = "$C$10:$D$11";
- String refDCSimple = "$C$10,$D$12,$E$14";
- String refDC2D = "$C$10:$C$11,$D$12,$E$14:$E$20";
+ String refDCSimple = "$C$10:$C$10,$D$12:$D$12,$E$14:$E$14";
+ String refDC2D = "$C$10:$C$11,$D$12:$D$12,$E$14:$E$20";
String refDC3D = "Tabelle1!$C$10:$C$14,Tabelle1!$D$10:$D$12";
// Check that we detect as contiguous properly
@@ -243,16 +243,16 @@
private static void confirmResolveCellRef(HSSFWorkbook wb, CellReference cref) {
HSSFSheet s = wb.getSheet(cref.getSheetName());
HSSFRow r = s.getRow(cref.getRow());
- HSSFCell c = r.getCell(cref.getCol());
+ HSSFCell c = r.getCell((int)cref.getCol());
assertNotNull(c);
}
public void testSpecialSheetNames() {
AreaReference ar;
- ar = new AreaReference("'Sheet A'!A1");
+ ar = new AreaReference("'Sheet A'!A1:A1");
confirmAreaSheetName(ar, "Sheet A", "'Sheet A'!A1");
- ar = new AreaReference("'Hey! Look Here!'!A1");
+ ar = new AreaReference("'Hey! Look Here!'!A1:A1");
confirmAreaSheetName(ar, "Hey! Look Here!", "'Hey! Look Here!'!A1");
ar = new AreaReference("'O''Toole'!A1:B2");
@@ -268,7 +268,24 @@
assertEquals(expectedFullText, ar.formatAsString());
}
- public static void main(String[] args) {
- junit.textui.TestRunner.run(TestAreaReference.class);
- }
+ public void testWholeColumnRefs() {
+ confirmWholeColumnRef("A:A", 0, 0, false, false);
+ confirmWholeColumnRef("$C:D", 2, 3, true, false);
+ confirmWholeColumnRef("AD:$AE", 29, 30, false, true);
+
+ }
+
+ private static void confirmWholeColumnRef(String ref, int firstCol, int lastCol, boolean firstIsAbs, boolean lastIsAbs) {
+ AreaReference ar = new AreaReference(ref);
+ confirmCell(ar.getFirstCell(), 0, firstCol, true, firstIsAbs);
+ confirmCell(ar.getLastCell(), 0xFFFF, lastCol, true, lastIsAbs);
+ }
+
+ private static void confirmCell(CellReference cell, int row, int col, boolean isRowAbs,
+ boolean isColAbs) {
+ assertEquals(row, cell.getRow());
+ assertEquals(col, cell.getCol());
+ assertEquals(isRowAbs, cell.isRowAbsolute());
+ assertEquals(isColAbs, cell.isColAbsolute());
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org