INT011 - Create Excel Document
1. In the section of the function containing the comment YOUR OWN LOGIC HERE, add code to clear the employees list, select from the logical file PSLMST1 with keys DEPTMENT and SECTION, number of keys *COMPUTE. Add entries to the employees list.
2. Function iiiFN12 will be called by an RDML functions which checks the requested department and section are valid and that employees exist for this section. This error checking will not be required in iiiFN12.
3. To create the Excel file, you need to use the CREATE command using FORMAT(*XLSX). Create the CREATE command string (JSMXCMD) and execute it using the JSMX_COMMAND BIF:
Review ExcelService in the LANSA Integrator Guide for details. You can open this guide directly from Help / Services in Integrator Studio.
4. To add a sheet you need to use the ADD command using OBJECT(*SHEET) SHEET(Sheet1)
5. Create the WRITE command string to write to Sheet1 in position Row 1, Cell 1 using R1C1(1,1). Execute it with the JSMX_COMMAND BIF. Since the sheet and row/cell parameters are both defaults, they do not need to be specified.
Note: In this case the TO_GET parameter must include the name of the employees working list.
6. To save the data and create the actual file you use the SAVE command using FILE(training/iiiempfn12.xlsx).
The file parameter of the command needs to define the file path (within the JSM instance), file name, and ensure that a new file is created each time (instead of overwriting one file repeatedly). Create the file in the /training subdirectory, with a name beginning iiiemp. Append the current date and time to the file name. The field DATETIMEC contains this value as a character value. The directory must exist.
For a real application you may need to consider how to separate the same output files created by different users.
7. Close the Excel file using the CLOSE command via the JSMX_COMMAND BIF. Note: This is the ExcelService CLOSE command, not the JSMX_CLOSE command.
8. Remember to include code to execute the CHECK_STS subroutine each time you execute the JSMX_COMMAND BIF and don't forgot to pass the parameter JSMXHDLE1.
9. At the end of your logic, if the JSM status (field JSMSTS) is OK, issue a message that the Excel file was created. Note that with RDMLX coding techniques you can easily add the generated Excel file name into this message.
10. Your logic should look like the following:
Function Options(*DIRECT)
Def_List Name(#employs) Fields(#empno #givename #surname #STARTDTER #PHONEHME #salary) Type(*working) Entrys(*max)
* OPEN JSM AND VERIFY STATUS
Use Builtin(jsmx_open) To_Get(#jsmsts #jsmmsg #jsmxhdle1)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
* BUILD THE SERVICE LOAD COMMAND
#jsmxcmd := 'SERVICE_LOAD SERVICE(ExcelService)'
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
* YOUR OWN LOGIC HERE
Clr_List Named(#employs)
Select Fields(#employs) From_File(pslmst1) With_Key(#deptment #section) Nbr_Keys(*compute)
Add_Entry To_List(#employs)
Endselect
* Create Excel File
#jsmxcmd := 'CREATE FORMAT(*XLSX)'
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
* Add Sheet
#jsmxcmd := 'ADD OBJECT(*SHEET) SHEET(Sheet1)'
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg #employs)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
* Write to Excel file
#jsmxcmd := 'WRITE'
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg #employs)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
* Save to Excel file
#jsmxcmd := 'SAVE FILE(training/jsmemp' + #datetimec + '.xlsx)'
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg #employs)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
* Close Excel
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 CLOSE) To_Get(#jsmsts #jsmmsg)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
If (#jsmsts = OK)
MESSAGE MSGTXT('Excel file ' + 'jsmemp' + #datetimec + '.xlsx produced')
Endif
* UNLOAD SERVICE
#JSMXCMD := 'SERVICE_UNLOAD'
USE BUILTIN(JSMX_COMMAND) WITH_ARGS(#JSMXHDLE1 #JSMXCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK_STS) WITH_PARMS(#JSMXHDLE1)
* CLOSE JSM AND VERIFY STATUS
Use Builtin(jsmx_close) With_Args(#jsmxhdle1) To_Get(#jsmsts #jsmmsg)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
Return
*
Subroutine Name(CHECK_STS) Parms(#W_HDLE)
*
Define Field(#MSGDTA) Type(*CHAR) Length(132)
Define Field(#W_HDLE) Type(*CHAR) Length(4)
*
If Cond('#JSMSTS *NE OK')
*
#MSGDTA := 'Error Status Code: ' + #JSMSTS
Message Msgid(DCM9899) Msgf(DC@M01) Msgdta(#MSGDTA)
#MSGDTA := 'Error Message: ' + #JSMMSG
Message Msgid(DCM9899) Msgf(DC@M01) Msgdta(#MSGDTA)
Endif
*
Endroutine
11. Compile function iiiFN12. If you are using the JSM server on your IBM i, check in and compile function iiiFN12.
