An open-source API changes IBM i data to Excel spreadsheets
Several years ago, I wrote “Creating Spreadsheets with an Open-Source Utility,” which discussed creating a spreadsheet from System i data. I showed how to use the JExcel open-source API to manipulate System i data and make a true Microsoft Excel spreadsheet from an i database file. Please refer to the original article for the reasons I chose JExcel and how the code was originally written.
The MKXL (Make XLS) series of utility programs use CL (and commands), RPG and Java to create the spreadsheet. The CL programs (and commands) control the overall flow of the application, and the RPG program provides the data gathering (headings and data) from the IBM i database file. The key to the MKXL application is the MakeXLS Java program. This is the program that uses the JExcel open-source utility APIs to transform the data gathered in the CL and RPG programs into the spreadsheet.
I published this to provide a free utility to create Microsoft Excel spreadsheets, and as a way for people to understand the JExcel open-source utility and use it for their unique business needs. I’m one of the people who use this utility. In fact, I initially wrote this utility for a real business reason, and it’s in use today for different applications. When the article first appeared, I received several e-mails from readers with questions about implementing JExcel. This is one of the most gratifying parts of writing articles—being able to communicate with and help people who read them.
So imagine my surprise when I recently received three e-mails within a couple of days asking about JExcel. Since the article appeared in 2005, I hadn’t expected to get new questions about it. The e-mails demonstrated that people wanted to implement the utility but they had questions. A revision was in order. Note the questions refer back to the original article so you’ll want to review it first.
The first question regarded the level of JExcel code. A reader had downloaded the latest version of the JExcel API (JExcelApi v2.6.12) from Andy Khan’s Web site but when she attempted to create the class file from the MakeXML.java code, with this command:
javac -classpath /JavaUtil/jexcelapi/jxl.jar MakeXLS.java
it produced the following error message:
MakeXLS.java:23: cannot access jxl.write.NumberFormats
bad class file:
class file has wrong version 50.0, should be 49.0
Please remove or make sure it appears in the correct subdirectory of the classpath.
This error message is an indication that the MakeXLS.java code hasn’t kept up with the changes to the JExcel API. You can do two things: implement the version of JExcelAPI I used to create MakeXLS.java or change the MakeXLS code to make it work with the latest version. I chose the former. If you want to implement the MKXL utility, use version JExcelApi v2.5.1.