It has been possible to create a Microsoft compatible Excel spreadsheet using SQL since the last Technology Refreshes. In my example I demonstrated how you could do this within a RPG program.
A few months have passed and I have been asked how to do the same within a CL program. With a minor change I will show it is possible.
Let me start by showing the code for this program:
01 DLTF FILE(QTEMP/STDOUT)
02 MONMSG MSGID(CPF0000)
03 OVRDBF FILE(STDOUT) TOFILE(QTEMP/STDOUT) OVRSCOPE(*JOB)
04 DLTF FILE(QTEMP/RETURNS)
05 MONMSG MSGID(CPF0000)
06 RUNSQL SQL('CREATE TABLE QTEMP.RETURNS +
07 (RTN_CODE) AS +
08 (SELECT SYSTOOLS.GENERATE_SPREADSHEET(+
09 PATH_NAME => ''/home/MyFolder/data'',+
10 LIBRARY_NAME => ''MYLIB'',+
11 FILE_NAME => ''TESTFILE'',+
12 SPREADSHEET_TYPE => ''xlsx'',+
13 COLUMN_HEADINGS => ''LABEL'') +
14 FROM SYSIBM.SYSDUMMY1) +
15 WITH DATA') +
16 COMMIT(*NONE)
17 DLTOVR FILE(STDOUT) LVL(*JOB)
|
Line 1: I need to have a file to contain the standard output created by the Java program called by the SQL. I always place the standard output file in QTEMP as most of the time I am not interested in its contents.
Line 3: I need to override the standard output to the file in QTEMP. I don't have to create it the Java program will do that. I do have to override at the job level. If I don't do that then the override effect the Java program.
Line 4: The file RETURNS will contain the return code returned from the GENERATE_SPREADSHEET scalar function.
Lines 6 – 16: I use a CREATE TABLE statement to create table that will contain the return code from the GENERATE_SPREADSHEET. It contains just one column, RTN_CODE, which is the return code from the next part of this statement.
Line 8: I am using Select statement with the GENERATE_SPREADSHEET scalar function. A Select statement requires a file to be defined. I have used IBM's SYSDUMMY1 file which is in the library SYSIBM, line 14. For a Select statement like this I like to use this file as every IBM i partition should have it.
Line 9: The Excel file will be generated in my folder with the name data.xlsx, from the data in a file in my library, lines 10 and 11.
Line 13: I want the spreadsheet columns to have same names as the columns' labels from TESTFILE.
When this statement is executed the file RETURNS in QTEMP will contain the return code from GENERATE_SPREADSHEET.
Note that all of the values use by GENERATE_SPREADSHEET parameters need to have two leading and trailing apostrophes ( ' ), this is not a double quote ( " ).
Line 17: As the spreadsheet has been created, I can delete the override for the standard output.
Now you have a choice. You can either create the spreadsheet from a RPG or a CL program.
This article was written for IBM i 7.5 TR3 and 7.4 TR9.
You could use VALUES instead of SELECT, i.e. VALUES SYSTOOLS.GENERATE_SPREADSHEET( and avoid create a temporary table.
ReplyDelete