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