1 package org.simantics.spreadsheet.graph.formula;
3 import org.simantics.databoard.binding.mutable.Variant;
4 import org.simantics.spreadsheet.graph.CellFormulaFunction;
5 import org.simantics.spreadsheet.graph.CellValueVisitor;
6 import org.simantics.spreadsheet.graph.SpreadsheetGraphUtils;
7 import org.simantics.spreadsheet.graph.SpreadsheetMatrix;
8 import org.simantics.spreadsheet.graph.parser.ast.AstArgList;
10 public class MatchFormulaFunction implements CellFormulaFunction<Object>{
13 public Object evaluate(CellValueVisitor visitor, AstArgList args) {
14 if (args.values.size() < 2 || args.values.size() > 3) throw new IllegalStateException();
16 SpreadsheetMatrix lookup_array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
17 if(lookup_array.getWidth()!=1 && lookup_array.getHeight()!=1) return FormulaError2.NA.getString();
19 Object lookup_value = args.values.get(0).accept(visitor);
23 if(args.values.size()==3){
24 int matchArg = ((Number)args.values.get(2).accept(visitor)).intValue();
32 } catch(Exception e) {
33 return FormulaError2.NA.getString();
36 boolean useHeight = false;
37 if(lookup_array.getWidth()==1) useHeight = true;
40 if(useHeight) max = lookup_array.getHeight();
41 else max = lookup_array.getWidth();
44 if(lookup_value instanceof Variant){
45 Object obj = ((Variant)lookup_value).getValue();
46 Number nVal = SpreadsheetGraphUtils.asValidNumber(obj);
47 if(nVal!=null) obj = nVal;
48 else obj = obj.toString();
51 if(lookup_value instanceof Number){
52 Double previousValue = null;
53 Double closestMatch = null;
54 Double lookup = ((Number)lookup_value).doubleValue();
55 int indexWhereCorrectOrderStartsAt = 0;
58 for(int i = 0; i < max;i++){
59 Double currValue = null;
62 currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));
64 currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));
67 currValue = currNum.doubleValue();
68 if(currValue != null){
69 if(previousValue!=null){
70 if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue))
71 indexWhereCorrectOrderStartsAt = i;
75 previousValue = currValue;
80 int begin = indexWhereCorrectOrderStartsAt;
82 for(int i = begin; i < max;i++){
83 Double currValue = null;
86 currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(i,0));
88 currNum = SpreadsheetGraphUtils.asValidNumber(lookup_array.get(0,i));
91 currValue = currNum.doubleValue();
92 if(currValue != null){
93 if(previousValue==null) previousValue = currValue;
94 if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue)){
97 previousValue = currValue;
99 int comp = lookup.compareTo(currValue);
101 closestMatch = currValue;
104 else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
105 if(closestMatch==null && pos==null){
106 closestMatch = currValue;
109 else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
110 closestMatch = currValue;
114 previousValue = currValue;
120 else if(lookup_value instanceof String){
121 String previousValue = null;
122 String closestMatch = null;
123 String lookup = (String)lookup_value;
124 int indexWhereCorrectOrderStartsAt = 0;
127 for(int i = 0; i < max;i++){
128 String currValue = null;
131 obj = lookup_array.get(i,0);
133 obj = lookup_array.get(0,i);
135 if(obj instanceof Variant){
136 obj = ((Variant)obj).getValue();
138 if(obj!=null && !(obj instanceof Number)){
139 currValue = obj.toString();
140 currValue = currValue.toLowerCase();
142 if(currValue != null && !currValue.equals("")){
143 if(previousValue!=null){
144 if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0))
145 indexWhereCorrectOrderStartsAt = i;
149 previousValue = currValue;
154 int begin = indexWhereCorrectOrderStartsAt;
156 for(int i = begin; i < max;i++){
157 String currValue = null;
160 obj = lookup_array.get(i,0);
162 obj = lookup_array.get(0,i);
164 if(obj instanceof Variant){
165 obj = ((Variant)obj).getValue();
167 if(obj!=null && !(obj instanceof Number)){
168 currValue = obj.toString();
169 currValue = currValue.toLowerCase();
172 if(currValue != null && !currValue.equals("")){
173 if(previousValue==null) previousValue = currValue;
174 if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0)){
177 previousValue = currValue;
179 int comp = lookup.compareTo(currValue);
181 closestMatch = currValue;
184 else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
185 if(closestMatch==null && pos==null){
186 closestMatch = currValue;
189 else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
190 closestMatch = currValue;
194 previousValue = currValue;
199 if(pos==null)return FormulaError2.NA.getString();