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 HlookupFormulaFunction 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;
30 SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
32 boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
34 if(array.values.length != 0){
36 Object obj = array.values[0];
37 if(obj instanceof Variant)
38 obj = ((Variant)obj).getValue();
39 if(Spreadsheets.asValidNumber(obj)!=null)
40 return FormulaError2.NA.getString();
42 String valStr = ((String)obj).toLowerCase();
43 if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
44 return FormulaError2.NA.getString();
46 else if(lookupValue instanceof Number){
47 Double d = ((Number)lookupValue).doubleValue();
48 Number d2 = Spreadsheets.asValidNumber(array.values[0]);
49 if(d2==null || d<(d2.doubleValue()))
50 return FormulaError2.NA.getString();
54 Number rowIndexN = (Number) Spreadsheets.asValidNumber(args.values.get(2).accept(visitor));
55 if(rowIndexN==null) return FormulaError2.REF.getString();
56 int rowIndexNum = rowIndexN.intValue();
57 if(rowIndexNum>array.getHeight()) return FormulaError2.REF.getString();
59 if (approximateMatch) {
60 Integer colPosOfLargestValSmallerThanLookUp = null;
62 String largestValSmallerThanLookUp = null;
63 String ref = lookupValue.toString();
64 String previousValue = null;
65 for(int i = 0; i < array.getWidth(); i++){
66 Object val = array.get(0, i);
67 if(val instanceof Variant)
68 val = ((Variant)val).getValue();
69 String currValue = val.toString();
70 int currCompedToRef = currValue.compareTo(ref);
71 if(currCompedToRef>=0){
73 return array.get(rowIndexNum-1, i);
74 else if(previousValue.compareTo(ref) > currCompedToRef )
75 return array.get(rowIndexNum-1, i);
77 return array.get(rowIndexNum - 1, i - 1);
79 if(colPosOfLargestValSmallerThanLookUp==null){
80 colPosOfLargestValSmallerThanLookUp = i;
81 largestValSmallerThanLookUp = currValue;
82 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
83 colPosOfLargestValSmallerThanLookUp = i;
84 largestValSmallerThanLookUp = currValue;
86 previousValue = currValue;
90 double ref = Spreadsheets.asNumber(lookupValue);
91 double lastDiff = Double.MIN_VALUE;
92 Double largestValSmallerThanLookUp = null;
93 for (int i = 0; i < array.getWidth(); i++) {
94 Object value = array.get(0, i);
95 double n = Spreadsheets.asNumber(value);
96 double diff = n - ref;
99 return array.get(rowIndexNum - 1, i);
100 else if (diff < -lastDiff)
101 return array.get(rowIndexNum - 1, i);
103 return array.get(rowIndexNum - 1, i - 1);
105 if(colPosOfLargestValSmallerThanLookUp==null){
106 colPosOfLargestValSmallerThanLookUp = i;
107 largestValSmallerThanLookUp = n;
108 } else if(largestValSmallerThanLookUp < n){
109 colPosOfLargestValSmallerThanLookUp = i;
110 largestValSmallerThanLookUp = n;
115 if(colPosOfLargestValSmallerThanLookUp!=null) return array.get(rowIndexNum - 1, colPosOfLargestValSmallerThanLookUp);
119 for (int i = 0; i < array.getWidth(); i++) {
120 Object value = array.get(0, i);
121 if(value instanceof Variant){
123 value = ((Variant)value).getValue().toString();
125 value = Spreadsheets.asNumber(value);
127 if (Spreadsheets.excelEquals(lookupValue, value)) {
128 return array.get(rowIndexNum - 1, i);
134 return FormulaError2.NA.getString();