SpreadsheetCells with Circular References support iterations.
[simantics/platform.git] / bundles / org.simantics.spreadsheet.graph / src / org / simantics / spreadsheet / graph / CellValueVisitor.java
1 package org.simantics.spreadsheet.graph;\r
2 \r
3 import org.simantics.databoard.binding.mutable.Variant;\r
4 import org.simantics.spreadsheet.Range;\r
5 import org.simantics.spreadsheet.graph.formula.FormulaError2;\r
6 import org.simantics.spreadsheet.graph.formula.SpreadsheetEvaluationEnvironment;\r
7 import org.simantics.spreadsheet.graph.parser.ast.AstApply;\r
8 import org.simantics.spreadsheet.graph.parser.ast.AstArgList;\r
9 import org.simantics.spreadsheet.graph.parser.ast.AstArithmeticExpression;\r
10 import org.simantics.spreadsheet.graph.parser.ast.AstArray;\r
11 import org.simantics.spreadsheet.graph.parser.ast.AstArrayFormulaReference;\r
12 import org.simantics.spreadsheet.graph.parser.ast.AstBoolean;\r
13 import org.simantics.spreadsheet.graph.parser.ast.AstDouble;\r
14 import org.simantics.spreadsheet.graph.parser.ast.AstFactor;\r
15 import org.simantics.spreadsheet.graph.parser.ast.AstIdentifier;\r
16 import org.simantics.spreadsheet.graph.parser.ast.AstInteger;\r
17 import org.simantics.spreadsheet.graph.parser.ast.AstNothing;\r
18 import org.simantics.spreadsheet.graph.parser.ast.AstNull;\r
19 import org.simantics.spreadsheet.graph.parser.ast.AstRange;\r
20 import org.simantics.spreadsheet.graph.parser.ast.AstRelation;\r
21 import org.simantics.spreadsheet.graph.parser.ast.AstString;\r
22 import org.simantics.spreadsheet.graph.parser.ast.AstTerm;\r
23 import org.simantics.spreadsheet.graph.parser.ast.AstValue;\r
24 import org.simantics.spreadsheet.graph.parser.ast.AstValueVisitor;\r
25 import org.simantics.spreadsheet.util.SpreadsheetUtils;\r
26 \r
27 import it.unimi.dsi.fastutil.longs.AbstractLongList;\r
28 import it.unimi.dsi.fastutil.longs.LongArrayList;\r
29 \r
30 public class CellValueVisitor implements AstValueVisitor<Object> {\r
31 \r
32         final public SpreadsheetBook book;\r
33         final private SpreadsheetEvaluationEnvironment env;\r
34         final private SpreadsheetCell thisCell;\r
35         final private LongArrayList references = new LongArrayList();\r
36         \r
37         public CellValueVisitor(SpreadsheetEvaluationEnvironment env, SpreadsheetCell thisCell) {\r
38                 this.book = env.getBook();\r
39                 this.env = env;\r
40                 this.thisCell = thisCell;\r
41         }\r
42         \r
43         public void addReference(long ref) {\r
44                 references.add(ref);\r
45         }\r
46         \r
47         public AbstractLongList getReferences() {\r
48                 return references;\r
49         }\r
50         \r
51         @Override\r
52         public Object visit(AstBoolean astBoolean) {\r
53                 return astBoolean.value;\r
54         }\r
55 \r
56         @Override\r
57         public Object visit(AstDouble astFloat) {\r
58                 return astFloat.value;\r
59         }\r
60 \r
61         @Override\r
62         public Object visit(AstInteger astInteger) {\r
63                 return astInteger.value;\r
64         }\r
65 \r
66         @Override\r
67         public Object visit(AstNull astNull) {\r
68                 throw new IllegalStateException();\r
69         }\r
70 \r
71         @Override\r
72         public Object visit(AstString astString) {\r
73                 return astString.value;\r
74         }\r
75 \r
76         @Override\r
77         public Object visit(AstRange astRange) {\r
78                 \r
79                 if(astRange.isRef()){\r
80                         return FormulaError2.REF.getString();\r
81                 }\r
82                 \r
83                 if(astRange.isCell()) {\r
84                         String ref = astRange.first;\r
85                         Range r = SpreadsheetUtils.decodeCell(ref, 0, 0);\r
86                         String sheetName = astRange.sheetName != null ? astRange.sheetName : thisCell.getEngine().getName();\r
87                         SpreadsheetCell cell = thisCell.getBook().get(sheetName, r.startRow, r.startColumn);\r
88                         if(cell == null) {\r
89                             SpreadsheetEngine eng = thisCell.getBook().getEngine(sheetName);\r
90                             SpreadsheetLine line = eng.getLine(r.startRow);\r
91                             \r
92                             if (line == null) {\r
93                                 line = new SpreadsheetLine(eng.lines, r.startRow);\r
94                                 eng.lines.lines.put(-r.startRow, line);\r
95                             }\r
96                             cell = SpreadsheetCell.empty(line, r.startColumn);\r
97                         }\r
98                         return cell.evaluate(env, this);\r
99                         \r
100                 } else {\r
101                         \r
102                         Object cached = thisCell.getEngine().getCachedRange(astRange);\r
103                         if(cached != null) {\r
104                                 \r
105                                 Range r_ = SpreadsheetUtils.decodeRange(astRange.first + ":" + astRange.second);\r
106                                 String sheetName = astRange.sheetName != null ? astRange.sheetName : thisCell.getEngine().getName();\r
107                                 SpreadsheetEngine eng = thisCell.getBook().getEngine(sheetName);\r
108                                 Range r = eng.actualRange(r_);\r
109                                 for(int row=0; row < r.height();row++) {\r
110                                         SpreadsheetLine line = eng.getLine(r.startRow + row);\r
111                                         if(line != null) {\r
112                                                 for(int column=0; column < r.width();column++) {\r
113                                                         int col = r.startColumn + column;\r
114                                                         if(line.cells.size() > col) {\r
115                                                                 SpreadsheetCell cell = line.cells.get(r.startColumn + column);\r
116                                                                 //Add references, but do not evaluate if there exists a cached range.\r
117                                                                 addReference(cell.makeReferenceKey());\r
118                                                         }\r
119                                                 }\r
120                                         }\r
121                                 }       \r
122                                 return cached;\r
123                         }\r
124                         \r
125                         Range r_ = SpreadsheetUtils.decodeRange(astRange.first + ":" + astRange.second);\r
126                         String sheetName = astRange.sheetName != null ? astRange.sheetName : thisCell.getEngine().getName();\r
127                         SpreadsheetEngine eng = thisCell.getBook().getEngine(sheetName);\r
128                         // Full ranges are resolved here\r
129                         Range r = eng.actualRange(r_);\r
130                         SpreadsheetMatrix result = new SpreadsheetMatrix(r.width(), r.height());\r
131                         for(int row=0; row < r.height();row++) {\r
132                                 SpreadsheetLine line = eng.getLine(r.startRow + row);\r
133                                 if(line != null) {\r
134                                         for(int column=0; column < r.width();column++) {\r
135                                                 int col = r.startColumn + column;\r
136                                                 if(line.cells.size() > col) {\r
137                                                         SpreadsheetCell cell = line.cells.get(r.startColumn + column);\r
138                                                         result.values[r.width()*row + column] = cell.evaluate(env, this);\r
139                                                 }\r
140                                         }\r
141                                 }\r
142                         }\r
143                         thisCell.getEngine().cacheRange(astRange, result);\r
144                         return result;\r
145                 }\r
146         }\r
147 \r
148         @Override\r
149         public Object visit(AstArgList astArgList) {\r
150                 throw new IllegalStateException();\r
151         }\r
152 \r
153         @Override\r
154         public Object visit(AstApply astApply) {\r
155                 CellFormulaFunction<?> fn = env.getFunction(astApply.value);\r
156                 if(fn != null) {\r
157                         return fn.evaluate(this, astApply.args);\r
158                 } else {\r
159                         return FormulaError2.NAME.getString();\r
160                 }\r
161         }\r
162 \r
163         @Override\r
164         public Object visit(AstRelation astRelation) {\r
165 \r
166                 Object leftResult = astRelation.left.accept(this);\r
167                 Object rightResult = astRelation.right.accept(this);\r
168                 \r
169                 FormulaError2 err = FormulaError2.forObject(leftResult);\r
170                 if(err!=null) return err.getString();\r
171                 FormulaError2 err2 = FormulaError2.forObject(rightResult);\r
172                 if(err2!=null) return err2.getString();\r
173                 \r
174                 if(leftResult instanceof Variant){\r
175                         Object leftTemp = ((Variant)leftResult).getValue();\r
176                         Double leftVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(leftTemp);\r
177                         if(leftVal==null) leftResult = leftTemp.toString();\r
178                         else leftResult = leftVal;\r
179                 }\r
180                 if(rightResult instanceof Variant){\r
181                         Object rightTemp = ((Variant)rightResult).getValue();\r
182                         Double rightVal = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(rightTemp);\r
183                         if(rightVal==null) rightResult = rightTemp.toString();\r
184                         else rightResult = rightVal;\r
185                 }\r
186                 \r
187                 if ((leftResult instanceof String) || (rightResult instanceof String)) {\r
188                         String leftString = (leftResult.toString()).toLowerCase();\r
189                         String rightString = (rightResult.toString()).toLowerCase();\r
190                         if("<".equals(astRelation.op.trim())) return leftString.compareTo(rightString) < 0;\r
191                         else if(">".equals(astRelation.op.trim())) return leftString.compareTo(rightString) > 0;\r
192                         else if("=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) == 0;\r
193                         else if("<>".equals(astRelation.op.trim())) return leftString.compareTo(rightString) != 0 ;\r
194                         else if("<=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) <= 0 ;\r
195                         else if(">=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) >= 0 ;\r
196                         else throw new IllegalStateException();\r
197                 } else {\r
198                         Number leftNumber = SpreadsheetGraphUtils.asNumber(leftResult);\r
199                         Number rightNumber = SpreadsheetGraphUtils.asNumber(rightResult);\r
200                         if("<".equals(astRelation.op.trim())) return leftNumber.doubleValue() < rightNumber.doubleValue();\r
201                         else if(">".equals(astRelation.op.trim())) return leftNumber.doubleValue() > rightNumber.doubleValue();\r
202                         else if("=".equals(astRelation.op.trim())) return leftNumber.doubleValue() == rightNumber.doubleValue();\r
203                         else if("<>".equals(astRelation.op.trim())) return leftNumber.doubleValue() != rightNumber.doubleValue();\r
204                         else if("<=".equals(astRelation.op.trim())) return leftNumber.doubleValue() <= rightNumber.doubleValue();\r
205                         else if(">=".equals(astRelation.op.trim())) return leftNumber.doubleValue() >= rightNumber.doubleValue();\r
206                         else throw new IllegalStateException();\r
207                 }\r
208         }\r
209         \r
210         Object leftValueWithPrefix(Object result, AstValue value, String prefix, boolean forceNumber) {\r
211                 if(result == null) {\r
212                         Object obj = value.accept(this);\r
213                         FormulaError2 err = FormulaError2.forObject(obj);\r
214                         if(err!=null) return err.getString();\r
215                         \r
216                         if("-".equals(prefix)) {\r
217                                 result = SpreadsheetGraphUtils.asNumber(obj);\r
218                                 return -((Number)result).doubleValue();\r
219                         } else {\r
220                                 if(forceNumber) return SpreadsheetGraphUtils.asNumber(obj);\r
221                                 else return obj;\r
222                         }\r
223                 }\r
224                 try{\r
225                         return (Number)Double.parseDouble(result.toString());\r
226                 } catch (NumberFormatException e){\r
227                         return result;\r
228                 }\r
229         }\r
230 \r
231         @Override\r
232         public Object visit(AstArithmeticExpression exp) {\r
233                 Object result = null;\r
234                 Object other = null;\r
235                 AstValue value = null;\r
236                 Object acceptedValue = null;\r
237                 \r
238                 for(int i=0;i<exp.rightCount();i++) {\r
239                         String op = exp.rightOp(i);\r
240                         value = exp.rightValue(i);\r
241                         acceptedValue = value.accept(this);\r
242                         if("+".equals(op)) {\r
243                                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
244                                 \r
245                                 if(!(result instanceof Number)) {\r
246                                         FormulaError2 err = FormulaError2.forObject(result);\r
247                                         if(err!=null) return err.getString();\r
248                                         \r
249                                         if(result instanceof String && !((String) result).isEmpty()){\r
250                                                 return FormulaError2.VALUE.getString();\r
251                                         } \r
252                                         else if(result instanceof Variant){\r
253                                                 Object val = ((Variant)result).getValue();\r
254                                                 if(val instanceof String && (val.toString().isEmpty())){\r
255                                                         result = 0.0;\r
256                                                 } else {\r
257                                                         Number resNum = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(val);\r
258                                                         if(resNum==null) return FormulaError2.VALUE.getString();\r
259                                                         else result = resNum;\r
260                                                 }\r
261                                         } else {\r
262                                                 result = 0.0;\r
263                                         }\r
264                                 }\r
265                                 \r
266                                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);\r
267                                 if(err2!=null) return err2.getString();\r
268                                 \r
269                                 other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(acceptedValue);\r
270                                 if(other==null)\r
271                                         return FormulaError2.handleErrorCall(acceptedValue);\r
272                                 \r
273                                 result = ((Number)result).doubleValue() + ((Number)other).doubleValue();\r
274                                 \r
275                         } else if("-".equals(op)) {\r
276                                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
277                                 \r
278                                 if(!(result instanceof Number)) {\r
279                                         FormulaError2 err = FormulaError2.forObject(result);\r
280                                         if(err!=null) return err.getString();\r
281                                         \r
282                                         if(result instanceof String && !((String) result).isEmpty()){\r
283                                                 return FormulaError2.VALUE.getString();\r
284                                         } \r
285                                         else if(result instanceof Variant){\r
286                                                 Object val = ((Variant)result).getValue();\r
287                                                 if(val instanceof String && (val.toString().isEmpty())){\r
288                                                         result = 0.0;\r
289                                                 } else {\r
290                                                         Number resNum = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(val);\r
291                                                         if(resNum==null)\r
292                                                                 return FormulaError2.VALUE.getString();\r
293                                                         else result = resNum;\r
294                                                 }\r
295                                         } else {\r
296                                                 result = 0.0;\r
297                                         }\r
298                                 }\r
299                                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);\r
300                                 if(err2!=null) return err2.getString();\r
301                                 \r
302                                 other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(acceptedValue);\r
303                                 if(other==null)\r
304                                         return FormulaError2.handleErrorCall(acceptedValue);\r
305                                 \r
306                                 result = ((Number)result).doubleValue() - ((Number)other).doubleValue();\r
307                                 \r
308                         } else if("&".equals(op)) {\r
309                                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
310                                 FormulaError2 err = FormulaError2.forObject(result);\r
311                                 if(err!=null) return err.getString();\r
312                                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);\r
313                                 if(err2!=null) return err2.getString();\r
314                                 \r
315                                 result = SpreadsheetGraphUtils.asString(result);\r
316                                 other = SpreadsheetGraphUtils.asString(acceptedValue);\r
317 \r
318                                 result += (String)other;\r
319                         }\r
320                 }\r
321                 return leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
322                 \r
323         }\r
324 \r
325         @Override\r
326         public Object visit(AstTerm exp) {\r
327                 Number result = null;\r
328                 for(int i=0;i<exp.rightCount();i++) {\r
329                         String op = exp.rightOp(i);\r
330                         AstValue value = exp.rightValue(i);\r
331                         Object leftValue = exp.left.accept(this);\r
332                         Object rightValue = value.accept(this);\r
333                         if("*".equals(op)) {\r
334                                 if(result == null) {\r
335                                         result = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(leftValue);\r
336                                         if(result == null)\r
337                                                 return FormulaError2.handleErrorCall(leftValue);\r
338                                 }\r
339                                 Number other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(rightValue);\r
340                                 if(other==null) return FormulaError2.handleErrorCall(rightValue);\r
341                                 \r
342                                 result = new Double(result.doubleValue() * other.doubleValue());\r
343                                 \r
344                         } else if("/".equals(op)) {\r
345                                 if(result == null) {\r
346                                         result = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(leftValue);\r
347                                         if(result == null)\r
348                                                 return FormulaError2.handleErrorCall(leftValue);\r
349                                 }\r
350                                 Number other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(rightValue);\r
351                                 if(other==null) return FormulaError2.handleErrorCall(rightValue);\r
352                                 if(other.doubleValue()==0.0) return FormulaError2.DIV0.getString();\r
353                                 \r
354                                 result = new Double(result.doubleValue() / other.doubleValue());        \r
355                         }\r
356                 }\r
357                 if(result == null) result = SpreadsheetGraphUtils.asNumber(exp.left.accept(this));\r
358                 return result;\r
359         }\r
360 \r
361         @Override\r
362         public Object visit(AstFactor exp) {\r
363                 Object result = null;\r
364                 for(int i=0;i<exp.rightCount();i++) {\r
365                         String op = exp.rightOp(i);\r
366                         AstValue value = exp.rightValue(i);\r
367                         if("^".equals(op)) {\r
368                                 if(result == null) {\r
369                                         Object leftValue = exp.left.accept(this);\r
370 \r
371                                         FormulaError2 err = FormulaError2.forObject(leftValue);\r
372                                         if(err!=null) return err.getString();\r
373                                         \r
374                                         if(leftValue instanceof Variant){\r
375                                                 Object leftTemp = ((Variant)leftValue).getValue();\r
376                                                 Double leftV = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(leftTemp);\r
377                                                 if(leftV==null) leftValue = leftTemp.toString();\r
378                                                 else leftValue = leftV;\r
379                                         }\r
380                                         \r
381                                         if(leftValue instanceof String){\r
382                                                 if((leftValue.toString()).isEmpty())\r
383                                                         result = 0;\r
384                                                  else \r
385                                                         return FormulaError2.VALUE.getString();\r
386                                         }\r
387                                         else if(leftValue instanceof SpreadsheetMatrix) \r
388                                                 result = leftValue;\r
389                                         else \r
390                                                 result = SpreadsheetGraphUtils.asNumber(leftValue);\r
391                                 }\r
392                                 Object otherValue = value.accept(this);\r
393                                 \r
394                                 FormulaError2 err2 = FormulaError2.forObject(otherValue);\r
395                                 if(err2!=null) return err2.getString();\r
396                                 \r
397                                 if(otherValue instanceof Variant){\r
398                                         Object otherTemp = ((Variant)otherValue).getValue();\r
399                                         Double otherV = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(otherTemp);\r
400                                         if(otherV==null) otherValue = otherTemp.toString();\r
401                                         else otherValue = otherV;\r
402                                 }\r
403                                 \r
404                                 if(otherValue instanceof String){\r
405                                         if((otherValue.toString()).isEmpty())\r
406                                                 otherValue = 0;\r
407                                         else \r
408                                                 return FormulaError2.VALUE.getString();\r
409                                 }\r
410                                 \r
411                                 if(result instanceof SpreadsheetMatrix) {\r
412                                         result = ((SpreadsheetMatrix)result).pow(otherValue);\r
413                                 } else {\r
414                                         if(otherValue instanceof SpreadsheetMatrix) {\r
415                                                 throw new IllegalStateException();\r
416                                         } else {\r
417                                                 Double base = ((Number)result).doubleValue();\r
418                                                 Double exponent = SpreadsheetGraphUtils.asNumber(otherValue);\r
419                                                 if(exponent==0 && base==0)\r
420                                                         return FormulaError2.NUM.getString();\r
421                                                 if(exponent<0 && base==0)\r
422                                                         return FormulaError2.DIV0.getString();\r
423                                                 result = Math.pow(base, exponent);\r
424                                                 if(result instanceof Double && Double.isInfinite((Double)result)){\r
425                                                         return FormulaError2.NUM.getString();\r
426                                                 }\r
427                                         }\r
428                                 }\r
429                         }\r
430                 }\r
431                 if(result == null) result = SpreadsheetGraphUtils.asNumber(exp.left.accept(this));\r
432                 return result;\r
433         }\r
434 \r
435         @Override\r
436         public Object visit(AstIdentifier id) {\r
437                 return FormulaError2.NAME.getString();\r
438                 //throw new IllegalStateException();\r
439         }\r
440         \r
441         @Override\r
442         public Object visit(AstArray array) {\r
443                 SpreadsheetMatrix m = new SpreadsheetMatrix(array.values.size(), 1);\r
444                 for(int i=0;i<array.values.size();i++) {\r
445                         m.values[i] = array.values.get(i).accept(this);\r
446                 }\r
447                 return m; \r
448         }\r
449         \r
450         @Override\r
451         public Object visit(AstNothing array) {\r
452                 return AstNothing.NOTHING;\r
453         }\r
454         \r
455         @Override\r
456         public Object visit(AstArrayFormulaReference ref) {\r
457                 \r
458                 Range thisRange = SpreadsheetUtils.decodeRange(thisCell.getName());\r
459                 Range arrayRange = SpreadsheetUtils.decodeRange(ref.range);\r
460                 int x = thisRange.startColumn - arrayRange.startColumn;\r
461                 int y = thisRange.startRow - arrayRange.startRow;\r
462                 \r
463                 SpreadsheetMatrix m = (SpreadsheetMatrix)ref.value.accept(this);\r
464                 return m.get(y, x);\r
465                 \r
466         }\r
467 \r
468 }\r