1 package org.simantics.spreadsheet.solver.formula;
3 import org.simantics.databoard.binding.mutable.Variant;
4 import org.simantics.spreadsheet.SpreadsheetMatrix;
5 import org.simantics.spreadsheet.Spreadsheets;
6 import org.simantics.spreadsheet.solver.formula.parser.ast.AstArgList;
8 public class MatchFormulaFunction implements CellFormulaFunction<Object>{
11 public Object evaluate(CellValueVisitor visitor, AstArgList args) {
12 if (args.values.size() < 2 || args.values.size() > 3) throw new IllegalStateException();
14 SpreadsheetMatrix lookup_array = (SpreadsheetMatrix) args.values.get(1).accept(visitor);
15 if(lookup_array.getWidth()!=1 && lookup_array.getHeight()!=1) return FormulaError2.NA.getString();
17 Object lookup_value = args.values.get(0).accept(visitor);
21 if(args.values.size()==3){
22 int matchArg = ((Number)args.values.get(2).accept(visitor)).intValue();
30 } catch(Exception e) {
31 return FormulaError2.NA.getString();
34 boolean useHeight = false;
35 if(lookup_array.getWidth()==1) useHeight = true;
38 if(useHeight) max = lookup_array.getHeight();
39 else max = lookup_array.getWidth();
42 if(lookup_value instanceof Variant){
43 Object obj = ((Variant)lookup_value).getValue();
44 Number nVal = Spreadsheets.asValidNumber(obj);
45 if(nVal!=null) obj = nVal;
46 else obj = obj.toString();
49 if(lookup_value instanceof Number){
50 Double previousValue = null;
51 Double closestMatch = null;
52 Double lookup = ((Number)lookup_value).doubleValue();
53 int indexWhereCorrectOrderStartsAt = 0;
56 for(int i = 0; i < max;i++){
57 Double currValue = null;
60 currNum = Spreadsheets.asValidNumber(lookup_array.get(i,0));
62 currNum = Spreadsheets.asValidNumber(lookup_array.get(0,i));
65 currValue = currNum.doubleValue();
66 if(currValue != null){
67 if(previousValue!=null){
68 if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue))
69 indexWhereCorrectOrderStartsAt = i;
73 previousValue = currValue;
78 int begin = indexWhereCorrectOrderStartsAt;
80 for(int i = begin; i < max;i++){
81 Double currValue = null;
84 currNum = Spreadsheets.asValidNumber(lookup_array.get(i,0));
86 currNum = Spreadsheets.asValidNumber(lookup_array.get(0,i));
89 currValue = currNum.doubleValue();
90 if(currValue != null){
91 if(previousValue==null) previousValue = currValue;
92 if((match_type==-1 && currValue>previousValue) || (match_type==1 && currValue<previousValue)){
95 previousValue = currValue;
97 int comp = lookup.compareTo(currValue);
99 closestMatch = currValue;
102 else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
103 if(closestMatch==null && pos==null){
104 closestMatch = currValue;
107 else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
108 closestMatch = currValue;
112 previousValue = currValue;
118 else if(lookup_value instanceof String){
119 String previousValue = null;
120 String closestMatch = null;
121 String lookup = (String)lookup_value;
122 int indexWhereCorrectOrderStartsAt = 0;
125 for(int i = 0; i < max;i++){
126 String currValue = null;
129 obj = lookup_array.get(i,0);
131 obj = lookup_array.get(0,i);
133 if(obj instanceof Variant){
134 obj = ((Variant)obj).getValue();
136 if(obj!=null && !(obj instanceof Number)){
137 currValue = obj.toString();
138 currValue = currValue.toLowerCase();
140 if(currValue != null && !currValue.equals("")){
141 if(previousValue!=null){
142 if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0))
143 indexWhereCorrectOrderStartsAt = i;
147 previousValue = currValue;
152 int begin = indexWhereCorrectOrderStartsAt;
154 for(int i = begin; i < max;i++){
155 String currValue = null;
158 obj = lookup_array.get(i,0);
160 obj = lookup_array.get(0,i);
162 if(obj instanceof Variant){
163 obj = ((Variant)obj).getValue();
165 if(obj!=null && !(obj instanceof Number)){
166 currValue = obj.toString();
167 currValue = currValue.toLowerCase();
170 if(currValue != null && !currValue.equals("")){
171 if(previousValue==null) previousValue = currValue;
172 if((match_type==-1 && currValue.compareTo(previousValue)>0) || (match_type==1 && currValue.compareTo(previousValue)<0)){
175 previousValue = currValue;
177 int comp = lookup.compareTo(currValue);
179 closestMatch = currValue;
182 else if((comp > 0 && match_type==1) || (comp<0 && match_type==-1)){
183 if(closestMatch==null && pos==null){
184 closestMatch = currValue;
187 else if((currValue.compareTo(closestMatch)>=0 && match_type==1) || (currValue.compareTo(closestMatch)<=0 && match_type==-1)){
188 closestMatch = currValue;
192 previousValue = currValue;
197 if(pos==null)return FormulaError2.NA.getString();