bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/HlookupFormulaFunction.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 HlookupFormulaFunction 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         SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
  31
  32         boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
  33
  34         if(array.values.length != 0){
  35             if(lookupIsString){
  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();
  41                 obj = obj.toString();
  42                 String valStr = ((String)obj).toLowerCase();
  43                 if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
  44                     return FormulaError2.NA.getString();
  45             }
  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();
  51             }
  52         }
  53
  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();
  58
  59         if (approximateMatch) {
  60             Integer colPosOfLargestValSmallerThanLookUp = null;
  61             if(lookupIsString){
  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){
  72                         if(i==0)
  73                             return array.get(rowIndexNum-1, i);
  74                         else if(previousValue.compareTo(ref) > currCompedToRef )
  75                             return array.get(rowIndexNum-1, i);
  76                         else
  77                             return array.get(rowIndexNum - 1, i - 1);
  78                     }
  79                     if(colPosOfLargestValSmallerThanLookUp==null){
  80                         colPosOfLargestValSmallerThanLookUp = i;
  81                         largestValSmallerThanLookUp = currValue;
  82                     } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
  83                         colPosOfLargestValSmallerThanLookUp = i;
  84                         largestValSmallerThanLookUp = currValue;
  85                     }
  86                     previousValue = currValue;
  87                 }
  88
  89             } else {
  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;
  97                     if (diff >= 0) {
  98                         if (i == 0)
  99                             return array.get(rowIndexNum - 1, i);
 100                         else if (diff < -lastDiff)
 101                             return array.get(rowIndexNum - 1, i);
 102                         else
 103                             return array.get(rowIndexNum - 1, i - 1);
 104                     }
 105                     if(colPosOfLargestValSmallerThanLookUp==null){
 106                         colPosOfLargestValSmallerThanLookUp = i;
 107                         largestValSmallerThanLookUp = n;
 108                     } else if(largestValSmallerThanLookUp < n){
 109                         colPosOfLargestValSmallerThanLookUp = i;
 110                         largestValSmallerThanLookUp = n;
 111                     }
 112                     lastDiff = diff;
 113                 }
 114             }
 115             if(colPosOfLargestValSmallerThanLookUp!=null) return array.get(rowIndexNum - 1, colPosOfLargestValSmallerThanLookUp);
 116
 117         } else {
 118
 119             for (int i = 0; i < array.getWidth(); i++) {
 120                 Object value = array.get(0, i);
 121                 if(value instanceof Variant){
 122                     if(lookupIsString)
 123                         value = ((Variant)value).getValue().toString();
 124                     else
 125                         value = Spreadsheets.asNumber(value);
 126                 }
 127                 if (Spreadsheets.excelEquals(lookupValue, value)) {
 128                     return array.get(rowIndexNum - 1, i);
 129                 }
 130             }
 131
 132         }
 133
 134         return FormulaError2.NA.getString();
 135     }
 136
 137 }