bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/VlookupFormulaFunction.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 VlookupFormulaFunction 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         \r
  33         SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);\r
  34 \r
  35         boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;\r
  36 \r
  37         boolean valueExists = false;\r
  38                 if(array.values.length != 0){\r
  39                         for(int i = 0; i < array.getHeight(); i++){\r
  40                                 if(lookupIsString){\r
  41                                         Object obj = array.get(i,0);\r
  42                                         if(obj instanceof Variant)\r
  43                                                 obj = ((Variant)obj).getValue();\r
  44                                         if(obj!=null && SpreadsheetGraphUtils.asValidNumber(obj)==null){\r
  45                                                 obj = obj.toString();\r
  46                                                 String valStr = ((String)obj).toLowerCase();\r
  47                                                 if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){\r
  48                                                         valueExists = true;\r
  49                                                         break;\r
  50                                                 }\r
  51                                         }\r
  52                                 }\r
  53                                 else if(lookupValue instanceof Number){\r
  54                                         Double d = ((Number)lookupValue).doubleValue();\r
  55                                         Number d2 = SpreadsheetGraphUtils.asValidNumber(array.get(i,0));\r
  56                                         if(d2!=null && d>=(d2.doubleValue())){\r
  57                                                 valueExists = true;\r
  58                                                 break;\r
  59                                         }\r
  60                                 }\r
  61                         }\r
  62 //                      if(lookupIsString){\r
  63 //                              Object obj = array.values[0];\r
  64 //                              if(obj instanceof Variant)\r
  65 //                                      obj = ((Variant)obj).getValue();\r
  66 //                              if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)\r
  67 //                                      return FormulaError2.NA.getString();\r
  68 //                              obj = obj.toString();\r
  69 //                              String valStr = ((String)obj).toLowerCase();\r
  70 //                              if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )\r
  71 //                                      return FormulaError2.NA.getString();\r
  72 //                      }\r
  73 //                      else if(lookupValue instanceof Number){\r
  74 //                              Double d = ((Number)lookupValue).doubleValue();\r
  75 //                              Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);\r
  76 //                              if(d2==null || d<(d2.doubleValue()))\r
  77 //                                      return FormulaError2.NA.getString();\r
  78 //                      }\r
  79                 }\r
  80                 if(!valueExists) return FormulaError2.NA.getString();\r
  81                 \r
  82                 Number colIndexN = SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));\r
  83                 if(colIndexN==null) return FormulaError2.REF.getString();\r
  84                 int colIndexNum = colIndexN.intValue();\r
  85         if(colIndexNum>array.getWidth()) return FormulaError2.REF.getString();\r
  86         \r
  87         if (approximateMatch) {\r
  88                 Integer rowPosOfLargestValSmallerThanLookUp = null;\r
  89                 if(lookupIsString){\r
  90                         String ref = lookupValue.toString().toLowerCase();\r
  91                         String previousValue = null;\r
  92                         String largestValSmallerThanLookUp = null;\r
  93                         for(int i = 0; i < array.getHeight(); i++){\r
  94                                 Object val = array.get(i, 0);\r
  95                                 if(val instanceof Variant)\r
  96                                         val = ((Variant)val).getValue();\r
  97                                 String currValue = val.toString().toLowerCase();\r
  98                                 if(currValue.compareTo(ref)>0){\r
  99                                         if(i==0)\r
 100                                                 return array.get(i, colIndexNum-1);\r
 101                                         else if(previousValue.compareTo(ref) > currValue.compareTo(ref))\r
 102                                                 return array.get(i, colIndexNum-1);\r
 103                                         else \r
 104                                                 return array.get(i - 1, colIndexNum - 1);\r
 105                                 }\r
 106                                 if(rowPosOfLargestValSmallerThanLookUp==null){\r
 107                                         rowPosOfLargestValSmallerThanLookUp = i;\r
 108                                         largestValSmallerThanLookUp = currValue;\r
 109                                 } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){\r
 110                                         rowPosOfLargestValSmallerThanLookUp = i;\r
 111                                         largestValSmallerThanLookUp = currValue;\r
 112                                 }\r
 113                                 previousValue = currValue;\r
 114                         }\r
 115                         \r
 116                 } else {\r
 117                         double ref = SpreadsheetGraphUtils.asNumber(lookupValue);\r
 118                 double lastDiff = Double.MIN_VALUE;\r
 119                 Double largestValSmallerThanLookUp = null;\r
 120                 for (int i = 0; i < array.getHeight(); i++) {\r
 121                         Object value = array.get(i, 0);\r
 122                         double n = SpreadsheetGraphUtils.asNumber(value);\r
 123                         double diff = n - ref;\r
 124                         if (diff > 0) {\r
 125                         if (i == 0)\r
 126                                 return array.get(i, colIndexNum - 1);\r
 127                         else if (diff < -lastDiff)\r
 128                                 return array.get(i, colIndexNum - 1);\r
 129                         else\r
 130                                 return array.get(i - 1, colIndexNum - 1);\r
 131                         }\r
 132                                 if(rowPosOfLargestValSmallerThanLookUp==null){\r
 133                                         rowPosOfLargestValSmallerThanLookUp = i;\r
 134                                         largestValSmallerThanLookUp = n;\r
 135                                 } else if(largestValSmallerThanLookUp < n){\r
 136                                         rowPosOfLargestValSmallerThanLookUp = i;\r
 137                                         largestValSmallerThanLookUp = n;\r
 138                                 }\r
 139                         lastDiff = diff;\r
 140                 }\r
 141                 }\r
 142                 \r
 143                 if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);\r
 144         \r
 145         } else {\r
 146             for (int i = 0; i < array.getHeight(); i++) {\r
 147                 Object value = array.get(i, 0);\r
 148                 if(value instanceof Variant){\r
 149                         if(lookupIsString)\r
 150                                 value = ((Variant)value).getValue().toString();\r
 151                         else \r
 152                                 value = SpreadsheetGraphUtils.asNumber(value);\r
 153                 }\r
 154                 if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {\r
 155                     return array.get(i, colIndexNum - 1);\r
 156                 }\r
 157             }\r
 158         }\r
 159 \r
 160         return FormulaError2.NA.getString();\r
 161     }\r
 162 \r
 163 }\r