-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 MatchFormulaFunction implements CellFormulaFunction<Object>{\r
- \r
- @Override\r
- public Object evaluate(CellValueVisitor visitor, AstArgList args) {\r
- if (args.values.size() < 2 || args.values.size() > 3) throw new IllegalStateException();\r
- \r
- SpreadsheetMatrix lookup_array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);\r
- if(lookup_array.getWidth()!=1 && lookup_array.getHeight()!=1) return FormulaError2.NA.getString();\r
- \r
- Object lookup_value = args.values.get(0).accept(visitor);\r
- \r
- int match_type = 1;\r
- try{\r
- if(args.values.size()==3){\r
- int matchArg = ((Number)args.values.get(2).accept(visitor)).intValue();\r
- if(matchArg<0)\r
- match_type = -1;\r
- else if(matchArg>0)\r
- match_type = 1;\r
- else\r
- match_type = 0;\r
- }\r
- } catch(Exception e) {\r
- return FormulaError2.NA.getString();\r
- }\r
-\r
- boolean useHeight = false;\r
- if(lookup_array.getWidth()==1) useHeight = true;\r
- \r
- int max = 0;\r
- if(useHeight) max = lookup_array.getHeight();\r
- else max = lookup_array.getWidth();\r
-\r
- Integer pos = null;\r
- if(lookup_value instanceof Variant){\r
- Object obj = ((Variant)lookup_value).getValue();\r
- Number nVal = SpreadsheetGraphUtils.asValidNumber(obj);\r
- if(nVal!=null) obj = nVal;\r
- else obj = obj.toString();\r
- }\r
- \r
- if(lookup_value instanceof Number){\r
- Double previousValue = null;\r
- Double closestMatch = null;\r
- Double lookup = ((Number)lookup_value).doubleValue();\r
- int indexWhereCorrectOrderStartsAt = 0;\r
- \r
- if(match_type!=0) {\r
- for(int i = 0; i < max;i++){\r
- Double currValue = null;\r
- Number currNum;\r
- if(useHeight){\r
- currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));\r
- } else {\r
- currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));\r
- }\r
- if(currNum!=null){\r
- currValue = currNum.doubleValue();\r
- if(currValue != null){\r
- if(previousValue!=null){\r
- if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue))\r
- indexWhereCorrectOrderStartsAt = i;\r
- else\r
- break;\r
- }\r
- previousValue = currValue;\r
- }\r
- }\r
- }\r
- }\r
- int begin = indexWhereCorrectOrderStartsAt;\r
- \r
- for(int i = begin; i < max;i++){\r
- Double currValue = null;\r
- Number currNum;\r
- if(useHeight){\r
- currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));\r
- } else {\r
- currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));\r
- }\r
- if(currNum!=null){\r
- currValue = currNum.doubleValue();\r
- if(currValue != null){\r
- if(previousValue==null) previousValue = currValue;\r
- if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue)){\r
- if(pos!=null)\r
- return pos+1;\r
- previousValue = currValue;\r
- }\r
- int comp = lookup.compareTo(currValue);\r
- if(comp == 0){\r
- closestMatch = currValue;\r
- pos = i;\r
- }\r
- else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){\r
- if(closestMatch==null && pos==null){\r
- closestMatch = currValue;\r
- pos = i;\r
- }\r
- else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){\r
- closestMatch = currValue;\r
- pos = i;\r
- }\r
- }\r
- previousValue = currValue;\r
- }\r
- }\r
- }\r
- }\r
- \r
- else if(lookup_value instanceof String){\r
- String previousValue = null;\r
- String closestMatch = null;\r
- String lookup = (String)lookup_value;\r
- int indexWhereCorrectOrderStartsAt = 0;\r
- \r
- if(match_type!=0) {\r
- for(int i = 0; i < max;i++){\r
- String currValue = null;\r
- Object obj;\r
- if(useHeight){\r
- obj = lookup_array.get(i,0);\r
- } else {\r
- obj = lookup_array.get(0,i);\r
- }\r
- if(obj instanceof Variant){\r
- obj = ((Variant)obj).getValue();\r
- }\r
- if(obj!=null && !(obj instanceof Number)){\r
- currValue = obj.toString();\r
- currValue = currValue.toLowerCase();\r
- }\r
- if(currValue != null && !currValue.equals("")){\r
- if(previousValue!=null){\r
- if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0))\r
- indexWhereCorrectOrderStartsAt = i;\r
- else\r
- break;\r
- }\r
- previousValue = currValue;\r
- }\r
- }\r
- }\r
- \r
- int begin = indexWhereCorrectOrderStartsAt;\r
-\r
- for(int i = begin; i < max;i++){\r
- String currValue = null;\r
- Object obj;\r
- if(useHeight){\r
- obj = lookup_array.get(i,0);\r
- } else {\r
- obj = lookup_array.get(0,i);\r
- }\r
- if(obj instanceof Variant){\r
- obj = ((Variant)obj).getValue();\r
- }\r
- if(obj!=null && !(obj instanceof Number)){\r
- currValue = obj.toString();\r
- currValue = currValue.toLowerCase();\r
- }\r
-\r
- if(currValue != null && !currValue.equals("")){\r
- if(previousValue==null) previousValue = currValue;\r
- if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0)){\r
- if(pos!=null)\r
- return pos+1;\r
- previousValue = currValue;\r
- }\r
- int comp = lookup.compareTo(currValue);\r
- if(comp == 0){\r
- closestMatch = currValue;\r
- pos = i;\r
- }\r
- else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){\r
- if(closestMatch==null && pos==null){\r
- closestMatch = currValue;\r
- pos = i;\r
- }\r
- else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){\r
- closestMatch = currValue;\r
- pos = i;\r
- }\r
- }\r
- previousValue = currValue;\r
- }\r
- }\r
- }\r
-\r
- if(pos==null)return FormulaError2.NA.getString();\r
- return pos+1;\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 MatchFormulaFunction implements CellFormulaFunction<Object>{
+
+ @Override
+ public Object evaluate(CellValueVisitor visitor, AstArgList args) {
+ if (args.values.size() < 2 || args.values.size() > 3) throw new IllegalStateException();
+
+ SpreadsheetMatrix lookup_array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
+ if(lookup_array.getWidth()!=1 && lookup_array.getHeight()!=1) return FormulaError2.NA.getString();
+
+ Object lookup_value = args.values.get(0).accept(visitor);
+
+ int match_type = 1;
+ try{
+ if(args.values.size()==3){
+ int matchArg = ((Number)args.values.get(2).accept(visitor)).intValue();
+ if(matchArg<0)
+ match_type = -1;
+ else if(matchArg>0)
+ match_type = 1;
+ else
+ match_type = 0;
+ }
+ } catch(Exception e) {
+ return FormulaError2.NA.getString();
+ }
+
+ boolean useHeight = false;
+ if(lookup_array.getWidth()==1) useHeight = true;
+
+ int max = 0;
+ if(useHeight) max = lookup_array.getHeight();
+ else max = lookup_array.getWidth();
+
+ Integer pos = null;
+ if(lookup_value instanceof Variant){
+ Object obj = ((Variant)lookup_value).getValue();
+ Number nVal = SpreadsheetGraphUtils.asValidNumber(obj);
+ if(nVal!=null) obj = nVal;
+ else obj = obj.toString();
+ }
+
+ if(lookup_value instanceof Number){
+ Double previousValue = null;
+ Double closestMatch = null;
+ Double lookup = ((Number)lookup_value).doubleValue();
+ int indexWhereCorrectOrderStartsAt = 0;
+
+ if(match_type!=0) {
+ for(int i = 0; i < max;i++){
+ Double currValue = null;
+ Number currNum;
+ if(useHeight){
+ currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));
+ } else {
+ currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));
+ }
+ if(currNum!=null){
+ currValue = currNum.doubleValue();
+ if(currValue != null){
+ if(previousValue!=null){
+ if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue))
+ indexWhereCorrectOrderStartsAt = i;
+ else
+ break;
+ }
+ previousValue = currValue;
+ }
+ }
+ }
+ }
+ int begin = indexWhereCorrectOrderStartsAt;
+
+ for(int i = begin; i < max;i++){
+ Double currValue = null;
+ Number currNum;
+ if(useHeight){
+ currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));
+ } else {
+ currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));
+ }
+ if(currNum!=null){
+ currValue = currNum.doubleValue();
+ if(currValue != null){
+ if(previousValue==null) previousValue = currValue;
+ if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue)){
+ if(pos!=null)
+ return pos+1;
+ previousValue = currValue;
+ }
+ int comp = lookup.compareTo(currValue);
+ if(comp == 0){
+ closestMatch = currValue;
+ pos = i;
+ }
+ else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
+ if(closestMatch==null && pos==null){
+ closestMatch = currValue;
+ pos = i;
+ }
+ else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
+ closestMatch = currValue;
+ pos = i;
+ }
+ }
+ previousValue = currValue;
+ }
+ }
+ }
+ }
+
+ else if(lookup_value instanceof String){
+ String previousValue = null;
+ String closestMatch = null;
+ String lookup = (String)lookup_value;
+ int indexWhereCorrectOrderStartsAt = 0;
+
+ if(match_type!=0) {
+ for(int i = 0; i < max;i++){
+ String currValue = null;
+ Object obj;
+ if(useHeight){
+ obj = lookup_array.get(i,0);
+ } else {
+ obj = lookup_array.get(0,i);
+ }
+ if(obj instanceof Variant){
+ obj = ((Variant)obj).getValue();
+ }
+ if(obj!=null && !(obj instanceof Number)){
+ currValue = obj.toString();
+ currValue = currValue.toLowerCase();
+ }
+ if(currValue != null && !currValue.equals("")){
+ if(previousValue!=null){
+ if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0))
+ indexWhereCorrectOrderStartsAt = i;
+ else
+ break;
+ }
+ previousValue = currValue;
+ }
+ }
+ }
+
+ int begin = indexWhereCorrectOrderStartsAt;
+
+ for(int i = begin; i < max;i++){
+ String currValue = null;
+ Object obj;
+ if(useHeight){
+ obj = lookup_array.get(i,0);
+ } else {
+ obj = lookup_array.get(0,i);
+ }
+ if(obj instanceof Variant){
+ obj = ((Variant)obj).getValue();
+ }
+ if(obj!=null && !(obj instanceof Number)){
+ currValue = obj.toString();
+ currValue = currValue.toLowerCase();
+ }
+
+ if(currValue != null && !currValue.equals("")){
+ if(previousValue==null) previousValue = currValue;
+ if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0)){
+ if(pos!=null)
+ return pos+1;
+ previousValue = currValue;
+ }
+ int comp = lookup.compareTo(currValue);
+ if(comp == 0){
+ closestMatch = currValue;
+ pos = i;
+ }
+ else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
+ if(closestMatch==null && pos==null){
+ closestMatch = currValue;
+ pos = i;
+ }
+ else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
+ closestMatch = currValue;
+ pos = i;
+ }
+ }
+ previousValue = currValue;
+ }
+ }
+ }
+
+ if(pos==null)return FormulaError2.NA.getString();
+ return pos+1;
+ }
+}