Hello Dave, On Tue, Apr 1, 2008 at 9:05 AM, David Fisher <[EMAIL PROTECTED]> wrote: > ... > Which puts him at (2) - needing to know how to set the workbook to > recalculate all formulas as soon as it is re-opened. So, what is the > POI-foo for that? > ...
As far as I know there is no (native) way to get Excel to automatically 're-parse' an incorrectly encoded formula. 're-parsing' is what's required (i.e. reproducing the GUI action of clicking on the green tick button), not 're-evaluating' because re-evaluating does not change the contents of the formula Ptg array. Re-calculating will just produce the same result '#NAME?' because the formula still contains the incorrect NamePtg (as currently encoded by POI). BTW - POI already sets the formula flag 'calcOnLoad' to true by default, and this is obviously making no difference. (Incidentally, I believe this default value of calcOnLoad=true is not always right. Excel certainly does not set this flag in most cases.) I think your attempt to organise all of this functionality into levels is a good idea. May I make a few comments to perhaps refine these a bit: > (1) Assuring that the formulas are not damaged in handling by POI. (1a) Reading a spreadsheet that contains calls to add-in functions, modifying anything but those cells and re-serialising makes a spreadsheet that works perfectly in Excel (1b) HSSFCell.getCellFormula() evaluates correctly for formulas that contain calls to add-in functions. > (2) Assuring that the formulas will re-evaluate when the workbook is > opened in Excel subsequent to being handled by POI. (2a) Writing a call to an add-in function with POI creates a spreadsheet in which Excel displays the right formula text, but does not evaluate properly. Manually forcing a re-parse in Excel causes the formula to be corrected. (2b) POI encodes new formulas with calls to add-in functions correctly, such that the formula works perfectly in Excel. > (3) Evaluating formulas within POI to the extent that all of > Microsoft's built-in functions are supported. > (4) Evaluating formulas within POI to the extent that with some > necessary limitations to OS - Add-in functions are supported. (4a) Evaluating functions from standard Excel add-ins such as the 'Analysis Toolpack'. (4b) Evaluating functions from non-standard but well known excel add-ins such as 'Bloomberg' (4c) Evaluating any arbitrary add-in function from a supplied .xlam or .xll add-in file (VBA / .Net assemblies). - Levels (1a), (1b) and (2a) are done. - Level (2a) is really not that useful, and this is the stopping point for the issue that started this thread. Level (2b) is expected to take quite some work, and is not expected to be done any time soon. - Work is progressing on (3). The architectural pattern is well set, and it is easy to fix bugs or add an implementation for a missing function. - Work is progressing on (4a). Hopefully this part will soon reach a similar state as (3) such that there is a base set of working (standard) add-in functions, and a clear pattern for augmenting this set. (4b) may or may not be feasible depending on how easily the functionality is re-coded/maintained in java. I guess (4c) is where you were referring to 'OS limitations'. I have no requirement for anything beyond (4a), so these will probably only occur is someone else is desperate for that functionality. -josh --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
