X-Git-Url: https://gerrit.simantics.org/r/gitweb?a=blobdiff_plain;f=bundles%2Forg.simantics.spreadsheet.graph%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Fgraph%2Fformula%2FVlookupFormulaFunction.java;fp=bundles%2Forg.simantics.spreadsheet.graph%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Fgraph%2Fformula%2FVlookupFormulaFunction.java;h=5f99cfc3517194c48f9c6c7a62169b295a78354f;hb=0ae2b770234dfc3cbb18bd38f324125cf0faca07;hp=fb0c325be637050c2398f4d4819091ad1bb22970;hpb=24e2b34260f219f0d1644ca7a138894980e25b14;p=simantics%2Fplatform.git

diff --git a/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/VlookupFormulaFunction.java b/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/VlookupFormulaFunction.java
index fb0c325be..5f99cfc35 100644
--- a/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/VlookupFormulaFunction.java
+++ b/bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/formula/VlookupFormulaFunction.java
@@ -1,163 +1,163 @@
-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();
-    }
-
-}
+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();
+    }
+
+}