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!!
Answer
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