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/10/26 09:17:07 UTC
svn commit: r707953 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/record/formula/functions/
testcases/org/apache/poi/hssf/record/formula/functions/
Author: josh
Date: Sun Oct 26 01:17:06 2008
New Revision: 707953
URL: http://svn.apache.org/viewvc?rev=707953&view=rev
Log:
Bugzilla 45966 - added implementation for FIND function (patch from Torstein Tauno Svendsen).
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFind.java
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/functions/Find.java
poi/trunk/src/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=707953&r1=707952&r2=707953&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Sun Oct 26 01:17:06 2008
@@ -37,6 +37,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.5-beta4" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">45966 - added implementation for FIND function</action>
<action dev="POI-DEVELOPERS" type="fix">45778 - fixed ObjRecord to read ftLbsData properly</action>
<action dev="POI-DEVELOPERS" type="fix">46053 - fixed evaluation cache dependency analysis when changing blank cells</action>
</release>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=707953&r1=707952&r2=707953&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sun Oct 26 01:17:06 2008
@@ -34,6 +34,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.5-beta4" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">45966 - added implementation for FIND function</action>
<action dev="POI-DEVELOPERS" type="fix">45778 - fixed ObjRecord to read ftLbsData properly</action>
<action dev="POI-DEVELOPERS" type="fix">46053 - fixed evaluation cache dependency analysis when changing blank cells</action>
</release>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Find.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Find.java?rev=707953&r1=707952&r2=707953&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Find.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/Find.java Sun Oct 26 01:17:06 2008
@@ -1,25 +1,65 @@
-/*
-* 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.Eval;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+
+/**
+ * Implementation of the FIND() function.<p/>
+ *
+ * <b>Syntax</b>:<br/>
+ * <b>FIND</b>(<b>find_text</b>, <b>within_text</b>, start_num)<p/>
*
+ * FIND returns the character position of the first occurrence of <tt>find_text</tt> inside
+ * <tt>within_text</tt>. The third parameter, <tt>start_num</tt>, is optional (default=1)
+ * and specifies where to start searching from. Character positions are 1-based.<p/>
+ *
+ * @author Torstein Tauno Svendsen (torstei@officenet.no)
*/
-package org.apache.poi.hssf.record.formula.functions;
+public class Find extends TextFunction {
-public class Find extends NotImplementedFunction {
+ protected ValueEval evaluateFunc(Eval[] args, int srcCellRow, short srcCellCol)
+ throws EvaluationException {
+ int nArgs = args.length;
+ if (nArgs < 2 || nArgs > 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ String needle = evaluateStringArg(args[0], srcCellRow, srcCellCol);
+ String haystack = evaluateStringArg(args[1], srcCellRow, srcCellCol);
+ int startpos;
+ if (nArgs == 3) {
+ startpos = evaluateIntArg(args[2], srcCellRow, srcCellCol);
+ if (startpos <= 0) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ startpos--; // convert 1-based to zero based
+ } else {
+ startpos = 0;
+ }
+ int result = haystack.indexOf(needle, startpos);
+ if (result == -1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return new NumberEval(result + 1);
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java?rev=707953&r1=707952&r2=707953&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java Sun Oct 26 01:17:06 2008
@@ -32,6 +32,7 @@
result.addTestSuite(TestAverage.class);
result.addTestSuite(TestCountFuncs.class);
result.addTestSuite(TestDate.class);
+ result.addTestSuite(TestFind.class);
result.addTestSuite(TestFinanceLib.class);
result.addTestSuite(TestIndex.class);
result.addTestSuite(TestIndexFunctionFromSpreadsheet.class);
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFind.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFind.java?rev=707953&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFind.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFind.java Sun Oct 26 01:17:06 2008
@@ -0,0 +1,76 @@
+/* ====================================================================
+ 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 junit.framework.TestCase;
+
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFErrorConstants;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue;
+
+/**
+ * Tests for {@link Find}
+ *
+ * @author Torstein Svendsen (torstei@officenet.no)
+ */
+public final class TestFind extends TestCase {
+
+ public void testFind() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
+
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+ confirmResult(fe, cell, "find(\"h\", \"haystack\")", 1);
+ confirmResult(fe, cell, "find(\"a\", \"haystack\",2)", 2);
+ confirmResult(fe, cell, "find(\"a\", \"haystack\",3)", 6);
+
+ // number args converted to text
+ confirmResult(fe, cell, "find(7, 32768)", 3);
+ confirmResult(fe, cell, "find(\"34\", 1341235233412, 3)", 10);
+ confirmResult(fe, cell, "find(5, 87654)", 4);
+
+ // Errors
+ confirmError(fe, cell, "find(\"n\", \"haystack\")", HSSFErrorConstants.ERROR_VALUE);
+ confirmError(fe, cell, "find(\"k\", \"haystack\",9)", HSSFErrorConstants.ERROR_VALUE);
+ confirmError(fe, cell, "find(\"k\", \"haystack\",#REF!)", HSSFErrorConstants.ERROR_REF);
+ confirmError(fe, cell, "find(\"k\", \"haystack\",0)", HSSFErrorConstants.ERROR_VALUE);
+ confirmError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", HSSFErrorConstants.ERROR_DIV_0);
+ confirmError(fe, cell, "find(2, #N/A, #REF!)", HSSFErrorConstants.ERROR_NA);
+ }
+
+ private static void confirmResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText,
+ int expectedResult) {
+ cell.setCellFormula(formulaText);
+ fe.notifyUpdateCell(cell);
+ CellValue result = fe.evaluate(cell);
+ assertEquals(result.getCellType(), HSSFCell.CELL_TYPE_NUMERIC);
+ assertEquals(expectedResult, result.getNumberValue(), 0.0);
+ }
+
+ private static void confirmError(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText,
+ int expectedErrorCode) {
+ cell.setCellFormula(formulaText);
+ fe.notifyUpdateCell(cell);
+ CellValue result = fe.evaluate(cell);
+ assertEquals(result.getCellType(), HSSFCell.CELL_TYPE_ERROR);
+ assertEquals(expectedErrorCode, result.getErrorValue());
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org