Author: cedricwalter
Date: Mon Nov 4 21:09:40 2013
New Revision: 1538765
URL: http://svn.apache.org/r1538765
Log:
Bug 55742: Apply patch for Oct2Dec and refactor Hex2Dec to also use
BaseNumberUtils.convertToDecimal
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/BaseNumberUtils.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Oct2Dec.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestHex2Dec.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestOct2Dec.java
- copied, changed from r1531506,
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDec2Hex.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Hex2Dec.java
poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1538765&r1=1538764&r2=1538765&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Mon
Nov 4 21:09:40 2013
@@ -136,7 +136,7 @@ public final class AnalysisToolPak imple
r(m, "MULTINOMIAL", null);
r(m, "NETWORKDAYS", NetworkdaysFunction.instance);
r(m, "NOMINAL", null);
- r(m, "OCT2BIN", null);
+ r(m, "OCT2BIN", Oct2Dec.instance);
r(m, "OCT2DEC", null);
r(m, "OCT2HEX", null);
r(m, "ODDFPRICE", null);
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/BaseNumberUtils.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/BaseNumberUtils.java?rev=1538765&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/BaseNumberUtils.java
(added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/BaseNumberUtils.java
Mon Nov 4 21:09:40 2013
@@ -0,0 +1,78 @@
+/* ====================================================================
+ 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.ss.formula.functions;
+
+/**
+ * <p>Some utils for converting from and to any base<p/>
+ *
+ * @author cedric dot walter @ gmail dot com
+ */
+public class BaseNumberUtils {
+
+
+ public static double convertToDecimal(String value, int base, int
maxNumberOfPlaces) throws IllegalArgumentException {
+ if (value.isEmpty()) {
+ return 0.0;
+ }
+
+ long stringLength = value.length();
+ if (stringLength > maxNumberOfPlaces) {
+ throw new IllegalArgumentException();
+ }
+
+ double decimalValue = 0.0;
+
+ long signedDigit = 0;
+ boolean hasSignedDigit = true;
+ char[] characters = value.toCharArray();
+ for (char character : characters) {
+ long digit;
+
+ if ('0' <= character && character <= '9') {
+ digit = character - '0';
+ } else if ('A' <= character && character <= 'Z') {
+ digit = 10 + (character - 'A');
+ } else if ('a' <= character && character <= 'z') {
+ digit = 10 + (character - 'a');
+ } else {
+ digit = base;
+ }
+
+ if (digit < base) {
+ if (hasSignedDigit) {
+ hasSignedDigit = false;
+ signedDigit = digit;
+ }
+ decimalValue = decimalValue * base + digit;
+ } else {
+ throw new IllegalArgumentException("character not allowed");
+ }
+ }
+
+ boolean isNegative = (!hasSignedDigit && stringLength ==
maxNumberOfPlaces && (signedDigit >= base / 2));
+ if (isNegative) {
+ decimalValue = getTwoComplement(base, maxNumberOfPlaces,
decimalValue);
+ decimalValue = decimalValue * -1.0;
+ }
+
+ return decimalValue;
+ }
+
+ private static double getTwoComplement(double base, double
maxNumberOfPlaces, double decimalValue) {
+ return (Math.pow(base, maxNumberOfPlaces) - decimalValue);
+ }
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Hex2Dec.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Hex2Dec.java?rev=1538765&r1=1538764&r2=1538765&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Hex2Dec.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Hex2Dec.java Mon Nov
4 21:09:40 2013
@@ -20,8 +20,6 @@ package org.apache.poi.ss.formula.functi
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.*;
-import java.math.BigInteger;
-
/**
* Implementation for Excel HEX2DEC() function.<p/>
* <p/>
@@ -41,53 +39,16 @@ public class Hex2Dec extends Fixed1ArgFu
public static final FreeRefFunction instance = new Hex2Dec();
+ static final int HEXADECIMAL_BASE = 16;
+ static final int MAX_NUMBER_OF_PLACES = 10;
+
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval
numberVE) {
- String number = OperandResolver.coerceValueToString(numberVE);
- if (number.length() > 10) {
+ String hex = OperandResolver.coerceValueToString(numberVE);
+ try {
+ return new NumberEval(BaseNumberUtils.convertToDecimal(hex,
HEXADECIMAL_BASE, MAX_NUMBER_OF_PLACES));
+ } catch (IllegalArgumentException e) {
return ErrorEval.NUM_ERROR;
}
-
- String unsigned;
- boolean isPositive = false;
- boolean isNegative = false;
- if (number.length() < 10) {
- unsigned = number;
- isPositive = true;
- } else {
- //remove sign bit
- unsigned = number.substring(1);
- isNegative =
- number.startsWith("8") || number.startsWith("9") ||
- number.startsWith("A") || number.startsWith("B") ||
- number.startsWith("C") || number.startsWith("D") ||
- number.startsWith("E") || number.startsWith("F");
- }
-
- long decimal;
- if (isPositive) {
- try {
- decimal = Integer.parseInt(unsigned, 16);
- } catch (NumberFormatException ee) {
- // number is not a valid hexadecimal number
- return ErrorEval.NUM_ERROR;
- }
- } else {
- if (isNegative) {
- BigInteger temp = new BigInteger(unsigned, 16);
- BigInteger subtract =
BigInteger.ONE.shiftLeft(unsigned.length() * 4);
- temp = temp.subtract(subtract);
- decimal = temp.longValue();
- } else {
- try {
- decimal = Integer.parseInt(unsigned, 16);
- } catch (NumberFormatException ee) {
- // number is not a valid hexadecimal number
- return ErrorEval.NUM_ERROR;
- }
- }
- }
-
- return new NumberEval(decimal);
}
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Oct2Dec.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Oct2Dec.java?rev=1538765&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Oct2Dec.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Oct2Dec.java Mon Nov
4 21:09:40 2013
@@ -0,0 +1,64 @@
+/* ====================================================================
+ 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.ss.formula.functions;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.OperandResolver;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+/**
+ * <p>Implementation for Excel Oct2Dec() function.<p/>
+ * <p>
+ * Converts an octal number to decimal.
+ * </p>
+ * <p>
+ * <b>Syntax</b>:<br/> <b>Oct2Dec </b>(<b>number</b> )
+ * </p>
+ * <p/>
+ * Number is the octal number you want to convert. Number may not contain
more than 10 octal characters (30 bits).
+ * The most significant bit of number is the sign bit. The remaining 29 bits
are magnitude bits.
+ * Negative numbers are represented using two's-complement notation..
+ * <p/>
+ * If number is not a valid octal number, OCT2DEC returns the #NUM! error
value.
+ *
+ * @author cedric dot walter @ gmail dot com
+ */
+public class Oct2Dec extends Fixed1ArgFunction implements FreeRefFunction {
+
+ public static final FreeRefFunction instance = new Oct2Dec();
+
+ static final int MAX_NUMBER_OF_PLACES = 10;
+ static final int OCTAL_BASE = 8;
+
+ public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval
numberVE) {
+ String octal = OperandResolver.coerceValueToString(numberVE);
+ try {
+ return new NumberEval(BaseNumberUtils.convertToDecimal(octal,
OCTAL_BASE, MAX_NUMBER_OF_PLACES));
+ } catch (IllegalArgumentException e) {
+ return ErrorEval.NUM_ERROR;
+ }
+ }
+
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
+ if (args.length != 1) {
+ return ErrorEval.VALUE_INVALID;
+ }
+ return evaluate(ec.getRowIndex(), ec.getColumnIndex(), args[0]);
+ }
+}
Added:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestHex2Dec.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestHex2Dec.java?rev=1538765&view=auto
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestHex2Dec.java
(added)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestHex2Dec.java
Mon Nov 4 21:09:40 2013
@@ -0,0 +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.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import junit.framework.TestCase;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.StringEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+/**
+ * Tests for {@link Hex2Dec}
+ *
+ * @author cedric dot walter @ gmail dot com
+ */
+public final class TestHex2Dec extends TestCase {
+
+ private static ValueEval invokeValue(String number1) {
+ ValueEval[] args = new ValueEval[] { new StringEval(number1) };
+ return new Hex2Dec().evaluate(args, -1, -1);
+ }
+
+ private static void confirmValue(String msg, String number1, String
expected) {
+ ValueEval result = invokeValue(number1);
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(msg, expected, ((NumberEval)
result).getStringValue());
+ }
+
+ private static void confirmValueError(String msg, String number1,
ErrorEval numError) {
+ ValueEval result = invokeValue(number1);
+ assertEquals(ErrorEval.class, result.getClass());
+ assertEquals(msg, numError, result);
+ }
+
+ public void testBasic() {
+ confirmValue("Converts octal 'A5' to decimal (165)", "A5",
"165");
+ confirmValue("Converts octal FFFFFFFF5B to decimal (-165)",
"FFFFFFFF5B", "-165");
+ confirmValue("Converts octal 3DA408B9 to decimal (-165)",
"3DA408B9", "1034160313");
+ }
+
+ public void testErrors() {
+ confirmValueError("not a valid octal number","GGGGGGG",
ErrorEval.NUM_ERROR);
+ confirmValueError("not a valid octal number","3.14159",
ErrorEval.NUM_ERROR);
+ }
+}
Copied:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestOct2Dec.java
(from r1531506,
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDec2Hex.java)
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestOct2Dec.java?p2=poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestOct2Dec.java&p1=poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDec2Hex.java&r1=1531506&r2=1538765&rev=1538765&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestDec2Hex.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestOct2Dec.java
Mon Nov 4 21:09:40 2013
@@ -19,60 +19,45 @@ package org.apache.poi.ss.formula.functi
import junit.framework.TestCase;
import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
/**
- * Tests for {@link Dec2Hex}
+ * Tests for {@link org.apache.poi.ss.formula.functions.Oct2Dec}
*
* @author cedric dot walter @ gmail dot com
*/
-public final class TestDec2Hex extends TestCase {
-
- private static ValueEval invokeValue(String number1, String number2) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1),
new StringEval(number2), };
- return new Dec2Hex().evaluate(args, -1, -1);
- }
+public final class TestOct2Dec extends TestCase {
private static ValueEval invokeValue(String number1) {
- ValueEval[] args = new ValueEval[] { new StringEval(number1), };
- return new Dec2Hex().evaluate(args, -1, -1);
- }
-
- private static void confirmValue(String msg, String number1, String
number2, String expected) {
- ValueEval result = invokeValue(number1, number2);
- assertEquals(StringEval.class, result.getClass());
- assertEquals(msg, expected, ((StringEval)
result).getStringValue());
+ ValueEval[] args = new ValueEval[] { new StringEval(number1) };
+ return new Oct2Dec().evaluate(args, -1, -1);
}
private static void confirmValue(String msg, String number1, String
expected) {
ValueEval result = invokeValue(number1);
- assertEquals(StringEval.class, result.getClass());
- assertEquals(msg, expected, ((StringEval)
result).getStringValue());
+ assertEquals(NumberEval.class, result.getClass());
+ assertEquals(msg, expected, ((NumberEval)
result).getStringValue());
}
- private static void confirmValueError(String msg, String number1, String
number2, ErrorEval numError) {
- ValueEval result = invokeValue(number1, number2);
+ private static void confirmValueError(String msg, String number1,
ErrorEval numError) {
+ ValueEval result = invokeValue(number1);
assertEquals(ErrorEval.class, result.getClass());
assertEquals(msg, numError, result);
}
public void testBasic() {
- confirmValue("Converts decimal 100 to hexadecimal with 0
characters (64)", "100","0", "64");
- confirmValue("Converts decimal 100 to hexadecimal with 4
characters (0064)", "100","4", "0064");
- confirmValue("Converts decimal 100 to hexadecimal with 5
characters (0064)", "100","5", "00064");
- confirmValue("Converts decimal 100 to hexadecimal with 10
(default) characters", "100","10", "0000000064");
- confirmValue("If argument places contains a decimal value,
dec2hex ignores the numbers to the right side of the decimal point.",
"100","10.0", "0000000064");
-
- confirmValue("Converts decimal -54 to hexadecimal, 2 is
ignored","-54", "2", "FFFFFFFFCA");
- confirmValue("places is optionnal","-54", "FFFFFFFFCA");
+ confirmValue("Converts octal '' to decimal (0)", "", "0");
+ confirmValue("Converts octal 54 to decimal (44)", "54", "44");
+ confirmValue("Converts octal 7777777533 to decimal (-165)",
"7777777533", "-165");
+ confirmValue("Converts octal 7000000000 to decimal
(-134217728)", "7000000000", "-134217728");
+ confirmValue("Converts octal 7776667533 to decimal (-299173)",
"7776667533", "-299173");
}
public void testErrors() {
- confirmValueError("Out of range min number","-549755813889","0",
ErrorEval.NUM_ERROR);
- confirmValueError("Out of range max number","549755813888","0",
ErrorEval.NUM_ERROR);
-
- confirmValueError("negative places not allowed","549755813888","-10",
ErrorEval.NUM_ERROR);
- confirmValueError("non number places not allowed","ABCDEF","0",
ErrorEval.VALUE_INVALID);
+ confirmValueError("not a valid octal number","ABCDEFGH",
ErrorEval.NUM_ERROR);
+ confirmValueError("not a valid octal number","99999999",
ErrorEval.NUM_ERROR);
+ confirmValueError("not a valid octal number","3.14159",
ErrorEval.NUM_ERROR);
}
}
Modified: poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls?rev=1538765&r1=1538764&r2=1538765&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
