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 }