Attachment #30389 for bug #55057

View | Details | Raw Unified | Return to bug 55057
Collapse All | Expand All




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.OperandResolver;
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;

import java.math.BigInteger;

/**
 * Implementation for Excel HEX2DEC() function.<p/>
 * <p/>
 * <b>Syntax</b>:<br/> <b>HEX2DEC  </b>(<b>number</b>)<br/>
 * <p/>
 * Converts a hexadecimal number to decimal.
 * <p/>
 * Number     is the hexadecimal number you want to convert. Number cannot contain more than 10 characters (40 bits).
 * The most significant bit of number is the sign bit.
 * The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.
 * Remark
 * If number is not a valid hexadecimal number, HEX2DEC returns the #NUM! error value.
 *
 * @author cedric dot walter @ gmail dot com
 */
public class Hex2Dec extends Fixed1ArgFunction implements FreeRefFunction {

    public static final FreeRefFunction instance = new Hex2Dec();

    @Override
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE) {
        String number = OperandResolver.coerceValueToString(numberVE);
        if (number.length() > 10) {
            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");
        }

        String result = "";
        if (isPositive) {
            try {
                result = String.valueOf(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);
                result = String.valueOf(temp.longValue());
            } else {
                try {
                    result = String.valueOf(Integer.parseInt(unsigned, 16));
                } catch (NumberFormatException ee) {
                    // number is not a valid hexadecimal number
                    return ErrorEval.NUM_ERROR;
                }
            }
        }

        return new StringEval(result);
    }

    @Override
    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
        if (args.length != 1) {
            return ErrorEval.VALUE_INVALID;
        }
        return evaluate(ec.getRowIndex(), ec.getColumnIndex(), args[0]);
    }
}

Lines 108-114 Link Here

(-)poi/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (-1 / +1 lines)
108	r(m, "GCD", null);	108	r(m, "GCD", null);
109	r(m, "GESTEP", null);	109	r(m, "GESTEP", null);
110	r(m, "HEX2BIN", null);	110	r(m, "HEX2BIN", null);
111	r(m, "HEX2DEC", null);	111	r(m, "HEX2DEC", Hex2Dec.instance);
112	r(m, "HEX2OCT", null);	112	r(m, "HEX2OCT", null);
113	r(m, "IFERROR", IfError.instance);	113	r(m, "IFERROR", IfError.instance);
114	r(m, "IMABS", null);	114	r(m, "IMABS", null);




/* ====================================================================
   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;

/**
 * Tests Hex2Dec() as loaded from a test data spreadsheet.<p/>
 *
 * @author cedric dot walter @ gmail dot com
 */
public class TestHex2DecFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet {

    @Override
    protected String getFilename() {
        return "Hex2DecFunctionTestCaseData.xls";
    }
}

Return to bug 55057

Line 0 Link Here

(-)poi/src/java/org/apache/poi/ss/formula/functions/Hex2Dec.java (+87 lines)
		1	package org.apache.poi.ss.formula.functions;
		2
		3	import org.apache.poi.ss.formula.OperationEvaluationContext;
		4	import org.apache.poi.ss.formula.eval.ErrorEval;
		5	import org.apache.poi.ss.formula.eval.OperandResolver;
		6	import org.apache.poi.ss.formula.eval.StringEval;
		7	import org.apache.poi.ss.formula.eval.ValueEval;
		8
		9	import java.math.BigInteger;
		10
		11	/**
		12	* Implementation for Excel HEX2DEC() function.<p/>
		13	* <p/>
		14	* <b>Syntax</b>:<br/> <b>HEX2DEC </b>(<b>number</b>)<br/>
		15	* <p/>
		16	* Converts a hexadecimal number to decimal.
		17	* <p/>
		18	* Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters (40 bits).
		19	* The most significant bit of number is the sign bit.
		20	* The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.
		21	* Remark
		22	* If number is not a valid hexadecimal number, HEX2DEC returns the #NUM! error value.
		23	*
		24	* @author cedric dot walter @ gmail dot com
		25	*/
		26	public class Hex2Dec extends Fixed1ArgFunction implements FreeRefFunction {
		27
		28	public static final FreeRefFunction instance = new Hex2Dec();
		29
		30	@Override
		31	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE) {
		32	String number = OperandResolver.coerceValueToString(numberVE);
		33	if (number.length() > 10) {
		34	return ErrorEval.NUM_ERROR;
		35	}
		36
		37	String unsigned = "";
		38	boolean isPositive = false;
		39	boolean isNegative = false;
		40	if (number.length() < 10) {
		41	unsigned = number;
		42	isPositive = true;
		43	} else {
		44	//remove sign bit
		45	unsigned = number.substring(1);
		46	isNegative =
		47	number.startsWith("8") \|\| number.startsWith("9") \|\|
		48	number.startsWith("A") \|\| number.startsWith("B") \|\|
		49	number.startsWith("C") \|\| number.startsWith("D") \|\|
		50	number.startsWith("E") \|\| number.startsWith("F");
		51	}
		52
		53	String result = "";
		54	if (isPositive) {
		55	try {
		56	result = String.valueOf(Integer.parseInt(unsigned, 16));
		57	} catch (NumberFormatException ee) {
		58	// number is not a valid hexadecimal number
		59	return ErrorEval.NUM_ERROR;
		60	}
		61	} else {
		62	if (isNegative) {
		63	BigInteger temp = new BigInteger(unsigned, 16);
		64	BigInteger subtract = BigInteger.ONE.shiftLeft(unsigned.length() * 4);
65	temp = temp.subtract(subtract);
66	result = String.valueOf(temp.longValue());
67	} else {
68	try {
69	result = String.valueOf(Integer.parseInt(unsigned, 16));
70	} catch (NumberFormatException ee) {
71	// number is not a valid hexadecimal number
72	return ErrorEval.NUM_ERROR;
73	}
74	}
75	}
76
77	return new StringEval(result);
78	}
79
80	@Override
81	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
82	if (args.length != 1) {
83	return ErrorEval.VALUE_INVALID;
84	}
85	return evaluate(ec.getRowIndex(), ec.getColumnIndex(), args[0]);
86	}
87	}

Line 0 Link Here

(-)poi/src/testcases/org/apache/poi/ss/formula/functions/TestHex2DecFunctionsFromSpreadsheet.java (+31 lines)
	1	/* ====================================================================
	2	Licensed to the Apache Software Foundation (ASF) under one or more
	3	contributor license agreements. See the NOTICE file distributed with
	4	this work for additional information regarding copyright ownership.
	5	The ASF licenses this file to You under the Apache License, Version 2.0
	6	(the "License"); you may not use this file except in compliance with
	7	the License. You may obtain a copy of the License at
	8
	9	http://www.apache.org/licenses/LICENSE-2.0
	10
	11	Unless required by applicable law or agreed to in writing, software
	12	distributed under the License is distributed on an "AS IS" BASIS,
	13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	14	See the License for the specific language governing permissions and
	15	limitations under the License.
	16	==================================================================== */
	17
	18	package org.apache.poi.ss.formula.functions;
	19
	20	/**
	21	* Tests Hex2Dec() as loaded from a test data spreadsheet.<p/>
	22	*
	23	* @author cedric dot walter @ gmail dot com
	24	*/
	25	public class TestHex2DecFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet {
	26
	27	@Override
	28	protected String getFilename() {
	29	return "Hex2DecFunctionTestCaseData.xls";
	30	}
	31	}