1 package org.simantics.spreadsheet.solver.formula;
3 import org.simantics.databoard.binding.mutable.Variant;
4 import org.simantics.spreadsheet.SpreadsheetMatrix;
5 import org.simantics.spreadsheet.Spreadsheets;
6 import org.simantics.spreadsheet.solver.formula.parser.ast.AstArgList;
8 public class VlookupFormulaFunction implements CellFormulaFunction<Object> {
11 public Object evaluate(CellValueVisitor visitor, AstArgList args) {
12 if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();
14 Object lookupValue = args.values.get(0).accept(visitor);
15 boolean lookupIsString = lookupValue instanceof String;
17 FormulaError2 error = FormulaError2.forObject(lookupValue);
18 if(error!=null) return error.getString();
20 if(lookupValue instanceof Variant){
21 Object varVal = ((Variant)lookupValue).getValue();
22 Double dVal = Spreadsheets.asDoubleWhereEmptyStringIsZero(varVal);
24 lookupValue = varVal.toString();
25 lookupIsString = true;
27 else lookupValue = dVal;
31 SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
33 boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
35 boolean valueExists = false;
36 if(array.values.length != 0){
37 for(int i = 0; i < array.getHeight(); i++){
39 Object obj = array.get(i,0);
40 if(obj instanceof Variant)
41 obj = ((Variant)obj).getValue();
42 if(obj!=null && Spreadsheets.asValidNumber(obj)==null){
44 String valStr = ((String)obj).toLowerCase();
45 if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){
51 else if(lookupValue instanceof Number){
52 Double d = ((Number)lookupValue).doubleValue();
53 Number d2 = Spreadsheets.asValidNumber(array.get(i,0));
54 if(d2!=null && d>=(d2.doubleValue())){
60 // if(lookupIsString){
61 // Object obj = array.values[0];
62 // if(obj instanceof Variant)
63 // obj = ((Variant)obj).getValue();
64 // if(Spreadsheets.asValidNumber(obj)!=null)
65 // return FormulaError2.NA.getString();
66 // obj = obj.toString();
67 // String valStr = ((String)obj).toLowerCase();
68 // if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
69 // return FormulaError2.NA.getString();
71 // else if(lookupValue instanceof Number){
72 // Double d = ((Number)lookupValue).doubleValue();
73 // Number d2 = Spreadsheets.asValidNumber(array.values[0]);
74 // if(d2==null || d<(d2.doubleValue()))
75 // return FormulaError2.NA.getString();
78 if(!valueExists) return FormulaError2.NA.getString();
80 Number colIndexN = Spreadsheets.asValidNumber(args.values.get(2).accept(visitor));
81 if(colIndexN==null) return FormulaError2.REF.getString();
82 int colIndexNum = colIndexN.intValue();
83 if(colIndexNum>array.getWidth()) return FormulaError2.REF.getString();
85 if (approximateMatch) {
86 Integer rowPosOfLargestValSmallerThanLookUp = null;
88 String ref = lookupValue.toString().toLowerCase();
89 String previousValue = null;
90 String largestValSmallerThanLookUp = null;
91 for(int i = 0; i < array.getHeight(); i++){
92 Object val = array.get(i, 0);
93 if(val instanceof Variant)
94 val = ((Variant)val).getValue();
95 String currValue = val.toString().toLowerCase();
96 if(currValue.compareTo(ref)>0){
98 return array.get(i, colIndexNum-1);
99 else if(previousValue.compareTo(ref) > currValue.compareTo(ref))
100 return array.get(i, colIndexNum-1);
102 return array.get(i - 1, colIndexNum - 1);
104 if(rowPosOfLargestValSmallerThanLookUp==null){
105 rowPosOfLargestValSmallerThanLookUp = i;
106 largestValSmallerThanLookUp = currValue;
107 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
108 rowPosOfLargestValSmallerThanLookUp = i;
109 largestValSmallerThanLookUp = currValue;
111 previousValue = currValue;
115 double ref = Spreadsheets.asNumber(lookupValue);
116 double lastDiff = Double.MIN_VALUE;
117 Double largestValSmallerThanLookUp = null;
118 for (int i = 0; i < array.getHeight(); i++) {
119 Object value = array.get(i, 0);
120 double n = Spreadsheets.asNumber(value);
121 double diff = n - ref;
124 return array.get(i, colIndexNum - 1);
125 else if (diff < -lastDiff)
126 return array.get(i, colIndexNum - 1);
128 return array.get(i - 1, colIndexNum - 1);
130 if(rowPosOfLargestValSmallerThanLookUp==null){
131 rowPosOfLargestValSmallerThanLookUp = i;
132 largestValSmallerThanLookUp = n;
133 } else if(largestValSmallerThanLookUp < n){
134 rowPosOfLargestValSmallerThanLookUp = i;
135 largestValSmallerThanLookUp = n;
141 if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);
144 for (int i = 0; i < array.getHeight(); i++) {
145 Object value = array.get(i, 0);
146 if(value instanceof Variant){
148 value = ((Variant)value).getValue().toString();
150 value = Spreadsheets.asNumber(value);
152 if (Spreadsheets.excelEquals(lookupValue, value)) {
153 return array.get(i, colIndexNum - 1);
158 return FormulaError2.NA.getString();