-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
+package org.simantics.spreadsheet.graph.formula;
+
+import org.simantics.databoard.binding.mutable.Variant;
+import org.simantics.spreadsheet.graph.CellFormulaFunction;
+import org.simantics.spreadsheet.graph.CellValueVisitor;
+import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;
+import org.simantics.spreadsheet.graph.SpreadsheetMatrix;
+import org.simantics.spreadsheet.graph.parser.ast.AstArgList;
+
+public class VlookupFormulaFunction implements CellFormulaFunction<Object> {
+
+ @Override
+ public Object evaluate(CellValueVisitor visitor, AstArgList args) {
+ if (args.values.size() < 3 || args.values.size() > 4) throw new IllegalStateException();
+
+ Object lookupValue = args.values.get(0).accept(visitor);
+ boolean lookupIsString = lookupValue instanceof String;
+
+ FormulaError2 error = FormulaError2.forObject(lookupValue);
+ if(error!=null) return error.getString();
+
+ if(lookupValue instanceof Variant){
+ Object varVal = ((Variant)lookupValue).getValue();
+ Double dVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(varVal);
+ if(dVal==null) {
+ lookupValue = varVal.toString();
+ lookupIsString = true;
+ }
+ else lookupValue = dVal;
+ }
+
+
+ SpreadsheetMatrix array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
+
+ boolean approximateMatch = args.values.size() == 4 ? (Boolean) args.values.get(3).accept(visitor) : true;
+
+ boolean valueExists = false;
+ if(array.values.length != 0){
+ for(int i = 0; i < array.getHeight(); i++){
+ if(lookupIsString){
+ Object obj = array.get(i,0);
+ if(obj instanceof Variant)
+ obj = ((Variant)obj).getValue();
+ if(obj!=null && SpreadsheetGraphUtils.asValidNumber(obj)==null){
+ obj = obj.toString();
+ String valStr = ((String)obj).toLowerCase();
+ if((lookupValue.toString().toLowerCase()).compareTo(valStr)>=0 ){
+ valueExists = true;
+ break;
+ }
+ }
+ }
+ else if(lookupValue instanceof Number){
+ Double d = ((Number)lookupValue).doubleValue();
+ Number d2 = SpreadsheetGraphUtils.asValidNumber(array.get(i,0));
+ if(d2!=null && d>=(d2.doubleValue())){
+ valueExists = true;
+ break;
+ }
+ }
+ }
+// if(lookupIsString){
+// Object obj = array.values[0];
+// if(obj instanceof Variant)
+// obj = ((Variant)obj).getValue();
+// if(SpreadsheetGraphUtils.asValidNumber(obj)!=null)
+// return FormulaError2.NA.getString();
+// obj = obj.toString();
+// String valStr = ((String)obj).toLowerCase();
+// if((lookupValue.toString().toLowerCase()).compareTo(valStr)<0 )
+// return FormulaError2.NA.getString();
+// }
+// else if(lookupValue instanceof Number){
+// Double d = ((Number)lookupValue).doubleValue();
+// Number d2 = SpreadsheetGraphUtils.asValidNumber(array.values[0]);
+// if(d2==null || d<(d2.doubleValue()))
+// return FormulaError2.NA.getString();
+// }
+ }
+ if(!valueExists) return FormulaError2.NA.getString();
+
+ Number colIndexN = SpreadsheetGraphUtils.asValidNumber(args.values.get(2).accept(visitor));
+ if(colIndexN==null) return FormulaError2.REF.getString();
+ int colIndexNum = colIndexN.intValue();
+ if(colIndexNum>array.getWidth()) return FormulaError2.REF.getString();
+
+ if (approximateMatch) {
+ Integer rowPosOfLargestValSmallerThanLookUp = null;
+ if(lookupIsString){
+ String ref = lookupValue.toString().toLowerCase();
+ String previousValue = null;
+ String largestValSmallerThanLookUp = null;
+ for(int i = 0; i < array.getHeight(); i++){
+ Object val = array.get(i, 0);
+ if(val instanceof Variant)
+ val = ((Variant)val).getValue();
+ String currValue = val.toString().toLowerCase();
+ if(currValue.compareTo(ref)>0){
+ if(i==0)
+ return array.get(i, colIndexNum-1);
+ else if(previousValue.compareTo(ref) > currValue.compareTo(ref))
+ return array.get(i, colIndexNum-1);
+ else
+ return array.get(i - 1, colIndexNum - 1);
+ }
+ if(rowPosOfLargestValSmallerThanLookUp==null){
+ rowPosOfLargestValSmallerThanLookUp = i;
+ largestValSmallerThanLookUp = currValue;
+ } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
+ rowPosOfLargestValSmallerThanLookUp = i;
+ largestValSmallerThanLookUp = currValue;
+ }
+ previousValue = currValue;
+ }
+
+ } else {
+ double ref = SpreadsheetGraphUtils.asNumber(lookupValue);
+ double lastDiff = Double.MIN_VALUE;
+ Double largestValSmallerThanLookUp = null;
+ for (int i = 0; i < array.getHeight(); i++) {
+ Object value = array.get(i, 0);
+ double n = SpreadsheetGraphUtils.asNumber(value);
+ double diff = n - ref;
+ if (diff > 0) {
+ if (i == 0)
+ return array.get(i, colIndexNum - 1);
+ else if (diff < -lastDiff)
+ return array.get(i, colIndexNum - 1);
+ else
+ return array.get(i - 1, colIndexNum - 1);
+ }
+ if(rowPosOfLargestValSmallerThanLookUp==null){
+ rowPosOfLargestValSmallerThanLookUp = i;
+ largestValSmallerThanLookUp = n;
+ } else if(largestValSmallerThanLookUp < n){
+ rowPosOfLargestValSmallerThanLookUp = i;
+ largestValSmallerThanLookUp = n;
+ }
+ lastDiff = diff;
+ }
+ }
+
+ if(rowPosOfLargestValSmallerThanLookUp!=null) return array.get(rowPosOfLargestValSmallerThanLookUp, colIndexNum - 1);
+
+ } else {
+ for (int i = 0; i < array.getHeight(); i++) {
+ Object value = array.get(i, 0);
+ if(value instanceof Variant){
+ if(lookupIsString)
+ value = ((Variant)value).getValue().toString();
+ else
+ value = SpreadsheetGraphUtils.asNumber(value);
+ }
+ if (SpreadsheetGraphUtils.excelEquals(lookupValue, value)) {
+ return array.get(i, colIndexNum - 1);
+ }
+ }
+ }
+
+ return FormulaError2.NA.getString();
+ }
+
+}
Code Review / simantics / platform.git / blobdiff