bundles/org.simantics.spreadsheet.graph/src/org/simantics/spreadsheet/graph/CellValueVisitor.java

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