bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/VlookupFormulaFunction.java

   1 package org.simantics.spreadsheet.solver.formula;
   2
   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;
   7
   8 public class VlookupFormulaFunction implements CellFormulaFunction<Object> {
   9
  10     @Override
  11     public Object evaluate(CellValueVisitor visitor, AstArgList args) {
  12         if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();
  13
  14         Object lookupValue = args.values.get(0).accept(visitor);
  15         boolean lookupIsString = lookupValue instanceof String;
  16
  17         FormulaError2 error = FormulaError2.forObject(lookupValue);
  18         if(error!=null) return error.getString();
  19
  20         if(lookupValue instanceof Variant){
  21             Object varVal = ((Variant)lookupValue).getValue();
  22             Double dVal = Spreadsheets.asDoubleWhereEmptyStringIsZero(varVal);
  23             if(dVal==null) {
  24                 lookupValue = varVal.toString();
  25                 lookupIsString = true;
  26             }
  27             else lookupValue = dVal;
  28         }
  29
  30
  31         SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
  32
  33         boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
  34
  35         boolean valueExists = false;
  36         if(array.values.length != 0){
  37             for(int i = 0; i < array.getHeight(); i++){
  38                 if(lookupIsString){
  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){
  43                         obj = obj.toString();
  44                         String valStr = ((String)obj).toLowerCase();
  45                         if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){
  46                             valueExists = true;
  47                             break;
  48                         }
  49                     }
  50                 }
  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())){
  55                         valueExists = true;
  56                         break;
  57                     }
  58                 }
  59             }
  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();
  70 //                      }
  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();
  76 //                      }
  77         }
  78         if(!valueExists) return FormulaError2.NA.getString();
  79
  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();
  84
  85         if (approximateMatch) {
  86             Integer rowPosOfLargestValSmallerThanLookUp = null;
  87             if(lookupIsString){
  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){
  97                         if(i==0)
  98                             return array.get(i, colIndexNum-1);
  99                         else if(previousValue.compareTo(ref) > currValue.compareTo(ref))
 100                             return array.get(i, colIndexNum-1);
 101                         else
 102                             return array.get(i - 1, colIndexNum - 1);
 103                     }
 104                     if(rowPosOfLargestValSmallerThanLookUp==null){
 105                         rowPosOfLargestValSmallerThanLookUp = i;
 106                         largestValSmallerThanLookUp = currValue;
 107                     } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
 108                         rowPosOfLargestValSmallerThanLookUp = i;
 109                         largestValSmallerThanLookUp = currValue;
 110                     }
 111                     previousValue = currValue;
 112                 }
 113
 114             } else {
 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;
 122                     if (diff > 0) {
 123                         if (i == 0)
 124                             return array.get(i, colIndexNum - 1);
 125                         else if (diff < -lastDiff)
 126                             return array.get(i, colIndexNum - 1);
 127                         else
 128                             return array.get(i - 1, colIndexNum - 1);
 129                     }
 130                     if(rowPosOfLargestValSmallerThanLookUp==null){
 131                         rowPosOfLargestValSmallerThanLookUp = i;
 132                         largestValSmallerThanLookUp = n;
 133                     } else if(largestValSmallerThanLookUp < n){
 134                         rowPosOfLargestValSmallerThanLookUp = i;
 135                         largestValSmallerThanLookUp = n;
 136                     }
 137                     lastDiff = diff;
 138                 }
 139             }
 140
 141             if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);
 142
 143         } else {
 144             for (int i = 0; i < array.getHeight(); i++) {
 145                 Object value = array.get(i, 0);
 146                 if(value instanceof Variant){
 147                     if(lookupIsString)
 148                         value = ((Variant)value).getValue().toString();
 149                     else
 150                         value = Spreadsheets.asNumber(value);
 151                 }
 152                 if (Spreadsheets.excelEquals(lookupValue, value)) {
 153                     return array.get(i, colIndexNum - 1);
 154                 }
 155             }
 156         }
 157
 158         return FormulaError2.NA.getString();
 159     }
 160
 161 }