GENERATE_SPREADSHEET now allows me to give sheet a name

Thanks to the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, we can now give our sheets generated by GENERATE_SPREADSHEET real names, rather than "Sheet 1". This only works for Microsoft Excel, XLS and XLSX, and OpenDocument, ODS, spreadsheets.

There is a new parameter for GENERATE_SPREADSHEET, SHEET_NAME, that allows you to give the sheet a name. While there does not appear to be a limit in the number of characters you can use in this parameter, Excel has a limitation of 31 characters for a sheet name. Before you go and start making your sheet name 31 characters you need to read on to understand why your sheet names will need to be less than that.

In my examples I want to generate a list of names in Excel from the PERSON file I have used before. I want to concatenate the last and first names together, and sort the results by name.

Let me jump straight to my first example, generating an Excel spreadsheet in ACS' Run SQL Scripts, RSS.

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/MyFolder/test_1', 03 SPREADSHEET_TYPE => 'xlsx', 04 SPREADSHEET_QUERY => 'select lname || '', '' || fname from rpgpgm1.person order by lname,fname', 05 KILL_DAEMON => 'yes', 06 SHEET_NAME => 'Players')

Line 1: As this is a scalar function, I can use the VALUES statement to run it.

Line 3: The output file type must be lower case. Here I will be generating a XLSX spreadsheet.

Line 4: The SQL statement that generates the results to appear in the spreadsheet.

Line 5: This was introduced in the previous TRs. This will "kill" the Java program that generates the spreadsheet. If you do not do this it will remain in memory.

line 6: This is the new parameter for the sheet name.

When this has finished the following result is shown:

00001 ------ 1

From that I know that the spreadsheet was generated.

When I open the Integrated File System tool, IFS, and navigate the folder /home/MyFolder I can see the XLSX file. When I open it, I see:

The sheet name is "Players>Sheet1", this is why you should not try to use 31-character long names for your sheets.

In the real world you not generate your spreadsheet in ACS RSS, you would have a program execute it for you. This is my example RPG program:

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) actgrp(*caller) ; 03 dcl-proc Main ; 04 dcl-s ReturnCode int(3) ; 05 dcl-s SQL varchar(100) ; 06 SQL = 'select lname || '', '' || fname from person order by lname,fname' ; 07 exec sql SET :ReturnCode = 08 SYSTOOLS.GENERATE_SPREADSHEET( 09 PATH_NAME => '/home/MyFolder/test_2', 10 SPREADSHEET_TYPE => 'xlsx', 11 SPREADSHEET_QUERY => :SQL, 12 KILL_DAEMON => 'yes', 13 SHEET_NAME => 'Players') ; 14 dsply ('Return Code = ' + %char(ReturnCode)) ; 15 return ; 16 end-proc ;

This program uses a Main procedure.

Line 2: My favorite control options.

Line 3: Start of the Main procedure, no RPG cycle processing in this program.

Lines 4 and 5: Definitions of two variables. The first will contain the return code from GENERATE_SPREADSHEET, other will contain the SQL statement that will be executed by GENERATE_SPREADSHEET.

Line 6: I create the SQL statement, same as the one I used before, and place it in the variable SQL.

Lines 7 – 13: A Set statement that will place the return code from GENERATE_SPREADSHEET into the variable ReturnCode.

Line 11: The SQL contained in the RPG variable SQL will be executed.

Line 13: Here is the SHEET_NAME parameter, where I am giving the sheet a name.

Line 14: Display the value in ReturnCode so that I know if GENERATE_SPREADSHEET completed successfully.

After the program is compiled, it is called. And I see:

DSPLY Return Code = 1

Which tells me the spreadsheet was created.

When I open it, I can see that the sheet has been named:

 

You can learn more about the GENERATE_SPREADSHEET SQL scalar function on the IBM website here.

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.