You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2007/12/21 13:24:54 UTC
svn commit: r606172 - in /poi/trunk/src: documentation/content/xdocs/
scratchpad/src/org/apache/poi/hssf/record/formula/functions/
scratchpad/testcases/org/apache/poi/hssf/data/
Author: nick
Date: Fri Dec 21 04:24:54 2007
New Revision: 606172
URL: http://svn.apache.org/viewvc?rev=606172&view=rev
Log:
Support for Mid, Replace and Substitute excel functions (bug #s 44095, 44097, 44099)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.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/src/org/apache/poi/hssf/record/formula/functions/Replace.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Substitute.java
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=606172&r1=606171&r2=606172&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Fri Dec 21 04:24:54 2007
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.0.2-FINAL" date="2007-??-??">
+ <action dev="POI-DEVELOPERS" type="add">44095, 44097, 44099 - [PATCH] Support for Mid, Replace and Substitute excel functions</action>
<action dev="POI-DEVELOPERS" type="add">44055 - [PATCH] Support for getting the from field from HSMF messages</action>
<action dev="POI-DEVELOPERS" type="add">43551 - [PATCH] Support for 1904 date windowing in HSSF (previously only supported 1900 date windowing)</action>
<action dev="POI-DEVELOPERS" type="add">41064 - [PATCH] Support for String continue records</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=606172&r1=606171&r2=606172&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Dec 21 04:24:54 2007
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.0.2-FINAL" date="2007-??-??">
+ <action dev="POI-DEVELOPERS" type="add">44095, 44097, 44099 - [PATCH] Support for Mid, Replace and Substitute excel functions</action>
<action dev="POI-DEVELOPERS" type="add">44055 - [PATCH] Support for getting the from field from HSMF messages</action>
<action dev="POI-DEVELOPERS" type="add">43551 - [PATCH] Support for 1904 date windowing in HSSF (previously only supported 1900 date windowing)</action>
<action dev="POI-DEVELOPERS" type="add">41064 - [PATCH] Support for String continue records</action>
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=606172&r1=606171&r2=606172&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 Fri Dec 21 04:24:54 2007
@@ -20,6 +20,80 @@
*/
package org.apache.poi.hssf.record.formula.functions;
-public class Mid extends NotImplementedFunction {
+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.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.
+ * @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.
+ *
+ * @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));
+ }
+ }
+ return retval;
+ }
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Replace.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Replace.java?rev=606172&r1=606171&r2=606172&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Replace.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Replace.java Fri Dec 21 04:24:54 2007
@@ -20,6 +20,93 @@
*/
package org.apache.poi.hssf.record.formula.functions;
-public class Replace extends NotImplementedFunction {
+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.StringEval;
+import org.apache.poi.hssf.record.formula.eval.StringValueEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+
+/**
+ * An implementation of the REPLACE function:
+ * Replaces part of a text string based on the number of characters
+ * you specify, with another text string.
+ * @author Manda Wilson < wilson at c bio dot msk cc dot org >
+ */
+public class Replace extends TextFunction {
+
+ /**
+ * Replaces part of a text string based on the number of characters
+ * you specify, with another text string.
+ *
+ * @see org.apache.poi.hssf.record.formula.eval.Eval
+ */
+ public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
+ Eval retval = null;
+ String oldStr = null;
+ String newStr = null;
+ int startNum = 0;
+ int numChars = 0;
+
+ switch (operands.length) {
+ default:
+ retval = ErrorEval.VALUE_INVALID;
+ case 4:
+ // first operand is text string containing characters to replace
+ // second operand is position of first character to replace
+ // third operand is the number of characters in the old string
+ // you want to replace with new string
+ // fourth operand is the new string
+ ValueEval firstveval = singleOperandEvaluate(operands[0], srcCellRow, srcCellCol);
+ ValueEval secondveval = singleOperandEvaluate(operands[1], srcCellRow, srcCellCol);
+ ValueEval thirdveval = singleOperandEvaluate(operands[2], srcCellRow, srcCellCol);
+ ValueEval fourthveval = singleOperandEvaluate(operands[3], srcCellRow, srcCellCol);
+ if (firstveval instanceof StringValueEval
+ && secondveval instanceof NumericValueEval
+ && thirdveval instanceof NumericValueEval
+ && fourthveval instanceof StringValueEval) {
+
+ StringValueEval oldStrEval = (StringValueEval) firstveval;
+ oldStr = oldStrEval.getStringValue();
+
+ NumericValueEval startNumEval = (NumericValueEval) secondveval;
+ // NOTE: it is safe to cast to int here
+ // because in Excel =REPLACE("task", 2.7, 3, "est")
+ // returns test
+ // so 2.7 must be truncated to 2
+ // and =REPLACE("task", 1, 1.9, "") returns ask
+ // so 1.9 must be truncated to 1
+ startNum = (int) startNumEval.getNumberValue();
+
+ NumericValueEval numCharsEval = (NumericValueEval) thirdveval;
+ numChars = (int) numCharsEval.getNumberValue();
+
+ StringValueEval newStrEval = (StringValueEval) fourthveval;
+ newStr = newStrEval.getStringValue();
+ } else {
+ retval = ErrorEval.VALUE_INVALID;
+ }
+ }
+
+ if (retval == null) {
+ if (startNum < 1 || numChars < 0) {
+ retval = ErrorEval.VALUE_INVALID;
+ } else {
+ StringBuffer strBuff = new StringBuffer(oldStr);
+ // remove any characters that should be replaced
+ if (startNum <= oldStr.length() && numChars != 0) {
+ strBuff.delete(startNum - 1, startNum - 1 + numChars);
+ }
+ // now insert (or append) newStr
+ if (startNum > strBuff.length()) {
+ strBuff.append(newStr);
+ } else {
+ strBuff.insert(startNum - 1, newStr);
+ }
+ retval = new StringEval(strBuff.toString());
+ }
+ }
+ return retval;
+ }
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Substitute.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Substitute.java?rev=606172&r1=606171&r2=606172&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Substitute.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Substitute.java Fri Dec 21 04:24:54 2007
@@ -20,6 +20,98 @@
*/
package org.apache.poi.hssf.record.formula.functions;
-public class Substitute extends NotImplementedFunction {
+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.StringEval;
+import org.apache.poi.hssf.record.formula.eval.StringValueEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+/**
+ * An implementation of the SUBSTITUTE function:
+ * Substitutes text in a text string with new text, some number of times.
+ * @author Manda Wilson < wilson at c bio dot msk cc dot org >
+ */
+public class Substitute extends TextFunction {
+ private static final int REPLACE_ALL = -1;
+
+ /**
+ *Substitutes text in a text string with new text, some number of times.
+ *
+ * @see org.apache.poi.hssf.record.formula.eval.Eval
+ */
+ public Eval evaluate(Eval[] operands, int srcCellRow, short srcCellCol) {
+ Eval retval = null;
+ String oldStr = null;
+ String searchStr = null;
+ String newStr = null;
+ int numToReplace = REPLACE_ALL;
+
+ switch (operands.length) {
+ default:
+ retval = ErrorEval.VALUE_INVALID;
+ case 4:
+ ValueEval fourthveval = singleOperandEvaluate(operands[3], srcCellRow, srcCellCol);
+ if (fourthveval instanceof NumericValueEval) {
+ NumericValueEval numToReplaceEval = (NumericValueEval) fourthveval;
+ // NOTE: it is safe to cast to int here
+ // because in Excel =SUBSTITUTE("teststr","t","T",1.9)
+ // returns Teststr
+ // so 1.9 must be truncated to 1
+ numToReplace = (int) numToReplaceEval.getNumberValue();
+ } else {
+ retval = ErrorEval.VALUE_INVALID;
+ }
+ case 3:
+ // first operand is text string containing characters to replace
+ // second operand is text to find
+ // third operand is replacement text
+ 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 StringValueEval
+ && thirdveval instanceof StringValueEval) {
+
+ StringValueEval oldStrEval = (StringValueEval) firstveval;
+ oldStr = oldStrEval.getStringValue();
+
+ StringValueEval searchStrEval = (StringValueEval) secondveval;
+ searchStr = searchStrEval.getStringValue();
+
+ StringValueEval newStrEval = (StringValueEval) thirdveval;
+ newStr = newStrEval.getStringValue();
+ } else {
+ retval = ErrorEval.VALUE_INVALID;
+ }
+ }
+
+ if (retval == null) {
+ if (numToReplace != REPLACE_ALL && numToReplace < 1) {
+ retval = ErrorEval.VALUE_INVALID;
+ } else if (searchStr.length() == 0) {
+ retval = new StringEval(oldStr);
+ } else {
+ StringBuffer strBuff = new StringBuffer();
+ int startIndex = 0;
+ int nextMatch = -1;
+ for (int leftToReplace = numToReplace;
+ (leftToReplace > 0 || numToReplace == REPLACE_ALL)
+ && (nextMatch = oldStr.indexOf(searchStr, startIndex)) != -1;
+ leftToReplace--) {
+ // store everything from end of last match to start of this match
+ strBuff.append(oldStr.substring(startIndex, nextMatch));
+ strBuff.append(newStr);
+ startIndex = nextMatch + searchStr.length();
+ }
+ // store everything from end of last match to end of string
+ if (startIndex < oldStr.length()) {
+ strBuff.append(oldStr.substring(startIndex));
+ }
+ retval = new StringEval(strBuff.toString());
+ }
+ }
+ return retval;
+ }
+
}
Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls?rev=606172&r1=606171&r2=606172&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org