]> gerrit.simantics Code Review - simantics/platform.git/blob - bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/HlookupFormulaFunction.java
Improved exception message
[simantics/platform.git] / bundles / org.simantics.spreadsheet.graph / src / org / simantics / spreadsheet / graph / formula / HlookupFormulaFunction.java
1 package org.simantics.spreadsheet.graph.formula;
2
3 import org.simantics.databoard.binding.mutable.Variant;
4 import org.simantics.spreadsheet.graph.CellFormulaFunction;
5 import org.simantics.spreadsheet.graph.CellValueVisitor;
6 import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;
7 import org.simantics.spreadsheet.graph.SpreadsheetMatrix;
8 import org.simantics.spreadsheet.graph.parser.ast.AstArgList;
9
10 public class HlookupFormulaFunction implements CellFormulaFunction<Object> {
11
12     @Override
13     public Object evaluate(CellValueVisitor visitor, AstArgList args) {
14         if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();
15
16         Object lookupValue = args.values.get(0).accept(visitor);
17         boolean lookupIsString = lookupValue instanceof String;
18
19         FormulaError2 error = FormulaError2.forObject(lookupValue);
20         if(error!=null) return error.getString();
21         
22         if(lookupValue instanceof Variant){
23                 Object varVal = ((Variant)lookupValue).getValue();
24                 Double dVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(varVal);
25                 if(dVal==null) {
26                         lookupValue = varVal.toString();
27                         lookupIsString = true;
28                 }
29                 else lookupValue = dVal;
30         }
31         
32         SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
33         
34         boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
35
36                 if(array.values.length != 0){
37                         if(lookupIsString){
38                                 Object obj = array.values[0];
39                                 if(obj instanceof Variant)
40                                         obj = ((Variant)obj).getValue();
41                                 if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)
42                                         return FormulaError2.NA.getString();
43                                 obj = obj.toString();
44                                 String valStr = ((String)obj).toLowerCase();
45                                 if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
46                                         return FormulaError2.NA.getString();
47                         }
48                         else if(lookupValue instanceof Number){
49                                 Double d = ((Number)lookupValue).doubleValue();
50                                 Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);
51                                 if(d2==null || d<(d2.doubleValue()))
52                                         return FormulaError2.NA.getString();
53                         }
54                 }
55
56         Number rowIndexN = (Number) SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));
57         if(rowIndexN==null) return FormulaError2.REF.getString();
58         int rowIndexNum = rowIndexN.intValue();
59                 if(rowIndexNum>array.getHeight()) return FormulaError2.REF.getString();
60                 
61         if (approximateMatch) {
62                 Integer colPosOfLargestValSmallerThanLookUp = null;
63                 if(lookupIsString){
64                         String largestValSmallerThanLookUp = null;
65                         String ref = lookupValue.toString();
66                         String previousValue = null;
67                         for(int i = 0; i < array.getWidth(); i++){
68                                 Object val = array.get(0, i);
69                                 if(val instanceof Variant)
70                                         val = ((Variant)val).getValue();
71                                 String currValue = val.toString();
72                                 int currCompedToRef = currValue.compareTo(ref);
73                                 if(currCompedToRef>=0){
74                                         if(i==0)
75                                                 return array.get(rowIndexNum-1, i);
76                                         else if(previousValue.compareTo(ref) > currCompedToRef )
77                                                 return array.get(rowIndexNum-1, i);
78                                         else 
79                                                 return array.get(rowIndexNum - 1, i - 1);
80                                 }
81                                 if(colPosOfLargestValSmallerThanLookUp==null){
82                                         colPosOfLargestValSmallerThanLookUp = i;
83                                         largestValSmallerThanLookUp = currValue;
84                                 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
85                                         colPosOfLargestValSmallerThanLookUp = i;
86                                         largestValSmallerThanLookUp = currValue;
87                                 }
88                                 previousValue = currValue;
89                         }
90                         
91                 } else {
92                         double ref = SpreadsheetGraphUtils.asNumber(lookupValue);
93                         double lastDiff = Double.MIN_VALUE;
94                         Double largestValSmallerThanLookUp = null;
95                 for (int i = 0; i < array.getWidth(); i++) {
96                         Object value = array.get(0, i);
97                         double n = SpreadsheetGraphUtils.asNumber(value);
98                         double diff = n - ref;
99                         if (diff >= 0) {
100                         if (i == 0)
101                                 return array.get(rowIndexNum - 1, i);
102                         else if (diff < -lastDiff)
103                                 return array.get(rowIndexNum - 1, i);
104                         else
105                                 return array.get(rowIndexNum - 1, i - 1);
106                         }
107                                 if(colPosOfLargestValSmallerThanLookUp==null){
108                                         colPosOfLargestValSmallerThanLookUp = i;
109                                         largestValSmallerThanLookUp = n;
110                                 } else if(largestValSmallerThanLookUp < n){
111                                         colPosOfLargestValSmallerThanLookUp = i;
112                                         largestValSmallerThanLookUp = n;
113                                 }
114                         lastDiff = diff;
115                 }
116                 }
117                 if(colPosOfLargestValSmallerThanLookUp!=null) return array.get(rowIndexNum - 1, colPosOfLargestValSmallerThanLookUp);
118                 
119         } else {
120
121             for (int i = 0; i < array.getWidth(); i++) {
122                 Object value = array.get(0, i);
123                 if(value instanceof Variant){
124                         if(lookupIsString)
125                                 value = ((Variant)value).getValue().toString();
126                         else 
127                                 value = SpreadsheetGraphUtils.asNumber(value);
128                 }
129                 if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {
130                     return array.get(rowIndexNum - 1, i);
131                 }
132             }
133
134         }
135
136         return FormulaError2.NA.getString();
137     }
138
139 }