bundles/org.simantics.spreadsheet/src/org/simantics/spreadsheet/solver/formula/CellValueVisitor.java

   1 package org.simantics.spreadsheet.solver.formula;
   2
   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;
  29
  30 import it.unimi.dsi.fastutil.longs.AbstractLongList;
  31 import it.unimi.dsi.fastutil.longs.LongArrayList;
  32
  33 public class CellValueVisitor implements AstValueVisitor<Object> {
  34
  35     final public SpreadsheetBook book;
  36     final private SpreadsheetEvaluationEnvironment env;
  37     final private SpreadsheetCell thisCell;
  38     final private LongArrayList references = new LongArrayList();
  39
  40     public CellValueVisitor(SpreadsheetEvaluationEnvironment env, SpreadsheetCell thisCell) {
  41         this.book = env.getBook();
  42         this.env = env;
  43         this.thisCell = thisCell;
  44     }
  45
  46     public void addReference(long ref) {
  47         references.add(ref);
  48     }
  49
  50     public AbstractLongList getReferences() {
  51         return references;
  52     }
  53
  54     @Override
  55     public Object visit(AstBoolean astBoolean) {
  56         return astBoolean.value;
  57     }
  58
  59     @Override
  60     public Object visit(AstDouble astFloat) {
  61         return astFloat.value;
  62     }
  63
  64     @Override
  65     public Object visit(AstInteger astInteger) {
  66         return astInteger.value;
  67     }
  68
  69     @Override
  70     public Object visit(AstNull astNull) {
  71         throw new IllegalStateException();
  72     }
  73
  74     @Override
  75     public Object visit(AstString astString) {
  76         return astString.value;
  77     }
  78
  79     @Override
  80     public Object visit(AstRange astRange) {
  81
  82         if(astRange.isRef()){
  83             return FormulaError2.REF.getString();
  84         }
  85
  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);
  91             if(cell == null) {
  92                 SpreadsheetEngine eng = thisCell.getBook().getEngine(sheetName);
  93                 SpreadsheetLine line = eng.getLine(r.startRow);
  94
  95                 if (line == null) {
  96                     line = new SpreadsheetLine(eng.lines, r.startRow);
  97                     eng.lines.lines.put(-r.startRow, line);
  98                 }
  99                 cell = SpreadsheetCell.empty(line, r.startColumn);
 100             }
 101             return cell.evaluate(env, this);
 102
 103         } else {
 104
 105             Object cached = thisCell.getEngine().getCachedRange(astRange);
 106             if(cached != null) {
 107
 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);
 114                     if(line != null) {
 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());
 121                             }
 122                         }
 123                     }
 124                 }
 125                 return cached;
 126             }
 127
 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);
 136                 if(line != null) {
 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);
 142                         }
 143                     }
 144                 }
 145             }
 146             thisCell.getEngine().cacheRange(astRange, result);
 147             return result;
 148         }
 149     }
 150
 151     @Override
 152     public Object visit(AstArgList astArgList) {
 153         throw new IllegalStateException();
 154     }
 155
 156     @Override
 157     public Object visit(AstApply astApply) {
 158         CellFormulaFunction<?> fn = env.getFunction(astApply.value);
 159         if(fn != null) {
 160             return fn.evaluate(this, astApply.args);
 161         } else {
 162             return FormulaError2.NAME.getString();
 163         }
 164     }
 165
 166     @Override
 167     public Object visit(AstRelation astRelation) {
 168
 169         Object leftResult = astRelation.left.accept(this);
 170         Object rightResult = astRelation.right.accept(this);
 171
 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();
 176
 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;
 182         }
 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;
 188         }
 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();
 199         } else {
 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();
 210         }
 211     }
 212
 213     Object leftValueWithPrefix(Object result, AstValue value, String prefix, boolean forceNumber) {
 214         if(result == null) {
 215             Object obj = value.accept(this);
 216             FormulaError2 err = FormulaError2.forObject(obj);
 217             if(err!=null) return err.getString();
 218
 219             if("-".equals(prefix)) {
 220                 result = Spreadsheets.asNumber(obj);
 221                 return -((Number)result).doubleValue();
 222             } else {
 223                 if(forceNumber) return Spreadsheets.asNumber(obj);
 224                 else return obj;
 225             }
 226         }
 227         try{
 228             return (Number)Double.parseDouble(result.toString());
 229         } catch (NumberFormatException e){
 230             return result;
 231         }
 232     }
 233
 234     @Override
 235     public Object visit(AstArithmeticExpression exp) {
 236         Object result = null;
 237         Object other = null;
 238         AstValue value = null;
 239         Object acceptedValue = null;
 240
 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);
 245             if("+".equals(op)) {
 246                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);
 247
 248                 if(!(result instanceof Number)) {
 249                     FormulaError2 err = FormulaError2.forObject(result);
 250                     if(err!=null) return err.getString();
 251
 252                     if(result instanceof String && !((String) result).isEmpty()){
 253                         Number num = Spreadsheets.asValidNumber(result);
 254                         if(num == null) {
 255                             return FormulaError2.VALUE.getString();
 256                         } else {
 257                             result = num;
 258                         }
 259                     }
 260                     else if(result instanceof Variant){
 261                         Object val = ((Variant)result).getValue();
 262                         if(val instanceof String && (val.toString().isEmpty())){
 263                             result = 0.0;
 264                         } else {
 265                             Number resNum = Spreadsheets.asDoubleWhereEmptyStringIsZero(val);
 266                             if(resNum==null) return FormulaError2.VALUE.getString();
 267                             else result = resNum;
 268                         }
 269                     } else {
 270                         result = 0.0;
 271                     }
 272                 }
 273
 274                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);
 275                 if(err2!=null) return err2.getString();
 276
 277                 other = Spreadsheets.asDoubleWhereEmptyStringIsZero(acceptedValue);
 278                 if(other==null)
 279                     return FormulaError2.handleErrorCall(acceptedValue);
 280
 281                 result = ((Number)result).doubleValue() + ((Number)other).doubleValue();
 282
 283             } else if("-".equals(op)) {
 284                 result = leftValueWithPrefix(result, exp.left, exp.prefix, false);
 285
 286                 if(!(result instanceof Number)) {
 287                     FormulaError2 err = FormulaError2.forObject(result);
 288                     if(err!=null) return err.getString();
 289
 290                     if(result instanceof String && !((String) result).isEmpty()){
 291                         Number num = Spreadsheets.asValidNumber(result);
 292                         if(num == null) {
 293                             return FormulaError2.VALUE.getString();
 294                         } else {
 295                             result = num;
 296                         }
 297                     }
 298                     else if(result instanceof Variant){
 299                         Object val = ((Variant)result).getValue();
 300                         if(val instanceof String && (val.toString().isEmpty())){
 301                             result = 0.0;
 302                         } else {
 303                             Number resNum = Spreadsheets.asDoubleWhereEmptyStringIsZero(val);
 304                             if(resNum==null)
 305                                 return FormulaError2.VALUE.getString();
 306                             else result = resNum;
 307                         }
 308                     } else {
 309                         result = 0.0;
 310                     }
 311                 }
 312                 FormulaError2 err2 = FormulaError2.forObject(acceptedValue);
 313                 if(err2!=null) return err2.getString();
 314
 315                 other = Spreadsheets.asDoubleWhereEmptyStringIsZero(acceptedValue);
 316                 if(other==null)
 317                     return FormulaError2.handleErrorCall(acceptedValue);
 318
 319                 result = ((Number)result).doubleValue() - ((Number)other).doubleValue();
 320
 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();
 327
 328                 result = Spreadsheets.asString(result);
 329                 other = Spreadsheets.asString(acceptedValue);
 330
 331                 result += (String)other;
 332             }
 333         }
 334         return leftValueWithPrefix(result, exp.left, exp.prefix, false);
 335
 336     }
 337
 338     @Override
 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);
 346             if("*".equals(op)) {
 347                 if(result == null) {
 348                     result = Spreadsheets.asDoubleWhereEmptyStringIsZero(leftValue);
 349                     if(result == null)
 350                         return FormulaError2.handleErrorCall(leftValue);
 351                 }
 352                 Number other = Spreadsheets.asDoubleWhereEmptyStringIsZero(rightValue);
 353                 if(other==null) return FormulaError2.handleErrorCall(rightValue);
 354
 355                 result = new Double(result.doubleValue() * other.doubleValue());
 356
 357             } else if("/".equals(op)) {
 358                 if(result == null) {
 359                     result = Spreadsheets.asDoubleWhereEmptyStringIsZero(leftValue);
 360                     if(result == null)
 361                         return FormulaError2.handleErrorCall(leftValue);
 362                 }
 363                 Number other = Spreadsheets.asDoubleWhereEmptyStringIsZero(rightValue);
 364                 if(other==null) return FormulaError2.handleErrorCall(rightValue);
 365                 if(other.doubleValue()==0.0) return FormulaError2.DIV0.getString();
 366
 367                 result = new Double(result.doubleValue() / other.doubleValue());
 368             }
 369         }
 370         if(result == null) result = Spreadsheets.asNumber(exp.left.accept(this));
 371         return result;
 372     }
 373
 374     @Override
 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);
 380             if("^".equals(op)) {
 381                 if(result == null) {
 382                     Object leftValue = exp.left.accept(this);
 383
 384                     FormulaError2 err = FormulaError2.forObject(leftValue);
 385                     if(err!=null) return err.getString();
 386
 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;
 392                     }
 393
 394                     if(leftValue instanceof String){
 395                         if((leftValue.toString()).isEmpty())
 396                             result = 0;
 397                         else
 398                             return FormulaError2.VALUE.getString();
 399                     }
 400                     else if(leftValue instanceof SpreadsheetMatrix)
 401                         result = leftValue;
 402                     else
 403                         result = Spreadsheets.asNumber(leftValue);
 404                 }
 405                 Object otherValue = value.accept(this);
 406
 407                 FormulaError2 err2 = FormulaError2.forObject(otherValue);
 408                 if(err2!=null) return err2.getString();
 409
 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;
 415                 }
 416
 417                 if(otherValue instanceof String){
 418                     if((otherValue.toString()).isEmpty())
 419                         otherValue = 0;
 420                     else
 421                         return FormulaError2.VALUE.getString();
 422                 }
 423
 424                 if(result instanceof SpreadsheetMatrix) {
 425                     result = ((SpreadsheetMatrix)result).pow(otherValue);
 426                 } else {
 427                     if(otherValue instanceof SpreadsheetMatrix) {
 428                         throw new IllegalStateException();
 429                     } else {
 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();
 439                         }
 440                     }
 441                 }
 442             }
 443         }
 444         if(result == null) result = Spreadsheets.asNumber(exp.left.accept(this));
 445         return result;
 446     }
 447
 448     @Override
 449     public Object visit(AstIdentifier id) {
 450         return FormulaError2.NAME.getString();
 451         //throw new IllegalStateException();
 452     }
 453
 454     @Override
 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);
 459         }
 460         return m;
 461     }
 462
 463     @Override
 464     public Object visit(AstNothing array) {
 465         return AstNothing.NOTHING;
 466     }
 467
 468     @Override
 469     public Object visit(AstArrayFormulaReference ref) {
 470
 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;
 475
 476         SpreadsheetMatrix m = (SpreadsheetMatrix)ref.value.accept(this);
 477         return m.get(y, x);
 478
 479     }
 480
 481 }