-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
hi,
enclosed is a patch for the function PROPER implemented in
org.apache.poi.hssf.record.formula.functions.Proper according to your
guidelines http://jakarta.apache.org/poi/hssf/eval-devguide.html.
regards,
- --
Marko Lauke
Diplom-Informatiker
tel: +49 8031 228 278
mobil: +49 179 4458 304
fax: +49 1212 511 204 726
e-mail: [EMAIL PROTECTED]
icq: 157108161
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)
iD8DBQFF0k4C6+OecltctaYRAln8AKChdOuyP4+eOQvSLgARS/VbDmUQ7QCfbyZr
XLqthAv5WYiHGq8YDAES9wU=
=28sF
-----END PGP SIGNATURE-----
Index:
C:/development/apache-poi-source/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Proper.java
===================================================================
---
C:/development/apache-poi-source/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Proper.java
(revision 507233)
+++
C:/development/apache-poi-source/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Proper.java
(working copy)
@@ -1,29 +1,79 @@
/*
-* 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.
-*/
+ * 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
*
*/
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.StringEval;
+import org.apache.poi.hssf.record.formula.eval.StringValueEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+
/**
- * @author
- *
+ * @author <a href="mailto:[EMAIL PROTECTED]">m.lauke</a>
+ *
*/
-public class Proper extends NotImplementedFunction {
+public class Proper extends TextFunction {
+ public Eval evaluate(Eval[] pOperands, int pSrcCellRow, short
pSrcCellCol) {
+ ValueEval retval = null;
+ String s = null;
+ switch (pOperands.length) {
+ default:
+ retval = ErrorEval.VALUE_INVALID;
+ break;
+ case 1:
+ ValueEval ve = singleOperandEvaluate(pOperands[0],
pSrcCellRow,
+ pSrcCellCol);
+ if (ve instanceof StringValueEval) {
+ StringValueEval sve = (StringValueEval) ve;
+ s = sve.getStringValue();
+ } else if (ve instanceof BlankEval) {
+ } else {
+ retval = ErrorEval.VALUE_INVALID;
+ break;
+ }
+ }
+ if (retval == null) {
+ s = (s == null) ? EMPTY_STRING : s;
+ retval = new StringEval(makeProper(s));
+ }
+ return retval;
+ }
+
+ protected String makeProper(String pString) {
+ StringBuffer sb = new StringBuffer();
+ boolean upper = true;
+ for (int i = 0, n = pString.length(); i < n; i++) {
+ char c = pString.charAt(i);
+ if (c == ' ' || c == '-' || Character.isDigit(c)) {
+ upper = true;
+ } else if (upper) {
+ c = Character.toUpperCase(c);
+ upper = false;
+ } else {
+ c = Character.toLowerCase(c);
+ }
+ sb.append(c);
+ }
+ return sb.toString();
+ }
}
Index:
C:/development/apache-poi-source/src/scratchpad/testcases/org/apache/poi/hssf/data/FormulaEvalTestData.xls
===================================================================
Cannot display: file marked as a binary type.
svn:mime-type = application/octet-stream
Index:
C:/development/apache-poi-source/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestEverything.java
===================================================================
---
C:/development/apache-poi-source/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestEverything.java
(revision 507233)
+++
C:/development/apache-poi-source/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/TestEverything.java
(working copy)
@@ -1,49 +1,60 @@
/*
-* 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.
-*/
+ * 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 11, 2005
*
*/
package org.apache.poi.hssf.record.formula.eval;
+import junit.framework.Test;
import junit.framework.TestSuite;
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
+ *
*/
public class TestEverything extends TestSuite {
- public static TestSuite suite() throws Exception {
- TestSuite suite = new TestSuite("Tests for OperationEval concrete
implementation classes.");
- suite.addTest(new GenericFormulaTestCase("D23"));
- suite.addTest(new GenericFormulaTestCase("D27"));
- suite.addTest(new GenericFormulaTestCase("D31"));
- suite.addTest(new GenericFormulaTestCase("D35"));
- suite.addTest(new GenericFormulaTestCase("D39"));
- suite.addTest(new GenericFormulaTestCase("D43"));
- suite.addTest(new GenericFormulaTestCase("D47"));
- suite.addTest(new GenericFormulaTestCase("D51"));
- suite.addTest(new GenericFormulaTestCase("D55"));
- suite.addTest(new GenericFormulaTestCase("D59"));
- suite.addTest(new GenericFormulaTestCase("D63"));
- suite.addTest(new GenericFormulaTestCase("D67"));
- suite.addTest(new GenericFormulaTestCase("D71"));
- suite.addTest(new GenericFormulaTestCase("D75"));
- return suite;
- }
+ public static TestSuite suite() throws Exception {
+ TestSuite suite = new TestSuite(
+ "Tests for OperationEval concrete
implementation classes.");
+ suite.addTest(createGenericTest("D23"));
+ suite.addTest(createGenericTest("D27"));
+ suite.addTest(createGenericTest("D31"));
+ suite.addTest(createGenericTest("D35"));
+ suite.addTest(createGenericTest("D39"));
+ suite.addTest(createGenericTest("D43"));
+ suite.addTest(createGenericTest("D47"));
+ suite.addTest(createGenericTest("D51"));
+ suite.addTest(createGenericTest("D55"));
+ suite.addTest(createGenericTest("D59"));
+ suite.addTest(createGenericTest("D63"));
+ suite.addTest(createGenericTest("D67"));
+ suite.addTest(createGenericTest("D71"));
+ suite.addTest(createGenericTest("D75"));
+
+ suite.addTest(createGenericTest("D1072"));
+ return suite;
+ }
+
+ private static Test createGenericTest(String pCellReference)
+ throws Exception {
+ TestSuite suite = new TestSuite("Test " + pCellReference);
+ suite.addTest(new GenericFormulaTestCase(pCellReference));
+ return suite;
+ }
}
Index:
C:/development/apache-poi-source/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/GenericFormulaTestCase.java
===================================================================
---
C:/development/apache-poi-source/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/GenericFormulaTestCase.java
(revision 507233)
+++
C:/development/apache-poi-source/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/eval/GenericFormulaTestCase.java
(working copy)
@@ -1,19 +1,19 @@
/*
-* 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.
-*/
+ * 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 11, 2005
*
@@ -21,6 +21,7 @@
package org.apache.poi.hssf.record.formula.eval;
import java.io.FileInputStream;
+import java.io.InputStream;
import junit.framework.AssertionFailedError;
import junit.framework.TestCase;
@@ -35,113 +36,142 @@
/**
* @author Amol S. Deshmukh < amolweb at ya hoo dot com >
- *
+ *
*/
public class GenericFormulaTestCase extends TestCase {
- protected final static String FILENAME =
System.getProperty("HSSF.testdata.path")+ "/FormulaEvalTestData.xls";
+ protected final static String FILENAME = System
+ .getProperty("HSSF.testdata.path", "")
+ + "/FormulaEvalTestData.xls";
- protected static HSSFWorkbook workbook = null;
+ protected static HSSFWorkbook workbook = null;
- protected CellReference beginCell;
- protected int getBeginRow() {
- return beginCell.getRow();
- }
-
- protected short getBeginCol() {
- return beginCell.getCol();
- }
+ protected CellReference beginCell;
- protected final HSSFCell getExpectedValueCell(HSSFSheet sheet, HSSFRow
row, HSSFCell cell) {
- HSSFCell retval = null;
- if (sheet != null) {
- row = sheet.getRow(row.getRowNum()+1);
- if (row != null) {
- retval = row.getCell(cell.getCellNum());
- }
- }
-
- return retval;
- }
+ protected int getBeginRow() {
+ return beginCell.getRow();
+ }
- protected void assertEquals(String msg, HSSFCell expected,
HSSFFormulaEvaluator.CellValue actual) {
- if (expected != null && actual!=null) {
- if (expected!=null && expected.getCellType() ==
HSSFCell.CELL_TYPE_STRING) {
- String value = expected.getRichStringCellValue().getString();
- if (value.startsWith("#")) {
- expected.setCellType(HSSFCell.CELL_TYPE_ERROR);
- }
- }
- if (!(expected == null || actual == null)) {
- switch (expected.getCellType()) {
- case HSSFCell.CELL_TYPE_BLANK:
- assertEquals(msg, HSSFCell.CELL_TYPE_BLANK,
actual.getCellType());
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN:
- assertEquals(msg, HSSFCell.CELL_TYPE_BOOLEAN,
actual.getCellType());
- assertEquals(msg, expected.getBooleanCellValue(),
actual.getBooleanValue());
- break;
- case HSSFCell.CELL_TYPE_ERROR:
- assertEquals(msg, HSSFCell.CELL_TYPE_ERROR,
actual.getCellType()); // TODO: check if exact error matches
- break;
- case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since
we will call method after formula evaluation
- throw new AssertionFailedError("Cannot expect formula as
result of formula evaluation: " + msg);
- case HSSFCell.CELL_TYPE_NUMERIC:
- assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC,
actual.getCellType());
- TestMathX.assertEquals(msg,
expected.getNumericCellValue(), actual.getNumberValue(), TestMathX.POS_ZERO,
TestMathX.DIFF_TOLERANCE_FACTOR);
-// double delta =
Math.abs(expected.getNumericCellValue()-actual.getNumberValue());
-// double pctExpected =
Math.abs(0.00001*expected.getNumericCellValue());
-// assertTrue(msg, delta <= pctExpected);
- break;
- case HSSFCell.CELL_TYPE_STRING:
- assertEquals(msg, HSSFCell.CELL_TYPE_STRING,
actual.getCellType());
- assertEquals(msg,
expected.getRichStringCellValue().getString(),
actual.getRichTextStringValue().getString());
- break;
- }
- }
- else {
- throw new AssertionFailedError("expected: " + expected + "
got:" + actual);
- }
- }
- }
+ protected short getBeginCol() {
+ return beginCell.getCol();
+ }
- public GenericFormulaTestCase(String beginCell) throws Exception {
- super("genericTest");
- if (workbook == null) {
- FileInputStream fin = new FileInputStream( FILENAME );
- workbook = new HSSFWorkbook( fin );
- fin.close();
- }
- this.beginCell = new CellReference(beginCell);
- }
-
- public void setUp() {
- }
-
- public void genericTest() throws Exception {
- HSSFSheet s = workbook.getSheetAt( 0 );
- HSSFRow r = s.getRow(getBeginRow());
- short endcolnum = r.getLastCellNum();
- HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(s, workbook);
- evaluator.setCurrentRow(r);
+ protected final HSSFCell getExpectedValueCell(HSSFSheet sheet, HSSFRow
row,
+ HSSFCell cell) {
+ HSSFCell retval = null;
+ if (sheet != null) {
+ row = sheet.getRow(row.getRowNum() + 1);
+ if (row != null) {
+ retval = row.getCell(cell.getCellNum());
+ }
+ }
- HSSFCell c = null;
- for (short colnum=getBeginCol(); colnum < endcolnum; colnum++) {
- try {
- c = r.getCell(colnum);
- if (c==null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
- continue;
-
- HSSFFormulaEvaluator.CellValue actualValue = evaluator.evaluate(c);
-
- HSSFCell expectedValueCell = getExpectedValueCell(s, r, c);
- assertEquals("Formula: " + c.getCellFormula()
- + " @ " + getBeginRow() + ":" + colnum,
- expectedValueCell, actualValue);
- } catch (RuntimeException re) {
- throw new
RuntimeException("CELL["+getBeginRow()+","+colnum+"]: "+re.getMessage(), re);
- }
- }
- }
-
+ return retval;
+ }
+
+ protected void assertEquals(String msg, HSSFCell expected,
+ HSSFFormulaEvaluator.CellValue actual) {
+ if (expected != null && actual != null) {
+ if (expected != null
+ && expected.getCellType() ==
HSSFCell.CELL_TYPE_STRING) {
+ String value =
expected.getRichStringCellValue().getString();
+ if (value.startsWith("#")) {
+
expected.setCellType(HSSFCell.CELL_TYPE_ERROR);
+ }
+ }
+ if (!(expected == null || actual == null)) {
+ switch (expected.getCellType()) {
+ case HSSFCell.CELL_TYPE_BLANK:
+ assertEquals(msg,
HSSFCell.CELL_TYPE_BLANK, actual
+ .getCellType());
+ break;
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ assertEquals(msg,
HSSFCell.CELL_TYPE_BOOLEAN, actual
+ .getCellType());
+ assertEquals(msg,
expected.getBooleanCellValue(), actual
+ .getBooleanValue());
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ assertEquals(msg,
HSSFCell.CELL_TYPE_ERROR, actual
+ .getCellType()); //
TODO: check if exact error
+ // matches
+ break;
+ case HSSFCell.CELL_TYPE_FORMULA: // will never
be used, since
+ // we will call method after
+ // formula evaluation
+ throw new AssertionFailedError(
+ "Cannot expect formula
as result of formula evaluation: "
+ + msg);
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ assertEquals(msg,
HSSFCell.CELL_TYPE_NUMERIC, actual
+ .getCellType());
+ TestMathX.assertEquals(msg,
expected.getNumericCellValue(),
+
actual.getNumberValue(), TestMathX.POS_ZERO,
+
TestMathX.DIFF_TOLERANCE_FACTOR);
+ // double delta =
+ //
Math.abs(expected.getNumericCellValue()-actual.getNumberValue());
+ // double pctExpected =
+ //
Math.abs(0.00001*expected.getNumericCellValue());
+ // assertTrue(msg, delta <=
pctExpected);
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ assertEquals(msg,
HSSFCell.CELL_TYPE_STRING, actual
+ .getCellType());
+ assertEquals(msg,
expected.getRichStringCellValue()
+ .getString(),
actual.getRichTextStringValue()
+ .getString());
+ break;
+ }
+ } else {
+ throw new AssertionFailedError("expected: " +
expected
+ + " got:" + actual);
+ }
+ }
+ }
+
+ public GenericFormulaTestCase(String pBeginCell) throws Exception {
+ super("genericTest");
+ if (workbook == null) {
+ InputStream stream =
Thread.currentThread().getContextClassLoader()
+ .getResourceAsStream(FILENAME);
+ if(stream == null){
+ stream = new FileInputStream(FILENAME);
+ }
+ workbook = new HSSFWorkbook(stream);
+ stream.close();
+ }
+ this.beginCell = new CellReference(pBeginCell);
+ }
+
+ public void setUp() {
+ }
+
+ public void genericTest() throws Exception {
+ HSSFSheet s = workbook.getSheetAt(0);
+ HSSFRow r = s.getRow(getBeginRow());
+ short endcolnum = r.getLastCellNum();
+ HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(s,
workbook);
+ evaluator.setCurrentRow(r);
+
+ HSSFCell c = null;
+ for (short colnum = getBeginCol(); colnum < endcolnum;
colnum++) {
+ try {
+ c = r.getCell(colnum);
+ if (c == null || c.getCellType() !=
HSSFCell.CELL_TYPE_FORMULA)
+ continue;
+
+ HSSFFormulaEvaluator.CellValue actualValue =
evaluator
+ .evaluate(c);
+
+ HSSFCell expectedValueCell =
getExpectedValueCell(s, r, c);
+ assertEquals("Formula: " + c.getCellFormula() +
" @ "
+ + getBeginRow() + ":" + colnum,
expectedValueCell,
+ actualValue);
+ } catch (RuntimeException re) {
+ throw new RuntimeException("CELL[" +
getBeginRow() + ","
+ + colnum + "]: " +
re.getMessage(), re);
+ }
+ }
+ }
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
