Serving Up Spreadsheets
August 13, 2008 Paul Tuohy
Our users are always looking for information in different formats. They want to see it on a screen, in a report, in a PDF document, in an email, or in a spreadsheet. There are many ways of achieving all of these but most require third-party products or quite a bit of study to master a new programming technique.
Well, there is a very easy way to generate the spreadsheets your users require using nothing more then Excel, RPG, CGIDEV2, and a little knowledge of XML (no experience required).
Excel (as of Excel 2002, I believe) allows you to save a spreadsheet in an XML format. This allows you to create a “skeleton” spreadsheet (using Excel), input that skeleton spreadsheet to an RPG program, and generate another Excel spreadsheet (also in XML format).
CGIDEV2 is a free tool that you can download at www.easy400.net. CGIDEV2 is best known for allowing RPG programs to interact with Web pages, but it can also be used to create a document in the IFS.
The best way to learn is to look at an example.
The Required Result
Figure 1 shows the spreadsheet I want to create. It lists products, their descriptions, the stock on hand, and the value of a single product. The extended value for each product is calculated (stock on hand multiplied by value) and the extended values are summarized.
The Skeleton Spreadsheet
You start by creating a skeleton spreadsheet, as shown in Figure 2. The main points to note are:
Select Save As to save the spreadsheet. In the resulting window (shown in Figure 3) select XML Spreadsheet as the Save As Type and give the spreadsheet a name of your choice. (StockValuationSkeleton.xml in this example.)
You now have an XML document that requires a few minor changes.
Changing the XML Skeleton Spreadsheet
Edit the XML Skeleton spreadsheet using any text editor of your choice–even Notepad. You need to change the XML to include the required CGIDEV2 section and variable names, and you’ll have to make one little tweak to ensure the generated spreadsheet may be loaded. There is a lot of XML but very few changes to be made. The changes are as follows (please refer to the corresponding numbers in Figure 4):
Click here to download the code shown in Figure 4.
Save the XML document and copy it to a directory in the IFS
The RPG Program
The RPG program is a standard CGIDEV2 program, as shown in Figure 5. The main points to note are:
Click here to download the code shown in Figure 5.
You may simply open the generated document with Excel and you have the spreadsheet shown in Figure 1.
In the End
CGIDEV2 provides a very simple means of generating Excel spreadsheets. All you have to do is get your hands on CGIDEV2 and start coding.
If you are already familiar with CGIDEV2 you will be generating spreadsheets in next to no time. This example took me 15 minutes to put together (creating the skeleton, changing it and writing the RPG program) and I’m sure it would have been quicker if I just had a better understanding of Excel!
Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.