This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/master by this push:
new 5927fed368 fixes to allow nested formulas on previously replaced
fields, with integration test. #3239
new 23ce8e2b92 Merge pull request #3273 from bamaer/3239
5927fed368 is described below
commit 5927fed36849b8128283987c23a4a4d03bb6a02c
Author: Bart Maertens <[email protected]>
AuthorDate: Thu Sep 28 20:36:30 2023 +0200
fixes to allow nested formulas on previously replaced fields, with
integration test. #3239
---
.../transforms/0042-formula-nested-replace.hpl | 175 +++++++++++++++++++++
.../datasets/golden-formula-nested-replace.csv | 2 +
.../main-0042-formula-nested-replace.hwf | 79 ++++++++++
.../dataset/golden-formula-nested-replace.json | 56 +++++++
.../0042-formula-nested-replace UNIT.json | 53 +++++++
.../hop/pipeline/transforms/formula/Formula.java | 147 +++++++++--------
.../transforms/formula/util/FormulaParser.java | 39 ++++-
7 files changed, 476 insertions(+), 75 deletions(-)
diff --git a/integration-tests/transforms/0042-formula-nested-replace.hpl
b/integration-tests/transforms/0042-formula-nested-replace.hpl
new file mode 100644
index 0000000000..c9078ae2bf
--- /dev/null
+++ b/integration-tests/transforms/0042-formula-nested-replace.hpl
@@ -0,0 +1,175 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0042-formula-nested-replace</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2023/09/28 19:57:01.124</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2023/09/28 19:57:01.124</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>foo, bar</from>
+ <to>formula - nested replace</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>formula - nested replace</from>
+ <to>verify</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>foo, bar</name>
+ <type>DataGrid</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <data>
+ <line>
+ <item>foo</item>
+ <item>bar</item>
+ <item>42</item>
+ </line>
+ </data>
+ <fields>
+ <field>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ <name>foo</name>
+ <type>String</type>
+ </field>
+ <field>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ <name>bar</name>
+ <type>String</type>
+ </field>
+ <field>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ <name>foo_numeric</name>
+ <type>Integer</type>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>112</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>formula - nested replace</name>
+ <type>Formula</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <formulas>
+ <formula>
+ <field_name>foobar</field_name>
+ <formula>[foo] & [bar]</formula>
+ <replace_field>bar</replace_field>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>2</value_type>
+ </formula>
+ <formula>
+ <field_name>foo_numeric2</field_name>
+ <formula>24</formula>
+ <replace_field>foo_numeric</replace_field>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>5</value_type>
+ </formula>
+ <formula>
+ <field_name>foobar2</field_name>
+ <formula>if( [bar] = "foobar", "REPLACED", "NOT YET
REPLACED")</formula>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>2</value_type>
+ </formula>
+ <formula>
+ <field_name>foobar3</field_name>
+ <formula>if( [foobar2] = "sdsdf", "WFWEF", [foobar])</formula>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>2</value_type>
+ </formula>
+ <formula>
+ <field_name>foobar4</field_name>
+ <formula>if( [foobar2] = "sdsdf", "WFWEF", [foo_numeric2])</formula>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>5</value_type>
+ </formula>
+ </formulas>
+ <attributes/>
+ <GUI>
+ <xloc>256</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>400</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git
a/integration-tests/transforms/datasets/golden-formula-nested-replace.csv
b/integration-tests/transforms/datasets/golden-formula-nested-replace.csv
new file mode 100644
index 0000000000..aad374c667
--- /dev/null
+++ b/integration-tests/transforms/datasets/golden-formula-nested-replace.csv
@@ -0,0 +1,2 @@
+foo,bar,foo_numeric,foobar2,foobar3,foobar4
+foo,foobar,24,REPLACED,foobar,24
diff --git a/integration-tests/transforms/main-0042-formula-nested-replace.hwf
b/integration-tests/transforms/main-0042-formula-nested-replace.hwf
new file mode 100644
index 0000000000..e19b56a52d
--- /dev/null
+++ b/integration-tests/transforms/main-0042-formula-nested-replace.hwf
@@ -0,0 +1,79 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<workflow>
+ <name>main-0042-formula-nested-replace</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2022/04/29 10:44:00.266</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2022/04/29 10:44:00.266</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>50</xloc>
+ <yloc>50</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Run Formula Nested Replace Unit Tests</name>
+ <description/>
+ <type>RunPipelineTests</type>
+ <attributes/>
+ <test_names>
+ <test_name>
+ <name>0042-formula-nested-replace UNIT</name>
+ </test_name>
+ </test_names>
+ <parallel>N</parallel>
+ <xloc>192</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>Run Formula Nested Replace Unit Tests</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
diff --git
a/integration-tests/transforms/metadata/dataset/golden-formula-nested-replace.json
b/integration-tests/transforms/metadata/dataset/golden-formula-nested-replace.json
new file mode 100644
index 0000000000..70e6c9aedc
--- /dev/null
+++
b/integration-tests/transforms/metadata/dataset/golden-formula-nested-replace.json
@@ -0,0 +1,56 @@
+{
+ "base_filename": "golden-formula-nested-replace.csv",
+ "name": "golden-formula-nested-replace",
+ "description": "",
+ "dataset_fields": [
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "foo",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "bar",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 5,
+ "field_precision": 0,
+ "field_name": "foo_numeric",
+ "field_format": "####0;-####0"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "foobar2",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "foobar3",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 5,
+ "field_precision": 0,
+ "field_name": "foobar4",
+ "field_format": "####0;-####0"
+ }
+ ],
+ "folder_name": ""
+}
\ No newline at end of file
diff --git
a/integration-tests/transforms/metadata/unit-test/0042-formula-nested-replace
UNIT.json
b/integration-tests/transforms/metadata/unit-test/0042-formula-nested-replace
UNIT.json
new file mode 100644
index 0000000000..605ea98c48
--- /dev/null
+++
b/integration-tests/transforms/metadata/unit-test/0042-formula-nested-replace
UNIT.json
@@ -0,0 +1,53 @@
+{
+ "variableValues": [],
+ "database_replacements": [],
+ "autoOpening": true,
+ "basePath": "",
+ "golden_data_sets": [
+ {
+ "field_mappings": [
+ {
+ "transform_field": "foo",
+ "data_set_field": "foo"
+ },
+ {
+ "transform_field": "bar",
+ "data_set_field": "bar"
+ },
+ {
+ "transform_field": "foo_numeric",
+ "data_set_field": "foo_numeric"
+ },
+ {
+ "transform_field": "foobar2",
+ "data_set_field": "foobar2"
+ },
+ {
+ "transform_field": "foobar3",
+ "data_set_field": "foobar3"
+ },
+ {
+ "transform_field": "foobar4",
+ "data_set_field": "foobar4"
+ }
+ ],
+ "field_order": [
+ "foo",
+ "bar",
+ "foo_numeric",
+ "foobar2",
+ "foobar3",
+ "foobar4"
+ ],
+ "data_set_name": "golden-formula-nested-replace",
+ "transform_name": "verify"
+ }
+ ],
+ "input_data_sets": [],
+ "name": "0042-formula-nested-replace UNIT",
+ "description": "",
+ "persist_filename": "",
+ "trans_test_tweaks": [],
+ "pipeline_filename": "./0042-formula-nested-replace.hpl",
+ "test_type": "UNIT_TEST"
+}
\ No newline at end of file
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
index 285cfd5665..067942b26d 100644
---
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
@@ -19,6 +19,7 @@ package org.apache.hop.pipeline.transforms.formula;
import java.sql.Timestamp;
import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.exception.HopTransformException;
import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.RowDataUtil;
import org.apache.hop.core.row.value.ValueMetaFactory;
@@ -37,12 +38,14 @@ import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.util.Arrays;
+import java.util.HashMap;
public class Formula extends BaseTransform<FormulaMeta, FormulaData> {
private XSSFWorkbook workBook;
private XSSFSheet workSheet;
private Row sheetRow;
+ private HashMap<String, String> replaceMap;
@Override
public boolean init() {
@@ -50,11 +53,7 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
workBook = new XSSFWorkbook();
workSheet = workBook.createSheet();
sheetRow = workSheet.createRow(0);
-
- data.returnType = new int[meta.getFormulas().size()];
- for (int i = 0; i < meta.getFormulas().size(); i++) {
- data.returnType[i] = -1;
- }
+ replaceMap = new HashMap<String, String>();
return true;
}
@@ -78,10 +77,20 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
return false;
}
-
if (first) {
first = false;
- data.outputRowMeta = getInputRowMeta().clone();
+
+ try {
+ data.outputRowMeta = getInputRowMeta().clone();
+ meta.getFields(data.outputRowMeta, getTransformName(), null, null,
this, metadataProvider);
+ } catch (HopTransformException e) {
+ throw new RuntimeException(e);
+ }
+
+ data.returnType = new int[meta.getFormulas().size()];
+ for (int i = 0; i < meta.getFormulas().size(); i++) {
+ data.returnType[i] = -1;
+ }
// Calculate replace indexes...
//
@@ -90,6 +99,9 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
FormulaMetaFunction fn = meta.getFormulas().get(j);
if (!Utils.isEmpty(fn.getReplaceField())) {
data.replaceIndex[j] =
data.outputRowMeta.indexOfValue(fn.getReplaceField());
+
+ // keep track of the formula fields and the fields they replace for
formula parsing later on.
+ replaceMap.put(fn.getFieldName(), fn.getReplaceField());
if (data.replaceIndex[j] < 0) {
throw new HopException(
"Unknown field specified to replace with a formula result:
["
@@ -101,7 +113,7 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
}
}
}
- meta.getFields(data.outputRowMeta, getTransformName(), null, null, this,
metadataProvider);
+
int tempIndex = getInputRowMeta().size();
@@ -114,73 +126,72 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
}
sheetRow = workSheet.createRow(0);
- Object outputValue = null;
Object[] outputRowData = RowDataUtil.resizeArray(r,
data.outputRowMeta.size());
+ Object outputValue = null;
for (int i = 0; i < meta.getFormulas().size(); i++) {
FormulaMetaFunction formula = meta.getFormulas().get(i);
- FormulaParser parser = new FormulaParser(formula, data.outputRowMeta, r,
sheetRow, variables);
- CellValue cellValue = parser.getFormulaValue();
-
- CellType cellType = cellValue.getCellType();
-
- switch (cellType) {
- case BLANK:
- // should never happen.
- break;
- case NUMERIC:
- outputValue = cellValue.getNumberValue();
- int outputValueType = formula.getValueType();
-
- switch (outputValueType) {
- case IValueMeta.TYPE_NUMBER:
- data.returnType[i] = FormulaData.RETURN_TYPE_NUMBER;
- formula.setNeedDataConversion(formula.getValueType() !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_INTEGER:
- data.returnType[i] = FormulaData.RETURN_TYPE_INTEGER;
- formula.setNeedDataConversion(formula.getValueType() !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_BIGNUMBER:
- data.returnType[i] = FormulaData.RETURN_TYPE_BIGDECIMAL;
- formula.setNeedDataConversion(formula.getValueType() !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_DATE:
- outputValue = DateUtil.getJavaDate(cellValue.getNumberValue());
- data.returnType[i] = FormulaData.RETURN_TYPE_DATE;
- formula.setNeedDataConversion(formula.getValueType() !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_TIMESTAMP:
- outputValue =
Timestamp.from(DateUtil.getJavaDate(cellValue.getNumberValue()).toInstant());
- data.returnType[i] = FormulaData.RETURN_TYPE_TIMESTAMP;
- formula.setNeedDataConversion(formula.getValueType() !=
IValueMeta.TYPE_NUMBER);
- break;
- default:
- break;
- }
- // get cell value
- break;
- case BOOLEAN:
- outputValue = cellValue.getBooleanValue();
- data.returnType[i] = FormulaData.RETURN_TYPE_BOOLEAN;
- formula.setNeedDataConversion(formula.getValueType() !=
IValueMeta.TYPE_BOOLEAN);
- break;
- case STRING:
- outputValue = cellValue.getStringValue();
- data.returnType[i] = FormulaData.RETURN_TYPE_STRING;
- formula.setNeedDataConversion(formula.getValueType() !=
IValueMeta.TYPE_STRING);
- break;
- default:
- break;
- }
-
- int realIndex = (data.replaceIndex[i] < 0) ? tempIndex++ :
data.replaceIndex[i];
-
+ FormulaParser parser = new FormulaParser(formula, data.outputRowMeta,
outputRowData, sheetRow, variables, replaceMap);
+ try {
+ CellValue cellValue = parser.getFormulaValue();
+ CellType cellType = cellValue.getCellType();
+
+ int outputValueType = formula.getValueType();
+ switch (cellType) {
+ case BLANK:
+ // should never happen.
+ break;
+ case NUMERIC:
+ outputValue = cellValue.getNumberValue();
+ switch (outputValueType) {
+ case IValueMeta.TYPE_NUMBER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_NUMBER;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_INTEGER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_INTEGER;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_BIGNUMBER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_BIGDECIMAL;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_DATE:
+ outputValue = DateUtil.getJavaDate(cellValue.getNumberValue());
+ data.returnType[i] = FormulaData.RETURN_TYPE_DATE;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_TIMESTAMP:
+ outputValue =
Timestamp.from(DateUtil.getJavaDate(cellValue.getNumberValue()).toInstant());
+ data.returnType[i] = FormulaData.RETURN_TYPE_TIMESTAMP;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ default:
+ break;
+ }
+ // get cell value
+ break;
+ case BOOLEAN:
+ outputValue = cellValue.getBooleanValue();
+ data.returnType[i] = FormulaData.RETURN_TYPE_BOOLEAN;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_BOOLEAN);
+ break;
+ case STRING:
+ outputValue = cellValue.getStringValue();
+ data.returnType[i] = FormulaData.RETURN_TYPE_STRING;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_STRING);
+ break;
+ default:
+ break;
+ }
+ int realIndex = (data.replaceIndex[i] < 0) ? tempIndex++ :
data.replaceIndex[i];
- outputRowData[realIndex] =
- getReturnValue(outputValue, data.returnType[i], realIndex, formula);
+ outputRowData[realIndex] = getReturnValue(outputValue,
data.returnType[i], realIndex, formula);
+ }catch(Exception e){
+ throw new HopException("Formula '" + formula.getFormula() + "' could
not not be parsed ", e);
+ }
}
putRow(data.outputRowMeta, outputRowData);
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
index 871c64f66c..b660c9c659 100644
---
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
@@ -26,13 +26,12 @@ import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
-import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
-import java.sql.Timestamp;
import java.util.ArrayList;
-import java.util.Date;
+import java.util.HashMap;
import java.util.List;
+import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@@ -46,24 +45,46 @@ public class FormulaParser {
private Object[] dataRow;
private Row sheetRow;
private FormulaEvaluator evaluator;
+ private HashMap<String, String> replaceMap;
public FormulaParser(
- FormulaMetaFunction formulaMetaFunction, IRowMeta rowMeta, Object[]
dataRow, Row sheetRow, IVariables variables) {
+ FormulaMetaFunction formulaMetaFunction, IRowMeta rowMeta, Object[]
dataRow, Row sheetRow, IVariables variables, HashMap<String, String>
replaceMap) {
this.formulaMetaFunction = formulaMetaFunction;
this.rowMeta = rowMeta;
this.dataRow = dataRow;
this.sheetRow = sheetRow;
fieldNames = rowMeta.getFieldNames();
+ this.replaceMap = replaceMap;
formula = variables.resolve(formulaMetaFunction.getFormula());
evaluator =
sheetRow.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
- formulaFieldList = new ArrayList<>();
+ formulaFieldList = getFormulaFieldList(formula);
+
+ boolean getNewList = false;
+ for(String formulaField : formulaFieldList){
+ // check if we are working with a field that was replaced earlier.
+ Set<String> replaceKeys = replaceMap.keySet();
+ if(replaceKeys.contains(formulaField)){
+ String realFieldName = replaceMap.get(formulaField);
+ formula = formula.replaceAll("\\[" + formulaField + "\\]", "\\[" +
realFieldName + "\\]");
+ getNewList = true;
+ }
+ }
+
+ if(getNewList){
+ formulaFieldList = getFormulaFieldList(formula);
+ }
+ }
+
+ private List<String> getFormulaFieldList(String formula){
+ List<String> theFields = new ArrayList<>();
Pattern regex = Pattern.compile("\\[(.*?)\\]");
Matcher regexMatcher = regex.matcher(formula);
while (regexMatcher.find()) {
- formulaFieldList.add(regexMatcher.group(1));
+ theFields.add(regexMatcher.group(1));
}
+ return theFields;
}
public CellValue getFormulaValue() throws HopValueException {
@@ -73,8 +94,11 @@ public class FormulaParser {
for (String formulaField : formulaFieldList) {
char s = (char) fieldIndex;
Cell cell = sheetRow.createCell(colIndex);
+
int fieldPosition = rowMeta.indexOfValue(formulaField);
+ parsedFormula = parsedFormula.replaceAll("\\[" + formulaField + "\\]", s
+ "1");
+
IValueMeta fieldMeta = rowMeta.getValueMeta(fieldPosition);
if (dataRow[fieldPosition] != null) {
if (fieldMeta.isBoolean()) {
@@ -92,9 +116,10 @@ public class FormulaParser {
} else {
cell.setCellValue(rowMeta.getString(dataRow, fieldPosition));
}
+ }else{
+ cell.setBlank();
}
- parsedFormula = parsedFormula.replaceAll("\\[" + formulaField + "\\]", s
+ "1");
fieldIndex++;
colIndex++;
}
