Example usage for org.apache.poi.ss.formula.eval ErrorEval VALUE_INVALID

List of usage examples for org.apache.poi.ss.formula.eval ErrorEval VALUE_INVALID

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.formula.eval.*
  5. ErrorEval
  6. VALUE_INVALID

Introduction

In this page you can find the example usage for org.apache.poi.ss.formula.eval ErrorEval VALUE_INVALID.

Prototype

ErrorEval VALUE_INVALID

To view the source code for org.apache.poi.ss.formula.eval ErrorEval VALUE_INVALID.

Click Source Link

Document

#VALUE! - Wrong type of operand

Usage

From source file:com.dataart.spreadsheetanalytics.functions.poi.data.DsLookupFunction.java

License:Apache License

@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;
    }//from ww  w.  j ava 2  s.c  o m
    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);
}

From source file:com.dataart.spreadsheetanalytics.functions.poi.data.FuncexecFunction.java

License:Apache License

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

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

    if (!(args[0] instanceof StringEval) && !(args[0] instanceof RefEval)) {
        log.warn(//from w  ww.  j a  va2  s  .c  om
                "The first argument of FUNCEXEC function must be a string (or a reference to a cell) - name of DEFINE function.");
        return ErrorEval.VALUE_INVALID;
    }

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

    MetaFunctionAccessor defines = (MetaFunctionAccessor) ec.getCustomEvaluationContext()
            .get(MetaFunctionAccessor.class);
    if (defines == null) {
        defines = this.external.getMetaFunctionAccessor();
    }

    if (defines.get(defineFunctionName) == null) {
        log.warn("No DEFINE function with name {} is found.", defineFunctionName);
        return ErrorEval.NAME_INVALID;
    }

    List<ValueEval> inputValues = new LinkedList<>();
    List<ValueEval> inArgs = new ArrayList(Arrays.asList(args));
    inArgs.remove(0); //remove define function name

    try {
        for (ValueEval v : ICustomFunction.prepareQueryArgs(inArgs)) {
            inputValues.add(getSingleValue(v, ec.getRowIndex(), ec.getColumnIndex()));
        }
    } catch (EvaluationException e) {
        log.error("Cannot resolve input values for FUNCEXEC function", e);
        return ErrorEval.VALUE_INVALID;
    }

    final DefineFunctionMeta meta = (DefineFunctionMeta) defines.get(defineFunctionName);
    log.info("Found DEFINE function to invoke. Name = {}.", defineFunctionName);

    if (meta.getInputs().size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for FUNCEXEC+DEFINE. Expected: {}, Actual: {}.",
                meta.getInputs().size(), args.length - 1);
        return ErrorEval.VALUE_INVALID;
    }

    List<IA1Address> inputAddresses = meta.getInputs();
    log.debug("Input Addresses for DEFINE: {}, Input Values for DEFINE: {}.", inputAddresses, inputValues);

    if (inputAddresses.size() != inputValues.size()) {
        log.warn("Wrong number of input arguments for {} function.", defineFunctionName);
        return ErrorEval.VALUE_INVALID;
    }

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

    IDataModel dmWithDefine = dataModels.get(meta.getDataModelId());

    Workbook book = toWorkbook(dmWithDefine);
    EvaluationWorkbook defineBook = toEvaluationWorkbook(book);

    Sheet s = book.getSheetAt(0); //TODO one sheet support
    for (int i = 0; i < inputAddresses.size(); i++) {

        Row defineRow = s.getRow(inputAddresses.get(i).row());
        if (defineRow == null) {
            defineRow = s.createRow(inputAddresses.get(i).row());
        }
        Cell defineCell = defineRow.getCell(inputAddresses.get(i).column());
        if (defineCell == null) {
            defineCell = defineRow.createCell(inputAddresses.get(i).column());
        }

        populateCellValue(defineCell, inputValues.get(i));
        updateCell(defineBook, defineCell);
    }

    WorkbookEvaluator defineEvaluator = new WorkbookEvaluator(defineBook,
            IStabilityClassifier.TOTALLY_IMMUTABLE, null);
    List<ValueEval> outputValues = meta.getOutputs().stream().map(
            a -> defineEvaluator.evaluate(getEvaluationCell(defineBook, a), ec.getCustomEvaluationContext()))
            .collect(Collectors.<ValueEval>toList());

    log.debug("Output Values of DEFINE execution: {}.", outputValues);

    return outputValues.size() == 1 ? outputValues.get(0) : toArrayEval(outputValues);
}

From source file:com.dataart.spreadsheetanalytics.functions.poi.data.QueryFunction.java

License:Apache License

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

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

    if (!(args[0] instanceof StringEval) && !(args[0] instanceof RefEval)) {
        log.warn("1st parameter in QUERY function must be a LazyDataSet name [String or Cell Reference].");
        return ErrorEval.VALUE_INVALID;
    }// w  w  w  .j  av  a  2 s  .  c o m

    if (!(args[1] instanceof StringEval) && !(args[1] instanceof RefEval)) {
        log.warn(
                "2d parameter in QUERY function must be a new (local) DataSet name [String or Cell Reference].");
        return ErrorEval.VALUE_INVALID;
    }

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

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

    List<ValueEval> queryArgs = new ArrayList<>(asList(args));
    /* remove DS name (1st arg) and remove local Ds name (2d arg) */
    queryArgs.remove(0);
    queryArgs.remove(0);

    List<Object> execParams = new LinkedList<>();
    try {
        for (ValueEval v : ICustomFunction.prepareQueryArgs(queryArgs)) {
            execParams.add(coerceValueTo(getSingleValue(v, ec.getRowIndex(), ec.getColumnIndex())));
        }

    } catch (Exception e) {
        log.error("Error while resolving input arguments for QUERY function.", e);
        return ErrorEval.VALUE_INVALID;
    }

    log.info("QUERY function for DataModel: {}, Local DataSet: {}, Resolved parameters: {}", execDataSet,
            cachedDataSet, execParams);

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

        IDataSet dset = dataSets.get(execDataSet, new Parameters(execDataSet, execParams));

        dset.setName(cachedDataSet);
        dataSets.add(dset, DataSetScope.LOCAL);

        return toTableEval(dset);
    } catch (Exception e) {
        log.error("No [Lazy]DataSet with name {} is found to execute QUERY in.", execDataSet);
        return ErrorEval.NA;
    }
}

From source file:com.iesvirgendelcarmen.acceso.tema01.CalculateMortgage.java

License:Apache License

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

    // verify that we have enough data
    if (args.length != 3) {
        return ErrorEval.VALUE_INVALID;
    }//from ww  w.  j  a va 2 s  . co m

    // 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);
}

From source file:com.wantdo.stat.excel.poi_src.formula.CalculateMortgage.java

License:Apache License

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

    // verify that we have enough data
    if (args.length != 3) {
        return ErrorEval.VALUE_INVALID;
    }//from  w  w w  .ja  v  a2  s. c om

    // 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);
}

From source file:nl.eur.ese.spreadsheettest.DStarRunner.java

License:Apache License

public final ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
    if (args.length == 3) {
        return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2]);
    } else {/*from   w  w  w.  jav a2s . c o  m*/
        return ErrorEval.VALUE_INVALID;
    }
}

From source file:nl.eur.ese.spreadsheettest.DStarRunner.java

License:Apache License

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval database, ValueEval filterColumn,
        ValueEval conditionDatabase) {// www  .  j  a va 2s.  co m
    // Input processing and error checks.
    if (!(database instanceof TwoDEval) || !(conditionDatabase instanceof TwoDEval)) {
        return ErrorEval.VALUE_INVALID;
    }
    TwoDEval db = (TwoDEval) database;
    TwoDEval cdb = (TwoDEval) conditionDatabase;

    int fc;
    try {
        fc = getColumnForName(filterColumn, db);
    } catch (EvaluationException e) {
        return ErrorEval.VALUE_INVALID;
    }
    if (fc == -1) { // column not found
        return ErrorEval.VALUE_INVALID;
    }

    // Create an algorithm runner.
    IDStarAlgorithm algorithm = fac.create();

    // Iterate over all DB entries.
    for (int row = 1; row < db.getHeight(); ++row) {
        boolean matches = true;
        try {
            matches = fullfillsConditions(db, row, cdb);
        } catch (EvaluationException e) {
            return ErrorEval.VALUE_INVALID;
        }
        // Filter each entry.
        if (matches) {
            try {
                ValueEval currentValueEval = solveReference(db.getValue(row, fc));
                // Pass the match to the algorithm and conditionally abort the search.
                boolean shouldContinue = algorithm.processMatch(currentValueEval);
                if (!shouldContinue) {
                    break;
                }
            } catch (EvaluationException e) {
                return e.getErrorEval();
            }
        }
    }

    // Return the result of the algorithm.
    return algorithm.getResult();
}

From source file:nl.eur.ese.spreadsheettest.DStarRunner.java

License:Apache License

/**
 * Resolve reference(-chains) until we have a normal value.
 *
 * @param field a ValueEval which can be a RefEval.
 * @return a ValueEval which is guaranteed not to be a RefEval
 * @throws EvaluationException If a multi-sheet reference was found along the way.
 *//*from  w  w  w. j  a v  a  2s  . co  m*/
private static ValueEval solveReference(ValueEval field) throws EvaluationException {
    if (field instanceof RefEval) {
        RefEval refEval = (RefEval) field;
        if (refEval.getNumberOfSheets() > 1) {
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }
        return solveReference(refEval.getInnerValueEval(refEval.getFirstSheetIndex()));
    } else {
        return field;
    }
}

From source file:nl.eur.ese.spreadsheettest.DStarRunner.java

License:Apache License

/**
 * Returns the first column index that matches the given name. The name can either be
 * a string or an integer, when it's an integer, then the respective column
 * (1 based index) is returned./*  w  w  w.  j a  va 2 s.  c  o m*/
 * @param nameValueEval
 * @param db
 * @return the first column index that matches the given name (or int)
 * @throws EvaluationException
 */
@SuppressWarnings("unused")
private static int getColumnForTag(ValueEval nameValueEval, TwoDEval db) throws EvaluationException {
    int resultColumn = -1;

    // Numbers as column indicator are allowed, check that.
    if (nameValueEval instanceof NumericValueEval) {
        double doubleResultColumn = ((NumericValueEval) nameValueEval).getNumberValue();
        resultColumn = (int) doubleResultColumn;
        // Floating comparisions are usually not possible, but should work for 0.0.
        if (doubleResultColumn - resultColumn != 0.0)
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        resultColumn -= 1; // Numbers are 1-based not 0-based.
    } else {
        resultColumn = getColumnForName(nameValueEval, db);
    }
    return resultColumn;
}

From source file:nl.eur.ese.spreadsheettest.DStarRunner.java

License:Apache License

/**
 * Checks a row in a database against a condition database.
 *
 * @param db Database.// w  w w.  j  a  v  a 2  s . c  om
 * @param row The row in the database to check.
 * @param cdb The condition database to use for checking.
 * @return Whether the row matches the conditions.
 * @throws EvaluationException If references could not be resolved or comparison
 * operators and operands didn't match.
 */
private static boolean fullfillsConditions(TwoDEval db, int row, TwoDEval cdb) throws EvaluationException {
    // Only one row must match to accept the input, so rows are ORed.
    // Each row is made up of cells where each cell is a condition,
    // all have to match, so they are ANDed.
    for (int conditionRow = 1; conditionRow < cdb.getHeight(); ++conditionRow) {
        boolean matches = true;
        for (int column = 0; column < cdb.getWidth(); ++column) { // columns are ANDed
            // Whether the condition column matches a database column, if not it's a
            // special column that accepts formulas.
            boolean columnCondition = true;
            ValueEval condition = null;
            try {
                // The condition to apply.
                condition = solveReference(cdb.getValue(conditionRow, column));
            } catch (java.lang.RuntimeException e) {
                // It might be a special formula, then it is ok if it fails.
                columnCondition = false;
            }
            // If the condition is empty it matches.
            if (condition instanceof BlankEval)
                continue;
            // The column in the DB to apply the condition to.
            ValueEval targetHeader = solveReference(cdb.getValue(0, column));
            targetHeader = solveReference(targetHeader);

            if (!(targetHeader instanceof StringValueEval)) {
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
            }

            if (getColumnForName(targetHeader, db) == -1)
                // No column found, it's again a special column that accepts formulas.
                columnCondition = false;

            if (columnCondition == true) { // normal column condition
                // Should not throw, checked above.
                ValueEval value = db.getValue(row, getColumnForName(targetHeader, db));
                if (!testNormalCondition(value, condition)) {
                    matches = false;
                    break;
                }
            } else { // It's a special formula condition.
                if (getStringFromValueEval(condition).isEmpty()) {
                    throw new EvaluationException(ErrorEval.VALUE_INVALID);
                }
                throw new NotImplementedException("D* function with formula conditions");
            }
        }
        if (matches == true) {
            return true;
        }
    }
    return false;
}