public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {

    // verify that we have enough data
    if (args.length != 3) {
      return ErrorEval.VALUE_INVALID;
    }

    // declare doubles for values
    double principal, rate, years, result;
    try {
      // extract values as ValueEval
      ValueEval v1 = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex());
      ValueEval v2 = OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex());
      ValueEval v3 = OperandResolver.getSingleValue(args[2], ec.getRowIndex(), ec.getColumnIndex());

      // get data as doubles
      principal = OperandResolver.coerceValueToDouble(v1);
      rate = OperandResolver.coerceValueToDouble(v2);
      years = OperandResolver.coerceValueToDouble(v3);

      result = calculateMortgagePayment(principal, rate, years);
      System.out.println("Result = " + result);

      checkValue(result);

    } catch (EvaluationException e) {
      return e.getErrorEval();
    }

    return new NumberEval(result);
  }
示例#2
0
  public List<String> getCellConstraints(Cell cell) {
    for (DataValidation _validation : getValidations())
      for (CellRangeAddress _region : _validation.getRegions().getCellRangeAddresses())
        if (_region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
          if (_validation.getValidationConstraint().getExplicitListValues() != null)
            return Arrays.asList(_validation.getValidationConstraint().getExplicitListValues());
          else if (_validation.getValidationConstraint().getFormula1() != null) {
            String formula = _validation.getValidationConstraint().getFormula1().split("\"")[1];
            String[] _names = formula.split("!");
            String _sheetName = _names[0];
            String _arrName = _names[1];
            int sheetIndex = evalWorkbook.getSheetIndex(_sheetName);

            EvaluationName nm = evalWorkbook.getName(_arrName, sheetIndex);
            if (nm == null || !nm.isRange()) {
              throw new RuntimeException(
                  "Specified name '" + _arrName + "' is not a range as expected.");
            }

            OperationEvaluationContext ec =
                new OperationEvaluationContext(
                    new WorkbookEvaluator(evalWorkbook, null, null),
                    evalWorkbook,
                    defaultSheet,
                    cell.getRowIndex(),
                    cell.getColumnIndex(),
                    null);

            Ptg[] ptgs = nm.getNameDefinition();
            if (ptgs.length == 1 && ptgs[0] instanceof Area3DPtg) {
              ValueEval result = ec.getArea3DEval((Area3DPtg) ptgs[0]);

              if (result instanceof AreaEvalBase) {
                AreaEvalBase _area = (AreaEvalBase) result;
                ArrayList<String> resultStrings = new ArrayList<>();
                for (int i = _area.getFirstRow(); i <= _area.getLastRow(); i++) {
                  String value =
                      getStringValue(
                          new CellCoord(_area.getFirstSheetIndex(), _area.getFirstColumn(), i));
                  if (value != null && value.length() > 0) resultStrings.add(value);
                }

                return resultStrings;
              }

              return null;
            }
          }
        }

    return null;
  }
  protected List<ValueEval> fetchValuesWithOptimisations(
      DsLookupParameters parameters, OperationEvaluationContext ec) {
    DataSetOptimisationsCache caches =
        (DataSetOptimisationsCache)
            ec.getCustomEvaluationContext().get(DataSetOptimisationsCache.class);
    if (caches == null) {
      caches = this.external.getDataSetOptimisationsCache();
    }

    Cache<DsLookupParameters, List> cache = caches.getDataSetToDsLookupParameters();

    if (cache.containsKey(parameters)) {
      return cache.get(parameters);
    }

    return null;
  }
  protected void updateOptimisationsCache(
      DsLookupParameters parameters,
      IDataSet dataSet,
      List<ValueEval> fetchedValues,
      OperationEvaluationContext ec) {
    if (fetchedValues == null || parameters == null) {
      return;
    }

    DataSetOptimisationsCache caches =
        (DataSetOptimisationsCache)
            ec.getCustomEvaluationContext().get(DataSetOptimisationsCache.class);
    if (caches == null) {
      caches = this.external.getDataSetOptimisationsCache();
    }

    Cache<DsLookupParameters, List> cache = caches.getDataSetToDsLookupParameters();

    cache.put(parameters, fetchedValues);
  }
  @Override
  public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {

    log.debug("In evaluate() of DSLOOKUP function. Args = {}", Arrays.toString(args));

    if (args.length < 4 || args.length % 2 != 0) {
      log.warn(
          "The number of input arguments of DSLOOKUP function should be even and no less than 4.");
      return ErrorEval.VALUE_INVALID;
    }
    if (!(args[0] instanceof StringValueEval) && !(args[0] instanceof RefEval)) {
      log.warn(
          "The first input argument of DSLOOKUP function should be a string (or a reference to a cell) with a dataset name.");
      return ErrorEval.VALUE_INVALID;
    }
    if (!(args[args.length - 1] instanceof StringValueEval)
        && !(args[args.length - 1] instanceof RefEval)) {
      log.warn(
          "The last input argument of DSLOOKUP function should be a string (or a reference to a cell) with a name of a column which values should be returned.");
      return ErrorEval.VALUE_INVALID;
    }

    String datasetName;
    try {
      datasetName =
          (String) coerceValueTo(getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()));
    } catch (EvaluationException e) {
      log.error(String.format("Cannot get the value of DataSet name: %s", args[0]), e);
      return ErrorEval.VALUE_INVALID;
    }

    String columnName;
    try {
      columnName =
          (String)
              coerceValueTo(
                  getSingleValue(args[args.length - 1], ec.getRowIndex(), ec.getColumnIndex()));
    } catch (EvaluationException e) {
      log.error(
          String.format("Cannot get the value of target column name: %s", args[args.length - 1]),
          e);
      return ErrorEval.VALUE_INVALID;
    }

    Map<Object, ValueEval> pairs = new HashMap<>();

    for (int i = 1; i < args.length - 1; i += 2) {

      if (!(args[i] instanceof StringEval) && !(args[i] instanceof RefEval)) {
        log.warn(
            "The {}th input argument in DSLOOKUP function should be a string (or a reference to a cell) with a name of a condition field",
            i);
        return ErrorEval.VALUE_INVALID;
      }

      try {
        String key =
            (String) coerceValueTo(getSingleValue(args[i], ec.getRowIndex(), ec.getColumnIndex()));
        ValueEval val = getSingleValue(args[i + 1], ec.getRowIndex(), ec.getColumnIndex());
        pairs.put(key, val);
      } catch (EvaluationException e) {
        log.error(String.format("Cannot get the value of matcher column: %s", args[i]), e);
        return ErrorEval.VALUE_INVALID;
      }
    }

    DataSetAccessor dataSets =
        (DataSetAccessor) ec.getCustomEvaluationContext().get(DataSetAccessor.class);
    if (dataSets == null) {
      dataSets = this.external.getDataSetAccessor();
    }

    IDataSet dataSet;
    try {
      dataSet = dataSets.get(datasetName);
    } catch (Exception e) {
      log.error(
          "The DataSet with name = {} cannot be found\retrived from DataSet storage.", datasetName);
      return ErrorEval.NA;
    }

    if (dataSet == null) {
      DataModelAccessor dataModels =
          (DataModelAccessor) ec.getCustomEvaluationContext().get(DataModelAccessor.class);
      if (dataModels == null) {
        dataModels = this.external.getDataModelAccessor();
      }

      dataSet = Converters.toDataSet(dataModels.get(datasetName));
    }

    if (dataSet == null) {
      log.error(
          "The DataSet with name = {} cannot found in DataSet/DataModel storage.", datasetName);
      return ErrorEval.NA;
    }

    Iterator<IDsRow> rowrator = dataSet.iterator();
    if (!rowrator.hasNext()) {
      log.warn("The spreadsheet shoud have at least 2 rows to run DSLOOKUP function");
      return ErrorEval.VALUE_INVALID;
    }

    int columnIndex = -1;
    IDsRow titleRow = rowrator.next();
    Map<Integer, Object> indexToValue = new HashMap<>();

    for (IDsCell cell : titleRow) {
      ICellValue value = cell.getValue();

      if (pairs.containsKey(value.get())) {
        indexToValue.put(cell.index(), pairs.get(value.get()));
      }

      if (columnName.equals(value.get())) {
        columnIndex = cell.index();
      }
    }

    if (columnIndex < 0) {
      log.warn("No such column to retreive value from is found: {}.", columnName);
      return ErrorEval.VALUE_INVALID;
    }

    if (indexToValue.isEmpty()) {
      log.warn("No filter columns are found.");
      return ErrorEval.VALUE_INVALID;
    }

    DsLookupParameters parameters =
        new DsLookupParameters(dataSet.getName(), indexToValue, columnIndex);
    List<ValueEval> fetchedValues = fetchValuesWithOptimisations(parameters, ec);

    if (fetchedValues == null) {
      fetchedValues = fetchValuesWithFullScan(dataSet, indexToValue, columnIndex);

      updateOptimisationsCache(parameters, dataSet, fetchedValues, ec);
    }

    // This is per PO decision: DSLOOKUP should return only one value - first found.
    return fetchedValues.isEmpty() ? ErrorEval.NA : fetchedValues.get(0);
  }