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