1 package org.simantics.spreadsheet.graph.formula;
\r
3 import org.simantics.databoard.binding.mutable.Variant;
\r
4 import org.simantics.spreadsheet.graph.CellFormulaFunction;
\r
5 import org.simantics.spreadsheet.graph.CellValueVisitor;
\r
6 import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;
\r
7 import org.simantics.spreadsheet.graph.SpreadsheetMatrix;
\r
8 import org.simantics.spreadsheet.graph.parser.ast.AstArgList;
\r
10 public class HlookupFormulaFunction implements CellFormulaFunction<Object> {
\r
13 public Object evaluate(CellValueVisitor visitor, AstArgList args) {
\r
14 if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();
\r
16 Object lookupValue = args.values.get(0).accept(visitor);
\r
17 boolean lookupIsString = lookupValue instanceof String;
\r
19 FormulaError2 error = FormulaError2.forObject(lookupValue);
\r
20 if(error!=null) return error.getString();
\r
22 if(lookupValue instanceof Variant){
\r
23 Object varVal = ((Variant)lookupValue).getValue();
\r
24 Double dVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(varVal);
\r
26 lookupValue = varVal.toString();
\r
27 lookupIsString = true;
\r
29 else lookupValue = dVal;
\r
32 SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
\r
34 boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
\r
36 if(array.values.length != 0){
\r
38 Object obj = array.values[0];
\r
39 if(obj instanceof Variant)
\r
40 obj = ((Variant)obj).getValue();
\r
41 if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)
\r
42 return FormulaError2.NA.getString();
\r
43 obj = obj.toString();
\r
44 String valStr = ((String)obj).toLowerCase();
\r
45 if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
\r
46 return FormulaError2.NA.getString();
\r
48 else if(lookupValue instanceof Number){
\r
49 Double d = ((Number)lookupValue).doubleValue();
\r
50 Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);
\r
51 if(d2==null || d<(d2.doubleValue()))
\r
52 return FormulaError2.NA.getString();
\r
56 Number rowIndexN = (Number) SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));
\r
57 if(rowIndexN==null) return FormulaError2.REF.getString();
\r
58 int rowIndexNum = rowIndexN.intValue();
\r
59 if(rowIndexNum>array.getHeight()) return FormulaError2.REF.getString();
\r
61 if (approximateMatch) {
\r
62 Integer colPosOfLargestValSmallerThanLookUp = null;
\r
64 String largestValSmallerThanLookUp = null;
\r
65 String ref = lookupValue.toString();
\r
66 String previousValue = null;
\r
67 for(int i = 0; i < array.getWidth(); i++){
\r
68 Object val = array.get(0, i);
\r
69 if(val instanceof Variant)
\r
70 val = ((Variant)val).getValue();
\r
71 String currValue = val.toString();
\r
72 int currCompedToRef = currValue.compareTo(ref);
\r
73 if(currCompedToRef>=0){
\r
75 return array.get(rowIndexNum-1, i);
\r
76 else if(previousValue.compareTo(ref) > currCompedToRef )
\r
77 return array.get(rowIndexNum-1, i);
\r
79 return array.get(rowIndexNum - 1, i - 1);
\r
81 if(colPosOfLargestValSmallerThanLookUp==null){
\r
82 colPosOfLargestValSmallerThanLookUp = i;
\r
83 largestValSmallerThanLookUp = currValue;
\r
84 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
\r
85 colPosOfLargestValSmallerThanLookUp = i;
\r
86 largestValSmallerThanLookUp = currValue;
\r
88 previousValue = currValue;
\r
92 double ref = SpreadsheetGraphUtils.asNumber(lookupValue);
\r
93 double lastDiff = Double.MIN_VALUE;
\r
94 Double largestValSmallerThanLookUp = null;
\r
95 for (int i = 0; i < array.getWidth(); i++) {
\r
96 Object value = array.get(0, i);
\r
97 double n = SpreadsheetGraphUtils.asNumber(value);
\r
98 double diff = n - ref;
\r
101 return array.get(rowIndexNum - 1, i);
\r
102 else if (diff < -lastDiff)
\r
103 return array.get(rowIndexNum - 1, i);
\r
105 return array.get(rowIndexNum - 1, i - 1);
\r
107 if(colPosOfLargestValSmallerThanLookUp==null){
\r
108 colPosOfLargestValSmallerThanLookUp = i;
\r
109 largestValSmallerThanLookUp = n;
\r
110 } else if(largestValSmallerThanLookUp < n){
\r
111 colPosOfLargestValSmallerThanLookUp = i;
\r
112 largestValSmallerThanLookUp = n;
\r
117 if(colPosOfLargestValSmallerThanLookUp!=null) return array.get(rowIndexNum - 1, colPosOfLargestValSmallerThanLookUp);
\r
121 for (int i = 0; i < array.getWidth(); i++) {
\r
122 Object value = array.get(0, i);
\r
123 if(value instanceof Variant){
\r
125 value = ((Variant)value).getValue().toString();
\r
127 value = SpreadsheetGraphUtils.asNumber(value);
\r
129 if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {
\r
130 return array.get(rowIndexNum - 1, i);
\r
136 return FormulaError2.NA.getString();
\r