Author: nick
Date: Wed Feb 20 10:14:30 2008
New Revision: 629552
URL: http://svn.apache.org/viewvc?rev=629552&view=rev
Log:
Patch from Josh from bug #44403 - Further support for unusual, but valid,
arguments to the Mid function
Added:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/EvaluationException.java
(with props)
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
(with props)
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
(with props)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/hslf/index.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.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=629552&r1=629551&r2=629552&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Wed Feb 20 10:14:30
2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44403 - Further support for
unusual, but valid, arguments to the Mid function</action>
<action dev="POI-DEVELOPERS" type="fix">44410 - Support for
whole-column ranges, such as C:C, in formula strings and the formula
evaluator</action>
<action dev="POI-DEVELOPERS" type="fix">44421 - Update Match
function to properly support Area references</action>
<action dev="POI-DEVELOPERS" type="fix">44417 - Improved handling
of references for the need to quote the sheet name for some formulas, but not
when fetching a sheet by name</action>
Modified: poi/trunk/src/documentation/content/xdocs/hslf/index.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/hslf/index.xml?rev=629552&r1=629551&r2=629552&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/hslf/index.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/hslf/index.xml Wed Feb 20
10:14:30 2008
@@ -45,8 +45,8 @@
<link
href="http://svn.apache.org/viewcvs.cgi/poi/trunk/src/scratchpad/";>scratchpad
area</link>
of the POI SVN repository.
Ensure that you have the scratchpad jar or the
scratchpad
- build area in your
- classpath before experimenting with this code.
+ build area in your classpath before experimenting with
+ this code - the main POI jar is not enough.
</note>
<p>The <link href="./quick-guide.html">quick
guide</link> documentation provides
information on using this API. Comments and fixes gratefully
accepted on the POI
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=629552&r1=629551&r2=629552&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Wed Feb 20 10:14:30
2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44403 - Further support for
unusual, but valid, arguments to the Mid function</action>
<action dev="POI-DEVELOPERS" type="fix">44410 - Support for
whole-column ranges, such as C:C, in formula strings and the formula
evaluator</action>
<action dev="POI-DEVELOPERS" type="fix">44421 - Update Match
function to properly support Area references</action>
<action dev="POI-DEVELOPERS" type="fix">44417 - Improved handling
of references for the need to quote the sheet name for some formulas, but not
when fetching a sheet by name</action>
Added:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/EvaluationException.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/EvaluationException.java?rev=629552&view=auto
==============================================================================
---
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/EvaluationException.java
(added)
+++
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/EvaluationException.java
Wed Feb 20 10:14:30 2008
@@ -0,0 +1,134 @@
+/* ====================================================================
+ 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.hssf.record.formula.eval;
+
+/**
+ * This class is used to simplify error handling logic <i>within</i> operator
and function
+ * implementations. Note - <tt>OperationEval.evaluate()</tt> and
<tt>Function.evaluate()</tt>
+ * method signatures do not throw this exception so it cannot propagate
outside.<p/>
+ *
+ * Here is an example coded without <tt>EvaluationException</tt>, to show how
it can help:
+ * <pre>
+ * public Eval evaluate(Eval[] args, int srcRow, short srcCol) {
+ * // ...
+ * Eval arg0 = args[0];
+ * if(arg0 instanceof ErrorEval) {
+ * return arg0;
+ * }
+ * if(!(arg0 instanceof AreaEval)) {
+ * return ErrorEval.VALUE_INVALID;
+ * }
+ * double temp = 0;
+ * AreaEval area = (AreaEval)arg0;
+ * ValueEval[] values = area.getValues();
+ * for (int i = 0; i < values.length; i++) {
+ * ValueEval ve = values[i];
+ * if(ve instanceof ErrorEval) {
+ * return ve;
+ * }
+ * if(!(ve instanceof NumericValueEval)) {
+ * return ErrorEval.VALUE_INVALID;
+ * }
+ * temp += ((NumericValueEval)ve).getNumberValue();
+ * }
+ * // ...
+ * }
+ * </pre>
+ * In this example, if any error is encountered while processing the
arguments, an error is
+ * returned immediately. This code is difficult to refactor due to all the
points where errors
+ * are returned.<br/>
+ * Using <tt>EvaluationException</tt> allows the error returning code to be
consolidated to one
+ * place.<p/>
+ * <pre>
+ * public Eval evaluate(Eval[] args, int srcRow, short srcCol) {
+ * try {
+ * // ...
+ * AreaEval area = getAreaArg(args[0]);
+ * double temp = sumValues(area.getValues());
+ * // ...
+ * } catch (EvaluationException e) {
+ * return e.getErrorEval();
+ * }
+ *}
+ *
+ *private static AreaEval getAreaArg(Eval arg0) throws EvaluationException {
+ * if (arg0 instanceof ErrorEval) {
+ * throw new EvaluationException((ErrorEval) arg0);
+ * }
+ * if (arg0 instanceof AreaEval) {
+ * return (AreaEval) arg0;
+ * }
+ * throw EvaluationException.invalidValue();
+ *}
+ *
+ *private double sumValues(ValueEval[] values) throws EvaluationException {
+ * double temp = 0;
+ * for (int i = 0; i < values.length; i++) {
+ * ValueEval ve = values[i];
+ * if (ve instanceof ErrorEval) {
+ * throw new EvaluationException((ErrorEval) ve);
+ * }
+ * if (!(ve instanceof NumericValueEval)) {
+ * throw EvaluationException.invalidValue();
+ * }
+ * temp += ((NumericValueEval) ve).getNumberValue();
+ * }
+ * return temp;
+ *}
+ * </pre>
+ * It is not mandatory to use EvaluationException, doing so might give the
following advantages:<br/>
+ * - Methods can more easily be extracted, allowing for re-use.<br/>
+ * - Type management (typecasting etc) is simpler because error conditions
have been separated from
+ * intermediate calculation values.<br/>
+ * - Fewer local variables are required. Local variables can have stronger
types.<br/>
+ * - It is easier to mimic common Excel error handling behaviour (exit upon
encountering first
+ * error), because exceptions conveniently propagate up the call stack
regardless of execution
+ * points or the number of levels of nested calls.<p/>
+ *
+ * <b>Note</b> - Only standard evaluation errors are represented by
<tt>EvaluationException</tt> (
+ * i.e. conditions expected to be encountered when evaluating arbitrary Excel
formulas). Conditions
+ * that could never occur in an Excel spreadsheet should result in runtime
exceptions. Care should
+ * be taken to not translate any POI internal error into an Excel evaluation
error code.
+ *
+ * @author Josh Micich
+ */
+public final class EvaluationException extends Exception {
+ private final ErrorEval _errorEval;
+
+ public EvaluationException(ErrorEval errorEval) {
+ _errorEval = errorEval;
+ }
+ // some convenience factory methods
+
+ /** <b>#VALUE!</b> - Wrong type of operand */
+ public static EvaluationException invalidValue() {
+ return new EvaluationException(ErrorEval.VALUE_INVALID);
+ }
+ /** <b>#REF!</b> - Illegal or deleted cell reference */
+ public static EvaluationException invalidRef() {
+ return new EvaluationException(ErrorEval.REF_INVALID);
+ }
+ /** <b>#NUM!</b> - Value range overflow */
+ public static EvaluationException numberError() {
+ return new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+
+ public ErrorEval getErrorEval() {
+ return _errorEval;
+ }
+}
Propchange:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/EvaluationException.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/EvaluationException.java
------------------------------------------------------------------------------
svn:executable = *
Added:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java?rev=629552&view=auto
==============================================================================
---
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
(added)
+++
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
Wed Feb 20 10:14:30 2008
@@ -0,0 +1,249 @@
+/* ====================================================================
+ 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.hssf.record.formula.eval;
+
+
+/**
+ * Provides functionality for evaluating arguments to functions and operators.
+ *
+ * @author Josh Micich
+ */
+public final class OperandResolver {
+
+ private OperandResolver() {
+ // no instances of this class
+ }
+
+ /**
+ * Retrieves a single value from a variety of different argument types
according to standard
+ * Excel rules. Does not perform any type conversion.
+ * @param arg the evaluated argument as passed to the function or
operator.
+ * @param srcCellRow used when arg is a single column AreaRef
+ * @param srcCellCol used when arg is a single row AreaRef
+ * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>,
<tt>BoolEval</tt> or <tt>BlankEval</tt>.
+ * Never <code>null</code> or <tt>ErrorEval</tt>.
+ * @throws EvaluationException(#VALUE!) if srcCellRow or srcCellCol do
not properly index into
+ * an AreaEval. If the actual value retrieved is an ErrorEval, a
corresponding
+ * EvaluationException is thrown.
+ */
+ public static ValueEval getSingleValue(Eval arg, int srcCellRow, short
srcCellCol)
+ throws EvaluationException {
+ if (arg instanceof RefEval) {
+ RefEval re = (RefEval) arg;
+ return re.getInnerValueEval();
+ }
+ Eval result;
+ if (arg instanceof AreaEval) {
+ result = chooseSingleElementFromArea((AreaEval) arg,
srcCellRow, srcCellCol);
+ } else {
+ result = arg;
+ }
+ if (result instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) result);
+ }
+ if (result instanceof ValueEval) {
+ return (ValueEval) result;
+ }
+ throw new RuntimeException("Unexpected eval type (" +
result.getClass().getName() + ")");
+ }
+
+ /**
+ * Implements (some perhaps not well known) Excel functionality to
select a single cell from an
+ * area depending on the coordinates of the calling cell. Here is an
example demonstrating
+ * both selection from a single row area and a single column area in
the same formula.
+ *
+ * <table border="1" cellpadding="1" cellspacing="1" summary="sample
spreadsheet">
+ *
<tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr>
+ *
<tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr>
+ *
<tr><th>2</th><td> </td><td> </td><td> </td><td>200</td></tr>
+ *
<tr><th>3</th><td> </td><td> </td><td> </td><td>300</td></tr>
+ *
<tr><th>3</th><td> </td><td> </td><td> </td><td>400</td></tr>
+ * </table>
+ *
+ * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2
to C4, the spreadsheet
+ * will look like this:
+ *
+ * <table border="1" cellpadding="1" cellspacing="1" summary="sample
spreadsheet">
+ *
<tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr>
+ *
<tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr>
+ *
<tr><th>2</th><td>1215</td><td>1220</td><td>#VALUE!</td><td>200</td></tr>
+ *
<tr><th>3</th><td>1315</td><td>1320</td><td>#VALUE!</td><td>300</td></tr>
+ *
<tr><th>4</th><td>#VALUE!</td><td>#VALUE!</td><td>#VALUE!</td><td>400</td></tr>
+ * </table>
+ *
+ * Note that the row area (A1:B1) does not include column C and the
column area (D2:D3) does
+ * not include row 4, so the values in C1(=25) and D4(=400) are not
accessible to the formula
+ * as written, but in the 4 cells A2:B3, the row and column selection
works ok.<p/>
+ *
+ * The same concept is extended to references across sheets, such that
even multi-row,
+ * multi-column areas can be useful.<p/>
+ *
+ * Of course with carefully (or carelessly) chosen parameters, cyclic
references can occur and
+ * hence this method <b>can</b> throw a 'circular reference'
EvaluationException. Note that
+ * this method does not attempt to detect cycles. Every cell in the
specified Area <tt>ae</tt>
+ * has already been evaluated prior to this method call. Any cell (or
cell<b>s</b>) part of
+ * <tt>ae</tt> that would incur a cyclic reference error if selected by
this method, will
+ * already have the value <t>ErrorEval.CIRCULAR_REF_ERROR</tt> upon
entry to this method. It
+ * is assumed logic exists elsewhere to produce this behaviour.
+ *
+ * @return whatever the selected cell's evaluated value is. Never
<code>null</code>. Never
+ * <tt>ErrorEval</tt>.
+ * @throws EvaluationException if there is a problem with indexing into
the area, or if the
+ * evaluated cell has an error.
+ */
+ public static ValueEval chooseSingleElementFromArea(AreaEval ae,
+ int srcCellRow, short srcCellCol) throws
EvaluationException {
+ ValueEval result = chooseSingleElementFromAreaInternal(ae,
srcCellRow, srcCellCol);
+ if(result == null) {
+ // This seems to be required because AreaEval.values()
array may contain nulls.
+ // perhaps that should not be allowed.
+ result = BlankEval.INSTANCE;
+ }
+ if (result instanceof ErrorEval) {
+ throw new EvaluationException((ErrorEval) result);
+
+ }
+ return result;
+ }
+
+ /**
+ * @return possibly <tt>ErrorEval</tt>, and <code>null</code>
+ */
+ private static ValueEval chooseSingleElementFromAreaInternal(AreaEval
ae,
+ int srcCellRow, short srcCellCol) throws
EvaluationException {
+
+ if(false) {
+ // this is too simplistic
+ if(ae.containsRow(srcCellRow) &&
ae.containsColumn(srcCellCol)) {
+ throw new
EvaluationException(ErrorEval.CIRCULAR_REF_ERROR);
+ }
+ /*
+ Circular references are not dealt with directly here, but it is
worth noting some issues.
+
+ ANY one of the return statements in this method could return a
cell that is identical
+ to the one immediately being evaluated. The evaluating cell is
identified by srcCellRow,
+ srcCellRow AND sheet. The sheet is not available in any nearby
calling method, so that's
+ one reason why circular references are not easy to detect here.
(The sheet of the returned
+ cell can be obtained from ae if it is an Area3DEval.)
+
+ Another reason there's little value in attempting to detect
circular references here is
+ that only direct circular references could be detected. If the
cycle involved two or more
+ cells this method could not detect it.
+
+ Logic to detect evaluation cycles of all kinds has been coded
in EvaluationCycleDetector
+ (and HSSFFormulaEvaluator).
+ */
+ }
+
+ if (ae.isColumn()) {
+ if(ae.isRow()) {
+ return ae.getValues()[0];
+ }
+ if(!ae.containsRow(srcCellRow)) {
+ throw EvaluationException.invalidValue();
+ }
+ return ae.getValueAt(srcCellRow, ae.getFirstColumn());
+ }
+ if(!ae.isRow()) {
+ // multi-column, multi-row area
+ if(ae.containsRow(srcCellRow) &&
ae.containsColumn(srcCellCol)) {
+ return ae.getValueAt(ae.getFirstRow(),
ae.getFirstColumn());
+ }
+ throw EvaluationException.invalidValue();
+ }
+ if(!ae.containsColumn(srcCellCol)) {
+ throw EvaluationException.invalidValue();
+ }
+ return ae.getValueAt(ae.getFirstRow(), srcCellCol);
+ }
+
+ /**
+ * Applies some conversion rules if the supplied value is not already
an integer.<br/>
+ * Value is first coerced to a <tt>double</tt> ( See
<tt>coerceValueToDouble()</tt> ).<p/>
+ *
+ * Excel typically converts doubles to integers by truncating toward
negative infinity.<br/>
+ * The equivalent java code is:<br/>
+ * <code>return (int)Math.floor(d);</code><br/>
+ * <b>not</b>:<br/>
+ * <code>return (int)d; // wrong - rounds toward
zero</code>
+ *
+ */
+ public static int coerceValueToInt(ValueEval ev) throws
EvaluationException {
+ double d = coerceValueToDouble(ev);
+ // Note - the standard java type conversion from double to int
truncates toward zero.
+ // but Math.floor() truncates toward negative infinity
+ return (int)Math.floor(d);
+ }
+
+ /**
+ * Applies some conversion rules if the supplied value is not already a
number.
+ * Note - <tt>BlankEval</tt> is not supported and must be handled by
the caller.
+ * @param ev must be a <tt>NumberEval</tt>, <tt>StringEval</tt> or
<tt>BoolEval</tt>
+ * @return actual, parsed or interpreted double value (respectively).
+ * @throws EvaluationException(#VALUE!) only if a StringEval is
supplied and cannot be parsed
+ * as a double (See <tt>parseDouble()</tt> for allowable formats).
+ * @throws RuntimeException if the supplied parameter is not
<tt>NumberEval</tt>,
+ * <tt>StringEval</tt> or <tt>BoolEval</tt>
+ */
+ public static double coerceValueToDouble(ValueEval ev) throws
EvaluationException {
+
+ if (ev instanceof NumericValueEval) {
+ // this also handles booleans
+ return ((NumericValueEval)ev).getNumberValue();
+ }
+ if (ev instanceof StringEval) {
+ Double dd = parseDouble(((StringEval) ev).getStringValue());
+ if (dd == null) {
+ throw EvaluationException.invalidValue();
+ }
+ return dd.doubleValue();
+ }
+ throw new RuntimeException("Unexpected arg eval type (" +
ev.getClass().getName() + ")");
+ }
+
+ /**
+ * Converts a string to a double using standard rules that Excel would
use.<br/>
+ * Tolerates currency prefixes, commas, leading and trailing spaces.<p/>
+ *
+ * Some examples:<br/>
+ * " 123 " -> 123.0<br/>
+ * ".123" -> 0.123<br/>
+ * " $ 1,000.00 " -> 1000.0<br/>
+ * "$1.25E4" -> 12500.0<br/>
+ *
+ * @param text
+ * @return <code>null</code> if the specified text cannot be parsed as
a number
+ */
+ public static Double parseDouble(String text) {
+ if(!Character.isDigit(text.charAt(0))) {
+ // avoid using NumberFormatException to tell when
string is not a number
+ return null;
+ }
+ // TODO - support notation like '1E3' (==1000)
+
+ double val;
+ try {
+ val = Double.parseDouble(text);
+ } catch (NumberFormatException e) {
+ return null;
+ }
+ return new Double(val);
+ }
+
+
+}
Propchange:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/OperandResolver.java
------------------------------------------------------------------------------
svn:executable = *
Modified:
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java?rev=629552&r1=629551&r2=629552&view=diff
==============================================================================
---
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java
(original)
+++
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java
Wed Feb 20 10:14:30 2008
@@ -1,99 +1,100 @@
/*
-* 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.
-*/
-/*
- * Created on May 15, 2005
+ * 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.hssf.record.formula.functions;
import org.apache.poi.hssf.record.formula.eval.BlankEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.Eval;
-import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.StringValueEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
/**
- * An implementation of the MID function:
- * Returns a specific number of characters from a text string,
- * starting at the position you specify, based on the number
- * of characters you specify.
+ * An implementation of the MID function<br/> MID returns a specific number of
+ * characters from a text string, starting at the specified position.<p/>
+ *
+ * <b>Syntax<b>:<br/> <b>MID</b>(<b>text</b>, <b>start_num</b>,
+ * <b>num_chars</b>)<br/>
+ *
* @author Manda Wilson < wilson at c bio dot msk cc dot org >
*/
public class Mid extends TextFunction {
/**
- * Returns a specific number of characters from a text string,
- * starting at the position you specify, based on the number
- * of characters you specify.
+ * Returns a specific number of characters from a text string, starting
at
+ * the position you specify, based on the number of characters you
specify.
*
* @see org.apache.poi.hssf.record.formula.eval.Eval
*/
- public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
- Eval retval = null;
- String str = null;
- int startNum = 0;
- int numChars = 0;
-
- switch (operands.length) {
- default:
- retval = ErrorEval.VALUE_INVALID;
- case 3:
- // first operand is text string containing characters
to extract
- // second operand is position of first character to extract
- // third operand is the number of characters to return
- ValueEval firstveval = singleOperandEvaluate(operands[0],
srcCellRow, srcCellCol);
- ValueEval secondveval = singleOperandEvaluate(operands[1],
srcCellRow, srcCellCol);
- ValueEval thirdveval = singleOperandEvaluate(operands[2],
srcCellRow, srcCellCol);
- if (firstveval instanceof StringValueEval
- && secondveval instanceof NumericValueEval
- && thirdveval instanceof NumericValueEval) {
-
- StringValueEval strEval = (StringValueEval) firstveval;
- str = strEval.getStringValue();
-
- NumericValueEval startNumEval = (NumericValueEval)
secondveval;
- // NOTE: it is safe to cast to int here
- // because in Excel =MID("test", 1, 1.7) returns t
- // so 1.7 must be truncated to 1
- // and =MID("test", 1.9, 2) returns te
- // so 1.9 must be truncated to 1
- startNum = (int) startNumEval.getNumberValue();
-
- NumericValueEval numCharsEval = (NumericValueEval)
thirdveval;
- numChars = (int) numCharsEval.getNumberValue();
-
- } else {
- retval = ErrorEval.VALUE_INVALID;
- }
- }
-
- if (retval == null) {
- if (startNum < 1 || numChars < 0) {
- retval = ErrorEval.VALUE_INVALID;
- } else if (startNum > str.length() || numChars == 0) {
- retval = BlankEval.INSTANCE;
- } else if (startNum + numChars > str.length()) {
- retval = new StringEval(str.substring(startNum
- 1));
- } else {
- retval = new StringEval(str.substring(startNum
- 1, (numChars + startNum - 1)));
- }
- }
- return retval;
- }
+ public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
+ if (args.length != 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ String text;
+ int startIx; // zero based
+ int numChars;
+
+ try {
+ text = evaluateTextArg(args[0], srcCellRow, srcCellCol);
+ int startCharNum = evaluateNumberArg(args[1],
srcCellRow, srcCellCol);
+ numChars = evaluateNumberArg(args[2], srcCellRow,
srcCellCol);
+ startIx = startCharNum - 1; // convert to zero-based
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+
+ int len = text.length();
+ if (startIx < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ if (numChars < 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ if (numChars < 0 || startIx > len) {
+ return new StringEval("");
+ }
+ int endIx = startIx + numChars;
+ if (endIx > len) {
+ endIx = len;
+ }
+ String result = text.substring(startIx, endIx);
+ return new StringEval(result);
+
+ }
+
+ public static int evaluateNumberArg(Eval arg, int srcCellRow, short
srcCellCol) throws EvaluationException {
+ ValueEval ev = OperandResolver.getSingleValue(arg, srcCellRow,
srcCellCol);
+ if (ev instanceof BlankEval) {
+ // Note - for start_num arg, blank causes
error(#VALUE!),
+ // but for num_chars causes empty string to be returned.
+ return 0;
+ }
+
+ return OperandResolver.coerceValueToInt(ev);
+ }
-}
+ private static String evaluateTextArg(Eval arg, int srcCellRow, short
srcCellCol) throws EvaluationException {
+ ValueEval ev = OperandResolver.getSingleValue(arg, srcCellRow,
srcCellCol);
+ if (ev instanceof StringValueEval) {
+ return ((StringValueEval) ev).getStringValue();
+ }
+ throw EvaluationException.invalidValue();
+ }
+}
\ No newline at end of file
Modified:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java?rev=629552&r1=629551&r2=629552&view=diff
==============================================================================
---
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
(original)
+++
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
Wed Feb 20 10:14:30 2008
@@ -35,6 +35,7 @@
result.addTestSuite(TestDate.class);
result.addTestSuite(TestFinanceLib.class);
result.addTestSuite(TestIndex.class);
+ result.addTestSuite(TestMid.class);
result.addTestSuite(TestMathX.class);
result.addTestSuite(TestMatch.class);
result.addTestSuite(TestRowCol.class);
Added:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java?rev=629552&view=auto
==============================================================================
---
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
(added)
+++
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
Wed Feb 20 10:14:30 2008
@@ -0,0 +1,114 @@
+/* ====================================================================
+ 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.hssf.record.formula.functions;
+
+import org.apache.poi.hssf.record.formula.AreaPtg;
+import org.apache.poi.hssf.record.formula.ReferencePtg;
+import org.apache.poi.hssf.record.formula.eval.Area2DEval;
+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;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
+import org.apache.poi.hssf.record.formula.eval.RefEval;
+import org.apache.poi.hssf.record.formula.eval.StringEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+
+import junit.framework.TestCase;
+/**
+ * Tests for Excel function MID()
+ *
+ * @author Josh Micich
+ */
+public final class TestMid extends TestCase {
+
+
+ private static Eval invokeMid(Eval text, Eval startPos, Eval numChars) {
+ Eval[] args = new Eval[] { text, startPos, numChars, };
+ return new Mid().evaluate(args, -1, (short)-1);
+ }
+
+ private void confirmMid(Eval text, Eval startPos, Eval numChars, String
expected) {
+ Eval result = invokeMid(text, startPos, numChars);
+ assertEquals(StringEval.class, result.getClass());
+ assertEquals(expected, ((StringEval)result).getStringValue());
+ }
+
+ private void confirmMid(Eval text, Eval startPos, Eval numChars,
ErrorEval expectedError) {
+ Eval result = invokeMid(text, startPos, numChars);
+ assertEquals(ErrorEval.class, result.getClass());
+ assertEquals(expectedError.getErrorCode(),
((ErrorEval)result).getErrorCode());
+ }
+
+ public void testBasic() {
+
+ confirmMid(new StringEval("galactic"), new NumberEval(3), new
NumberEval(4), "lact");
+ }
+
+ /**
+ * Valid cases where args are not precisely (string, int, int) but can
be resolved OK.
+ */
+ public void testUnusualArgs() {
+ // startPos with fractional digits
+ confirmMid(new StringEval("galactic"), new NumberEval(3.1), new
NumberEval(4), "lact");
+
+ // string startPos
+ confirmMid(new StringEval("galactic"), new StringEval("3"), new
NumberEval(4), "lact");
+
+ // text (first) arg type is number, other args are strings with
fractional digits
+ confirmMid(new NumberEval(123456), new StringEval("3.1"), new
StringEval("2.9"), "34");
+
+ // startPos is 1x1 area ref, numChars is cell ref
+ AreaEval aeStart = new Area2DEval(new AreaPtg("A1:A1"), new
ValueEval[] { new NumberEval(2), } );
+ RefEval reNumChars = new Ref2DEval(new ReferencePtg("B1"), new
NumberEval(3),false);
+ confirmMid(new StringEval("galactic"), aeStart, reNumChars,
"ala");
+
+ confirmMid(new StringEval("galactic"), new NumberEval(3.1),
BlankEval.INSTANCE, "");
+
+ confirmMid(new StringEval("galactic"), new NumberEval(3),
BoolEval.FALSE, "");
+ confirmMid(new StringEval("galactic"), new NumberEval(3),
BoolEval.TRUE, "l");
+
+ }
+
+ /**
+ * Extreme values for startPos and numChars
+ */
+ public void testExtremes() {
+ confirmMid(new StringEval("galactic"), new NumberEval(4), new
NumberEval(400), "actic");
+
+ confirmMid(new StringEval("galactic"), new NumberEval(30), new
NumberEval(4), "");
+ confirmMid(new StringEval("galactic"), new NumberEval(3), new
NumberEval(0), "");
+ }
+
+ /**
+ * All sorts of ways to make MID return defined errors.
+ */
+ public void testErrors() {
+ confirmMid(ErrorEval.NAME_INVALID, new NumberEval(3), new
NumberEval(4), ErrorEval.NAME_INVALID);
+ confirmMid(new StringEval("galactic"), ErrorEval.NAME_INVALID,
new NumberEval(4), ErrorEval.NAME_INVALID);
+ confirmMid(new StringEval("galactic"), new NumberEval(3),
ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID);
+ confirmMid(new StringEval("galactic"), ErrorEval.DIV_ZERO,
ErrorEval.NAME_INVALID, ErrorEval.DIV_ZERO);
+
+ confirmMid(new StringEval("galactic"), BlankEval.INSTANCE, new
NumberEval(3.1), ErrorEval.VALUE_INVALID);
+
+ confirmMid(new StringEval("galactic"), new NumberEval(0), new
NumberEval(4), ErrorEval.VALUE_INVALID);
+ confirmMid(new StringEval("galactic"), new NumberEval(1), new
NumberEval(-1), ErrorEval.VALUE_INVALID);
+ }
+}
Propchange:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java
------------------------------------------------------------------------------
svn:executable = *
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
