List of usage examples for org.apache.poi.ss.formula.eval ErrorEval NA
ErrorEval NA
To view the source code for org.apache.poi.ss.formula.eval ErrorEval NA.
Click Source Link
From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java
License:Apache License
/** Inserts a value to a Cell based on a value type (class). */ static void populateCellValue(final Cell cell, final ICellValue value) { if (cell == null) { return;/*from w ww . j a v a 2 s .c om*/ } int cellType = resolveCellType(value); cell.setCellType(cellType); switch (cellType) { case CELL_TYPE_BLANK: { break; } case CELL_TYPE_BOOLEAN: { cell.setCellValue((Boolean) value.get()); break; } case CELL_TYPE_NUMERIC: { cell.setCellValue((Double) value.get()); break; } case CELL_TYPE_FORMULA: { try { cell.setCellFormula(((String) value.get()).substring(1)); break; } catch (FormulaParseNameException e) { log.error("Formula parsing error while trying to set formula field in cell " + e.getMessage()); cell.setCellFormula(ErrorEval.NAME_INVALID.getErrorString()); break; } catch (FormulaParseNAException e) { log.error("Formula parsing error while trying to set formula field in cell " + e.getMessage()); cell.setCellFormula(ErrorEval.NA.getErrorString()); break; } } case CELL_TYPE_ERROR: { cell.setCellErrorValue(FormulaError.forString((String) value.get()).getCode()); break; } case CELL_TYPE_STRING: { cell.setCellValue((String) value.get()); break; } default: { throw new CalculationEngineException(String.format("Type of value %s is not supported: %s", value, value.getClass().getSimpleName())); } } }
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; }/* w ww.ja va2s. co 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.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; }//from w w w . j a v a 2 s .c om 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; } }