Serving Up Spreadsheets

Published: August 13, 2008

by 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.

Figure 1: Expected Spreadsheet

The Skeleton Spreadsheet

You start by creating a skeleton spreadsheet, as shown in Figure 2. The main points to note are:

• Unnecessary worksheets are deleted and the worksheet is named as required (Stock Valuation)
• Cells are set to required widths and formatted (numeric formats, etc.) as required
• Define one row as it should appear in the spreadsheet
• Extended Value is a formula (C3*D3)
• Total Extended Value is a formula (=SUM(E3:E3))

Figure 2: Skeleton Spreadsheet

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.)

Figure 3: Saving Spreadsheet in XML Format

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):

1. Put a section name (/$start) at the start of the document.
2. Change the value of the ExpandedRowCount attribute in the Table element to reflect the maximum number of rows that can be in the generated spreadsheet. This is very important. If the actual number of rows in the generated spreadsheet exceeds this number, the spreadsheet will not be accessible.
3. Put a section name (/$line) at the start of the product row.
4. Replace the data values for the four cells with variable names (/%ProductCode%/, /%ProductDescription%/, /%stoh%/ and /%buyingprice%/)
5. Put a section name (/$end) after the product row.
6. Change the value (1) of the row reference in the first part of the SUM formula to a variable name /%NumRows%/. Cell references are relative and this row reference will indicate the starting row (relative to the total row) for the sum function.

Figure 4: Editing the XML Spreadsheet for use with CGIDEV2

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:

1. The CGIDEV2 service program (CGISRVPGM2) is listed in the binding directory CGIBNDDIR.
2. Copy directives include the required CGIDEV2 copy members.
3. The XML skeleton spreadsheet is loaded.
4. Start of the document is written.
5. Variables are updated and a row is written for each product.
6. Number of rows for SUM function is updated and the rest of the document is written. Note that the document is written with an extension of xls.

Figure 5: The RPG Program to generate the spreadsheet

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.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot