Thanks Mark for the detailed explanation. Just one more query although I have asked this before too in this forum and also debugged the code to look into the case but not sure why this is happening.
So if my excel option is automatic although if I do evaluateFormulaCell() but as the formula remains in the cell excel does the calculation again by itself. Now for some formula it works fine. But for some functions in excel like VALUE,TRIM,ADDRESS this gives me #VALUE! error. So I need to use evaluateInCell() but unfortunately our client is not happy that the formula gets removed from the cell :(. So my question is why excel gives #VALUE! error for above functions. Is there some compatibility issue. 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!. Thanks, Aniket On Fri, Jan 28, 2011 at 8:42 PM, Mark Beardsley <[email protected]>wrote: > > Do not waste your time. I have just had a good look and this setting > relates > to the application and not to any file. Therefore, there will be no setting > within the .xls or .xlsx file that has any effect upon whether the > calculation mode is automaic or not. As of now, the only way I know to set > this programmatically is with a macro. You would need to define a macro > within the workbook that ran whenever the user opened the workbook and > which > adjusted the settings as required. There are quite a few problems with this > approach; > > 1. The change in settings would persist - that means that the next workbook > the user opened would also be affected by the changes you made. > 2. Whilst macros can be signed with Office 2007 files (.xlsm), this feature > is not supported with the older binary file format. Thus, when the user > opens a workbok they will be faced with a message telling them it contains > macros, that these could perform harmful actions and offering them the > option of disabling the macro. > > So, to sum up, I guess the answer is that it can be done, not with POI or a > patched version of the api and that you need to think carefully before > doing > this - how happy will your users me if your workbook makes changes to their > copy of Excel? > > Yours > > Mark B > -- > View this message in context: > http://apache-poi.1045710.n5.nabble.com/Formula-Calculation-Option-with-POI-tp3361199p3361591.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] > >
