It seems everyone is suddenly downloading data to MS Excel. Could it be a tax-time issue? I don't know. But questions about how to do it have come up on the Web about a dozen times in the last month or so.

You have several methods for sending data to the PC so that it can be opened with a spreadsheet program, such as MS Excel. One way is to use Client Access (is that its current name?) to transfer the data to the PC; just specify the BIFF7 or BIFF8 file format for the output file. Another option is to use the ODBC plug-in for Excel that comes with Client Access. A third way is to write something yourself, which is advisable only if you have customized formatting or selection criteria. A fourth way is to use a third-party tool.

Third-Party Solutions

Several third-party tools copy data to MS Excel. One allows you to write directly to native Excel .xls format. This package from the Jakarta POI project is written in Java and can be integrated into RPG IV, provided you have strong Java and RPG IV subprocedure/prototyping skills.

If you're a hacker and can decipher MS technical specifications, consider looking at the OpenOffice document, which contains a lot of the specification. It's interesting but probably more than an RPG IV programmer cares to discover.

Another popular solution is ASC's SEQUEL. SEQUEL makes it painlessly easy to drill down to your data and then link it into MS Excel. You can view the data or email the resulting Excel file to someone on your network.

A product that is becoming one of the fastest-growing iSeries packages is Linoma Software's Transfer Anywhere. It allows you to convert data to Excel format and send it, as the name implies, just about anywhere. Transfer Anywhere also offers encryption and data compression if those feature are important to your shop.

Another choice is Goering's iExcelGen. This package allows you to create an Excel document using a CL command. It converts spool files and database files to Excel format.

If you need to convert data to Excel in batch, you may want to look at KemeTECH's ConversionSuite. This package is a collection of utilities that convert database files and spool files to MS Excel 2000 file format.

There is also Gumbo Software's Excel-erator. This package converts database files to Excel format and stores them on the IFS or allows you to send them as email attachments.

Finally, my own RPG xTools package includes a CPYTOCSV CL command that allows you to copy database files to CSV format. Obviously, CSV format is not native Excel format, and if you need Excel format, you should strongly consider one of the above packages. If all you need is CSV, however, RPG xTools has a very flexible set of subprocedures that allows you to either convert an entire file or selectively convert records to CSV within your RPG IV program.

Whichever method you choose, providing this capability to your users seems to be a critical requirement today. I recommend you use one of the packages listed above to make it easier. The Jakarta POI option should probably be your last resort. Unless you're skilled at integrating Java with RPG IV, using POI will probably cost your company much more in terms of time and value than simply licensing one of the third-party solutions.

Bob Cozzi is a programmer/consultant, writer/author, and software developer. His popular RPG xTools add-on subprocedure library for RPG IV is fast becoming a standard with RPG developers. His book The Modern RPG Language has been the most widely used RPG programming book for more than a decade. He, along with others, speaks at and produces the highly popular RPG World conference for RPG programmers.

---

MC Press books written by Robert Cozzi available now on the MC Press Bookstore.

		RPG TnT Get this jam-packed resource of quick, easy-to-implement RPG tips! List Price $65.00 Now On Sale
		The Modern RPG IV Language Cozzi on everything RPG! What more could you want? List Price $99.95 Now On Sale