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 }