• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Focal Point Solutions Group

    A CloudSAFE Company

    The Power of Services:
    IBM Cloud and Managed Solutions

    Upgrade your business processes, and save time and resources with specialized, best-in-class IT solutions.

    Managed, Cloud, and Custom Solutions

    Managed Services

    • Infrastructure Monitoring & Management
    • Server Patching
    • Application Patching
    • Managed Backup
    • High Availability/Disaster Recovery Monitoring
    • Cloud Environment Monitoring
    • Office 365 Management
    • Endpoint Management
    • Managed Colocation

    Cloud Infrastructure

    • IBM i Private Cloud
    • IBM AIX Private Cloud
    • VMware Private Cloud
    • VMware Cloud Director
    • Multi-Tenant Cloud
    • Desktop as a Service

    Data Protection & High Availability

    • Disaster Recovery as a Service
    • Backup as a Service
    • IBM i Vaulting

    Security

    • Security Consulting
    • Remote Security Awareness Training & Education
    • Onsite Security Awareness Training & Education
    • Phishing Tests
    • Penetration Tests
    • Mail Security
    • Managed Detection & Response
    • Managed Firewall
    • Endpoint Protection
    • Vulnerability Management
    • Vendor Risk Assessments
    • Security Risk Assessments

    Professional Services

    • Server Virtualization
    • Data & Infrastructure Migrations
    • Hardware & Software Installation
    • Microsoft Office 365 Implementation & Migration
    • Infrastructure Assessments
    • IBM i Consulting
    • IBM AIX Consulting

    Focal Point provides all the tools you need to protect your data, ensure the integrity of your IT infrastructure, and keep your business running.

    Contact Focal Point to Learn More About Our IBM Solutions and Partnerships

    Follow us on LinkedIn

    focalpointsg.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    looksoftware:  snap the best back-end into the coolest front-end
    Computer Measurement Group:  CMG '08 International Conference, December 7-12, Las Vegas
    COMMON:  Join us at the Focus 2008 workshop conference, October 5 - 8, in San Francisco, California

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    New President Onboard at BluePhoenix PowerTech Acquired by Help/Systems, Private Equity Firm

    Leave a Reply Cancel reply

Volume 8, Number 29 -- August 13, 2008
THIS ISSUE SPONSORED BY:

WorksRight Software
Help/Systems
Group8 Security

Table of Contents

  • Serving Up Spreadsheets
  • V6R1 Enhancements for Run SQL Scripts
  • Admin Alert: Common Mistakes When Failing Over to a CBU

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Development and Modernization is Getting A Fresche Start with Some Ground-Breaking Subscriptions
  • CloudSAFE And Focal Point Solutions Group Combine Services, Unify Brands
  • Guru: Partitioning Result Sets Using SQL
  • As I See It: Elusive Connections
  • IBM i PTF Guide, Volume 25, Number 47
  • AWS Inks Deal With Connectria To Have a Power Play
  • IBM i Shops Have Alternatives to Db2 Web Query
  • Eradani Lays Waste to API Payload Restrictions
  • Four Hundred Monitor, November 15
  • Old PHP and Other PASE Apps Break on IBM i 7.5

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle