RE: XLS from RPGLE

Storing both may work for me. I know they want the formulas in some sheets because they will adjust numbers to see "what if". Now to see how they feel about saving changes they did not make so some people can see the data on their phones. Some of these formulas are not going to fun to evaluate manually, but thanks for giving me another option.

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of John Yeung
Sent: Wednesday, April 06, 2016 3:59 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: XLS from RPGLE

Oh, so I forgot to mention what to do about it.

My recommendation is: if the file is just for viewing and the formulas are not very important, then do the calculation yourself, write the value, and don't bother writing any Excel formulas. I think in most cases, the value is much more important than the formula. Hey, if you were scraping a spooled file into Excel, or copying a physical file into Excel, all you'd get is the value anyway.

If the formula is important, or at least helpful (because people will actually be opening the file in Excel and playing around with it), and you *can* write both the formula and the value, then of course do that. (Just be aware that if you open such a file in the genuine article Excel, and then without ANY input on your part, you decide to close out of it, Excel will ask you if you want to save your changes.
What changes? You didn't touch anything! Well, Excel can recognize that the values stored in the file are "dirty" and so when it recalculates everything anyway, those recalculations count as changes to the sheet. I think in principle it should be possible for a third-party writer to spoof the file so that Excel thinks the stored values are its own, but I have not heard of any packages that do this by default, nor have I seen any that expose and document an easy way to do this yourself. I think I sort of managed to do it once with Python and XlsxWriter, though I can't say I really know what I'm doing in this regard, and I may well have broken something else in the process, I don't remember.)

John Y.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.midrange.com_mailman_listinfo_rpg400-2Dl&d=CwICAg&c=S6jE3hMA0s16gx2_CUBBn4vjc1odLunI6MSvd_AfdV8&r=Ub_YbVSedaHB7-0yGANN94iTrfkU2nCKT1D5-5Jv01Y&m=myD1j2UTdCeOD91DytZwJGoFcNXErlW3IJI6MyYFuWw&s=d9b-pWyc6dSI0iUtfWbUwrJsfIRM1XvalptGIXwvRX0&e=
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=http-3A__archive.midrange.com_rpg400-2Dl&d=CwICAg&c=S6jE3hMA0s16gx2_CUBBn4vjc1odLunI6MSvd_AfdV8&r=Ub_YbVSedaHB7-0yGANN94iTrfkU2nCKT1D5-5Jv01Y&m=myD1j2UTdCeOD91DytZwJGoFcNXErlW3IJI6MyYFuWw&s=oo8iWjREY35XC68mXfatAM3KckjJ4kijLLbWpvRhHcE&e= .

Please contact support@xxxxxxxxxxxx for any subscription related questions.