X-Git-Url: https://gerrit.simantics.org/r/gitweb?a=blobdiff_plain;f=bundles%2Forg.simantics.spreadsheet%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Fsolver%2Fformula%2FVlookupFormulaFunction.java;fp=bundles%2Forg.simantics.spreadsheet%2Fsrc%2Forg%2Fsimantics%2Fspreadsheet%2Fsolver%2Fformula%2FVlookupFormulaFunction.java;h=0c8bc8f4f0ebda13b602a1740f2614c7e15ef264;hb=c07a3818f0024e932a27eb85cbfd3f2291475a65;hp=0000000000000000000000000000000000000000;hpb=6c99e980d250fb9201aba93be7dcb1f55564dccd;p=simantics%2Fplatform.git

diff --git a/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/VlookupFormulaFunction.java b/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/VlookupFormulaFunction.java
new file mode 100644
index 000000000..0c8bc8f4f
--- /dev/null
+++ b/bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/VlookupFormulaFunction.java
@@ -0,0 +1,161 @@
+package org.simantics.spreadsheet.solver.formula;
+
+import org.simantics.databoard.binding.mutable.Variant;
+import org.simantics.spreadsheet.SpreadsheetMatrix;
+import org.simantics.spreadsheet.Spreadsheets;
+import org.simantics.spreadsheet.solver.formula.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 = Spreadsheets.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 && Spreadsheets.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 = Spreadsheets.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(Spreadsheets.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 = Spreadsheets.asValidNumber(array.values[0]);
+//				if(d2==null || d<(d2.doubleValue()))
+//					return FormulaError2.NA.getString();
+//			}
+        }
+        if(!valueExists) return FormulaError2.NA.getString();
+
+        Number colIndexN = Spreadsheets.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 = Spreadsheets.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 = Spreadsheets.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 = Spreadsheets.asNumber(value);
+                }
+                if (Spreadsheets.excelEquals(lookupValue, value)) {
+                    return array.get(i, colIndexNum - 1);
+                }
+            }
+        }
+
+        return FormulaError2.NA.getString();
+    }
+
+}