I am coming across this error: "java.lang.RuntimeException: Invalid arg type for SUMPRODUCT: (org.apache.poi.ss.formula.eval.ErrorEval)" when enabling the "Recalculate formula" option in tFileOutputExcel.
Can anyone help on why this happens and what to do to solve it?
Are you checking that the values being added to the cells that make up the arrays are legitimate values after the component has written the data? Maybe switch the "Recalculate formula" option off and manually check the file after it is loaded.
Yes rhall, I can ensure that the cells content are legitimate values. Moreover, when I write the data without the "Recalculate formula" option, and open the resulting excel file and manually add any number in the end of the column involved in SUMPRODUCT formula, the formulas then work
This functionality relies on the Apache POI API. This looks like it could be a bug or an unsupported feature in that. This is an assumption since I cannot see the formula you are trying to use.
It does look like. You may be able to put together a workaround using a more recent version of the Apache POI Jar. I have not tried this, but it would be my first attempt at getting around this. I would write a routine using the new library and simply open the file at the end of your job, recalculate and close it again. That *might* work if the newest Jar has this bug fixed.
