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

(-)src/java/org/apache/poi/hssf/usermodel/HSSFErrorConstants.java (-17 / +59 lines)
Lines 15-40 Link Here
15
   limitations under the License.
15
   limitations under the License.
16
==================================================================== */
16
==================================================================== */
17
17
18
19
/*
20
 * HSSFErrorConstants.java
21
 *
22
 * Created on January 19, 2002, 9:30 AM
23
 */
24
package org.apache.poi.hssf.usermodel;
18
package org.apache.poi.hssf.usermodel;
25
19
26
/**
20
/**
27
 * contains constants representing Excel error codes.
21
 * Contains raw Excel error codes (as defined in OOO's excelfileformat.pdf (2.5.6)
22
 * 
28
 * @author  Michael Harhen
23
 * @author  Michael Harhen
29
 */
24
 */
30
25
public final class HSSFErrorConstants {
31
public interface HSSFErrorConstants
26
    private HSSFErrorConstants() {
32
{
27
        // no instances of this class
33
    public static final byte ERROR_NULL  = 0x00;   // #NULL!
28
    }
34
    public static final byte ERROR_DIV_0 = 0x07;   // #DIV/0!
29
    
35
    public static final byte ERROR_VALUE = 0x0f;   // #VALUE!
30
    /** <b>#NULL!</b>  - Intersection of two cell ranges is empty */
36
    public static final byte ERROR_REF   = 0x17;   // #REF!
31
    public static final int ERROR_NULL = 0x00;
37
    public static final byte ERROR_NAME  = 0x1d;   // #NAME?
32
    /** <b>#DIV/0!</b> - Division by zero */
38
    public static final byte ERROR_NUM   = 0x24;   // #NUM!
33
    public static final int ERROR_DIV_0 = 0x07;
39
    public static final byte ERROR_NA    = 0x2a;   // #N/A
34
    /** <b>#VALUE!</b> - Wrong type of operand */
35
    public static final int ERROR_VALUE = 0x0F; 
36
    /** <b>#REF!</b> - Illegal or deleted cell reference */
37
    public static final int ERROR_REF = 0x17;  
38
    /** <b>#NAME?</b> - Wrong function or range name */
39
    public static final int ERROR_NAME = 0x1D; 
40
    /** <b>#NUM!</b> - Value range overflow */
41
    public static final int ERROR_NUM = 0x24; 
42
    /** <b>#N/A</b> - Argument or function not available */
43
    public static final int ERROR_NA = 0x2A;
44
    
45
    
46
    /**
47
     * @return Standard Excel error literal for the specified error code. 
48
     * @throws IllegalArgumentException if the specified error code is not one of the 7 
49
     * standard error codes
50
     */
51
    public static final String getText(int errorCode) {
52
        switch(errorCode) {
53
            case ERROR_NULL:  return "#NULL!";
54
            case ERROR_DIV_0: return "#DIV/0!";
55
            case ERROR_VALUE: return "#VALUE!";
56
            case ERROR_REF:   return "#REF!";
57
            case ERROR_NAME:  return "#NAME?";
58
            case ERROR_NUM:   return "#NUM!";
59
            case ERROR_NA:    return "#N/A";
60
        }
61
        throw new IllegalArgumentException("Bad error code (" + errorCode + ")");
62
    }
63
    
64
    /**
65
     * @return <code>true</code> if the specified error code is a standard Excel error code. 
66
     */
67
    public static final boolean isValidCode(int errorCode) {
68
        // This method exists because it would be bad to force clients to catch 
69
        // IllegalArgumentException if there were potential for passing an invalid error code.  
70
        switch(errorCode) {
71
            case ERROR_NULL:
72
            case ERROR_DIV_0:
73
            case ERROR_VALUE:
74
            case ERROR_REF:
75
            case ERROR_NAME:
76
            case ERROR_NUM:
77
            case ERROR_NA:
78
                return true;
79
        }
80
        return false;
81
    }
40
}
82
}
(-)src/java/org/apache/poi/hssf/record/formula/ErrPtg.java (-39 / +40 lines)
Lines 26-91 Link Here
26
/**
26
/**
27
 * @author Daniel Noll (daniel at nuix dot com dot au)
27
 * @author Daniel Noll (daniel at nuix dot com dot au)
28
 */
28
 */
29
public class ErrPtg extends Ptg
29
public final class ErrPtg extends Ptg {
30
{
30
    
31
    // convenient access to namespace
32
    private static final HSSFErrorConstants EC = null;
33
    
34
    /** <b>#NULL!</b>  - Intersection of two cell ranges is empty */
35
    public static final ErrPtg NULL_INTERSECTION = new ErrPtg(EC.ERROR_NULL); 
36
    /** <b>#DIV/0!</b> - Division by zero */
37
    public static final ErrPtg DIV_ZERO = new ErrPtg(EC.ERROR_DIV_0);
38
    /** <b>#VALUE!</b> - Wrong type of operand */
39
    public static final ErrPtg VALUE_INVALID = new ErrPtg(EC.ERROR_VALUE);
40
    /** <b>#REF!</b> - Illegal or deleted cell reference */
41
    public static final ErrPtg REF_INVALID = new ErrPtg(EC.ERROR_REF);
42
    /** <b>#NAME?</b> - Wrong function or range name */
43
    public static final ErrPtg NAME_INVALID = new ErrPtg(EC.ERROR_NAME); 
44
    /** <b>#NUM!</b> - Value range overflow */
45
    public static final ErrPtg NUM_ERROR = new ErrPtg(EC.ERROR_NUM);
46
    /** <b>#N/A</b> - Argument or function not available */
47
    public static final ErrPtg N_A = new ErrPtg(EC.ERROR_NA);
48
    
49
    
31
    public static final short sid  = 0x1c;
50
    public static final short sid  = 0x1c;
32
    private static final int  SIZE = 2;
51
    private static final int  SIZE = 2;
33
    private byte              field_1_error_code;
52
    private int              field_1_error_code;
34
53
35
    /** Creates new ErrPtg */
54
    /** Creates new ErrPtg */
36
55
37
    public ErrPtg()
56
    public ErrPtg(int errorCode) {
38
    {
57
        if(!HSSFErrorConstants.isValidCode(errorCode)) {
58
            throw new IllegalArgumentException("Invalid error code (" + errorCode + ")");
59
        }
60
        field_1_error_code = errorCode;
39
    }
61
    }
40
62
 
41
    public ErrPtg(RecordInputStream in)
63
    public ErrPtg(RecordInputStream in) {
42
    {
64
        this(in.readByte());
43
        field_1_error_code = in.readByte();
44
    }
65
    }
45
66
46
    public void writeBytes(byte [] array, int offset)
67
    public void writeBytes(byte [] array, int offset)
47
    {
68
    {
48
        array[offset] = (byte) (sid + ptgClass);
69
        array[offset] = (byte) (sid + ptgClass);
49
        array[offset + 1] = field_1_error_code;
70
        array[offset + 1] = (byte)field_1_error_code;
50
    }
71
    }
51
72
52
    public String toFormulaString(Workbook book)
73
    public String toFormulaString(Workbook book) {
53
    {
74
        return HSSFErrorConstants.getText(field_1_error_code);
54
        switch(field_1_error_code)
55
        {
56
            case HSSFErrorConstants.ERROR_NULL:
57
                return "#NULL!";
58
            case HSSFErrorConstants.ERROR_DIV_0:
59
                return "#DIV/0!";
60
            case HSSFErrorConstants.ERROR_VALUE:
61
                return "#VALUE!";
62
            case HSSFErrorConstants.ERROR_REF:
63
                return "#REF!";
64
            case HSSFErrorConstants.ERROR_NAME:
65
                return "#NAME?";
66
            case HSSFErrorConstants.ERROR_NUM:
67
                return "#NUM!";
68
            case HSSFErrorConstants.ERROR_NA:
69
                return "#N/A";
70
        }
71
72
        // Shouldn't happen anyway.  Excel docs say that this is returned for all other codes.
73
        return "#N/A";
74
    }
75
    }
75
76
76
    public int getSize()
77
    public int getSize() {
77
    {
78
        return SIZE;
78
        return SIZE;
79
    }
79
    }
80
80
81
    public byte getDefaultOperandClass()
81
    public byte getDefaultOperandClass() {
82
    {
83
        return Ptg.CLASS_VALUE;
82
        return Ptg.CLASS_VALUE;
84
    }
83
    }
85
84
86
    public Object clone() {
85
    public Object clone() {
87
        ErrPtg ptg = new ErrPtg();
86
        return new ErrPtg(field_1_error_code);
88
        ptg.field_1_error_code = field_1_error_code;
89
        return ptg;
90
    }
87
    }
88
89
    public int getErrorCode() {
90
        return field_1_error_code;
91
    }
91
}
92
}
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/ErrorEval.java (-70 / +63 lines)
Lines 14-125 Link Here
14
* See the License for the specific language governing permissions and
14
* See the License for the specific language governing permissions and
15
* limitations under the License.
15
* limitations under the License.
16
*/
16
*/
17
/*
17
18
 * Created on May 8, 2005
19
 *
20
 */
21
package org.apache.poi.hssf.record.formula.eval;
18
package org.apache.poi.hssf.record.formula.eval;
22
19
20
import org.apache.poi.hssf.usermodel.HSSFErrorConstants;
21
23
/**
22
/**
24
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
23
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
25
 * 
24
 *
26
 */
25
 */
27
public final class ErrorEval implements ValueEval {
26
public final class ErrorEval implements ValueEval {
28
    /**
29
     * Contains raw Excel error codes (as defined in OOO's excelfileformat.pdf (2.5.6)
30
     */
31
    private static final class ErrorCode {
32
        /** <b>#NULL!</b>  - Intersection of two cell ranges is empty */
33
        public static final int NULL = 0x00;
34
        /** <b>#DIV/0!</b> - Division by zero */
35
        public static final int DIV_0 = 0x07;
36
        /** <b>#VALUE!</b> - Wrong type of operand */
37
        public static final int VALUE = 0x0F; 
38
        /** <b>#REF!</b> - Illegal or deleted cell reference */
39
        public static final int REF = 0x17;  
40
        /** <b>#NAME?</b> - Wrong function or range name */
41
        public static final int NAME = 0x1D; 
42
        /** <b>#NUM!</b> - Value range overflow */
43
        public static final int NUM = 0x24; 
44
        /** <b>#N/A</b> - Argument or function not available */
45
        public static final int N_A = 0x2A;   
46
        
47
        public static final String getText(int errorCode) {
48
            switch(errorCode) {
49
                case NULL:  return "#NULL!";
50
                case DIV_0: return "#DIV/0!";
51
                case VALUE: return "#VALUE!";
52
                case REF:   return "#REF!";
53
                case NAME:  return "#NAME?";
54
                case NUM:   return "#NUM!";
55
                case N_A:   return "#N/A";
56
            }
57
            return "???";
58
        }
59
    }
60
27
28
    // convenient access to namespace
29
    private static final HSSFErrorConstants EC = null;
30
61
    /** <b>#NULL!</b>  - Intersection of two cell ranges is empty */
31
    /** <b>#NULL!</b>  - Intersection of two cell ranges is empty */
62
    public static final ErrorEval NULL_INTERSECTION = new ErrorEval(ErrorCode.NULL); 
32
    public static final ErrorEval NULL_INTERSECTION = new ErrorEval(EC.ERROR_NULL);
63
    /** <b>#DIV/0!</b> - Division by zero */
33
    /** <b>#DIV/0!</b> - Division by zero */
64
    public static final ErrorEval DIV_ZERO = new ErrorEval(ErrorCode.DIV_0);
34
    public static final ErrorEval DIV_ZERO = new ErrorEval(EC.ERROR_DIV_0);
65
    /** <b>#VALUE!</b> - Wrong type of operand */
35
    /** <b>#VALUE!</b> - Wrong type of operand */
66
    public static final ErrorEval VALUE_INVALID = new ErrorEval(ErrorCode.VALUE);
36
    public static final ErrorEval VALUE_INVALID = new ErrorEval(EC.ERROR_VALUE);
67
    /** <b>#REF!</b> - Illegal or deleted cell reference */
37
    /** <b>#REF!</b> - Illegal or deleted cell reference */
68
    public static final ErrorEval REF_INVALID = new ErrorEval(ErrorCode.REF);
38
    public static final ErrorEval REF_INVALID = new ErrorEval(EC.ERROR_REF);
69
    /** <b>#NAME?</b> - Wrong function or range name */
39
    /** <b>#NAME?</b> - Wrong function or range name */
70
    public static final ErrorEval NAME_INVALID = new ErrorEval(ErrorCode.NAME); 
40
    public static final ErrorEval NAME_INVALID = new ErrorEval(EC.ERROR_NAME);
71
    /** <b>#NUM!</b> - Value range overflow */
41
    /** <b>#NUM!</b> - Value range overflow */
72
    public static final ErrorEval NUM_ERROR = new ErrorEval(ErrorCode.NUM);
42
    public static final ErrorEval NUM_ERROR = new ErrorEval(EC.ERROR_NUM);
73
    /** <b>#N/A</b> - Argument or function not available */
43
    /** <b>#N/A</b> - Argument or function not available */
74
    public static final ErrorEval NA = new ErrorEval(ErrorCode.N_A);
44
    public static final ErrorEval NA = new ErrorEval(EC.ERROR_NA);
75
45
76
    
46
47
    // POI internal error codes
48
    private static final int CIRCULAR_REF_ERROR_CODE = 0xFFFFFFC4;
49
    private static final int FUNCTION_NOT_IMPLEMENTED_CODE = 0xFFFFFFE2;
50
77
    /**
51
    /**
78
     * Translates an Excel internal error code into the corresponding POI ErrorEval instance 
52
     * @deprecated do not use this error code. For conditions that should never occur, throw an
53
     *  unchecked exception. For all other situations use the error code that corresponds to the
54
     *  error Excel would have raised under the same circumstances.
55
     */
56
    public static final ErrorEval UNKNOWN_ERROR = new ErrorEval(-20);
57
    public static final ErrorEval FUNCTION_NOT_IMPLEMENTED = new ErrorEval(FUNCTION_NOT_IMPLEMENTED_CODE);
58
    // Note - Excel does not seem to represent this condition with an error code
59
    public static final ErrorEval CIRCULAR_REF_ERROR = new ErrorEval(CIRCULAR_REF_ERROR_CODE);
60
61
62
    /**
63
     * Translates an Excel internal error code into the corresponding POI ErrorEval instance
79
     * @param errorCode
64
     * @param errorCode
80
     */
65
     */
81
    public static ErrorEval valueOf(int errorCode) {
66
    public static ErrorEval valueOf(int errorCode) {
82
        switch(errorCode) {
67
        switch(errorCode) {
83
            case ErrorCode.NULL: return NULL_INTERSECTION;
68
            case HSSFErrorConstants.ERROR_NULL:  return NULL_INTERSECTION;
84
            case ErrorCode.DIV_0: return DIV_ZERO;
69
            case HSSFErrorConstants.ERROR_DIV_0: return DIV_ZERO;
85
            case ErrorCode.VALUE: return VALUE_INVALID;
70
            case HSSFErrorConstants.ERROR_VALUE: return VALUE_INVALID;
86
//            case ErrorCode.REF: return REF_INVALID;
71
            case HSSFErrorConstants.ERROR_REF:   return REF_INVALID;
87
            case ErrorCode.REF: return UNKNOWN_ERROR;
72
            case HSSFErrorConstants.ERROR_NAME:  return NAME_INVALID;
88
            case ErrorCode.NAME: return NAME_INVALID;
73
            case HSSFErrorConstants.ERROR_NUM:   return NUM_ERROR;
89
            case ErrorCode.NUM: return NUM_ERROR;
74
            case HSSFErrorConstants.ERROR_NA:    return NA;
90
            case ErrorCode.N_A: return NA;
75
            // non-std errors (conditions modeled as errors by POI)
91
            
76
            case CIRCULAR_REF_ERROR_CODE:        return CIRCULAR_REF_ERROR;
92
            // these cases probably shouldn't be coming through here 
77
            case FUNCTION_NOT_IMPLEMENTED_CODE:  return FUNCTION_NOT_IMPLEMENTED;
93
            // but (as of Jan-2008) a lot of code depends on it. 
94
//            case -20: return UNKNOWN_ERROR;
95
//            case -30: return FUNCTION_NOT_IMPLEMENTED;
96
//            case -60: return CIRCULAR_REF_ERROR;
97
        }
78
        }
98
        throw new RuntimeException("Unexpected error code (" + errorCode + ")");
79
        throw new RuntimeException("Unexpected error code (" + errorCode + ")");
99
    }
80
    }
100
    
101
    // POI internal error codes
102
    public static final ErrorEval UNKNOWN_ERROR = new ErrorEval(-20);
103
    public static final ErrorEval FUNCTION_NOT_IMPLEMENTED = new ErrorEval(-30);
104
    // Note - Excel does not seem to represent this condition with an error code
105
    public static final ErrorEval CIRCULAR_REF_ERROR = new ErrorEval(-60); 
106
81
82
    /**
83
     * Converts error codes to text.  Handles non-standard error codes OK.  
84
     * For debug/test purposes (and for formatting error messages).
85
     * @return the String representation of the specified Excel error code.
86
     */
87
    public static String getText(int errorCode) {
88
        if(HSSFErrorConstants.isValidCode(errorCode)) {
89
            return HSSFErrorConstants.getText(errorCode);
90
        }
91
        // It is desirable to make these (arbitrary) strings look clearly different from any other
92
        // value expression that might appear in a formula.  In addition these error strings should
93
        // look unlike the standard Excel errors.  Hence tilde ('~') was used.
94
        switch(errorCode) {
95
            case CIRCULAR_REF_ERROR_CODE: return "~CIRCULAR~REF~";
96
            case FUNCTION_NOT_IMPLEMENTED_CODE: return "~FUNCTION~NOT~IMPLEMENTED~";
97
        }
98
        return "~non~std~err(" + errorCode + ")~";
99
    }
107
100
108
    private int errorCode;
101
    private int _errorCode;
109
    /**
102
    /**
110
     * @param errorCode an 8-bit value
103
     * @param errorCode an 8-bit value
111
     */
104
     */
112
    private ErrorEval(int errorCode) {
105
    private ErrorEval(int errorCode) {
113
        this.errorCode = errorCode;
106
        _errorCode = errorCode;
114
    }
107
    }
115
108
116
    public int getErrorCode() {
109
    public int getErrorCode() {
117
        return errorCode;
110
        return _errorCode;
118
    }
111
    }
119
    public String toString() {
112
    public String toString() {
120
        StringBuffer sb = new StringBuffer(64);
113
        StringBuffer sb = new StringBuffer(64);
121
        sb.append(getClass().getName()).append(" [");
114
        sb.append(getClass().getName()).append(" [");
122
        sb.append(ErrorCode.getText(errorCode));
115
        sb.append(getText(_errorCode));
123
        sb.append("]");
116
        sb.append("]");
124
        return sb.toString();
117
        return sb.toString();
125
    }
118
    }
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/StringValueEval.java (-5 / +5 lines)
Lines 14-23 Link Here
14
* See the License for the specific language governing permissions and
14
* See the License for the specific language governing permissions and
15
* limitations under the License.
15
* limitations under the License.
16
*/
16
*/
17
/*
17
18
 * Created on May 8, 2005
19
 *
20
 */
21
package org.apache.poi.hssf.record.formula.eval;
18
package org.apache.poi.hssf.record.formula.eval;
22
19
23
/**
20
/**
Lines 26-30 Link Here
26
 */
23
 */
27
public interface StringValueEval extends ValueEval {
24
public interface StringValueEval extends ValueEval {
28
25
29
    public String getStringValue();
26
    /**
27
     * @return never <code>null</code>, possibly empty string.
28
     */
29
    String getStringValue();
30
}
30
}
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/eval/StringEval.java (-7 / +14 lines)
Lines 14-23 Link Here
14
* See the License for the specific language governing permissions and
14
* See the License for the specific language governing permissions and
15
* limitations under the License.
15
* limitations under the License.
16
*/
16
*/
17
/*
17
18
 * Created on May 8, 2005
19
 *
20
 */
21
package org.apache.poi.hssf.record.formula.eval;
18
package org.apache.poi.hssf.record.formula.eval;
22
19
23
import org.apache.poi.hssf.record.formula.Ptg;
20
import org.apache.poi.hssf.record.formula.Ptg;
Lines 27-47 Link Here
27
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
24
 * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
28
 *  
25
 *  
29
 */
26
 */
30
public class StringEval implements StringValueEval {
27
public final class StringEval implements StringValueEval {
31
28
32
    public static final StringEval EMPTY_INSTANCE = new StringEval("");
29
    public static final StringEval EMPTY_INSTANCE = new StringEval("");
33
    
30
    
34
    private String value;
31
    private final String value;
35
32
36
    public StringEval(Ptg ptg) {
33
    public StringEval(Ptg ptg) {
37
        this.value = ((StringPtg) ptg).getValue();
34
        this(((StringPtg) ptg).getValue());
38
    }
35
    }
39
36
40
    public StringEval(String value) {
37
    public StringEval(String value) {
38
        if(value == null) {
39
            throw new IllegalArgumentException("value must not be null");
40
        }
41
        this.value = value;
41
        this.value = value;
42
    }
42
    }
43
43
44
    public String getStringValue() {
44
    public String getStringValue() {
45
        return value;
45
        return value;
46
    }
46
    }
47
    public String toString() {
48
        StringBuffer sb = new StringBuffer(64);
49
        sb.append(getClass().getName()).append(" [");
50
        sb.append(value);
51
        sb.append("]");
52
        return sb.toString();
53
    }
47
}
54
}
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Lookup.java (-21 / +92 lines)
Lines 1-25 Link Here
1
/*
1
/* ====================================================================
2
* Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
* contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
4
* this work for additional information regarding copyright ownership.
4
   this work for additional information regarding copyright ownership.
5
* The ASF licenses this file to You under the Apache License, Version 2.0
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
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
7
   the License.  You may obtain a copy of the License at
8
*
8
9
*     http://www.apache.org/licenses/LICENSE-2.0
9
       http://www.apache.org/licenses/LICENSE-2.0
10
*
10
11
* Unless required by applicable law or agreed to in writing, software
11
   Unless required by applicable law or agreed to in writing, software
12
* distributed under the License is distributed on an "AS IS" BASIS,
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
* See the License for the specific language governing permissions and
14
   See the License for the specific language governing permissions and
15
* limitations under the License.
15
   limitations under the License.
16
*/
16
==================================================================== */
17
/*
17
18
 * Created on May 15, 2005
19
 *
20
 */
21
package org.apache.poi.hssf.record.formula.functions;
18
package org.apache.poi.hssf.record.formula.functions;
22
19
23
public class Lookup extends NotImplementedFunction {
20
import org.apache.poi.hssf.record.formula.eval.AreaEval;
21
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
22
import org.apache.poi.hssf.record.formula.eval.Eval;
23
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
24
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
25
import org.apache.poi.hssf.record.formula.eval.ValueEval;
26
import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
24
27
28
/**
29
 * Implementation of Excel function LOOKUP.<p/>
30
 * 
31
 * LOOKUP finds an index  row in a lookup table by the first column value and returns the value from another column.
32
 * 
33
 * <b>Syntax</b>:<br/>
34
 * <b>VLOOKUP</b>(<b>lookup_value</b>, <b>lookup_vector</b>, result_vector)<p/>
35
 * 
36
 * <b>lookup_value</b>  The value to be found in the lookup vector.<br/>
37
 * <b>lookup_vector</> An area reference for the lookup data. <br/>
38
 * <b>result_vector</b> Single row or single column area reference from which the result value is chosen.<br/>
39
 * 
40
 * @author Josh Micich
41
 */
42
public final class Lookup implements Function {
43
	private static final class SimpleValueVector implements ValueVector {
44
		private final ValueEval[] _values;
45
46
		public SimpleValueVector(ValueEval[] values) {
47
			_values = values;
48
		}
49
		public ValueEval getItem(int index) {
50
			return _values[index];
51
		}
52
		public int getSize() {
53
			return _values.length;
54
		}
55
	}
56
57
	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
58
		switch(args.length) {
59
			case 3:
60
				break;
61
			case 2:
62
				// complex rules to choose lookupVector and resultVector from the single area ref
63
				throw new RuntimeException("Two arg version of LOOKUP not supported yet");
64
			default:
65
				return ErrorEval.VALUE_INVALID;
66
		}
67
		
68
		
69
		try {
70
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
71
			AreaEval aeLookupVector = LookupUtils.resolveTableArrayArg(args[1]);
72
			AreaEval aeResultVector = LookupUtils.resolveTableArrayArg(args[2]);
73
			
74
			ValueVector lookupVector = createVector(aeLookupVector);
75
			ValueVector resultVector = createVector(aeResultVector);
76
			if(lookupVector.getSize() > resultVector.getSize()) {
77
				// Excel seems to handle this by accessing past the end of the result vector.
78
				throw new RuntimeException("Lookup vector and result vector of differing sizes not supported yet");
79
			}
80
			int index = LookupUtils.lookupIndexOfValue(lookupValue, lookupVector, true);
81
			
82
			return resultVector.getItem(index);
83
		} catch (EvaluationException e) {
84
			return e.getErrorEval();
85
		}
86
	}
87
88
	private static ValueVector createVector(AreaEval ae) {
89
		
90
		if(!ae.isRow() && !ae.isColumn()) {
91
			// extra complexity required to emulate the way LOOKUP can handles these abnormal cases.
92
			throw new RuntimeException("non-vector lookup or result areas not supported yet");
93
		}
94
		return new SimpleValueVector(ae.getValues());
95
	}
25
}
96
}
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Vlookup.java (-21 / +119 lines)
Lines 1-25 Link Here
1
/*
1
/* ====================================================================
2
* Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
* contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
4
* this work for additional information regarding copyright ownership.
4
   this work for additional information regarding copyright ownership.
5
* The ASF licenses this file to You under the Apache License, Version 2.0
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
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
7
   the License.  You may obtain a copy of the License at
8
*
8
9
*     http://www.apache.org/licenses/LICENSE-2.0
9
       http://www.apache.org/licenses/LICENSE-2.0
10
*
10
11
* Unless required by applicable law or agreed to in writing, software
11
   Unless required by applicable law or agreed to in writing, software
12
* distributed under the License is distributed on an "AS IS" BASIS,
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
* See the License for the specific language governing permissions and
14
   See the License for the specific language governing permissions and
15
* limitations under the License.
15
   limitations under the License.
16
*/
16
==================================================================== */
17
/*
17
18
 * Created on May 15, 2005
19
 *
20
 */
21
package org.apache.poi.hssf.record.formula.functions;
18
package org.apache.poi.hssf.record.formula.functions;
22
19
23
public class Vlookup extends NotImplementedFunction {
20
import org.apache.poi.hssf.record.formula.eval.AreaEval;
21
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
22
import org.apache.poi.hssf.record.formula.eval.Eval;
23
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
24
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
25
import org.apache.poi.hssf.record.formula.eval.ValueEval;
26
import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
27
/**
28
 * Implementation of the VLOOKUP() function.<p/>
29
 * 
30
 * VLOOKUP finds a row in a lookup table by the first column value and returns the value from another column.
31
 * 
32
 * <b>Syntax</b>:<br/>
33
 * <b>VLOOKUP</b>(<b>lookup_value</b>, <b>table_array</b>, <b>col_index_num</b>, range_lookup)<p/>
34
 * 
35
 * <b>lookup_value</b>  The value to be found in the first column of the table array.<br/>
36
 * <b>table_array</> An area reference for the lookup data. <br/>
37
 * <b>col_index_num</b> a 1 based index specifying which column value of the lookup data will be returned.<br/>
38
 * <b>range_lookup</b> If TRUE (default), VLOOKUP finds the largest value less than or equal to 
39
 * the lookup_value.  If FALSE, only exact matches will be considered<br/>   
40
 * 
41
 * @author Josh Micich
42
 */
43
public final class Vlookup implements Function {
44
	
45
	private static final class ColumnVector implements ValueVector {
24
46
47
		private final AreaEval _tableArray;
48
		private final int _size;
49
		private final int _columnAbsoluteIndex;
50
		private final int _firstRowAbsoluteIndex;
51
52
		public ColumnVector(AreaEval tableArray, int columnIndex) {
53
			_columnAbsoluteIndex = tableArray.getFirstColumn() + columnIndex;
54
			if(!tableArray.containsColumn((short)_columnAbsoluteIndex)) {
55
				int lastColIx =  tableArray.getLastColumn() -  tableArray.getFirstColumn();
56
				throw new IllegalArgumentException("Specified column index (" + columnIndex 
57
						+ ") is outside the allowed range (0.." + lastColIx + ")");
58
			}
59
			_tableArray = tableArray;
60
			_size = tableArray.getLastRow() - tableArray.getFirstRow() + 1;
61
			if(_size < 1) {
62
				throw new RuntimeException("bad table array size zero");
63
			}
64
			_firstRowAbsoluteIndex = tableArray.getFirstRow();
65
		}
66
67
		public ValueEval getItem(int index) {
68
			if(index>_size) {
69
				throw new ArrayIndexOutOfBoundsException("Specified index (" + index 
70
						+ ") is outside the allowed range (0.." + (_size-1) + ")");
71
			}
72
			return _tableArray.getValueAt(_firstRowAbsoluteIndex + index, (short)_columnAbsoluteIndex);
73
		}
74
		public int getSize() {
75
			return _size;
76
		}
77
	}
78
79
	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
80
		Eval arg3 = null;
81
		switch(args.length) {
82
			case 4:
83
				arg3 = args[3]; // important: assumed array element is never null
84
			case 3:
85
				break;
86
			default:
87
				// wrong number of arguments
88
				return ErrorEval.VALUE_INVALID;
89
		}
90
		try {
91
			// Evaluation order:
92
			// arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 col_index, fetch result
93
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
94
			AreaEval tableArray = LookupUtils.resolveTableArrayArg(args[1]);
95
			boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
96
			int rowIndex = LookupUtils.lookupIndexOfValue(lookupValue, new ColumnVector(tableArray, 0), isRangeLookup);
97
			ValueEval veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
98
			int colIndex = LookupUtils.resolveRowOrColIndexArg(veColIndex);
99
			ValueVector resultCol = createResultColumnVector(tableArray, colIndex);
100
			return resultCol.getItem(rowIndex);
101
		} catch (EvaluationException e) {
102
			return e.getErrorEval();
103
		}
104
	}
105
106
107
	/**
108
	 * Returns one column from an <tt>AreaEval</tt>
109
	 * 
110
	 * @throws EvaluationException (#VALUE!) if colIndex is negative, (#REF!) if colIndex is too high
111
	 */
112
	private ValueVector createResultColumnVector(AreaEval tableArray, int colIndex) throws EvaluationException {
113
		if(colIndex < 0) {
114
			throw EvaluationException.invalidValue();
115
		}
116
		int nCols = tableArray.getLastColumn() - tableArray.getFirstColumn() + 1;
117
		
118
		if(colIndex >= nCols) {
119
			throw EvaluationException.invalidRef();
120
		}
121
		return new ColumnVector(tableArray, colIndex);
122
	}
25
}
123
}
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Hlookup.java (-21 / +119 lines)
Lines 1-25 Link Here
1
/*
1
/* ====================================================================
2
* Licensed to the Apache Software Foundation (ASF) under one or more
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
* contributor license agreements.  See the NOTICE file distributed with
3
   contributor license agreements.  See the NOTICE file distributed with
4
* this work for additional information regarding copyright ownership.
4
   this work for additional information regarding copyright ownership.
5
* The ASF licenses this file to You under the Apache License, Version 2.0
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
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
7
   the License.  You may obtain a copy of the License at
8
*
8
9
*     http://www.apache.org/licenses/LICENSE-2.0
9
       http://www.apache.org/licenses/LICENSE-2.0
10
*
10
11
* Unless required by applicable law or agreed to in writing, software
11
   Unless required by applicable law or agreed to in writing, software
12
* distributed under the License is distributed on an "AS IS" BASIS,
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
* See the License for the specific language governing permissions and
14
   See the License for the specific language governing permissions and
15
* limitations under the License.
15
   limitations under the License.
16
*/
16
==================================================================== */
17
/*
17
18
 * Created on May 15, 2005
19
 *
20
 */
21
package org.apache.poi.hssf.record.formula.functions;
18
package org.apache.poi.hssf.record.formula.functions;
22
19
23
public class Hlookup extends NotImplementedFunction {
20
import org.apache.poi.hssf.record.formula.eval.AreaEval;
21
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
22
import org.apache.poi.hssf.record.formula.eval.Eval;
23
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
24
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
25
import org.apache.poi.hssf.record.formula.eval.ValueEval;
26
import org.apache.poi.hssf.record.formula.functions.LookupUtils.ValueVector;
27
/**
28
 * Implementation of the VLOOKUP() function.<p/>
29
 * 
30
 * HLOOKUP finds a column in a lookup table by the first row value and returns the value from another row.
31
 * 
32
 * <b>Syntax</b>:<br/>
33
 * <b>HLOOKUP</b>(<b>lookup_value</b>, <b>table_array</b>, <b>row_index_num</b>, range_lookup)<p/>
34
 * 
35
 * <b>lookup_value</b>  The value to be found in the first column of the table array.<br/>
36
 * <b>table_array</> An area reference for the lookup data. <br/>
37
 * <b>row_index_num</b> a 1 based index specifying which row value of the lookup data will be returned.<br/>
38
 * <b>range_lookup</b> If TRUE (default), HLOOKUP finds the largest value less than or equal to 
39
 * the lookup_value.  If FALSE, only exact matches will be considered<br/>   
40
 * 
41
 * @author Josh Micich
42
 */
43
public final class Hlookup implements Function {
44
	
45
	private static final class RowVector implements ValueVector {
24
46
47
		private final AreaEval _tableArray;
48
		private final int _size;
49
		private final int _rowAbsoluteIndex;
50
		private final int _firstColumnAbsoluteIndex;
51
52
		public RowVector(AreaEval tableArray, int rowIndex) {
53
			_rowAbsoluteIndex = tableArray.getFirstRow() + rowIndex;
54
			if(!tableArray.containsRow(_rowAbsoluteIndex)) {
55
				int lastRowIx =  tableArray.getLastRow() -  tableArray.getFirstRow();
56
				throw new IllegalArgumentException("Specified row index (" + rowIndex 
57
						+ ") is outside the allowed range (0.." + lastRowIx + ")");
58
			}
59
			_tableArray = tableArray;
60
			_size = tableArray.getLastColumn() - tableArray.getFirstColumn() + 1;
61
			if(_size < 1) {
62
				throw new RuntimeException("bad table array size zero");
63
			}
64
			_firstColumnAbsoluteIndex = tableArray.getFirstColumn();
65
		}
66
67
		public ValueEval getItem(int index) {
68
			if(index>_size) {
69
				throw new ArrayIndexOutOfBoundsException("Specified index (" + index 
70
						+ ") is outside the allowed range (0.." + (_size-1) + ")");
71
			}
72
			return _tableArray.getValueAt(_rowAbsoluteIndex, (short) (_firstColumnAbsoluteIndex + index));
73
		}
74
		public int getSize() {
75
			return _size;
76
		}
77
	}
78
79
	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
80
		Eval arg3 = null;
81
		switch(args.length) {
82
			case 4:
83
				arg3 = args[3]; // important: assumed array element is never null
84
			case 3:
85
				break;
86
			default:
87
				// wrong number of arguments
88
				return ErrorEval.VALUE_INVALID;
89
		}
90
		try {
91
			// Evaluation order:
92
			// arg0 lookup_value, arg1 table_array, arg3 range_lookup, find lookup value, arg2 row_index, fetch result
93
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
94
			AreaEval tableArray = LookupUtils.resolveTableArrayArg(args[1]);
95
			boolean isRangeLookup = LookupUtils.resolveRangeLookupArg(arg3, srcCellRow, srcCellCol);
96
			int colIndex = LookupUtils.lookupIndexOfValue(lookupValue, new RowVector(tableArray, 0), isRangeLookup);
97
			ValueEval veColIndex = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
98
			int rowIndex = LookupUtils.resolveRowOrColIndexArg(veColIndex);
99
			ValueVector resultCol = createResultColumnVector(tableArray, rowIndex);
100
			return resultCol.getItem(colIndex);
101
		} catch (EvaluationException e) {
102
			return e.getErrorEval();
103
		}
104
	}
105
106
107
	/**
108
	 * Returns one column from an <tt>AreaEval</tt>
109
	 * 
110
	 * @throws EvaluationException (#VALUE!) if colIndex is negative, (#REF!) if colIndex is too high
111
	 */
112
	private ValueVector createResultColumnVector(AreaEval tableArray, int colIndex) throws EvaluationException {
113
		if(colIndex < 0) {
114
			throw EvaluationException.invalidValue();
115
		}
116
		int nCols = tableArray.getLastColumn() - tableArray.getFirstRow() + 1;
117
		
118
		if(colIndex >= nCols) {
119
			throw EvaluationException.invalidRef();
120
		}
121
		return new RowVector(tableArray, colIndex);
122
	}
25
}
123
}
(-)src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Match.java (-153 / +67 lines)
Lines 18-31 Link Here
18
package org.apache.poi.hssf.record.formula.functions;
18
package org.apache.poi.hssf.record.formula.functions;
19
19
20
import org.apache.poi.hssf.record.formula.eval.AreaEval;
20
import org.apache.poi.hssf.record.formula.eval.AreaEval;
21
import org.apache.poi.hssf.record.formula.eval.BoolEval;
22
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
21
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
23
import org.apache.poi.hssf.record.formula.eval.Eval;
22
import org.apache.poi.hssf.record.formula.eval.Eval;
23
import org.apache.poi.hssf.record.formula.eval.EvaluationException;
24
import org.apache.poi.hssf.record.formula.eval.NumberEval;
24
import org.apache.poi.hssf.record.formula.eval.NumberEval;
25
import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
25
import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
26
import org.apache.poi.hssf.record.formula.eval.OperandResolver;
26
import org.apache.poi.hssf.record.formula.eval.RefEval;
27
import org.apache.poi.hssf.record.formula.eval.RefEval;
27
import org.apache.poi.hssf.record.formula.eval.StringEval;
28
import org.apache.poi.hssf.record.formula.eval.StringEval;
28
import org.apache.poi.hssf.record.formula.eval.ValueEval;
29
import org.apache.poi.hssf.record.formula.eval.ValueEval;
30
import org.apache.poi.hssf.record.formula.functions.LookupUtils.CompareResult;
31
import org.apache.poi.hssf.record.formula.functions.LookupUtils.LookupValueComparer;
29
32
30
/**
33
/**
31
 * Implementation for the MATCH() Excel function.<p/>
34
 * Implementation for the MATCH() Excel function.<p/>
Lines 62-79 Link Here
62
 */
65
 */
63
public final class Match implements Function {
66
public final class Match implements Function {
64
	
67
	
65
	private static final class EvalEx extends Exception {
66
		private final ErrorEval _error;
67
68
68
		public EvalEx(ErrorEval error) {
69
			_error = error;
70
		}
71
		public ErrorEval getError() {
72
			return _error;
73
		}
74
	}
75
	
76
77
	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
69
	public Eval evaluate(Eval[] args, int srcCellRow, short srcCellCol) {
78
		
70
		
79
		double match_type = 1; // default
71
		double match_type = 1; // default
Lines 82-88 Link Here
82
			case 3:
74
			case 3:
83
				try {
75
				try {
84
					match_type = evaluateMatchTypeArg(args[2], srcCellRow, srcCellCol);
76
					match_type = evaluateMatchTypeArg(args[2], srcCellRow, srcCellCol);
85
				} catch (EvalEx e) {
77
				} catch (EvaluationException e) {
86
					// Excel/MATCH() seems to have slightly abnormal handling of errors with
78
					// Excel/MATCH() seems to have slightly abnormal handling of errors with
87
					// the last parameter.  Errors do not propagate up.  Every error gets
79
					// the last parameter.  Errors do not propagate up.  Every error gets
88
					// translated into #REF!
80
					// translated into #REF!
Lines 100-208 Link Here
100
		
92
		
101
		
93
		
102
		try {
94
		try {
103
			ValueEval lookupValue = evaluateLookupValue(args[0], srcCellRow, srcCellCol);
95
			ValueEval lookupValue = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
104
			ValueEval[] lookupRange = evaluateLookupRange(args[1]);
96
			ValueEval[] lookupRange = evaluateLookupRange(args[1]);
105
			int index = findIndexOfValue(lookupValue, lookupRange, matchExact, findLargestLessThanOrEqual);
97
			int index = findIndexOfValue(lookupValue, lookupRange, matchExact, findLargestLessThanOrEqual);
106
			return new NumberEval(index + 1); // +1 to convert to 1-based
98
			return new NumberEval(index + 1); // +1 to convert to 1-based
107
		} catch (EvalEx e) {
99
		} catch (EvaluationException e) {
108
			return e.getError();
100
			return e.getErrorEval();
109
		}
101
		}
110
	}
102
	}
111
103
112
	private static ValueEval chooseSingleElementFromArea(AreaEval ae, 
104
	private static ValueEval[] evaluateLookupRange(Eval eval) throws EvaluationException {
113
			int srcCellRow, short srcCellCol) throws EvalEx {
114
		if (ae.isColumn()) {
115
			if(ae.isRow()) {
116
				return ae.getValues()[0];
117
			}
118
			if(!ae.containsRow(srcCellRow)) {
119
				throw new EvalEx(ErrorEval.VALUE_INVALID);
120
			}
121
			return ae.getValueAt(srcCellRow, ae.getFirstColumn());
122
		}
123
		if(!ae.isRow()) {
124
			throw new EvalEx(ErrorEval.VALUE_INVALID);
125
		}
126
		if(!ae.containsColumn(srcCellCol)) {
127
			throw new EvalEx(ErrorEval.VALUE_INVALID);
128
		}
129
		return ae.getValueAt(ae.getFirstRow(), srcCellCol);
130
		
131
	}
132
133
	private static ValueEval evaluateLookupValue(Eval eval, int srcCellRow, short srcCellCol)
134
			throws EvalEx {
135
		if (eval instanceof RefEval) {
105
		if (eval instanceof RefEval) {
136
			RefEval re = (RefEval) eval;
106
			RefEval re = (RefEval) eval;
137
			return re.getInnerValueEval();
138
		}
139
		if (eval instanceof AreaEval) {
140
			return chooseSingleElementFromArea((AreaEval) eval, srcCellRow, srcCellCol);
141
		}
142
		if (eval instanceof ValueEval) {
143
			return (ValueEval) eval;
144
		}
145
		throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
146
	}
147
148
149
	private static ValueEval[] evaluateLookupRange(Eval eval) throws EvalEx {
150
		if (eval instanceof RefEval) {
151
			RefEval re = (RefEval) eval;
152
			return new ValueEval[] { re.getInnerValueEval(), };
107
			return new ValueEval[] { re.getInnerValueEval(), };
153
		}
108
		}
154
		if (eval instanceof AreaEval) {
109
		if (eval instanceof AreaEval) {
155
			AreaEval ae = (AreaEval) eval;
110
			AreaEval ae = (AreaEval) eval;
156
			if(!ae.isColumn() && !ae.isRow()) {
111
			if(!ae.isColumn() && !ae.isRow()) {
157
				throw new EvalEx(ErrorEval.NA);
112
				throw new EvaluationException(ErrorEval.NA);
158
			}
113
			}
159
			return ae.getValues();
114
			return ae.getValues();
160
		}
115
		}
161
		
116
		
162
		// Error handling for lookup_range arg is also unusual
117
		// Error handling for lookup_range arg is also unusual
163
		if(eval instanceof NumericValueEval) {
118
		if(eval instanceof NumericValueEval) {
164
			throw new EvalEx(ErrorEval.NA);
119
			throw new EvaluationException(ErrorEval.NA);
165
		}
120
		}
166
		if (eval instanceof StringEval) {
121
		if (eval instanceof StringEval) {
167
			StringEval se = (StringEval) eval;
122
			StringEval se = (StringEval) eval;
168
			Double d = parseDouble(se.getStringValue());
123
			Double d = OperandResolver.parseDouble(se.getStringValue());
169
			if(d == null) {
124
			if(d == null) {
170
				// plain string
125
				// plain string
171
				throw new EvalEx(ErrorEval.VALUE_INVALID);
126
				throw new EvaluationException(ErrorEval.VALUE_INVALID);
172
			}
127
			}
173
			// else looks like a number
128
			// else looks like a number
174
			throw new EvalEx(ErrorEval.NA);
129
			throw new EvaluationException(ErrorEval.NA);
175
		}
130
		}
176
		throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
131
		throw new RuntimeException("Unexpected eval type (" + eval.getClass().getName() + ")");
177
	}
132
	}
178
133
179
134
180
	private static Double parseDouble(String stringValue) {
181
		// TODO find better home for parseDouble
182
		return Countif.parseDouble(stringValue);
183
	}
184
135
185
186
187
	private static double evaluateMatchTypeArg(Eval arg, int srcCellRow, short srcCellCol) 
136
	private static double evaluateMatchTypeArg(Eval arg, int srcCellRow, short srcCellCol) 
188
			throws EvalEx {
137
			throws EvaluationException {
189
		Eval match_type = arg;
138
		Eval match_type = OperandResolver.getSingleValue(arg, srcCellRow, srcCellCol);
190
		if(arg instanceof AreaEval) {
139
191
			AreaEval ae = (AreaEval) arg;
192
			// an area ref can work as a scalar value if it is 1x1
193
			if(ae.isColumn() &&  ae.isRow()) {
194
				match_type = ae.getValues()[0];
195
			} else {
196
				match_type = chooseSingleElementFromArea(ae, srcCellRow, srcCellCol);
197
			}
198
		}
199
		
200
		if(match_type instanceof RefEval) {
201
			RefEval re = (RefEval) match_type;
202
			match_type = re.getInnerValueEval();
203
		}
204
		if(match_type instanceof ErrorEval) {
140
		if(match_type instanceof ErrorEval) {
205
			throw new EvalEx((ErrorEval)match_type);
141
			throw new EvaluationException((ErrorEval)match_type);
206
		}
142
		}
207
		if(match_type instanceof NumericValueEval) {
143
		if(match_type instanceof NumericValueEval) {
208
			NumericValueEval ne = (NumericValueEval) match_type;
144
			NumericValueEval ne = (NumericValueEval) match_type;
Lines 210-221 Link Here
210
		}
146
		}
211
		if (match_type instanceof StringEval) {
147
		if (match_type instanceof StringEval) {
212
			StringEval se = (StringEval) match_type;
148
			StringEval se = (StringEval) match_type;
213
			Double d = parseDouble(se.getStringValue());
149
			Double d = OperandResolver.parseDouble(se.getStringValue());
214
			if(d == null) {
150
			if(d == null) {
215
				// plain string
151
				// plain string
216
				throw new EvalEx(ErrorEval.VALUE_INVALID);
152
				throw new EvaluationException(ErrorEval.VALUE_INVALID);
217
			}
153
			}
218
			// if the string parses as a number, it is ok
154
			// if the string parses as a number, it is OK
219
			return d.doubleValue();
155
			return d.doubleValue();
220
		}
156
		}
221
		throw new RuntimeException("Unexpected match_type type (" + match_type.getClass().getName() + ")");
157
		throw new RuntimeException("Unexpected match_type type (" + match_type.getClass().getName() + ")");
Lines 225-312 Link Here
225
	 * @return zero based index
161
	 * @return zero based index
226
	 */
162
	 */
227
	private static int findIndexOfValue(ValueEval lookupValue, ValueEval[] lookupRange,
163
	private static int findIndexOfValue(ValueEval lookupValue, ValueEval[] lookupRange,
228
			boolean matchExact, boolean findLargestLessThanOrEqual) throws EvalEx {
164
			boolean matchExact, boolean findLargestLessThanOrEqual) throws EvaluationException {
229
		// TODO - wildcard matching when matchExact and lookupValue is text containing * or ?
165
166
		LookupValueComparer lookupComparer = createLookupComparer(lookupValue, matchExact);
167
		
230
		if(matchExact) {
168
		if(matchExact) {
231
			for (int i = 0; i < lookupRange.length; i++) {
169
			for (int i = 0; i < lookupRange.length; i++) {
232
				ValueEval lri = lookupRange[i];
170
				if(lookupComparer.compareTo(lookupRange[i]).isEqual()) {
233
				if(lri.getClass() != lookupValue.getClass()) {
234
					continue;
235
				}
236
				if(compareValues(lookupValue, lri) == 0) {
237
					return i;
171
					return i;
238
				}
172
				}
239
			}
173
			}
240
		} else {
174
			throw new EvaluationException(ErrorEval.NA);
175
		}
176
		
177
		if(findLargestLessThanOrEqual) {
241
			// Note - backward iteration
178
			// Note - backward iteration
242
			if(findLargestLessThanOrEqual) {
179
			for (int i = lookupRange.length - 1; i>=0;  i--) {
243
				for (int i = lookupRange.length - 1; i>=0;  i--) {
180
				CompareResult cmp = lookupComparer.compareTo(lookupRange[i]);
244
					ValueEval lri = lookupRange[i];
181
				if(cmp.isTypeMismatch()) {
245
					if(lri.getClass() != lookupValue.getClass()) {
182
					continue;
246
						continue;
247
					}
248
					int cmp = compareValues(lookupValue, lri);
249
					if(cmp == 0) {
250
						return i;
251
					}
252
					if(cmp > 0) {
253
						return i;
254
					}
255
				}
183
				}
256
			} else {
184
				if(!cmp.isLessThan()) {
257
				// find smallest greater than or equal to
185
					return i;
258
				for (int i = 0; i<lookupRange.length; i++) {
259
					ValueEval lri = lookupRange[i];
260
					if(lri.getClass() != lookupValue.getClass()) {
261
						continue;
262
					}
263
					int cmp = compareValues(lookupValue, lri);
264
					if(cmp == 0) {
265
						return i;
266
					}
267
					if(cmp > 0) {
268
						if(i<1) {
269
							throw new EvalEx(ErrorEval.NA);
270
						}
271
						return i-1;
272
					}
273
				}
186
				}
274
				
275
			}
187
			}
188
			throw new EvaluationException(ErrorEval.NA);
276
		}
189
		}
190
		
191
		// else - find smallest greater than or equal to
192
		// TODO - is binary search used for (match_type==+1) ?
193
		for (int i = 0; i<lookupRange.length; i++) {
194
			CompareResult cmp = lookupComparer.compareTo(lookupRange[i]);
195
			if(cmp.isEqual()) {
196
				return i;
197
			}
198
			if(cmp.isGreaterThan()) {
199
				if(i<1) {
200
					throw new EvaluationException(ErrorEval.NA);
201
				}
202
				return i-1;
203
			}
204
		}
277
205
278
		throw new EvalEx(ErrorEval.NA);
206
		throw new EvaluationException(ErrorEval.NA);
279
	}
207
	}
280
208
281
209
	private static LookupValueComparer createLookupComparer(ValueEval lookupValue, boolean matchExact) throws EvaluationException {
282
	/**
210
		if (matchExact && lookupValue instanceof StringEval) {
283
	 * This method can only compare a pair of <tt>NumericValueEval</tt>s, <tt>StringEval</tt>s
211
			String stringValue = ((StringEval) lookupValue).getStringValue();
284
	 * or <tt>BoolEval</tt>s
212
			if(isLookupValueWild(stringValue)) {
285
	 * @return negative for a&lt;b, positive for a&gt;b and 0 for a = b
213
				throw new RuntimeException("Wildcard lookup values '" + stringValue + "' not supported yet");
286
	 */
287
	private static int compareValues(ValueEval a, ValueEval b) {
288
		if (a instanceof StringEval) {
289
			StringEval sa = (StringEval) a;
290
			StringEval sb = (StringEval) b;
291
			return sa.getStringValue().compareToIgnoreCase(sb.getStringValue());
292
		}
293
		if (a instanceof NumericValueEval) {
294
			NumericValueEval na = (NumericValueEval) a;
295
			NumericValueEval nb = (NumericValueEval) b;
296
			return Double.compare(na.getNumberValue(), nb.getNumberValue());
297
		}
298
		if (a instanceof BoolEval) {
299
			boolean ba = ((BoolEval) a).getBooleanValue();
300
			boolean bb = ((BoolEval) b).getBooleanValue();
301
			if(ba == bb) {
302
				return 0;
303
			}
214
			}
304
			// TRUE > FALSE
215
			
305
			if(ba) {
306
				return +1;
307
			}
308
			return -1;
309
		}
216
		}
310
		throw new RuntimeException("bad eval type (" + a.getClass().getName() + ")");
217
		return LookupUtils.createLookupComparer(lookupValue);
311
	}
218
	}
219
220
	private static boolean isLookupValueWild(String stringValue) {
221
		if(stringValue.indexOf('?') >=0 || stringValue.indexOf('*') >=0) {
222
			return true;
223
		}
224
		return false;
225
	}
312
}
226
}

Return to bug 44450