--- /dev/null
+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 HlookupFormulaFunction 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;
+
+ if(array.values.length != 0){
+ 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();
+ }
+ }
+
+ Number rowIndexN = (Number) Spreadsheets.asValidNumber(args.values.get(2).accept(visitor));
+ if(rowIndexN==null) return FormulaError2.REF.getString();
+ int rowIndexNum = rowIndexN.intValue();
+ if(rowIndexNum>array.getHeight()) return FormulaError2.REF.getString();
+
+ if (approximateMatch) {
+ Integer colPosOfLargestValSmallerThanLookUp = null;
+ if(lookupIsString){
+ String largestValSmallerThanLookUp = null;
+ String ref = lookupValue.toString();
+ String previousValue = null;
+ for(int i = 0; i < array.getWidth(); i++){
+ Object val = array.get(0, i);
+ if(val instanceof Variant)
+ val = ((Variant)val).getValue();
+ String currValue = val.toString();
+ int currCompedToRef = currValue.compareTo(ref);
+ if(currCompedToRef>=0){
+ if(i==0)
+ return array.get(rowIndexNum-1, i);
+ else if(previousValue.compareTo(ref) > currCompedToRef )
+ return array.get(rowIndexNum-1, i);
+ else
+ return array.get(rowIndexNum - 1, i - 1);
+ }
+ if(colPosOfLargestValSmallerThanLookUp==null){
+ colPosOfLargestValSmallerThanLookUp = i;
+ largestValSmallerThanLookUp = currValue;
+ } else if(largestValSmallerThanLookUp.compareTo(currValue)<0){
+ colPosOfLargestValSmallerThanLookUp = 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.getWidth(); i++) {
+ Object value = array.get(0, i);
+ double n = Spreadsheets.asNumber(value);
+ double diff = n - ref;
+ if (diff >= 0) {
+ if (i == 0)
+ return array.get(rowIndexNum - 1, i);
+ else if (diff < -lastDiff)
+ return array.get(rowIndexNum - 1, i);
+ else
+ return array.get(rowIndexNum - 1, i - 1);
+ }
+ if(colPosOfLargestValSmallerThanLookUp==null){
+ colPosOfLargestValSmallerThanLookUp = i;
+ largestValSmallerThanLookUp = n;
+ } else if(largestValSmallerThanLookUp < n){
+ colPosOfLargestValSmallerThanLookUp = i;
+ largestValSmallerThanLookUp = n;
+ }
+ lastDiff = diff;
+ }
+ }
+ if(colPosOfLargestValSmallerThanLookUp!=null) return array.get(rowIndexNum - 1, colPosOfLargestValSmallerThanLookUp);
+
+ } else {
+
+ for (int i = 0; i < array.getWidth(); i++) {
+ Object value = array.get(0, i);
+ if(value instanceof Variant){
+ if(lookupIsString)
+ value = ((Variant)value).getValue().toString();
+ else
+ value = Spreadsheets.asNumber(value);
+ }
+ if (Spreadsheets.excelEquals(lookupValue, value)) {
+ return array.get(rowIndexNum - 1, i);
+ }
+ }
+
+ }
+
+ return FormulaError2.NA.getString();
+ }
+
+}