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 VlookupFormulaFunction 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
33 SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
\r
35 boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
\r
37 boolean valueExists = false;
\r
38 if(array.values.length != 0){
\r
39 for(int i = 0; i < array.getHeight(); i++){
\r
41 Object obj = array.get(i,0);
\r
42 if(obj instanceof Variant)
\r
43 obj = ((Variant)obj).getValue();
\r
44 if(obj!=null && SpreadsheetGraphUtils.asValidNumber(obj)==null){
\r
45 obj = obj.toString();
\r
46 String valStr = ((String)obj).toLowerCase();
\r
47 if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){
\r
53 else if(lookupValue instanceof Number){
\r
54 Double d = ((Number)lookupValue).doubleValue();
\r
55 Number d2 = SpreadsheetGraphUtils.asValidNumber(array.get(i,0));
\r
56 if(d2!=null && d>=(d2.doubleValue())){
\r
62 // if(lookupIsString){
\r
63 // Object obj = array.values[0];
\r
64 // if(obj instanceof Variant)
\r
65 // obj = ((Variant)obj).getValue();
\r
66 // if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)
\r
67 // return FormulaError2.NA.getString();
\r
68 // obj = obj.toString();
\r
69 // String valStr = ((String)obj).toLowerCase();
\r
70 // if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
\r
71 // return FormulaError2.NA.getString();
\r
73 // else if(lookupValue instanceof Number){
\r
74 // Double d = ((Number)lookupValue).doubleValue();
\r
75 // Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);
\r
76 // if(d2==null || d<(d2.doubleValue()))
\r
77 // return FormulaError2.NA.getString();
\r
80 if(!valueExists) return FormulaError2.NA.getString();
\r
82 Number colIndexN = SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));
\r
83 if(colIndexN==null) return FormulaError2.REF.getString();
\r
84 int colIndexNum = colIndexN.intValue();
\r
85 if(colIndexNum>array.getWidth()) return FormulaError2.REF.getString();
\r
87 if (approximateMatch) {
\r
88 Integer rowPosOfLargestValSmallerThanLookUp = null;
\r
90 String ref = lookupValue.toString().toLowerCase();
\r
91 String previousValue = null;
\r
92 String largestValSmallerThanLookUp = null;
\r
93 for(int i = 0; i < array.getHeight(); i++){
\r
94 Object val = array.get(i, 0);
\r
95 if(val instanceof Variant)
\r
96 val = ((Variant)val).getValue();
\r
97 String currValue = val.toString().toLowerCase();
\r
98 if(currValue.compareTo(ref)>0){
\r
100 return array.get(i, colIndexNum-1);
\r
101 else if(previousValue.compareTo(ref) > currValue.compareTo(ref))
\r
102 return array.get(i, colIndexNum-1);
\r
104 return array.get(i - 1, colIndexNum - 1);
\r
106 if(rowPosOfLargestValSmallerThanLookUp==null){
\r
107 rowPosOfLargestValSmallerThanLookUp = i;
\r
108 largestValSmallerThanLookUp = currValue;
\r
109 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
\r
110 rowPosOfLargestValSmallerThanLookUp = i;
\r
111 largestValSmallerThanLookUp = currValue;
\r
113 previousValue = currValue;
\r
117 double ref = SpreadsheetGraphUtils.asNumber(lookupValue);
\r
118 double lastDiff = Double.MIN_VALUE;
\r
119 Double largestValSmallerThanLookUp = null;
\r
120 for (int i = 0; i < array.getHeight(); i++) {
\r
121 Object value = array.get(i, 0);
\r
122 double n = SpreadsheetGraphUtils.asNumber(value);
\r
123 double diff = n - ref;
\r
126 return array.get(i, colIndexNum - 1);
\r
127 else if (diff < -lastDiff)
\r
128 return array.get(i, colIndexNum - 1);
\r
130 return array.get(i - 1, colIndexNum - 1);
\r
132 if(rowPosOfLargestValSmallerThanLookUp==null){
\r
133 rowPosOfLargestValSmallerThanLookUp = i;
\r
134 largestValSmallerThanLookUp = n;
\r
135 } else if(largestValSmallerThanLookUp < n){
\r
136 rowPosOfLargestValSmallerThanLookUp = i;
\r
137 largestValSmallerThanLookUp = n;
\r
143 if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);
\r
146 for (int i = 0; i < array.getHeight(); i++) {
\r
147 Object value = array.get(i, 0);
\r
148 if(value instanceof Variant){
\r
150 value = ((Variant)value).getValue().toString();
\r
152 value = SpreadsheetGraphUtils.asNumber(value);
\r
154 if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {
\r
155 return array.get(i, colIndexNum - 1);
\r
160 return FormulaError2.NA.getString();
\r