]> gerrit.simantics Code Review - simantics/platform.git/blob - bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/VlookupFormulaFunction.java
Improved exception message
[simantics/platform.git] / bundles / org.simantics.spreadsheet.graph / src / org / simantics / spreadsheet / graph / formula / VlookupFormulaFunction.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 VlookupFormulaFunction 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         
33         SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
34
35         boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
36
37         boolean valueExists = false;
38                 if(array.values.length != 0){
39                         for(int i = 0; i < array.getHeight(); i++){
40                                 if(lookupIsString){
41                                         Object obj = array.get(i,0);
42                                         if(obj instanceof Variant)
43                                                 obj = ((Variant)obj).getValue();
44                                         if(obj!=null && SpreadsheetGraphUtils.asValidNumber(obj)==null){
45                                                 obj = obj.toString();
46                                                 String valStr = ((String)obj).toLowerCase();
47                                                 if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){
48                                                         valueExists = true;
49                                                         break;
50                                                 }
51                                         }
52                                 }
53                                 else if(lookupValue instanceof Number){
54                                         Double d = ((Number)lookupValue).doubleValue();
55                                         Number d2 = SpreadsheetGraphUtils.asValidNumber(array.get(i,0));
56                                         if(d2!=null && d>=(d2.doubleValue())){
57                                                 valueExists = true;
58                                                 break;
59                                         }
60                                 }
61                         }
62 //                      if(lookupIsString){
63 //                              Object obj = array.values[0];
64 //                              if(obj instanceof Variant)
65 //                                      obj = ((Variant)obj).getValue();
66 //                              if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)
67 //                                      return FormulaError2.NA.getString();
68 //                              obj = obj.toString();
69 //                              String valStr = ((String)obj).toLowerCase();
70 //                              if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
71 //                                      return FormulaError2.NA.getString();
72 //                      }
73 //                      else if(lookupValue instanceof Number){
74 //                              Double d = ((Number)lookupValue).doubleValue();
75 //                              Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);
76 //                              if(d2==null || d<(d2.doubleValue()))
77 //                                      return FormulaError2.NA.getString();
78 //                      }
79                 }
80                 if(!valueExists) return FormulaError2.NA.getString();
81                 
82                 Number colIndexN = SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));
83                 if(colIndexN==null) return FormulaError2.REF.getString();
84                 int colIndexNum = colIndexN.intValue();
85         if(colIndexNum>array.getWidth()) return FormulaError2.REF.getString();
86         
87         if (approximateMatch) {
88                 Integer rowPosOfLargestValSmallerThanLookUp = null;
89                 if(lookupIsString){
90                         String ref = lookupValue.toString().toLowerCase();
91                         String previousValue = null;
92                         String largestValSmallerThanLookUp = null;
93                         for(int i = 0; i < array.getHeight(); i++){
94                                 Object val = array.get(i, 0);
95                                 if(val instanceof Variant)
96                                         val = ((Variant)val).getValue();
97                                 String currValue = val.toString().toLowerCase();
98                                 if(currValue.compareTo(ref)>0){
99                                         if(i==0)
100                                                 return array.get(i, colIndexNum-1);
101                                         else if(previousValue.compareTo(ref) > currValue.compareTo(ref))
102                                                 return array.get(i, colIndexNum-1);
103                                         else 
104                                                 return array.get(i - 1, colIndexNum - 1);
105                                 }
106                                 if(rowPosOfLargestValSmallerThanLookUp==null){
107                                         rowPosOfLargestValSmallerThanLookUp = i;
108                                         largestValSmallerThanLookUp = currValue;
109                                 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
110                                         rowPosOfLargestValSmallerThanLookUp = i;
111                                         largestValSmallerThanLookUp = currValue;
112                                 }
113                                 previousValue = currValue;
114                         }
115                         
116                 } else {
117                         double ref = SpreadsheetGraphUtils.asNumber(lookupValue);
118                 double lastDiff = Double.MIN_VALUE;
119                 Double largestValSmallerThanLookUp = null;
120                 for (int i = 0; i < array.getHeight(); i++) {
121                         Object value = array.get(i, 0);
122                         double n = SpreadsheetGraphUtils.asNumber(value);
123                         double diff = n - ref;
124                         if (diff > 0) {
125                         if (i == 0)
126                                 return array.get(i, colIndexNum - 1);
127                         else if (diff < -lastDiff)
128                                 return array.get(i, colIndexNum - 1);
129                         else
130                                 return array.get(i - 1, colIndexNum - 1);
131                         }
132                                 if(rowPosOfLargestValSmallerThanLookUp==null){
133                                         rowPosOfLargestValSmallerThanLookUp = i;
134                                         largestValSmallerThanLookUp = n;
135                                 } else if(largestValSmallerThanLookUp < n){
136                                         rowPosOfLargestValSmallerThanLookUp = i;
137                                         largestValSmallerThanLookUp = n;
138                                 }
139                         lastDiff = diff;
140                 }
141                 }
142                 
143                 if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);
144         
145         } else {
146             for (int i = 0; i < array.getHeight(); i++) {
147                 Object value = array.get(i, 0);
148                 if(value instanceof Variant){
149                         if(lookupIsString)
150                                 value = ((Variant)value).getValue().toString();
151                         else 
152                                 value = SpreadsheetGraphUtils.asNumber(value);
153                 }
154                 if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {
155                     return array.get(i, colIndexNum - 1);
156                 }
157             }
158         }
159
160         return FormulaError2.NA.getString();
161     }
162
163 }