Two popular utilities from some years back get updated, and a favorite author from the past resurfaces.

It's been over a decade since I presented two utilities that gave you the ability to create XLS worksheets from an SQL instruction in System i. Now it's time to present a revamped version of these utilities, the new SQL2XLSX command that will allow you to create XLSX worksheets.

I won't discuss details about the differences between XLS and XLSX; let's just say that XLS, created by Microsoft in 2003 (officially designated "Microsoft Excel 97-2003 Worksheet"), is still very popular, but in the Microsoft Office 2007 release, Microsoft decided to change the default file format to XLSX, based on the Office Open XML format.

You can get several benefits from using the new XLSX format; for example, in terms of the file size issue, it is reported that the new XLSX files can be up to 75 percent smaller than the old XLS files. Furthermore, in an XLS workbook, the limit is 65,536 rows and 256 columns while XLSX workbooks limits are increased to 1,048,576 rows and 16,384 columns.

XLSX is stored in a text file format, while XLS was stored in binary format. Therefore, the old macro support for this new file format is no longer supported, and Microsoft assigned a totally different file extension that allows the use of macros; it is named XLSM. The standard XLS file was able to hold spreadsheets that contain macros or not.

You can find more detailed info on Office Open XML format here.

Are you eager to build and try the new SQL2XLSX command that allows you to create both XLSX and XLSM? Let's do that.

In this article, I will give you the code and the instructions needed to create basic XLSX worksheets. You could create XLSM as well; just read the warnings at the end of the article.

You should be able to run this command on your system if your OS is between V5R3 and V7Rxx, provided that you have at least Java 6 installed (if you are still at V5R3/R4, you may need additional jar files, which you can find and download from the web; if you get errors while running the command on old releases, specify the JDEBUG(*YES) option and see what's missing). You can check the current Java version by typing QSH CMD('java -version') from the command line and the Java versions available by typing DSPLNK OBJ('/QOpenSys/QIBM/ProdData/JavaVM/*').

This SQL2XLSX command relies on POI Apache libraries as well, but you will see that if you want to create XLSX worksheets, you need more jars than in the past. You can find all in the official Apache POI - the Java API for Microsoft Documents site.

In order to have this utility running on earlier OS releases, instead of downloading the latest Apache POI library version, we'll use POI 3.9 because this is the latest one that runs with Java 6 on V5R3. A version implemented with the latest (at time of writing) Apache POI 1.17 will be presented in a future article.

That said, you can start to implement this utility by creating a subdirectory in your system where you will put the Apache POI jar files. Provided that in the past you already created the main directory /excel as required in the original SQL2XLS version, now create a new subdirectory in it with MD DIR('/excel/poi-3.9'). If you haven't yet created the main /excel directory, create it and then create the subdirectory in it.

Now connect to the download section of the Apache POI - the Java API for Microsoft Documents site and download the poi-bin-3.9-20121203.zip on your desktop.

Then extract and copy the following jar files into the subdirectory /excel/poi-3.9 you just created:

poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar

Rename them:

poi-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar

You can rename with Navigator or from the command line:

RNM ('/excel/poi-3.9/poi-3.9-20121203.jar') NEWOBJ('poi-3.9.jar')  

...

In the downloaded zip file within the folder ooxml-lib, you will find the following jar files:

dom4j-1.6.1.jar
xmlbeans-2.3.0.jar

You must also extract these and copy them into your subdirectory.

At the end, just to make sure that the required jars are in the right place, use this command to check:

DSPLNK OBJ('/excel/poi-3.9/*') DETAIL(*NAME)

Figure 1: Ensure that your jar files are in your subdirectory.

Now download the zip file of code onto your desktop.

Extract and copy SQL2XLSX(CMD), SQL2XLSXC (CLLE), and SQL2XLSXR (RPGLE) into a source file and compile as usual. Remember to create the SQL2XLSX command with parameter PGM(SQL2XLSXC).

Extract and copy the SQL2XLXNS.java into the /excel directory and create the .class file. To do so, create a CLP source member and paste the following QSH command into it:

QSH CMD('javac +
         -classpath /QIBM/ProdData/OS400/jt400/lib/jt400.jar:+
         /excel/poi-3.9/poi-3.9.jar:+
         /excel/poi-3.9/poi-ooxml-3.9.jar:+
         /excel/jtds.jar:+
         /excel/ojdbc14.jar: +
         -d /excel +
         /excel/SQL2XLXNS.java')

Compile the CLP and run it.

The jtds.jar and ojdbc14.jar are needed if you want to extract data from SQL server or an Oracle database; otherwise, they are not necessary.

That's all. Now you should be able to create XLSX worksheets.

Create the Worksheets

As I mentioned, the command presented here is a revamped version of the original SQL2XLS and SQL2POI commands presented in 2004. SQL2XLSX has some new parameters, but old SQL2XLS/SQL2POI parameters are consistent within both implementations; therefore, you could easily replace old command calls with this new one. In this version, when using templates, you can put the same file name into TOXLS and FROMXLS parameters, which allows you to easily create a multi-sheet XLSX. The new parameter SETLIBL(*LIBL) makes the Java thread run with the library list of the requester.

Just be aware of the following:

• While an XLSX worksheet can hold up to 1,048,576 rows, the command version presented here (the starter kit) runs in XSSF mode; this means that, while the command runs, rows and columns are prepared and kept in memory, but the final XLSX is written to disk, and memory is freed only at the end of the process. This means that you can create your worksheet with a reasonable number of rows (say, like the old XLS limits). In a future article, you will find a SXSSF low-memory version that writes data in a small temporary buffer and flushes them while the buffer is full. Then you will be able to reach the 1,048,576 with a low memory usage without crashing the system.
• The POI 3.9 contains a bug fixed in more POI recent releases: each extraction leaves a poi-sxssf-sheetnnnnnnnnnnn.xml file in the /tmp directory. I suggest you periodically clear them by running the command DEL PARM('poi-sxssf-sheet*.xml').
• When explaining the differences between XLS and XLSX, I've mentioned that the new worksheet with macros differs from the old one. SQL2XLSX preserves existing macros contained in the template as do the old versions. But now, if you plan to create a worksheet with macros, both the template (that has macros inside) and the final file must have the XLSM extension, and you should download the additional poi-ooxml-schemas-1.0.jar file. Consider that this jar file has a size of 14 MB and is not needed for XLSX files.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions