On Thu, May 1, 2014 at 4:49 PM, (WalzCraft) Jerry Forss
<JForss@xxxxxxxxxxxxx> wrote:
I was just pointing out that when a cell contains a formula
that you get the formula, not the result of the formula.
This seems strange to me.
I'm not a POI user, but my experience with other Excel-reading
packages (using Python, not Java) is that Excel saves both the formula
and the result. Now, it actually can be quite a bit of trouble to
read the formula (it's not saved in a way that most of us would find
intuitive), so if you can only get one, it's usually the result.
Some caveats and subtleties:
The .xlsx format, while not a thing of beauty, is WAY easier to work
with than the .xls format. So packages that read .xlsx are a little
more likely to be able to give you both the formula and the result
than packages that read .xls. (Just talking in general terms here.
Some packages are overachievers. Some are... well, let's just say
they were created to scratch an itch, and their authors didn't find
formulas itchy enough to be worth scratching.)
There is often a difference between files that are written by
Microsoft Excel and those that are written by third-party packages.
One classic and big way is that files containing formulas, when
written by third-party packages, often do NOT contain the results of
formulas. This is because there is no guarantee that the third-party
package will be able to evaluate Excel's built-in functions exactly
the same way that Excel does. Thus the recommendation (from Microsoft
itself, actually) is for third-party packages to write the formula,
set the value to zero, and set the global "auto-recalculate" flag on
(so if the file is opened in Excel, Excel will go ahead and calculate
all formula results automatically).
Thus, what you're describing (that formulas are available but results
are not) sounds like what I would expect to be generated by POI, not
by Excel.
John
midrange.com
