1

I am exporting an Excel file with Apache POI using an Excel file as source and for some cells (not all) simply referencing others (like ='worksheet2'.B13) or calculating a value with referenced cells (like ='worksheet2'.C13 + 'worksheet2'.D13), they only display zero (0) instead of the right value.

Weird thing is that when I click on the formula and change something in it, as typing a space and deleting it and then I type enter, the right value is displayed !

For info, the type of those cells is "Number / Standard".

I also read somewhere that I should check "Enable iterative calculation" in "Excel options", but it didn't work for me.

Thank you in advance for your help

Jenna SMITH
  • 75
  • 1
  • 1
  • 10
  • 1
    @Buddha I don't think it's something related to code because it's the same classes that I use to generate the whole file (mainly adding rows and copying formulas) and it is working perfectly for other cells. So I think it's rather something off with the cells. – Jenna SMITH Oct 27 '14 at 17:07
  • Did you try `wb.setForceFormulaRecalculation(true);`? - see this link: http://poi.apache.org/spreadsheet/eval.html – John Bustos Oct 27 '14 at 18:26
  • Did you ask Apache POI to calculate the cached values of the excel formulas you changed/added? – Gagravarr Oct 27 '14 at 19:30
  • @JohnBustos thank you for the precious help wb.setForceFormulaRecalculation(true) didn't work for me but the link you gave was helpful; I didn't know that POI cached the previous calculated values so wb.getCreationHelper().createFormulaEvaluator().evaluateAll() fixed the problem. – Jenna SMITH Oct 28 '14 at 09:46
  • @Gagravarr you were right, it was the cached values thing, I fixed it with wb.getCreationHelper().createFormulaEvaluator().evaluateAll(). Thank you, I appreciated your help! – Jenna SMITH Oct 28 '14 at 09:48

1 Answers1

5

With the help of @JohnBustos and @Gagravarr, I fixed the problem using : wb.getCreationHelper().createFormulaEvaluator().evaluateAll();

Apparently, Excel caches previously calculated results (in my case it was the Zero in the source Excel file) and we need to trigger recalculation to update them.

See this link : http://poi.apache.org/spreadsheet/eval.html / Section : "Recalculation of Formulas"

Jenna SMITH
  • 75
  • 1
  • 1
  • 10