CODEWITHSUNDEEP
excelapache-poijxls
qxlab
qxlab

Reputation: 1536

Formulas won't calculate automatically

I have a big sheet with a lot of formulas that have a dependency hierarchy between them. It starts with a cell with a date value. Then, cell x:y (and others), has formula depending on this date. Then cell w:z (and others) has a formula depending on cell x:y. And so on...

This main cell with a date value is filled using apache poi.

And now my problem: when I open the generated excel file, the date is there, but none of the formulas are calculated. They all have the error "A value used in the formula is of the wrong data type". It seems that when the formula try to evaluate it self the date isn't there yet.

Solutions: 1) If I click in the cell, and just press ENTER, the formula is correctly evaluated. But then I would have to do this for all cells. 2) I click in the date cell, copy it, and then paste it in the same place, and all formulas in the sheet are evaluated! 3) I could iterate in all cells in my application, evaluating each one with evaluateFormulaCell method from FormulaEvaluator class. But I have a lot of formulas and the performance of this is terrible.

Does someone have a solution for this?

Thanks!!

Upvotes: 2

Views: 10499

Answers (3)

drakid
drakid

Reputation: 320

Because Excel don't calculale automatically, so just change this.

Excel 2010: File -> Options -> Formulas: Find Workbook calculation, and change to Automatically.

It worked for me.

Upvotes: 1

qxlab
qxlab

Reputation: 1536

To solve this, I changed all formulas in my template, to use instead of the regular formula sintax (=SUM(A:D)) the following sintax: $[SUM(A:D)]

http://jxls.sourceforge.net/reference/formulas.html

Upvotes: 0

K_B
K_B

Reputation: 3678

Your date value isnt recognized by the excel formulas as a date, what you could do is have another cell dat will first take the datevalue DATEVALUE() of the cell filled using Apache poi.

Then direct your first level formulas to that cell instead of the one filled using Apache poi

Upvotes: 1

Related Questions