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                         // empty string should equal zero (TODO: this is a hack, the proper fix should be somewhere earlier so other cases would work as well)\r
 193                         else if("=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) == 0 || emptyAndZero(leftString, rightString) || emptyAndZero(rightString, leftString);\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 if(">=".equals(astRelation.op.trim())) return leftString.compareTo(rightString) >= 0 ;\r
 197                         else throw new IllegalStateException();\r
 198                 } else {\r
 199                         Number leftNumber = SpreadsheetGraphUtils.asNumber(leftResult);\r
 200                         Number rightNumber = SpreadsheetGraphUtils.asNumber(rightResult);\r
 201                         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 if(">=".equals(astRelation.op.trim())) return leftNumber.doubleValue() >= rightNumber.doubleValue();\r
 207                         else throw new IllegalStateException();\r
 208                 }\r
 209         }\r
 210         \r
 211         private static boolean emptyAndZero(String a, String b) {\r
 212             try {\r
 213                 return a.isEmpty() && (Double.parseDouble(b) == 0);\r
 214             }\r
 215             catch (NumberFormatException e) {\r
 216                 return false;\r
 217             }\r
 218         }\r
 219         \r
 220         Object leftValueWithPrefix(Object result, AstValue value, String prefix, boolean forceNumber) {\r
 221                 if(result == null) {\r
 222                         Object obj = value.accept(this);\r
 223                         FormulaError2 err = FormulaError2.forObject(obj);\r
 224                         if(err!=null) return err.getString();\r
 225                         \r
 226                         if("-".equals(prefix)) {\r
 227                                 result = SpreadsheetGraphUtils.asNumber(obj);\r
 228                                 return -((Number)result).doubleValue();\r
 229                         } else {\r
 230                                 if(forceNumber) return SpreadsheetGraphUtils.asNumber(obj);\r
 231                                 else return obj;\r
 232                         }\r
 233                 }\r
 234                 try{\r
 235                         return (Number)Double.parseDouble(result.toString());\r
 236                 } catch (NumberFormatException e){\r
 237                         return result;\r
 238                 }\r
 239         }\r
 240 \r
 241         @Override\r
 242         public Object visit(AstArithmeticExpression exp) {\r
 243                 Object result = null;\r
 244                 Object other = null;\r
 245                 AstValue value = null;\r
 246                 Object acceptedValue = null;\r
 247                 \r
 248                 for(int i=0;i<exp.rightCount();i++) {\r
 249                         String op = exp.rightOp(i);\r
 250                         value = exp.rightValue(i);\r
 251                         acceptedValue = value.accept(this);\r
 252                         if("+".equals(op)) {\r
 253                                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
 254                                 \r
 255                                 if(!(result instanceof Number)) {\r
 256                                         FormulaError2 err = FormulaError2.forObject(result);\r
 257                                         if(err!=null) return err.getString();\r
 258                                         \r
 259                                         if(result instanceof String && !((String) result).isEmpty()){\r
 260                                                 return FormulaError2.VALUE.getString();\r
 261                                         } \r
 262                                         else if(result instanceof Variant){\r
 263                                                 Object val = ((Variant)result).getValue();\r
 264                                                 if(val instanceof String && (val.toString().isEmpty())){\r
 265                                                         result = 0.0;\r
 266                                                 } else {\r
 267                                                         Number resNum = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(val);\r
 268                                                         if(resNum==null) return FormulaError2.VALUE.getString();\r
 269                                                         else result = resNum;\r
 270                                                 }\r
 271                                         } else {\r
 272                                                 result = 0.0;\r
 273                                         }\r
 274                                 }\r
 275                                 \r
 276                                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);\r
 277                                 if(err2!=null) return err2.getString();\r
 278                                 \r
 279                                 other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(acceptedValue);\r
 280                                 if(other==null)\r
 281                                         return FormulaError2.handleErrorCall(acceptedValue);\r
 282                                 \r
 283                                 result = ((Number)result).doubleValue() + ((Number)other).doubleValue();\r
 284                                 \r
 285                         } else if("-".equals(op)) {\r
 286                                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
 287                                 \r
 288                                 if(!(result instanceof Number)) {\r
 289                                         FormulaError2 err = FormulaError2.forObject(result);\r
 290                                         if(err!=null) return err.getString();\r
 291                                         \r
 292                                         if(result instanceof String && !((String) result).isEmpty()){\r
 293                                                 return FormulaError2.VALUE.getString();\r
 294                                         } \r
 295                                         else if(result instanceof Variant){\r
 296                                                 Object val = ((Variant)result).getValue();\r
 297                                                 if(val instanceof String && (val.toString().isEmpty())){\r
 298                                                         result = 0.0;\r
 299                                                 } else {\r
 300                                                         Number resNum = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(val);\r
 301                                                         if(resNum==null)\r
 302                                                                 return FormulaError2.VALUE.getString();\r
 303                                                         else result = resNum;\r
 304                                                 }\r
 305                                         } else {\r
 306                                                 result = 0.0;\r
 307                                         }\r
 308                                 }\r
 309                                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);\r
 310                                 if(err2!=null) return err2.getString();\r
 311                                 \r
 312                                 other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(acceptedValue);\r
 313                                 if(other==null)\r
 314                                         return FormulaError2.handleErrorCall(acceptedValue);\r
 315                                 \r
 316                                 result = ((Number)result).doubleValue() - ((Number)other).doubleValue();\r
 317                                 \r
 318                         } else if("&".equals(op)) {\r
 319                                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
 320                                 FormulaError2 err = FormulaError2.forObject(result);\r
 321                                 if(err!=null) return err.getString();\r
 322                                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);\r
 323                                 if(err2!=null) return err2.getString();\r
 324                                 \r
 325                                 result = SpreadsheetGraphUtils.asString(result);\r
 326                                 other = SpreadsheetGraphUtils.asString(acceptedValue);\r
 327 \r
 328                                 result += (String)other;\r
 329                         }\r
 330                 }\r
 331                 return leftValueWithPrefix(result, exp.left, exp.prefix, false);\r
 332                 \r
 333         }\r
 334 \r
 335         @Override\r
 336         public Object visit(AstTerm exp) {\r
 337                 Number result = null;\r
 338                 for(int i=0;i<exp.rightCount();i++) {\r
 339                         String op = exp.rightOp(i);\r
 340                         AstValue value = exp.rightValue(i);\r
 341                         Object leftValue = exp.left.accept(this);\r
 342                         Object rightValue = value.accept(this);\r
 343                         if("*".equals(op)) {\r
 344                                 if(result == null) {\r
 345                                         result = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(leftValue);\r
 346                                         if(result == null)\r
 347                                                 return FormulaError2.handleErrorCall(leftValue);\r
 348                                 }\r
 349                                 Number other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(rightValue);\r
 350                                 if(other==null) return FormulaError2.handleErrorCall(rightValue);\r
 351                                 \r
 352                                 result = new Double(result.doubleValue() * other.doubleValue());\r
 353                                 \r
 354                         } else if("/".equals(op)) {\r
 355                                 if(result == null) {\r
 356                                         result = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(leftValue);\r
 357                                         if(result == null)\r
 358                                                 return FormulaError2.handleErrorCall(leftValue);\r
 359                                 }\r
 360                                 Number other = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(rightValue);\r
 361                                 if(other==null) return FormulaError2.handleErrorCall(rightValue);\r
 362                                 if(other.doubleValue()==0.0) return FormulaError2.DIV0.getString();\r
 363                                 \r
 364                                 result = new Double(result.doubleValue() / other.doubleValue());        \r
 365                         }\r
 366                 }\r
 367                 if(result == null) result = SpreadsheetGraphUtils.asNumber(exp.left.accept(this));\r
 368                 return result;\r
 369         }\r
 370 \r
 371         @Override\r
 372         public Object visit(AstFactor exp) {\r
 373                 Object result = null;\r
 374                 for(int i=0;i<exp.rightCount();i++) {\r
 375                         String op = exp.rightOp(i);\r
 376                         AstValue value = exp.rightValue(i);\r
 377                         if("^".equals(op)) {\r
 378                                 if(result == null) {\r
 379                                         Object leftValue = exp.left.accept(this);\r
 380 \r
 381                                         FormulaError2 err = FormulaError2.forObject(leftValue);\r
 382                                         if(err!=null) return err.getString();\r
 383                                         \r
 384                                         if(leftValue instanceof Variant){\r
 385                                                 Object leftTemp = ((Variant)leftValue).getValue();\r
 386                                                 Double leftV = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(leftTemp);\r
 387                                                 if(leftV==null) leftValue = leftTemp.toString();\r
 388                                                 else leftValue = leftV;\r
 389                                         }\r
 390                                         \r
 391                                         if(leftValue instanceof String){\r
 392                                                 if((leftValue.toString()).isEmpty())\r
 393                                                         result = 0;\r
 394                                                  else \r
 395                                                         return FormulaError2.VALUE.getString();\r
 396                                         }\r
 397                                         else if(leftValue instanceof SpreadsheetMatrix) \r
 398                                                 result = leftValue;\r
 399                                         else \r
 400                                                 result = SpreadsheetGraphUtils.asNumber(leftValue);\r
 401                                 }\r
 402                                 Object otherValue = value.accept(this);\r
 403                                 \r
 404                                 FormulaError2 err2 = FormulaError2.forObject(otherValue);\r
 405                                 if(err2!=null) return err2.getString();\r
 406                                 \r
 407                                 if(otherValue instanceof Variant){\r
 408                                         Object otherTemp = ((Variant)otherValue).getValue();\r
 409                                         Double otherV = SpreadsheetGraphUtils.asDoubleWhereEmptyStringIsZero(otherTemp);\r
 410                                         if(otherV==null) otherValue = otherTemp.toString();\r
 411                                         else otherValue = otherV;\r
 412                                 }\r
 413                                 \r
 414                                 if(otherValue instanceof String){\r
 415                                         if((otherValue.toString()).isEmpty())\r
 416                                                 otherValue = 0;\r
 417                                         else \r
 418                                                 return FormulaError2.VALUE.getString();\r
 419                                 }\r
 420                                 \r
 421                                 if(result instanceof SpreadsheetMatrix) {\r
 422                                         result = ((SpreadsheetMatrix)result).pow(otherValue);\r
 423                                 } else {\r
 424                                         if(otherValue instanceof SpreadsheetMatrix) {\r
 425                                                 throw new IllegalStateException();\r
 426                                         } else {\r
 427                                                 Double base = ((Number)result).doubleValue();\r
 428                                                 Double exponent = SpreadsheetGraphUtils.asNumber(otherValue);\r
 429                                                 if(exponent==0 && base==0)\r
 430                                                         return FormulaError2.NUM.getString();\r
 431                                                 if(exponent<0 && base==0)\r
 432                                                         return FormulaError2.DIV0.getString();\r
 433                                                 result = Math.pow(base, exponent);\r
 434                                                 if(result instanceof Double && Double.isInfinite((Double)result)){\r
 435                                                         return FormulaError2.NUM.getString();\r
 436                                                 }\r
 437                                         }\r
 438                                 }\r
 439                         }\r
 440                 }\r
 441                 if(result == null) result = SpreadsheetGraphUtils.asNumber(exp.left.accept(this));\r
 442                 return result;\r
 443         }\r
 444 \r
 445         @Override\r
 446         public Object visit(AstIdentifier id) {\r
 447                 return FormulaError2.NAME.getString();\r
 448                 //throw new IllegalStateException();\r
 449         }\r
 450         \r
 451         @Override\r
 452         public Object visit(AstArray array) {\r
 453                 SpreadsheetMatrix m = new SpreadsheetMatrix(array.values.size(), 1);\r
 454                 for(int i=0;i<array.values.size();i++) {\r
 455                         m.values[i] = array.values.get(i).accept(this);\r
 456                 }\r
 457                 return m; \r
 458         }\r
 459         \r
 460         @Override\r
 461         public Object visit(AstNothing array) {\r
 462                 return AstNothing.NOTHING;\r
 463         }\r
 464         \r
 465         @Override\r
 466         public Object visit(AstArrayFormulaReference ref) {\r
 467                 \r
 468                 Range thisRange = SpreadsheetUtils.decodeRange(thisCell.getName());\r
 469                 Range arrayRange = SpreadsheetUtils.decodeRange(ref.range);\r
 470                 int x = thisRange.startColumn - arrayRange.startColumn;\r
 471                 int y = thisRange.startRow - arrayRange.startRow;\r
 472                 \r
 473                 SpreadsheetMatrix m = (SpreadsheetMatrix)ref.value.accept(this);\r
 474                 return m.get(y, x);\r
 475                 \r
 476         }\r
 477 \r
 478 }\r