List of usage examples for org.apache.poi.ss.formula OperationEvaluationContext getColumnIndex
public int getColumnIndex()
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 w w . j a va2s. com*/ 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 av a 2 s .c o m "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 ww .ja v a 2s . co 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 www . j a va 2s . com // 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; }/* ww w.j a v a2 s . c o 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:de.enerko.reports2.functions.NormInv.java
License:Apache License
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { try {/*from w ww . j a va2 s. c o m*/ final ValueEval p = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()); final ValueEval mu = OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex()); final ValueEval sigma = OperandResolver.getSingleValue(args[2], ec.getRowIndex(), ec.getColumnIndex()); return new NumberEval(this.compute(OperandResolver.coerceValueToDouble(p), OperandResolver.coerceValueToDouble(mu), OperandResolver.coerceValueToDouble(sigma))); } catch (EvaluationException e) { return e.getErrorEval(); } }