bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/HlookupFormulaFunction.java

   1 package org.simantics.spreadsheet.graph.formula;\r
   2 \r
   3 import org.simantics.databoard.binding.mutable.Variant;\r
   4 import org.simantics.spreadsheet.graph.CellFormulaFunction;\r
   5 import org.simantics.spreadsheet.graph.CellValueVisitor;\r
   6 import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;\r
   7 import org.simantics.spreadsheet.graph.SpreadsheetMatrix;\r
   8 import org.simantics.spreadsheet.graph.parser.ast.AstArgList;\r
   9 \r
  10 public class HlookupFormulaFunction implements CellFormulaFunction<Object> {\r
  11 \r
  12     @Override\r
  13     public Object evaluate(CellValueVisitor visitor, AstArgList args) {\r
  14         if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();\r
  15 \r
  16         Object lookupValue = args.values.get(0).accept(visitor);\r
  17         boolean lookupIsString = lookupValue instanceof String;\r
  18 \r
  19         FormulaError2 error = FormulaError2.forObject(lookupValue);\r
  20         if(error!=null) return error.getString();\r
  21         \r
  22         if(lookupValue instanceof Variant){\r
  23                 Object varVal = ((Variant)lookupValue).getValue();\r
  24                 Double dVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(varVal);\r
  25                 if(dVal==null) {\r
  26                         lookupValue = varVal.toString();\r
  27                         lookupIsString = true;\r
  28                 }\r
  29                 else lookupValue = dVal;\r
  30         }\r
  31         \r
  32         SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);\r
  33         \r
  34         boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;\r
  35 \r
  36                 if(array.values.length != 0){\r
  37                         if(lookupIsString){\r
  38                                 Object obj = array.values[0];\r
  39                                 if(obj instanceof Variant)\r
  40                                         obj = ((Variant)obj).getValue();\r
  41                                 if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)\r
  42                                         return FormulaError2.NA.getString();\r
  43                                 obj = obj.toString();\r
  44                                 String valStr = ((String)obj).toLowerCase();\r
  45                                 if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )\r
  46                                         return FormulaError2.NA.getString();\r
  47                         }\r
  48                         else if(lookupValue instanceof Number){\r
  49                                 Double d = ((Number)lookupValue).doubleValue();\r
  50                                 Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);\r
  51                                 if(d2==null || d<(d2.doubleValue()))\r
  52                                         return FormulaError2.NA.getString();\r
  53                         }\r
  54                 }\r
  55 \r
  56         Number rowIndexN = (Number) SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));\r
  57         if(rowIndexN==null) return FormulaError2.REF.getString();\r
  58         int rowIndexNum = rowIndexN.intValue();\r
  59                 if(rowIndexNum>array.getHeight()) return FormulaError2.REF.getString();\r
  60                 \r
  61         if (approximateMatch) {\r
  62                 Integer colPosOfLargestValSmallerThanLookUp = null;\r
  63                 if(lookupIsString){\r
  64                         String largestValSmallerThanLookUp = null;\r
  65                         String ref = lookupValue.toString();\r
  66                         String previousValue = null;\r
  67                         for(int i = 0; i < array.getWidth(); i++){\r
  68                                 Object val = array.get(0, i);\r
  69                                 if(val instanceof Variant)\r
  70                                         val = ((Variant)val).getValue();\r
  71                                 String currValue = val.toString();\r
  72                                 int currCompedToRef = currValue.compareTo(ref);\r
  73                                 if(currCompedToRef>=0){\r
  74                                         if(i==0)\r
  75                                                 return array.get(rowIndexNum-1, i);\r
  76                                         else if(previousValue.compareTo(ref) > currCompedToRef )\r
  77                                                 return array.get(rowIndexNum-1, i);\r
  78                                         else \r
  79                                                 return array.get(rowIndexNum - 1, i - 1);\r
  80                                 }\r
  81                                 if(colPosOfLargestValSmallerThanLookUp==null){\r
  82                                         colPosOfLargestValSmallerThanLookUp = i;\r
  83                                         largestValSmallerThanLookUp = currValue;\r
  84                                 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){\r
  85                                         colPosOfLargestValSmallerThanLookUp = i;\r
  86                                         largestValSmallerThanLookUp = currValue;\r
  87                                 }\r
  88                                 previousValue = currValue;\r
  89                         }\r
  90                         \r
  91                 } else {\r
  92                         double ref = SpreadsheetGraphUtils.asNumber(lookupValue);\r
  93                         double lastDiff = Double.MIN_VALUE;\r
  94                         Double largestValSmallerThanLookUp = null;\r
  95                 for (int i = 0; i < array.getWidth(); i++) {\r
  96                         Object value = array.get(0, i);\r
  97                         double n = SpreadsheetGraphUtils.asNumber(value);\r
  98                         double diff = n - ref;\r
  99                         if (diff >= 0) {\r
 100                         if (i == 0)\r
 101                                 return array.get(rowIndexNum - 1, i);\r
 102                         else if (diff < -lastDiff)\r
 103                                 return array.get(rowIndexNum - 1, i);\r
 104                         else\r
 105                                 return array.get(rowIndexNum - 1, i - 1);\r
 106                         }\r
 107                                 if(colPosOfLargestValSmallerThanLookUp==null){\r
 108                                         colPosOfLargestValSmallerThanLookUp = i;\r
 109                                         largestValSmallerThanLookUp = n;\r
 110                                 } else if(largestValSmallerThanLookUp < n){\r
 111                                         colPosOfLargestValSmallerThanLookUp = i;\r
 112                                         largestValSmallerThanLookUp = n;\r
 113                                 }\r
 114                         lastDiff = diff;\r
 115                 }\r
 116                 }\r
 117                 if(colPosOfLargestValSmallerThanLookUp!=null) return array.get(rowIndexNum - 1, colPosOfLargestValSmallerThanLookUp);\r
 118                 \r
 119         } else {\r
 120 \r
 121             for (int i = 0; i < array.getWidth(); i++) {\r
 122                 Object value = array.get(0, i);\r
 123                 if(value instanceof Variant){\r
 124                         if(lookupIsString)\r
 125                                 value = ((Variant)value).getValue().toString();\r
 126                         else \r
 127                                 value = SpreadsheetGraphUtils.asNumber(value);\r
 128                 }\r
 129                 if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {\r
 130                     return array.get(rowIndexNum - 1, i);\r
 131                 }\r
 132             }\r
 133 \r
 134         }\r
 135 \r
 136         return FormulaError2.NA.getString();\r
 137     }\r
 138 \r
 139 }\r