Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: MS Excel Writer - formula problems

  1. #1
    Join Date
    Feb 2011
    Posts
    840

    Default MS Excel Writer - formula problems

    So, new week, new problems needing help =p I'm playing now with formulas on Excel Writer output... but I can't seem to get it right.

    First few problems were kinda dumb, actually... MS in PT-BR uses ; instead of , to separate arguments inside functions, but that was easy to find out. Translation of functions from pt-br to en was also easy to find... but now I'm getting a strange message, but searching google for it is returning me almost only this source code.

    Formulas used:
    Code:
    VLOOKUP("100",$B:$F,5,0) - VLOOKUP("105",$B:$F,5,0)
    ROUND(VLOOKUP("100",PR!B:F,5,0),2)
    IF(VLOOKUP("960",'ATIVO PERMANENTE - LIMITE'!B:D,3,0)>=0,0,ABS(VLOOKUP("960",'ATIVO PERMANENTE - LIMITE'!B:D,3,0)))
    VLOOKUP("720",$B:$F,5,0) + VLOOKUP("800",$B:$F,5,0) + VLOOKUP("810",$B:$F,5,0) + VLOOKUP("820",$B:$F,5,0) + VLOOKUP("830",$B:$F,5,0) + VLOOKUP("840",$B:$F,5,0) + VLOOKUP("850",$B:$F,5,0) + VLOOKUP("860",$B:$F,5,0) + VLOOKUP("870",$B:$F,5,0) + VLOOKUP("880",$B:$F,5,0)
    VLOOKUP("720",PEPR!$B:$K,10,0)
    VLOOKUP("870",POPR!B15:N49,12,0)
    VLOOKUP("100",$B:$F,5,0)*0.11/VLOOKUP("900",$B:$F,5,0)
    VLOOKUP("890",RBAN!B7:H26,7,0)
    Errors received, running (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy):
    Code:
    ERROR : Unexpected errorERROR : java.lang.IllegalStateException: evaluation stack not empty
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
    ERROR :     at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
    ERROR :     at org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
    ERROR :     at org.apache.poi.ss.formula.eval.AreaEvalBase.getValue(AreaEvalBase.java:109)
    ERROR :     at org.apache.poi.ss.formula.functions.LookupUtils$ColumnVector.getItem(LookupUtils.java:99)
    ERROR :     at org.apache.poi.ss.formula.functions.Vlookup.evaluate(Vlookup.java:61)
    ERROR :     at org.apache.poi.ss.formula.functions.Var3or4ArgFunction.evaluate(Var3or4ArgFunction.java:36)
    ERROR :     at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
    ERROR :     at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
    ERROR :     at org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
    ERROR :     at org.apache.poi.ss.formula.eval.AreaEvalBase.getValue(AreaEvalBase.java:109)
    ERROR :     at org.apache.poi.ss.formula.functions.LookupUtils$ColumnVector.getItem(LookupUtils.java:99)
    ERROR :     at org.apache.poi.ss.formula.functions.Vlookup.evaluate(Vlookup.java:61)
    ERROR :     at org.apache.poi.ss.formula.functions.Var3or4ArgFunction.evaluate(Var3or4ArgFunction.java:36)
    ERROR :     at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
    ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
    ERROR :     at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
    ERROR :     at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
    ERROR :     at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.recalculateAllWorkbookFormulas(ExcelWriterStep.java:240)
    ERROR :     at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.closeOutputFile(ExcelWriterStep.java:217)
    ERROR :     at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.processRow(ExcelWriterStep.java:172)
    ERROR :     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    ERROR :     at java.lang.Thread.run(Unknown Source)
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  2. #2
    Join Date
    Feb 2011
    Posts
    840

    Default

    wow, 9 months later I got stuck with this problem again and still no answer =( bump?
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  3. #3
    Join Date
    Apr 2013
    Posts
    15

    Default

    Hello, you could try using the latest apache POI. This may solve your problem.

    http://poi.apache.org/index.html

  4. #4
    Join Date
    Feb 2011
    Posts
    840

    Default

    latest, you mean, dev/unstable? coz PDI 5 already comes with POI version 3.9

    anyway... tried 3.10 beta 2 just now, I'm still getting the dreaded "evaluation stack not empty" error.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.