One Star

tFileExcelWorkbookSave - recalculate

Hi Experts,
I have a problem with my Excelupdate. The Job runs well, but the formulas e.g. simple sum() is not being recalculated after writing my values into the sheet.
When I try to use the "Calculate all formulas" checkbox in the tFileExcelWorkbookSave, i always get dumps.
Any ideas?
See dumps below.

Exception in component tFileExcelWorkbookSave_1
org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'Deckblatt NT Blätter'!D70
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:357)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:298)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:351)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAll(HSSFFormulaEvaluator.java:343)
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.evaluateAllFormulars(SpreadsheetFile.java:71)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileExcelWorkbookSave_1Process(J_UPDATE_PLANUNGSEXCELS.java:8557)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileExcelWorkbookOpen_1Process(J_UPDATE_PLANUNGSEXCELS.java:8479)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileInputExcel_3Process(J_UPDATE_PLANUNGSEXCELS.java:8365)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileInputExcel_2Process(J_UPDATE_PLANUNGSEXCELS.java:7530)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileInputExcel_1Process(J_UPDATE_PLANUNGSEXCELS.java:6697)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tMSSqlConnection_1Process(J_UPDATE_PLANUNGSEXCELS.java:5942)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.runJobInTOS(J_UPDATE_PLANUNGSEXCELS.java:9562)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.runJob(J_UPDATE_PLANUNGSEXCELS.java:9359)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tRunJob_2Process(CJ_WRITE_PLANUNGSEXCELS.java:1888)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tFileList_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1655)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tMSSqlConnection_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1315)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.runJobInTOS(CJ_WRITE_PLANUNGSEXCELS.java:2752)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.main(CJ_WRITE_PLANUNGSEXCELS.java:2542)
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell 'Deckblatt NT Blätter'!D89
at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:357)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:298)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
at org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
at org.apache.poi.ss.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29)
at org.apache.poi.ss.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
at org.apache.poi.ss.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
... 19 more
Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: COUNTIFS
at org.apache.poi.ss.formula.atp.AnalysisToolPak$NotImplemented.evaluate(AnalysisToolPak.java:38)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:64)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
2014-01-28 16:24:54|0qKMAA|fDLSFJ|fDLSFJ|kunde|J_UPDATE_PLANUNGSEXCELS|Default|6|Java Exception|tFileExcelWorkbookSave_1|org.apache.poi.ss.formula.eval.NotImplementedException:Error evaluating cell 'Deckblatt NT Blätter'!D70|1
period
12
... 29 more
Exception in component tRunJob_2
java.lang.RuntimeException: Child job running failed
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tRunJob_2Process(CJ_WRITE_PLANUNGSEXCELS.java:1906)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tFileList_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1655)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tMSSqlConnection_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1315)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.runJobInTOS(CJ_WRITE_PLANUNGSEXCELS.java:2752)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.main(CJ_WRITE_PLANUNGSEXCELS.java:2542)
the end is near

 

Starte Job CJ_WRITE_PLANUNGSEXCELS am 10:57 27/02/2014.
connecting to socket on port 3826
connected
path filename newPath
CP: 11
R3-cM-ELSE: 11
WY: 13
R3-ECI: 19
CEC: S
CELL_ROW CELL_COL CELL_VALUE CELL_COMMENT DO_NOT_WRITE_NULL
Kostenstelle Kontenrange Periode Jahr Summe
7601202 |500|11 |2013|0.0000
11|S |0.0000|27.02.14|
7601202 |520|11 |2013|0.0000
13|S |0.0000|27.02.14|
7601202 |700|11 |2013|686.6500
15|S |686.6500|27.02.14|
7601202 |800|11 |2013|-1816.1500
18|S |-1816.1500|27.02.14|
Exception in component tFileExcelWorkbookSave_1
java.lang.IllegalArgumentException: Invalid sheetIndex: -1.
at org.apache.poi.ss.formula.SheetRefEvaluator.(SheetRefEvaluator.java:39)
at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:95)
at org.apache.poi.ss.formula.OperationEvaluationContext.getArea3DEval(OperationEvaluationContext.java:273)
at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:660)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:527)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:351)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324)
2014-02-27 10:57:33|rGDAWh|ZX4Q5e|ZX4Q5e|kunde|J_UPDATE_PLANUNGSEXCELS|Default|6|Java Exception|tFileExcelWorkbookSave_1|java.lang.IllegalArgumentException:Invalid sheetIndex: -1.|1
period
11
2014-02-27 10:57:33|ZX4Q5e|ZX4Q5e|ZX4Q5e|kunde|CJ_WRITE_PLANUNGSEXCELS|Default|5|tDie|tDie_1|the end is near|4
2014-02-27 10:57:33|ZX4Q5e|ZX4Q5e|ZX4Q5e|kunde|CJ_WRITE_PLANUNGSEXCELS|Default|6|Java Exception|tRunJob_2|java.lang.RuntimeException:Child job running failed|1
disconnected
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAll(HSSFFormulaEvaluator.java:343)
at de.cimt.talendcomp.tfileexcelpoi.SpreadsheetFile.evaluateAllFormulars(SpreadsheetFile.java:71)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileExcelWorkbookSave_1Process(J_UPDATE_PLANUNGSEXCELS.java:8557)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileExcelWorkbookOpen_1Process(J_UPDATE_PLANUNGSEXCELS.java:8479)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileInputExcel_3Process(J_UPDATE_PLANUNGSEXCELS.java:8365)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileInputExcel_2Process(J_UPDATE_PLANUNGSEXCELS.java:7530)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tFileInputExcel_1Process(J_UPDATE_PLANUNGSEXCELS.java:6697)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.tMSSqlConnection_1Process(J_UPDATE_PLANUNGSEXCELS.java:5942)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.runJobInTOS(J_UPDATE_PLANUNGSEXCELS.java:9562)
at kunde.j_update_planungsexcels_0_3.J_UPDATE_PLANUNGSEXCELS.runJob(J_UPDATE_PLANUNGSEXCELS.java:9359)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tRunJob_2Process(CJ_WRITE_PLANUNGSEXCELS.java:1888)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tFileList_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1655)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tMSSqlConnection_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1315)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.runJobInTOS(CJ_WRITE_PLANUNGSEXCELS.java:2752)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.main(CJ_WRITE_PLANUNGSEXCELS.java:2542)
Exception in component tRunJob_2
java.lang.RuntimeException: Child job running failed
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tRunJob_2Process(CJ_WRITE_PLANUNGSEXCELS.java:1906)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tFileList_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1655)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.tMSSqlConnection_1Process(CJ_WRITE_PLANUNGSEXCELS.java:1315)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.runJobInTOS(CJ_WRITE_PLANUNGSEXCELS.java:2752)
at kunde.cj_write_planungsexcels_0_1.CJ_WRITE_PLANUNGSEXCELS.main(CJ_WRITE_PLANUNGSEXCELS.java:2542)
the end is near
Job CJ_WRITE_PLANUNGSEXCELS endet am 10:57 27/02/2014.
  • Exchange components
1 REPLY
Seventeen Stars

Re: tFileExcelWorkbookSave - recalculate

Yes, the Apache POI API does not cover all possibilities of Excel. Thats why you get these Exceptions. Formula evaluation is indeed only possible within the same sheet and also not all (but mostly all) functions.
The Apache project adds with every new release more and more functions. Thats why I update these components when ever a new release is available.
Sorry, but the components are limited to the POI features.
Please refer to the Apache POI doc to check which function is implemented and which is not:
Here the current list:
Address
AggregateFunction
BaseNumberUtils
Bin2Dec
BooleanFunction
CalendarFieldFunction
Choose
Code
Column
Columns
Complex
Count
Counta
Countblank
Countif
Countif.StringMatcher
Countifs
DateFunc
Days360
Dec2Bin
Dec2Hex
Delta
EDate
Errortype
Even
FactDouble
Finance
FinanceFunction
FinanceLib
Fixed0ArgFunction
Fixed1ArgFunction
Fixed2ArgFunction
Fixed3ArgFunction
Fixed4ArgFunction
Hex2Dec
Hlookup
Hyperlink
IfFunc
Imaginary
ImReal
Index
Indirect
Intercept
IPMT
Irr
LinearRegressionFunction
LogicalFunction
Lookup
Match
MinaMaxa
Mirr
Mode
MultiOperandNumericFunction
Na
NotImplementedFunction
Now
Npv
NumericFunction
NumericFunction.OneArg
NumericFunction.TwoArg
Oct2Dec
Odd
Offset
PPMT
Quotient
Rank
Rate
Replace
Rept
Roman
RowFunc
Rows
Slope
Substitute
Subtotal
Sumif
Sumifs
Sumproduct
Sumx2my2
Sumx2py2
Sumxmy2
T
TextFunction
TimeFunc
Today
Value
Vlookup
WeekdayFunc
WeekNum
XYNumericFunction
And here the URL:
http://poi.apache.org/spreadsheet/index.html