Thanks Mark. Actually both the method evaluateForlulaCell() and evaluateInCell() calculates the same way. But only the thing that is different is the way they store the result. Now what I can guess is as Formula Option Automatic calculation is ON so excel tries to do the calculation and gives the error. But suppose the formula is not set from POI and it already exists in excel then it evaluates properly.
On Mon, Jan 31, 2011 at 1:43 PM, Mark Beardsley <[email protected]>wrote: > > I am not trying to evade answering your questions here but I have one to > ask > myself. > > It seems as though your evaluating the formulae with POI prioir to opening > the workbook using Excel might be causing these issues. Do you need to > evaluate the formulae with POI for any reason? If not, what would happen > when the workbook was opened with Excel? Maybe this will manage to > circumvent the problem you are facing. > > Now, as to your other questions; > > So my question is why excel gives #VALUE! error for above functions. Is > there some compatibility issue. > > I honestly do not know the answer to this question. This error is quite > common and occurs when the workng type of value - for example a text value > rather than a number - or the incorect operand is used. Look carefuly at > the > formulae and the cells they are referencing just to make sure that you are > not trying to include the string value '30' rather than the integer value > 30 > into a SUM for example. It may simply be that Excel is far stricter about > this than POI is and will refuse to make any assumptions. > > How can I solve this problem OR how should I approach to solve this problem > where I need to keep both formula and result without #VALUE!. > > Well, I guess that there are two answers to this question really. If you > can > solve the #VALUE issue above then the problem may well - hopefully - > resolve > itself. If not, then I would suggest you will need to identify what the two > calcaultion methods are doing differently and try to 'normalise' them, i.e. > try to make sure that both operate in the appropriate manner. Aside from > that, I do not know enough about the Excel file structure to offer any > advice - I do know that Excel stores the result of the last calculation run > fro formulae away to the file somewehere. But, I do not know eher this is > and if it is possible to get at it with the API. The thought going through > my mind is if you could update the cached value to hold the result of the > formulas evaluation then the forumla could remain the the cell but I do not > know if this is possible or even if it might work. > > Sorry to not be of more help. > > Yours > > Mark B > -- > View this message in context: > http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3364162.html > Sent from the POI - User mailing list archive at Nabble.com. > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
