Migrated source code from Simantics SVN

[simantics/platform.git] / bundles / org.simantics.spreadsheet.graph / src / org / simantics / spreadsheet / graph / formula / HlookupFormulaFunction.java

diff --git a/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/HlookupFormulaFunction.java b/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/HlookupFormulaFunction.java
new file mode 100644 (file)
index 0000000..95cd47c
--- /dev/null
+++ b/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/HlookupFormulaFunction.java
@@ -0,0 +1,139 @@
+package org.simantics.spreadsheet.graph.formula;\r
+\r
+import org.simantics.databoard.binding.mutable.Variant;\r
+import org.simantics.spreadsheet.graph.CellFormulaFunction;\r
+import org.simantics.spreadsheet.graph.CellValueVisitor;\r
+import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;\r
+import org.simantics.spreadsheet.graph.SpreadsheetMatrix;\r
+import org.simantics.spreadsheet.graph.parser.ast.AstArgList;\r
+\r
+public class HlookupFormulaFunction implements CellFormulaFunction<Object> {\r
+\r
+    @Override\r
+    public Object evaluate(CellValueVisitor visitor, AstArgList args) {\r
+        if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();\r
+\r
+        Object lookupValue = args.values.get(0).accept(visitor);\r
+        boolean lookupIsString = lookupValue instanceof String;\r
+\r
+        FormulaError2 error = FormulaError2.forObject(lookupValue);\r
+        if(error!=null) return error.getString();\r
+        \r
+        if(lookupValue instanceof Variant){\r
+               Object varVal = ((Variant)lookupValue).getValue();\r
+               Double dVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(varVal);\r
+               if(dVal==null) {\r
+                       lookupValue = varVal.toString();\r
+                       lookupIsString = true;\r
+               }\r
+               else lookupValue = dVal;\r
+        }\r
+        \r
+        SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);\r
+        \r
+        boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;\r
+\r
+               if(array.values.length != 0){\r
+                       if(lookupIsString){\r
+                               Object obj = array.values[0];\r
+                               if(obj instanceof Variant)\r
+                                       obj = ((Variant)obj).getValue();\r
+                               if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)\r
+                                       return FormulaError2.NA.getString();\r
+                               obj = obj.toString();\r
+                               String valStr = ((String)obj).toLowerCase();\r
+                               if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )\r
+                                       return FormulaError2.NA.getString();\r
+                       }\r
+                       else if(lookupValue instanceof Number){\r
+                               Double d = ((Number)lookupValue).doubleValue();\r
+                               Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);\r
+                               if(d2==null || d<(d2.doubleValue()))\r
+                                       return FormulaError2.NA.getString();\r
+                       }\r
+               }\r
+\r
+        Number rowIndexN = (Number) SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));\r
+        if(rowIndexN==null) return FormulaError2.REF.getString();\r
+        int rowIndexNum = rowIndexN.intValue();\r
+               if(rowIndexNum>array.getHeight()) return FormulaError2.REF.getString();\r
+               \r
+        if (approximateMatch) {\r
+               Integer colPosOfLargestValSmallerThanLookUp = null;\r
+               if(lookupIsString){\r
+                       String largestValSmallerThanLookUp = null;\r
+                       String ref = lookupValue.toString();\r
+                       String previousValue = null;\r
+                       for(int i = 0; i < array.getWidth(); i++){\r
+                               Object val = array.get(0, i);\r
+                               if(val instanceof Variant)\r
+                                       val = ((Variant)val).getValue();\r
+                               String currValue = val.toString();\r
+                               int currCompedToRef = currValue.compareTo(ref);\r
+                               if(currCompedToRef>=0){\r
+                                       if(i==0)\r
+                                               return array.get(rowIndexNum-1, i);\r
+                                       else if(previousValue.compareTo(ref) > currCompedToRef )\r
+                                               return array.get(rowIndexNum-1, i);\r
+                                       else \r
+                                               return array.get(rowIndexNum - 1, i - 1);\r
+                               }\r
+                               if(colPosOfLargestValSmallerThanLookUp==null){\r
+                                       colPosOfLargestValSmallerThanLookUp = i;\r
+                                       largestValSmallerThanLookUp = currValue;\r
+                               } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){\r
+                                       colPosOfLargestValSmallerThanLookUp = i;\r
+                                       largestValSmallerThanLookUp = currValue;\r
+                               }\r
+                               previousValue = currValue;\r
+                       }\r
+                       \r
+               } else {\r
+                       double ref = SpreadsheetGraphUtils.asNumber(lookupValue);\r
+                       double lastDiff = Double.MIN_VALUE;\r
+                       Double largestValSmallerThanLookUp = null;\r
+               for (int i = 0; i < array.getWidth(); i++) {\r
+                       Object value = array.get(0, i);\r
+                       double n = SpreadsheetGraphUtils.asNumber(value);\r
+                       double diff = n - ref;\r
+                       if (diff >= 0) {\r
+                       if (i == 0)\r
+                               return array.get(rowIndexNum - 1, i);\r
+                       else if (diff < -lastDiff)\r
+                               return array.get(rowIndexNum - 1, i);\r
+                       else\r
+                               return array.get(rowIndexNum - 1, i - 1);\r
+                       }\r
+                               if(colPosOfLargestValSmallerThanLookUp==null){\r
+                                       colPosOfLargestValSmallerThanLookUp = i;\r
+                                       largestValSmallerThanLookUp = n;\r
+                               } else if(largestValSmallerThanLookUp < n){\r
+                                       colPosOfLargestValSmallerThanLookUp = i;\r
+                                       largestValSmallerThanLookUp = n;\r
+                               }\r
+                       lastDiff = diff;\r
+               }\r
+               }\r
+               if(colPosOfLargestValSmallerThanLookUp!=null) return array.get(rowIndexNum - 1, colPosOfLargestValSmallerThanLookUp);\r
+               \r
+        } else {\r
+\r
+            for (int i = 0; i < array.getWidth(); i++) {\r
+                Object value = array.get(0, i);\r
+                if(value instanceof Variant){\r
+                       if(lookupIsString)\r
+                               value = ((Variant)value).getValue().toString();\r
+                       else \r
+                               value = SpreadsheetGraphUtils.asNumber(value);\r
+                }\r
+                if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {\r
+                    return array.get(rowIndexNum - 1, i);\r
+                }\r
+            }\r
+\r
+        }\r
+\r
+        return FormulaError2.NA.getString();\r
+    }\r
+\r
+}\r