A few years back IBM created the SYSTOOLS.GENERATE_SPREADSHEET SQL function which wraps around the IBM i Access acsbundle.jar Java JAR file to generate Excel spreadsheets from a database file. This function is typically used from RPG applications to create spreadsheets.
Internally the Java functionality uses the Apache POI JAR file to create the actual spreadsheet output to an IFs file.
But what about CL, RPG's distant cousin.
I discovered a post from Scott Schollenberger over on TechXchange where he used my QSHONI utility to create a wrapper process to export an IBM i database table to an Excel file without needing RPG. I had seen this technique before but hadn't used it until this weekend.
Essentially he called the acsbundle.jar file using Java and the appropriate command line parameters and the QSHEXEC command which is my high level wrapper around QShell and PASE calls.
After mixing the appropriate elements a spreadsheet complete with column heading magically appears in IFS.
If you have a need to use the GENERATE_SPREADSHEET style functionality from a CL program or command, check out the GENXLSACS command I created. This might save you several hours of programming output to Excel files.
You can also marry this command with DB2 services or other summary data by first generating an output file (OUTFILE) from an SQL query using a command such as QSHQRYTMP or QSHQRYSRC from my QShell on i utilities. These two commands allow you to run an SQL query with or without soft coded parameter data and output the results to an output file in the QTEMP library or elsewhere.
