+++ /dev/null
-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