Attachment #15394 for bug #35290

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




import java.util.Stack;

import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.util.HexDump;
import org.apache.poi.util.LittleEndian;

/**

{
    
    public static final boolean EXPERIMENTAL_FORMULA_SUPPORT_ENABLED=true;
    public static final byte STRING_VALUE = 0x00;
    public static final byte BOOL_VALUE   = 0x01;
    public static final byte ERROR_VALUE  = 0x02;
    public static final byte BLANK_VALUE  = 0x03;
    public static final byte NUMERIC_VALUE = Byte.MAX_VALUE; // non std code
    
    public static final byte ERROR_CODE_NULL = 0x00; // #NULL! Intersection of two cell ranges is empty
    public static final byte ERROR_CODE_DIVZERO = 0x07; // #DIV/0! Error
    public static final byte ERROR_CODE_VALUE = 0x0F; // #VALUE! Wrong type of operand
    public static final byte ERROR_CODE_REF = 0x17; // #REF! Illegal or deleted cell reference
    public static final byte ERROR_CODE_NAME = 0x1D; // #NAME? Wrong function or range name
    public static final byte ERROR_CODE_NUM = 0x24; // #NUM! Value range overflow
    public static final byte ERROR_CODE_NA = 0x2A ; // #N/A! Argument or function not available
    
    public static final short sid =
        0x06;   // docs say 406...because of a bug Microsoft support site article #Q184647)

    private int             field_1_row;
    private short             field_2_column;
    private short             field_3_xf;
    
    // stored formula result can be numeric/string/bool/error/<blank> 
    private double            field_4_value;
    //private String            field_4_string; Commented since string is stored externally
    private boolean           field_4_bool;
    private byte              field_4_errtype;
    
    private short             field_5_options;
    private int               field_6_zero;
    private short             field_7_expression_len;
    private Stack             field_8_parsed_expr;
    
    private byte              value_type_id; // 
    
    /**
     * Since the NaN support seems sketchy (different constants) we'll store and spit it out directly
     */

        field_1_row            = LittleEndian.getUShort(data, 0 + offset);
        field_2_column         = LittleEndian.getShort(data, 2 + offset);
        field_3_xf             = LittleEndian.getShort(data, 4 + offset);
        field_4_value          = LittleEndian.getDouble(data, 6 + offset);
		field_5_options        = LittleEndian.getShort(data, 14 + offset);
        field_4_value          = LittleEndian.getDouble(data, 6 + offset);
		        
        value_data = new byte[8];
        System.arraycopy(data, offset+6, value_data, 0, 8);
        
        if (Double.isNaN(field_4_value)) {
            value_type_id = value_data[0];
            switch (value_type_id) {
            case ERROR_VALUE:
                field_4_errtype = value_data[2];
                break;
            case BOOL_VALUE:
                field_4_bool = (value_data[2] == 1);
                break;
            case STRING_VALUE:
                // do nothing since string is stored outside this record
                break;
            default: //blank
            }
        }
        else {
            value_type_id = NUMERIC_VALUE;
        }
        
        field_6_zero           = LittleEndian.getInt(data, 16 + offset);

    }

    /**
     * set the calculated numeric value of the formula
     *
     * @param value  calculated value
     */

    public void setValue(double value) {
        if (Double.isNaN(value)) {
            setValue(ERROR_CODE_DIVZERO);
        }
        else if (Double.isInfinite(value)) {
            setValue(ERROR_CODE_NUM);
        }
        else {
            value_type_id = NUMERIC_VALUE;
            field_4_value = value;
        }
    }
    
    /**
     * set the calculated boolean value of the formula
     *
     * @param value  calculated value
     */
    public void setValue(boolean value) {
        field_4_value = Double.NaN;
        value_type_id = BOOL_VALUE;
        field_4_bool = value;
        
        if (value_data==null) value_data = new byte[8];
        value_data[0] = value_type_id;
        value_data[2] = (byte) (value ? 1 : 0);
        value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
    }
    
    /**
     * set the calculated string value of the formula
     *
     * @param value  value is ignored. calculated value
     * is stored outside the formula record.
     */
    public void setValue(String value) {
        field_4_value = Double.NaN;
        value_type_id = STRING_VALUE;
        
        if (value_data==null) value_data = new byte[8];
        value_data[0] = value_type_id;
        value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
    }
    
    /**
     * set the error code as result of the formula
     * Use the error codes defined in this class.
     * @param value  calculated value
     */
    public void setValue(byte value) {
        field_4_value = Double.NaN;
        value_type_id = ERROR_VALUE;
        field_4_errtype = value;
        
        if (value_data==null) value_data = new byte[8];
        value_data[0] = value_type_id;
        value_data[2] = value;
        value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
    }
    
    /**
     * set the calculated value of the formula as blank
     *
     * @param value  calculated value
     */
    public void setValue() {
        field_4_value = Double.NaN;
        value_type_id = BLANK_VALUE;
        
        if (value_data==null) value_data = new byte[8];
        value_data[0] = value_type_id;
        value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
    }
    

    /**
     * set the option flags

    }

    /**
     * returned value indicates the type of formula
     * value. Depending on the returned value, the
     * appropriate getXYZValue() or getValue() method
     * should be called to get the actual value.
     * @see getValue(), getStringValue(), getBoolValue(),
     * getErrorCodeValue()
     * @return
     */
    public byte getValueType() {
        return value_type_id;
    }
    
    /**
     * get the calculated value of the formula
     * @see getValueType()
     * @return calculated value
     */


    {
        return field_4_value;
    }
    
    /**
     * get the calculated value of the formula
     * (to be used when the formula value is of type boolean) 
     * @see getValueType()
     * @return
     */
    public boolean getBooleanValue() {
        return field_4_bool;
    }
    
    /**
     * get the calculated value of the formula
     * (to be used when the formula value is of type error) 
     * @see getValueType()
     * @return
     */
    public byte getErrorCodeValue() {
        return field_4_errtype;
    }    
    
    /**
     * get the option flags
     *

                .append("\n");
            buffer.append("    .xf              = ")
                .append(Integer.toHexString(getXFIndex())).append("\n");
            if (Double.isNaN(this.getValue()) && value_data != null) {
                buffer.append("    .value ");
                switch (value_type_id) {
                case STRING_VALUE:
                    buffer.append("(String)     [string is stored outside formula record] "); 
                    // note: string value is not stored in this record.
                    break;
                case ERROR_VALUE:
                    buffer.append("(ErrorCode)     = ");
                    buffer.append(field_4_errtype);
                    break;
                case BOOL_VALUE:
                    buffer.append("(Bool)     = ");
                    buffer.append(field_4_bool);
                    break;
                case BLANK_VALUE:
                    buffer.append("(Blank)     = ");
                    break;
                default:
                    buffer.append("(UNKNOWN)     = ");
                    buffer.append(HexDump.dump(value_data, 0, 0));
                }
                buffer.append("\n");
            }
            else
              buffer.append("    .value           = ").append(getValue())
                  .append("\n");

      rec.field_1_row = field_1_row;
      rec.field_2_column = field_2_column;
      rec.field_3_xf = field_3_xf;
      rec.field_4_value = field_4_value; // numeric
      rec.field_4_bool = field_4_bool; // boolean
      rec.field_4_errtype = field_4_errtype; // error code
      rec.value_type_id = value_type_id; // type indicator
      
      rec.field_5_options = field_5_options;
      rec.field_6_zero = field_6_zero;
      rec.field_7_expression_len = field_7_expression_len;

Lines 17-23 Link Here

(-)src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java (-1 / +111 lines)
17		17
18	package org.apache.poi.hssf.record.aggregates;	18	package org.apache.poi.hssf.record.aggregates;
19		19
20	import org.apache.poi.hssf.record.*;	20	import org.apache.poi.hssf.record.CellValueRecordInterface;
		21	import org.apache.poi.hssf.record.FormulaRecord;
		22	import org.apache.poi.hssf.record.Record;
		23	import org.apache.poi.hssf.record.SharedFormulaRecord;
		24	import org.apache.poi.hssf.record.StringRecord;
21		25
22	/**	26	/**
23	* The formula record aggregate is used to join together the formula record and it's	27	* The formula record aggregate is used to join together the formula record and it's
Lines 230-237 Link Here
230	return true;	234	return true;
231	}	235	}
232		236
		237	/**
		238	* returns the string value that is the stored value of the
		239	* formula evaluation.
		240	* This function should be called only if the function getValueType()
		241	* returns the value FormulaRecord.STRING_VALUE
		242	* @see getValueType()
		243	* @return
		244	*/
233	public String getStringValue() {	245	public String getStringValue() {
234	if(stringRecord==null) return null;	246	if(stringRecord==null) return null;
235	return stringRecord.getString();	247	return stringRecord.getString();
236	}	248	}
		249
		250	/**
		251	* returns the numeric value that is the stored value of the
		252	* formula evaluation.
		253	* This function should be called only if the function getValueType()
		254	* returns the value FormulaRecord.NUMERIC_VALUE
		255	* @return
		256	*/
		257	public double getNumberValue() {
		258	return formulaRecord.getValue();
		259	}
		260
		261	/**
		262	* returns the boolean value that is the stored value of the
		263	* formula evaluation.
		264	* This function should be called only if the function getValueType()
		265	* returns the value FormulaRecord.BOOLEAN_VALUE
		266	* @return
		267	*/
		268	public boolean getBooleanValue() {
		269	return formulaRecord.getBooleanValue();
		270	}
		271
		272	/**
		273	* returns the error code that is the stored value of the formula evaluation.
		274	* This function should be called only if the function getValueType()
		275	* returns the value FormulaRecord.ERROR_VALUE
		276	* @return
		277	*/
		278	public byte getErrorCodeValue() {
		279	return formulaRecord.getErrorCodeValue();
		280	}
		281
		282	/**
		283	* returns the type of the stored value of formula evaluation.
		284	* @return
		285	*/
		286	public byte getValueType() {
		287	return formulaRecord.getValueType();
		288	}
		289
		290	/**
		291	* sets the numeric value for the stored result of formula evaluation.
		292	* Implicitly modifies the result of getValueType() function.
		293	* @see getValueType()
		294	* @param value
		295	*/
		296	public void setValue(double value) {
		297	formulaRecord.setValue(value);
		298	}
		299
		300	/**
		301	* sets the value of the formula evaluation to the specified string value.
		302	* This creates an additional StringRecord which actually stores the String
		303	* value.
		304	* Implicitly modifies the result of getValueType() function.
		305	* @see getValueType()
		306	* @param value
		307	*/
		308	public void setValue(String value) {
		309	short id = 0x207;
		310	this.stringRecord = new StringRecord();
		311	stringRecord.setString(value);
		312
313	// the arg is ignored by method it is used only for proper method binding
314	formulaRecord.setValue("");
315	}
316
317	/**
318	* sets the value of the formula evaluation to the specified boolean
319	* Implicitly modifies the result of getValueType() function.
320	* @see getValueType()
321	* @param value
322	*/
323	public void setValue(boolean value) {
324	formulaRecord.setValue(value);
325	}
326
327	/**
328	* sets the value of the formula evaluation as Error
329	* with the specified error code.
330	* Implicitly modifies the result of getValueType() function.
331	* @see getValueType()
332	* @param errorCode
333	*/
334	public void setValue(byte errorCode) {
335	formulaRecord.setValue(errorCode);
336	}
337
338	/**
339	* Use this for setting the value as BLANK
340	* Implicitly modifies the result of getValueType() function.
341	* @see getValueType()
342	*/
343	public void setValue() {
344	formulaRecord.setValue();
345	}
346
237	}	347	}




    }
    
    
    public void testFormulaGetValue() throws Exception {
        byte[] formulaByte = new byte[27];
        formulaByte[0] =(byte)0x00; // row idx
        formulaByte[2] =(byte)0x00; // col idx
        formulaByte[4] =(byte)0x0F; // XF rec idx
        
        formulaByte[14]=(byte)0x08; // opt flag (bits: 0,1,3: recalc, calc on open, part of shared formula)
        formulaByte[18]=(byte)0xE0; // ?? 
        formulaByte[19]=(byte)0xFD; // ??
        formulaByte[20]=(byte)0x05; // ??
        formulaByte[22]=(byte)0x01; // ??
        
        FormulaRecord record = null;
        // STRING VALUE
        formulaByte[6] =(byte)0x00; // calc value: type string
        formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("String value: type check ", FormulaRecord.STRING_VALUE, record.getValueType());
        assertTrue("String value: nan check ", Double.isNaN(record.getValue()));
        
        // BOOL VALUE
        formulaByte[6] =(byte)0x01; // calc value: type bool
        formulaByte[8] = (byte) 0x01;
        formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Bool value: type check ", FormulaRecord.BOOL_VALUE, record.getValueType());
        assertEquals("Bool value: value check ", true, record.getBooleanValue());
        formulaByte[8] = (byte) 0x00;
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Bool value: value check ", false, record.getBooleanValue());
        assertTrue("Bool value: nan check ", Double.isNaN(record.getValue()));
        
        // ERROR VALUE
        formulaByte[6] =(byte)0x02; // calc value: type error
        formulaByte[8] =(byte)0x07; // error type div/0
        formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Error value: type check ", FormulaRecord.ERROR_VALUE, record.getValueType());
        assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_DIVZERO, record.getErrorCodeValue());
        formulaByte[8] =(byte)0x1D; // error type div/0
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_NAME, record.getErrorCodeValue());
        assertTrue("Error value: nan check ", Double.isNaN(record.getValue()));
        
        // BLANK VALUE
        formulaByte[6] =(byte)0x03; // calc value: type blank
        formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Blank value: type check ", FormulaRecord.BLANK_VALUE, record.getValueType());
        assertTrue("Blank value: nan check ", Double.isNaN(record.getValue()));
    }
    
    public void testFormulaSetValue() throws Exception {
        byte[] formulaByte = new byte[27];
        formulaByte[0] =(byte)0x00; // row idx
        formulaByte[2] =(byte)0x00; // col idx
        formulaByte[4] =(byte)0x0F; // XF rec idx
        
        formulaByte[14]=(byte)0x08; // opt flag (bits: 0,1,3: recalc, calc on open, part of shared formula)
        formulaByte[18]=(byte)0xE0; // ?? 
        formulaByte[19]=(byte)0xFD; // ??
        formulaByte[20]=(byte)0x05; // ??
        formulaByte[22]=(byte)0x01; // ??
        
        FormulaRecord record = null;
        // STRING VALUE
        formulaByte[6] =(byte)0x00; // calc value: type string
        formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
        record.setValue("TEST");
        assertEquals("String value: type check ", FormulaRecord.STRING_VALUE, record.getValueType());
        assertTrue("String value: nan check ", Double.isNaN(record.getValue()));
        
        // BOOL VALUE
        formulaByte[6] =(byte)0x01; // calc value: type bool
        formulaByte[8] = (byte) 0x01;
        formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
        record.setValue(true);
        assertEquals("Bool value: type check ", FormulaRecord.BOOL_VALUE, record.getValueType());
        assertEquals("Bool value: value check ", true, record.getBooleanValue());
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        record.setValue(false);
        assertEquals("Bool value: value check ", false, record.getBooleanValue());
        assertTrue("Bool value: nan check ", Double.isNaN(record.getValue()));
        
        // ERROR VALUE
        formulaByte[6] =(byte)0x02; // calc value: type string
        formulaByte[8] =(byte)0x07; // error type div/0
        formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
        record.setValue(FormulaRecord.ERROR_CODE_DIVZERO);
        assertEquals("Error value: type check ", FormulaRecord.ERROR_VALUE, record.getValueType());
        assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_DIVZERO, record.getErrorCodeValue());
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        record.setValue(FormulaRecord.ERROR_CODE_NAME);
        assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_NAME, record.getErrorCodeValue());
        assertTrue("Error value: nan check ", Double.isNaN(record.getValue()));
        
        // BLANK VALUE
        formulaByte[6] =(byte)0x03; // calc value: type blank
        formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
        formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
        record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
        assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
        record.setValue();
        assertEquals("Blank value: type check ", FormulaRecord.BLANK_VALUE, record.getValueType());
        assertTrue("Blank value: nan check ", Double.isNaN(record.getValue()));
    }
    
    public static void main(String [] ignored_args)
    {
        String filename = System.getProperty("HSSF.testdata.path");




        
    }
    
    public void testValueSetters() {
        FormulaRecord f = new FormulaRecord();
        FormulaRecordAggregate fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue(true);
        assertEquals("boolean ", true, fagg.getBooleanValue());
        assertEquals("underlying boolean ", true, f.getBooleanValue());
        assertEquals("type ", FormulaRecord.BOOL_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.BOOL_VALUE, f.getValueType());
        
        f = new FormulaRecord();
        fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue(false);
        assertEquals("boolean ", false, fagg.getBooleanValue());
        assertEquals("underlying boolean ", false, f.getBooleanValue());
        assertEquals("type ", FormulaRecord.BOOL_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.BOOL_VALUE, f.getValueType());
        
        f = new FormulaRecord();
        fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue(FormulaRecord.ERROR_CODE_DIVZERO);
        assertEquals("error ", FormulaRecord.ERROR_CODE_DIVZERO, fagg.getErrorCodeValue());
        assertEquals("underlying error ", FormulaRecord.ERROR_CODE_DIVZERO, f.getErrorCodeValue());
        assertEquals("type ", FormulaRecord.ERROR_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.ERROR_VALUE, f.getValueType());
        
        f = new FormulaRecord();
        fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue(FormulaRecord.ERROR_CODE_NAME);
        assertEquals("error ", FormulaRecord.ERROR_CODE_NAME, fagg.getErrorCodeValue());
        assertEquals("underlying error ", FormulaRecord.ERROR_CODE_NAME, f.getErrorCodeValue());
        assertEquals("type ", FormulaRecord.ERROR_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.ERROR_VALUE, f.getValueType());
        
        f = new FormulaRecord();
        fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue();
        assertEquals("type ", FormulaRecord.BLANK_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.BLANK_VALUE, f.getValueType());
        
        f = new FormulaRecord();
        fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue(12.34);
        assertEquals("number ", 12.34, fagg.getNumberValue(), 0.00000000001);
        assertEquals("underlying error ", 12.34, f.getValue(), 0.00000000001);
        assertEquals("type ", FormulaRecord.NUMERIC_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.NUMERIC_VALUE, f.getValueType());
        
        f = new FormulaRecord();
        fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue(-12.34);
        assertEquals("number ", -12.34, fagg.getNumberValue(), 0.00000000001);
        assertEquals("underlying error ", -12.34, f.getValue(), 0.00000000001);
        assertEquals("type ", FormulaRecord.NUMERIC_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.NUMERIC_VALUE, f.getValueType());
        
        f = new FormulaRecord();
        fagg = new FormulaRecordAggregate(f,null);
        fagg.setValue("Test String");
        assertEquals("string ", "Test String", fagg.getStringValue());
        assertEquals("type ", FormulaRecord.STRING_VALUE, fagg.getValueType());
        assertEquals("underlying type ", FormulaRecord.STRING_VALUE, f.getValueType());
        
    }
    
}

Return to bug 35290

Lines 108-113 Link Here

(-)src/testcases/org/apache/poi/hssf/record/TestFormulaRecord.java (+113 lines)
108	}	108	}
109		109
110		110
		111	public void testFormulaGetValue() throws Exception {
		112	byte[] formulaByte = new byte[27];
		113	formulaByte[0] =(byte)0x00; // row idx
		114	formulaByte[2] =(byte)0x00; // col idx
		115	formulaByte[4] =(byte)0x0F; // XF rec idx
		116
		117	formulaByte[14]=(byte)0x08; // opt flag (bits: 0,1,3: recalc, calc on open, part of shared formula)
		118	formulaByte[18]=(byte)0xE0; // ??
		119	formulaByte[19]=(byte)0xFD; // ??
		120	formulaByte[20]=(byte)0x05; // ??
		121	formulaByte[22]=(byte)0x01; // ??
		122
		123	FormulaRecord record = null;
		124	// STRING VALUE
		125	formulaByte[6] =(byte)0x00; // calc value: type string
		126	formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
		127	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
		128	assertEquals("String value: type check ", FormulaRecord.STRING_VALUE, record.getValueType());
		129	assertTrue("String value: nan check ", Double.isNaN(record.getValue()));
		130
		131	// BOOL VALUE
		132	formulaByte[6] =(byte)0x01; // calc value: type bool
		133	formulaByte[8] = (byte) 0x01;
		134	formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
		135	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
		136	assertEquals("Bool value: type check ", FormulaRecord.BOOL_VALUE, record.getValueType());
		137	assertEquals("Bool value: value check ", true, record.getBooleanValue());
		138	formulaByte[8] = (byte) 0x00;
		139	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
		140	assertEquals("Bool value: value check ", false, record.getBooleanValue());
		141	assertTrue("Bool value: nan check ", Double.isNaN(record.getValue()));
		142
		143	// ERROR VALUE
		144	formulaByte[6] =(byte)0x02; // calc value: type error
		145	formulaByte[8] =(byte)0x07; // error type div/0
		146	formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
		147	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
		148	assertEquals("Error value: type check ", FormulaRecord.ERROR_VALUE, record.getValueType());
		149	assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_DIVZERO, record.getErrorCodeValue());
		150	formulaByte[8] =(byte)0x1D; // error type div/0
		151	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
		152	assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_NAME, record.getErrorCodeValue());
		153	assertTrue("Error value: nan check ", Double.isNaN(record.getValue()));
		154
		155	// BLANK VALUE
		156	formulaByte[6] =(byte)0x03; // calc value: type blank
		157	formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
		158	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
		159	assertEquals("Blank value: type check ", FormulaRecord.BLANK_VALUE, record.getValueType());
		160	assertTrue("Blank value: nan check ", Double.isNaN(record.getValue()));
		161	}
		162
		163	public void testFormulaSetValue() throws Exception {
		164	byte[] formulaByte = new byte[27];
		165	formulaByte[0] =(byte)0x00; // row idx
		166	formulaByte[2] =(byte)0x00; // col idx
		167	formulaByte[4] =(byte)0x0F; // XF rec idx
		168
		169	formulaByte[14]=(byte)0x08; // opt flag (bits: 0,1,3: recalc, calc on open, part of shared formula)
		170	formulaByte[18]=(byte)0xE0; // ??
		171	formulaByte[19]=(byte)0xFD; // ??
		172	formulaByte[20]=(byte)0x05; // ??
		173	formulaByte[22]=(byte)0x01; // ??
		174
175	FormulaRecord record = null;
176	// STRING VALUE
177	formulaByte[6] =(byte)0x00; // calc value: type string
178	formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
179	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
180	assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
181	record.setValue("TEST");
182	assertEquals("String value: type check ", FormulaRecord.STRING_VALUE, record.getValueType());
183	assertTrue("String value: nan check ", Double.isNaN(record.getValue()));
184
185	// BOOL VALUE
186	formulaByte[6] =(byte)0x01; // calc value: type bool
187	formulaByte[8] = (byte) 0x01;
188	formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
189	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
190	assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
191	record.setValue(true);
192	assertEquals("Bool value: type check ", FormulaRecord.BOOL_VALUE, record.getValueType());
193	assertEquals("Bool value: value check ", true, record.getBooleanValue());
194	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
195	record.setValue(false);
196	assertEquals("Bool value: value check ", false, record.getBooleanValue());
197	assertTrue("Bool value: nan check ", Double.isNaN(record.getValue()));
198
199	// ERROR VALUE
200	formulaByte[6] =(byte)0x02; // calc value: type string
201	formulaByte[8] =(byte)0x07; // error type div/0
202	formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
203	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
204	assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
205	record.setValue(FormulaRecord.ERROR_CODE_DIVZERO);
206	assertEquals("Error value: type check ", FormulaRecord.ERROR_VALUE, record.getValueType());
207	assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_DIVZERO, record.getErrorCodeValue());
208	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
209	record.setValue(FormulaRecord.ERROR_CODE_NAME);
210	assertEquals("Error value: value check ", FormulaRecord.ERROR_CODE_NAME, record.getErrorCodeValue());
211	assertTrue("Error value: nan check ", Double.isNaN(record.getValue()));
212
213	// BLANK VALUE
214	formulaByte[6] =(byte)0x03; // calc value: type blank
215	formulaByte[12] =(byte)0xFF; formulaByte[13] =(byte)0xFF; // 11 bits: 7FF = "IEEE 754 NaN"
216	formulaByte[12] =(byte)0x00; formulaByte[13] =(byte)0x00; // numeric zero
217	record = new FormulaRecord(FormulaRecord.sid, (short)27, formulaByte);
218	assertEquals("Numeric check ", FormulaRecord.NUMERIC_VALUE, record.getValueType());
219	record.setValue();
220	assertEquals("Blank value: type check ", FormulaRecord.BLANK_VALUE, record.getValueType());
221	assertTrue("Blank value: nan check ", Double.isNaN(record.getValue()));
222	}
223
111	public static void main(String [] ignored_args)	224	public static void main(String [] ignored_args)
112	{	225	{
113	String filename = System.getProperty("HSSF.testdata.path");	226	String filename = System.getProperty("HSSF.testdata.path");

Lines 27-32 Link Here

(-)src/java/org/apache/poi/hssf/record/FormulaRecord.java (-14 / +180 lines)
27	import java.util.Stack;	27	import java.util.Stack;
28		28
29	import org.apache.poi.hssf.record.formula.Ptg;	29	import org.apache.poi.hssf.record.formula.Ptg;
		30	import org.apache.poi.util.HexDump;
30	import org.apache.poi.util.LittleEndian;	31	import org.apache.poi.util.LittleEndian;
31		32
32	/**	33	/**
Lines 43-48 Link Here
43	{	44	{
44		45
45	public static final boolean EXPERIMENTAL_FORMULA_SUPPORT_ENABLED=true;	46	public static final boolean EXPERIMENTAL_FORMULA_SUPPORT_ENABLED=true;
		47	public static final byte STRING_VALUE = 0x00;
		48	public static final byte BOOL_VALUE = 0x01;
		49	public static final byte ERROR_VALUE = 0x02;
		50	public static final byte BLANK_VALUE = 0x03;
		51	public static final byte NUMERIC_VALUE = Byte.MAX_VALUE; // non std code
		52
		53	public static final byte ERROR_CODE_NULL = 0x00; // #NULL! Intersection of two cell ranges is empty
		54	public static final byte ERROR_CODE_DIVZERO = 0x07; // #DIV/0! Error
		55	public static final byte ERROR_CODE_VALUE = 0x0F; // #VALUE! Wrong type of operand
		56	public static final byte ERROR_CODE_REF = 0x17; // #REF! Illegal or deleted cell reference
		57	public static final byte ERROR_CODE_NAME = 0x1D; // #NAME? Wrong function or range name
		58	public static final byte ERROR_CODE_NUM = 0x24; // #NUM! Value range overflow
		59	public static final byte ERROR_CODE_NA = 0x2A ; // #N/A! Argument or function not available
46		60
47	public static final short sid =	61	public static final short sid =
48	0x06; // docs say 406...because of a bug Microsoft support site article #Q184647)	62	0x06; // docs say 406...because of a bug Microsoft support site article #Q184647)
Lines 51-62 Link Here
51	private int field_1_row;	65	private int field_1_row;
52	private short field_2_column;	66	private short field_2_column;
53	private short field_3_xf;	67	private short field_3_xf;
		68
		69	// stored formula result can be numeric/string/bool/error/<blank>
54	private double field_4_value;	70	private double field_4_value;
		71	//private String field_4_string; Commented since string is stored externally
		72	private boolean field_4_bool;
		73	private byte field_4_errtype;
		74
55	private short field_5_options;	75	private short field_5_options;
56	private int field_6_zero;	76	private int field_6_zero;
57	private short field_7_expression_len;	77	private short field_7_expression_len;
58	private Stack field_8_parsed_expr;	78	private Stack field_8_parsed_expr;
59		79
		80	private byte value_type_id; //
		81
60	/**	82	/**
61	* Since the NaN support seems sketchy (different constants) we'll store and spit it out directly	83	* Since the NaN support seems sketchy (different constants) we'll store and spit it out directly
62	*/	84	*/
Lines 107-118 Link Here
107	field_1_row = LittleEndian.getUShort(data, 0 + offset);	129	field_1_row = LittleEndian.getUShort(data, 0 + offset);
108	field_2_column = LittleEndian.getShort(data, 2 + offset);	130	field_2_column = LittleEndian.getShort(data, 2 + offset);
109	field_3_xf = LittleEndian.getShort(data, 4 + offset);	131	field_3_xf = LittleEndian.getShort(data, 4 + offset);
110	field_4_value = LittleEndian.getDouble(data, 6 + offset);
111	field_5_options = LittleEndian.getShort(data, 14 + offset);	132	field_5_options = LittleEndian.getShort(data, 14 + offset);
		133	field_4_value = LittleEndian.getDouble(data, 6 + offset);
112		134
		135	value_data = new byte[8];
		136	System.arraycopy(data, offset+6, value_data, 0, 8);
		137
113	if (Double.isNaN(field_4_value)) {	138	if (Double.isNaN(field_4_value)) {
114	value_data = new byte[8];	139	value_type_id = value_data[0];
115	System.arraycopy(data, offset+6, value_data, 0, 8);	140	switch (value_type_id) {
		141	case ERROR_VALUE:
		142	field_4_errtype = value_data[2];
		143	break;
		144	case BOOL_VALUE:
		145	field_4_bool = (value_data[2] == 1);
		146	break;
		147	case STRING_VALUE:
		148	// do nothing since string is stored outside this record
		149	break;
		150	default: //blank
		151	}
		152	}
		153	else {
		154	value_type_id = NUMERIC_VALUE;
116	}	155	}
117		156
118	field_6_zero = LittleEndian.getInt(data, 16 + offset);	157	field_6_zero = LittleEndian.getInt(data, 16 + offset);
Lines 165-179 Link Here
165	}	204	}
166		205
167	/**	206	/**
168	* set the calculated value of the formula	207	* set the calculated numeric value of the formula
169	*	208	*
170	* @param value calculated value	209	* @param value calculated value
171	*/	210	*/
172		211
173	public void setValue(double value)	212	public void setValue(double value) {
174	{	213	if (Double.isNaN(value)) {
175	field_4_value = value;	214	setValue(ERROR_CODE_DIVZERO);
		215	}
		216	else if (Double.isInfinite(value)) {
		217	setValue(ERROR_CODE_NUM);
		218	}
		219	else {
		220	value_type_id = NUMERIC_VALUE;
		221	field_4_value = value;
		222	}
		223	}
		224
		225	/**
		226	* set the calculated boolean value of the formula
		227	*
		228	* @param value calculated value
		229	*/
		230	public void setValue(boolean value) {
		231	field_4_value = Double.NaN;
		232	value_type_id = BOOL_VALUE;
		233	field_4_bool = value;
		234
		235	if (value_data==null) value_data = new byte[8];
		236	value_data[0] = value_type_id;
		237	value_data[2] = (byte) (value ? 1 : 0);
		238	value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
		239	}
		240
		241	/**
		242	* set the calculated string value of the formula
		243	*
		244	* @param value value is ignored. calculated value
		245	* is stored outside the formula record.
		246	*/
		247	public void setValue(String value) {
		248	field_4_value = Double.NaN;
		249	value_type_id = STRING_VALUE;
		250
		251	if (value_data==null) value_data = new byte[8];
		252	value_data[0] = value_type_id;
		253	value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
		254	}
		255
		256	/**
		257	* set the error code as result of the formula
		258	* Use the error codes defined in this class.
		259	* @param value calculated value
		260	*/
		261	public void setValue(byte value) {
		262	field_4_value = Double.NaN;
		263	value_type_id = ERROR_VALUE;
		264	field_4_errtype = value;
		265
		266	if (value_data==null) value_data = new byte[8];
		267	value_data[0] = value_type_id;
		268	value_data[2] = value;
		269	value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
		270	}
		271
		272	/**
		273	* set the calculated value of the formula as blank
		274	*
		275	* @param value calculated value
		276	*/
		277	public void setValue() {
		278	field_4_value = Double.NaN;
279	value_type_id = BLANK_VALUE;
280
281	if (value_data==null) value_data = new byte[8];
282	value_data[0] = value_type_id;
283	value_data[6] = (byte) 0xFF; value_data[7] = (byte) 0xFF;
176	}	284	}
		285
177		286
178	/**	287	/**
179	* set the option flags	288	* set the option flags
Lines 213-220 Link Here
213	}	322	}
214		323
215	/**	324	/**
		325	* returned value indicates the type of formula
		326	* value. Depending on the returned value, the
		327	* appropriate getXYZValue() or getValue() method
		328	* should be called to get the actual value.
		329	* @see getValue(), getStringValue(), getBoolValue(),
		330	* getErrorCodeValue()
		331	* @return
		332	*/
		333	public byte getValueType() {
		334	return value_type_id;
		335	}
		336
		337	/**
216	* get the calculated value of the formula	338	* get the calculated value of the formula
217	*	339	* @see getValueType()
218	* @return calculated value	340	* @return calculated value
219	*/	341	*/
220		342
Lines 222-228 Link Here
222	{	344	{
223	return field_4_value;	345	return field_4_value;
224	}	346	}
225		347
		348	/**
		349	* get the calculated value of the formula
		350	* (to be used when the formula value is of type boolean)
		351	* @see getValueType()
		352	* @return
		353	*/
		354	public boolean getBooleanValue() {
		355	return field_4_bool;
		356	}
		357
		358	/**
		359	* get the calculated value of the formula
		360	* (to be used when the formula value is of type error)
		361	* @see getValueType()
		362	* @return
		363	*/
		364	public byte getErrorCodeValue() {
		365	return field_4_errtype;
		366	}
		367
226	/**	368	/**
227	* get the option flags	369	* get the option flags
228	*	370	*
Lines 520-529 Link Here
520	.append("\n");	662	.append("\n");
521	buffer.append(" .xf = ")	663	buffer.append(" .xf = ")
522	.append(Integer.toHexString(getXFIndex())).append("\n");	664	.append(Integer.toHexString(getXFIndex())).append("\n");
523	if (Double.isNaN(this.getValue()) && value_data != null)	665	if (Double.isNaN(this.getValue()) && value_data != null) {
524	buffer.append(" .value (NaN) = ")	666	buffer.append(" .value ");
525	.append(org.apache.poi.util.HexDump.dump(value_data,0,0))	667	switch (value_type_id) {
526	.append("\n");	668	case STRING_VALUE:
		669	buffer.append("(String) [string is stored outside formula record] ");
		670	// note: string value is not stored in this record.
		671	break;
		672	case ERROR_VALUE:
		673	buffer.append("(ErrorCode) = ");
		674	buffer.append(field_4_errtype);
		675	break;
		676	case BOOL_VALUE:
		677	buffer.append("(Bool) = ");
		678	buffer.append(field_4_bool);
		679	break;
		680	case BLANK_VALUE:
		681	buffer.append("(Blank) = ");
		682	break;
		683	default:
		684	buffer.append("(UNKNOWN) = ");
		685	buffer.append(HexDump.dump(value_data, 0, 0));
		686	}
		687	buffer.append("\n");
		688	}
527	else	689	else
528	buffer.append(" .value = ").append(getValue())	690	buffer.append(" .value = ").append(getValue())
529	.append("\n");	691	.append("\n");
Lines 566-572 Link Here
566	rec.field_1_row = field_1_row;	728	rec.field_1_row = field_1_row;
567	rec.field_2_column = field_2_column;	729	rec.field_2_column = field_2_column;
568	rec.field_3_xf = field_3_xf;	730	rec.field_3_xf = field_3_xf;
569	rec.field_4_value = field_4_value;	731	rec.field_4_value = field_4_value; // numeric
		732	rec.field_4_bool = field_4_bool; // boolean
		733	rec.field_4_errtype = field_4_errtype; // error code
		734	rec.value_type_id = value_type_id; // type indicator
		735
570	rec.field_5_options = field_5_options;	736	rec.field_5_options = field_5_options;
571	rec.field_6_zero = field_6_zero;	737	rec.field_6_zero = field_6_zero;
572	rec.field_7_expression_len = field_7_expression_len;	738	rec.field_7_expression_len = field_7_expression_len;

Lines 47-50 Link Here

(-)src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java (+64 lines)
47		47
48	}	48	}
49		49
		50	public void testValueSetters() {
		51	FormulaRecord f = new FormulaRecord();
		52	FormulaRecordAggregate fagg = new FormulaRecordAggregate(f,null);
		53	fagg.setValue(true);
		54	assertEquals("boolean ", true, fagg.getBooleanValue());
		55	assertEquals("underlying boolean ", true, f.getBooleanValue());
		56	assertEquals("type ", FormulaRecord.BOOL_VALUE, fagg.getValueType());
		57	assertEquals("underlying type ", FormulaRecord.BOOL_VALUE, f.getValueType());
		58
		59	f = new FormulaRecord();
		60	fagg = new FormulaRecordAggregate(f,null);
		61	fagg.setValue(false);
		62	assertEquals("boolean ", false, fagg.getBooleanValue());
		63	assertEquals("underlying boolean ", false, f.getBooleanValue());
		64	assertEquals("type ", FormulaRecord.BOOL_VALUE, fagg.getValueType());
		65	assertEquals("underlying type ", FormulaRecord.BOOL_VALUE, f.getValueType());
		66
		67	f = new FormulaRecord();
		68	fagg = new FormulaRecordAggregate(f,null);
		69	fagg.setValue(FormulaRecord.ERROR_CODE_DIVZERO);
		70	assertEquals("error ", FormulaRecord.ERROR_CODE_DIVZERO, fagg.getErrorCodeValue());
		71	assertEquals("underlying error ", FormulaRecord.ERROR_CODE_DIVZERO, f.getErrorCodeValue());
		72	assertEquals("type ", FormulaRecord.ERROR_VALUE, fagg.getValueType());
		73	assertEquals("underlying type ", FormulaRecord.ERROR_VALUE, f.getValueType());
		74
		75	f = new FormulaRecord();
		76	fagg = new FormulaRecordAggregate(f,null);
		77	fagg.setValue(FormulaRecord.ERROR_CODE_NAME);
		78	assertEquals("error ", FormulaRecord.ERROR_CODE_NAME, fagg.getErrorCodeValue());
		79	assertEquals("underlying error ", FormulaRecord.ERROR_CODE_NAME, f.getErrorCodeValue());
		80	assertEquals("type ", FormulaRecord.ERROR_VALUE, fagg.getValueType());
		81	assertEquals("underlying type ", FormulaRecord.ERROR_VALUE, f.getValueType());
		82
		83	f = new FormulaRecord();
		84	fagg = new FormulaRecordAggregate(f,null);
		85	fagg.setValue();
		86	assertEquals("type ", FormulaRecord.BLANK_VALUE, fagg.getValueType());
		87	assertEquals("underlying type ", FormulaRecord.BLANK_VALUE, f.getValueType());
		88
		89	f = new FormulaRecord();
		90	fagg = new FormulaRecordAggregate(f,null);
		91	fagg.setValue(12.34);
		92	assertEquals("number ", 12.34, fagg.getNumberValue(), 0.00000000001);
		93	assertEquals("underlying error ", 12.34, f.getValue(), 0.00000000001);
		94	assertEquals("type ", FormulaRecord.NUMERIC_VALUE, fagg.getValueType());
		95	assertEquals("underlying type ", FormulaRecord.NUMERIC_VALUE, f.getValueType());
		96
		97	f = new FormulaRecord();
		98	fagg = new FormulaRecordAggregate(f,null);
		99	fagg.setValue(-12.34);
		100	assertEquals("number ", -12.34, fagg.getNumberValue(), 0.00000000001);
		101	assertEquals("underlying error ", -12.34, f.getValue(), 0.00000000001);
		102	assertEquals("type ", FormulaRecord.NUMERIC_VALUE, fagg.getValueType());
		103	assertEquals("underlying type ", FormulaRecord.NUMERIC_VALUE, f.getValueType());
		104
		105	f = new FormulaRecord();
		106	fagg = new FormulaRecordAggregate(f,null);
		107	fagg.setValue("Test String");
		108	assertEquals("string ", "Test String", fagg.getStringValue());
		109	assertEquals("type ", FormulaRecord.STRING_VALUE, fagg.getValueType());
		110	assertEquals("underlying type ", FormulaRecord.STRING_VALUE, f.getValueType());
		111
		112	}
		113
50	}	114	}