Re: XLS from RPGLE

On Thu, Apr 7, 2016 at 9:16 AM, Darren Strong <darren@xxxxxxxxx> wrote:

Well, this is the prototype and call example, but I still get the message
about "You need to save your changes" when I close the generated
spreadsheet.

This is normal. I was probably not clear enough in my post which discusses this.

There are very obscure flags in the Excel file which Excel itself uses
to check whether the stored values (the POI site and others refer to
them as "cached" values) were calculated by Excel. So you can
calculate a result (using RPG or whatever) and get exactly the same
result that Excel would have gotten[1], and you can write that result
to your file as a cached value; but if you don't set those flags
properly, Excel will not trust any of the cached values.

Naturally, once Excel has opened the file and recalculated everything
for itself, to its own satisfaction (even if all the values it comes
up with turn out to be identical to the values you wrote into the
file), it will then set the magic flags that indicate the last
recalculation was performed by Excel, and *that* will constitute a
"change" to the workbook.

You can think of it as Excel signing the document to indicate its
approval. What I was trying to get across was: I have not heard of any
third-party software which properly forges Excel's signature on the
document.

In principle, I do not believe it should be difficult to do. But it's
both obscure (underdocumented, if not undocumented) and relatively
low-priority in terms of where to allocate limited development and
maintenance resources.

John Y.


[1]For simple calculations like 4.0 + 3.0, the value you come up with
will be bit-for-bit identical to what Excel comes up with (7.0 in this
case). But keep in mind that *every* numerical value in Excel is a
float, and that has implications for representation of values and
arithmetic on those values. Now, you may be aware of the differences
between float and IBM's decimal types, and you may be careful to use
float in your calculations. But even then, you still have to contend
with the fact that Excel's floats don't *strictly* adhere to IEEE 754.
Excel uses its own subtle tweaks. So there can be cases where you will
have done everything you can, and it still won't be bit-for-bit
identical to Excel's result.