--- /dev/null
+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 VlookupFormulaFunction 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
+ \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
+ boolean valueExists = false;\r
+ if(array.values.length != 0){\r
+ for(int i = 0; i < array.getHeight(); i++){\r
+ if(lookupIsString){\r
+ Object obj = array.get(i,0);\r
+ if(obj instanceof Variant)\r
+ obj = ((Variant)obj).getValue();\r
+ if(obj!=null && SpreadsheetGraphUtils.asValidNumber(obj)==null){\r
+ obj = obj.toString();\r
+ String valStr = ((String)obj).toLowerCase();\r
+ if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){\r
+ valueExists = true;\r
+ break;\r
+ }\r
+ }\r
+ }\r
+ else if(lookupValue instanceof Number){\r
+ Double d = ((Number)lookupValue).doubleValue();\r
+ Number d2 = SpreadsheetGraphUtils.asValidNumber(array.get(i,0));\r
+ if(d2!=null && d>=(d2.doubleValue())){\r
+ valueExists = true;\r
+ break;\r
+ }\r
+ }\r
+ }\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
+ if(!valueExists) return FormulaError2.NA.getString();\r
+ \r
+ Number colIndexN = SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));\r
+ if(colIndexN==null) return FormulaError2.REF.getString();\r
+ int colIndexNum = colIndexN.intValue();\r
+ if(colIndexNum>array.getWidth()) return FormulaError2.REF.getString();\r
+ \r
+ if (approximateMatch) {\r
+ Integer rowPosOfLargestValSmallerThanLookUp = null;\r
+ if(lookupIsString){\r
+ String ref = lookupValue.toString().toLowerCase();\r
+ String previousValue = null;\r
+ String largestValSmallerThanLookUp = null;\r
+ for(int i = 0; i < array.getHeight(); i++){\r
+ Object val = array.get(i, 0);\r
+ if(val instanceof Variant)\r
+ val = ((Variant)val).getValue();\r
+ String currValue = val.toString().toLowerCase();\r
+ if(currValue.compareTo(ref)>0){\r
+ if(i==0)\r
+ return array.get(i, colIndexNum-1);\r
+ else if(previousValue.compareTo(ref) > currValue.compareTo(ref))\r
+ return array.get(i, colIndexNum-1);\r
+ else \r
+ return array.get(i - 1, colIndexNum - 1);\r
+ }\r
+ if(rowPosOfLargestValSmallerThanLookUp==null){\r
+ rowPosOfLargestValSmallerThanLookUp = i;\r
+ largestValSmallerThanLookUp = currValue;\r
+ } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){\r
+ rowPosOfLargestValSmallerThanLookUp = 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.getHeight(); i++) {\r
+ Object value = array.get(i, 0);\r
+ double n = SpreadsheetGraphUtils.asNumber(value);\r
+ double diff = n - ref;\r
+ if (diff > 0) {\r
+ if (i == 0)\r
+ return array.get(i, colIndexNum - 1);\r
+ else if (diff < -lastDiff)\r
+ return array.get(i, colIndexNum - 1);\r
+ else\r
+ return array.get(i - 1, colIndexNum - 1);\r
+ }\r
+ if(rowPosOfLargestValSmallerThanLookUp==null){\r
+ rowPosOfLargestValSmallerThanLookUp = i;\r
+ largestValSmallerThanLookUp = n;\r
+ } else if(largestValSmallerThanLookUp < n){\r
+ rowPosOfLargestValSmallerThanLookUp = i;\r
+ largestValSmallerThanLookUp = n;\r
+ }\r
+ lastDiff = diff;\r
+ }\r
+ }\r
+ \r
+ if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);\r
+ \r
+ } else {\r
+ for (int i = 0; i < array.getHeight(); i++) {\r
+ Object value = array.get(i, 0);\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(i, colIndexNum - 1);\r
+ }\r
+ }\r
+ }\r
+\r
+ return FormulaError2.NA.getString();\r
+ }\r
+\r
+}\r
Code Review / simantics / platform.git / blobdiff