1 package org.simantics.spreadsheet.solver.formula;
3 import org.simantics.databoard.binding.mutable.Variant;
4 import org.simantics.spreadsheet.Range;
5 import org.simantics.spreadsheet.SpreadsheetMatrix;
6 import org.simantics.spreadsheet.Spreadsheets;
7 import org.simantics.spreadsheet.solver.SpreadsheetBook;
8 import org.simantics.spreadsheet.solver.SpreadsheetCell;
9 import org.simantics.spreadsheet.solver.SpreadsheetEngine;
10 import org.simantics.spreadsheet.solver.SpreadsheetLine;
11 import org.simantics.spreadsheet.solver.formula.parser.ast.AstApply;
12 import org.simantics.spreadsheet.solver.formula.parser.ast.AstArgList;
13 import org.simantics.spreadsheet.solver.formula.parser.ast.AstArithmeticExpression;
14 import org.simantics.spreadsheet.solver.formula.parser.ast.AstArray;
15 import org.simantics.spreadsheet.solver.formula.parser.ast.AstArrayFormulaReference;
16 import org.simantics.spreadsheet.solver.formula.parser.ast.AstBoolean;
17 import org.simantics.spreadsheet.solver.formula.parser.ast.AstDouble;
18 import org.simantics.spreadsheet.solver.formula.parser.ast.AstFactor;
19 import org.simantics.spreadsheet.solver.formula.parser.ast.AstIdentifier;
20 import org.simantics.spreadsheet.solver.formula.parser.ast.AstInteger;
21 import org.simantics.spreadsheet.solver.formula.parser.ast.AstNothing;
22 import org.simantics.spreadsheet.solver.formula.parser.ast.AstNull;
23 import org.simantics.spreadsheet.solver.formula.parser.ast.AstRange;
24 import org.simantics.spreadsheet.solver.formula.parser.ast.AstRelation;
25 import org.simantics.spreadsheet.solver.formula.parser.ast.AstString;
26 import org.simantics.spreadsheet.solver.formula.parser.ast.AstTerm;
27 import org.simantics.spreadsheet.solver.formula.parser.ast.AstValue;
28 import org.simantics.spreadsheet.solver.formula.parser.ast.AstValueVisitor;
30 import it.unimi.dsi.fastutil.longs.AbstractLongList;
31 import it.unimi.dsi.fastutil.longs.LongArrayList;
33 public class CellValueVisitor implements AstValueVisitor<Object> {
35 final public SpreadsheetBook book;
36 final private SpreadsheetEvaluationEnvironment env;
37 final private SpreadsheetCell thisCell;
38 final private LongArrayList references = new LongArrayList();
40 public CellValueVisitor(SpreadsheetEvaluationEnvironment env, SpreadsheetCell thisCell) {
41 this.book = env.getBook();
43 this.thisCell = thisCell;
46 public void addReference(long ref) {
50 public AbstractLongList getReferences() {
55 public Object visit(AstBoolean astBoolean) {
56 return astBoolean.value;
60 public Object visit(AstDouble astFloat) {
61 return astFloat.value;
65 public Object visit(AstInteger astInteger) {
66 return astInteger.value;
70 public Object visit(AstNull astNull) {
71 throw new IllegalStateException();
75 public Object visit(AstString astString) {
76 return astString.value;
80 public Object visit(AstRange astRange) {
83 return FormulaError2.REF.getString();
86 if(astRange.isCell()) {
87 String ref = astRange.first;
88 Range r = Spreadsheets.decodeCell(ref, 0, 0);
89 String sheetName = astRange.sheetName != null ? astRange.sheetName : thisCell.getEngine().getName();
90 SpreadsheetCell cell = thisCell.getBook().get(sheetName, r.startRow, r.startColumn);
92 SpreadsheetEngine eng = thisCell.getBook().getEngine(sheetName);
93 SpreadsheetLine line = eng.getLine(r.startRow);
96 line = new SpreadsheetLine(eng.lines, r.startRow);
97 eng.lines.lines.put(-r.startRow, line);
99 cell = SpreadsheetCell.empty(line, r.startColumn);
101 return cell.evaluate(env, this);
105 Object cached = thisCell.getEngine().getCachedRange(astRange);
108 Range r_ = Spreadsheets.decodeRange(astRange.first + ":" + astRange.second);
109 String sheetName = astRange.sheetName != null ? astRange.sheetName : thisCell.getEngine().getName();
110 SpreadsheetEngine eng = thisCell.getBook().getEngine(sheetName);
111 Range r = eng.actualRange(r_);
112 for(int row=0; row < r.height();row++) {
113 SpreadsheetLine line = eng.getLine(r.startRow + row);
115 for(int column=0; column < r.width();column++) {
116 int col = r.startColumn + column;
117 if(line.cells.size() > col) {
118 SpreadsheetCell cell = line.cells.get(r.startColumn + column);
119 //Add references, but do not evaluate if there exists a cached range.
120 addReference(cell.makeReferenceKey());
128 Range r_ = Spreadsheets.decodeRange(astRange.first + ":" + astRange.second);
129 String sheetName = astRange.sheetName != null ? astRange.sheetName : thisCell.getEngine().getName();
130 SpreadsheetEngine eng = thisCell.getBook().getEngine(sheetName);
131 // Full ranges are resolved here
132 Range r = eng.actualRange(r_);
133 SpreadsheetMatrix result = new SpreadsheetMatrix(r.width(), r.height());
134 for(int row=0; row < r.height();row++) {
135 SpreadsheetLine line = eng.getLine(r.startRow + row);
137 for(int column=0; column < r.width();column++) {
138 int col = r.startColumn + column;
139 if(line.cells.size() > col) {
140 SpreadsheetCell cell = line.cells.get(r.startColumn + column);
141 result.values[r.width()*row + column] = cell.evaluate(env, this);
146 thisCell.getEngine().cacheRange(astRange, result);
152 public Object visit(AstArgList astArgList) {
153 throw new IllegalStateException();
157 public Object visit(AstApply astApply) {
158 CellFormulaFunction<?> fn = env.getFunction(astApply.value);
160 return fn.evaluate(this, astApply.args);
162 return FormulaError2.NAME.getString();
167 public Object visit(AstRelation astRelation) {
169 Object leftResult = astRelation.left.accept(this);
170 Object rightResult = astRelation.right.accept(this);
172 FormulaError2 err = FormulaError2.forObject(leftResult);
173 if(err!=null) return err.getString();
174 FormulaError2 err2 = FormulaError2.forObject(rightResult);
175 if(err2!=null) return err2.getString();
177 if(leftResult instanceof Variant){
178 Object leftTemp = ((Variant)leftResult).getValue();
179 Double leftVal = Spreadsheets.asDoubleWhereEmptyStringIsZero(leftTemp);
180 if(leftVal==null) leftResult = leftTemp.toString();
181 else leftResult = leftVal;
183 if(rightResult instanceof Variant){
184 Object rightTemp = ((Variant)rightResult).getValue();
185 Double rightVal = Spreadsheets.asDoubleWhereEmptyStringIsZero(rightTemp);
186 if(rightVal==null) rightResult = rightTemp.toString();
187 else rightResult = rightVal;
189 if ((leftResult instanceof String) && (rightResult instanceof String)) {
190 String leftString = (leftResult.toString()).toLowerCase();
191 String rightString = (rightResult.toString()).toLowerCase();
192 if("<".equals(astRelation.op.trim())) return leftString.compareTo(rightString) < 0;
193 else if(">".equals(astRelation.op.trim())) return leftString.compareTo(rightString) > 0;
194 else if("=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) == 0;
195 else if("<>".equals(astRelation.op.trim())) return leftString.compareTo(rightString) != 0 ;
196 else if("<=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) <= 0 ;
197 else if(">=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) >= 0 ;
198 else throw new IllegalStateException();
200 Number leftNumber = Spreadsheets.asDoubleWhereEmptyStringIsZero(leftResult);
201 Number rightNumber = Spreadsheets.asDoubleWhereEmptyStringIsZero(rightResult);
202 if(leftNumber==null || rightNumber==null) return false;
203 if("<".equals(astRelation.op.trim())) return leftNumber.doubleValue() < rightNumber.doubleValue();
204 else if(">".equals(astRelation.op.trim())) return leftNumber.doubleValue() > rightNumber.doubleValue();
205 else if("=".equals(astRelation.op.trim())) return leftNumber.doubleValue() == rightNumber.doubleValue();
206 else if("<>".equals(astRelation.op.trim())) return leftNumber.doubleValue() != rightNumber.doubleValue();
207 else if("<=".equals(astRelation.op.trim())) return leftNumber.doubleValue() <= rightNumber.doubleValue();
208 else if(">=".equals(astRelation.op.trim())) return leftNumber.doubleValue() >= rightNumber.doubleValue();
209 else throw new IllegalStateException();
213 Object leftValueWithPrefix(Object result, AstValue value, String prefix, boolean forceNumber) {
215 Object obj = value.accept(this);
216 FormulaError2 err = FormulaError2.forObject(obj);
217 if(err!=null) return err.getString();
219 if("-".equals(prefix)) {
220 result = Spreadsheets.asNumber(obj);
221 return -((Number)result).doubleValue();
223 if(forceNumber) return Spreadsheets.asNumber(obj);
228 return (Number)Double.parseDouble(result.toString());
229 } catch (NumberFormatException e){
235 public Object visit(AstArithmeticExpression exp) {
236 Object result = null;
238 AstValue value = null;
239 Object acceptedValue = null;
241 for(int i=0;i<exp.rightCount();i++) {
242 String op = exp.rightOp(i);
243 value = exp.rightValue(i);
244 acceptedValue = value.accept(this);
246 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);
248 if(!(result instanceof Number)) {
249 FormulaError2 err = FormulaError2.forObject(result);
250 if(err!=null) return err.getString();
252 if(result instanceof String && !((String) result).isEmpty()){
253 Number num = Spreadsheets.asValidNumber(result);
255 return FormulaError2.VALUE.getString();
260 else if(result instanceof Variant){
261 Object val = ((Variant)result).getValue();
262 if(val instanceof String && (val.toString().isEmpty())){
265 Number resNum = Spreadsheets.asDoubleWhereEmptyStringIsZero(val);
266 if(resNum==null) return FormulaError2.VALUE.getString();
267 else result = resNum;
274 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);
275 if(err2!=null) return err2.getString();
277 other = Spreadsheets.asDoubleWhereEmptyStringIsZero(acceptedValue);
279 return FormulaError2.handleErrorCall(acceptedValue);
281 result = ((Number)result).doubleValue() + ((Number)other).doubleValue();
283 } else if("-".equals(op)) {
284 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);
286 if(!(result instanceof Number)) {
287 FormulaError2 err = FormulaError2.forObject(result);
288 if(err!=null) return err.getString();
290 if(result instanceof String && !((String) result).isEmpty()){
291 Number num = Spreadsheets.asValidNumber(result);
293 return FormulaError2.VALUE.getString();
298 else if(result instanceof Variant){
299 Object val = ((Variant)result).getValue();
300 if(val instanceof String && (val.toString().isEmpty())){
303 Number resNum = Spreadsheets.asDoubleWhereEmptyStringIsZero(val);
305 return FormulaError2.VALUE.getString();
306 else result = resNum;
312 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);
313 if(err2!=null) return err2.getString();
315 other = Spreadsheets.asDoubleWhereEmptyStringIsZero(acceptedValue);
317 return FormulaError2.handleErrorCall(acceptedValue);
319 result = ((Number)result).doubleValue() - ((Number)other).doubleValue();
321 } else if("&".equals(op)) {
322 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);
323 FormulaError2 err = FormulaError2.forObject(result);
324 if(err!=null) return err.getString();
325 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);
326 if(err2!=null) return err2.getString();
328 result = Spreadsheets.asString(result);
329 other = Spreadsheets.asString(acceptedValue);
331 result += (String)other;
334 return leftValueWithPrefix(result, exp.left, exp.prefix, false);
339 public Object visit(AstTerm exp) {
340 Number result = null;
341 for(int i=0;i<exp.rightCount();i++) {
342 String op = exp.rightOp(i);
343 AstValue value = exp.rightValue(i);
344 Object leftValue = exp.left.accept(this);
345 Object rightValue = value.accept(this);
348 result = Spreadsheets.asDoubleWhereEmptyStringIsZero(leftValue);
350 return FormulaError2.handleErrorCall(leftValue);
352 Number other = Spreadsheets.asDoubleWhereEmptyStringIsZero(rightValue);
353 if(other==null) return FormulaError2.handleErrorCall(rightValue);
355 result = new Double(result.doubleValue() * other.doubleValue());
357 } else if("/".equals(op)) {
359 result = Spreadsheets.asDoubleWhereEmptyStringIsZero(leftValue);
361 return FormulaError2.handleErrorCall(leftValue);
363 Number other = Spreadsheets.asDoubleWhereEmptyStringIsZero(rightValue);
364 if(other==null) return FormulaError2.handleErrorCall(rightValue);
365 if(other.doubleValue()==0.0) return FormulaError2.DIV0.getString();
367 result = new Double(result.doubleValue() / other.doubleValue());
370 if(result == null) result = Spreadsheets.asNumber(exp.left.accept(this));
375 public Object visit(AstFactor exp) {
376 Object result = null;
377 for(int i=0;i<exp.rightCount();i++) {
378 String op = exp.rightOp(i);
379 AstValue value = exp.rightValue(i);
382 Object leftValue = exp.left.accept(this);
384 FormulaError2 err = FormulaError2.forObject(leftValue);
385 if(err!=null) return err.getString();
387 if(leftValue instanceof Variant){
388 Object leftTemp = ((Variant)leftValue).getValue();
389 Double leftV = Spreadsheets.asDoubleWhereEmptyStringIsZero(leftTemp);
390 if(leftV==null) leftValue = leftTemp.toString();
391 else leftValue = leftV;
394 if(leftValue instanceof String){
395 if((leftValue.toString()).isEmpty())
398 return FormulaError2.VALUE.getString();
400 else if(leftValue instanceof SpreadsheetMatrix)
403 result = Spreadsheets.asNumber(leftValue);
405 Object otherValue = value.accept(this);
407 FormulaError2 err2 = FormulaError2.forObject(otherValue);
408 if(err2!=null) return err2.getString();
410 if(otherValue instanceof Variant){
411 Object otherTemp = ((Variant)otherValue).getValue();
412 Double otherV = Spreadsheets.asDoubleWhereEmptyStringIsZero(otherTemp);
413 if(otherV==null) otherValue = otherTemp.toString();
414 else otherValue = otherV;
417 if(otherValue instanceof String){
418 if((otherValue.toString()).isEmpty())
421 return FormulaError2.VALUE.getString();
424 if(result instanceof SpreadsheetMatrix) {
425 result = ((SpreadsheetMatrix)result).pow(otherValue);
427 if(otherValue instanceof SpreadsheetMatrix) {
428 throw new IllegalStateException();
430 Double base = ((Number)result).doubleValue();
431 Double exponent = Spreadsheets.asNumber(otherValue);
432 if(exponent==0 && base==0)
433 return FormulaError2.NUM.getString();
434 if(exponent<0 && base==0)
435 return FormulaError2.DIV0.getString();
436 result = Math.pow(base, exponent);
437 if(result instanceof Double && Double.isInfinite((Double)result)){
438 return FormulaError2.NUM.getString();
444 if(result == null) result = Spreadsheets.asNumber(exp.left.accept(this));
449 public Object visit(AstIdentifier id) {
450 return FormulaError2.NAME.getString();
451 //throw new IllegalStateException();
455 public Object visit(AstArray array) {
456 SpreadsheetMatrix m = new SpreadsheetMatrix(array.values.size(), 1);
457 for(int i=0;i<array.values.size();i++) {
458 m.values[i] = array.values.get(i).accept(this);
464 public Object visit(AstNothing array) {
465 return AstNothing.NOTHING;
469 public Object visit(AstArrayFormulaReference ref) {
471 Range thisRange = Spreadsheets.decodeRange(thisCell.getName());
472 Range arrayRange = Spreadsheets.decodeRange(ref.range);
473 int x = thisRange.startColumn - arrayRange.startColumn;
474 int y = thisRange.startRow - arrayRange.startRow;
476 SpreadsheetMatrix m = (SpreadsheetMatrix)ref.value.accept(this);