Step 2. Complete function iiiFN12
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 OPEN command using *WRITE mode. Create the OPEN command string (JSMXCMD) and execute it using the JSMX_COMMAND BIF:
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.
Review in the guide for details. You can open this guide directly from in .
4. Create the WRITE command string to write to Sheet1 in position Row 1, Cell 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.
5. Close the Excel file using the CLOSE command via the JSMX_COMMAND BIF. Note: This is the ExcelReadService CLOSE command, not the JSMX_CLOSE command.
6. Remember to include code to execute the CHECK_STS subroutine each time you execute the JSMX_COMMAND BIF.
7. 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.
8. Your logic should look like the following:
FUNCTION OPTIONS(*DIRECT)
DEF_LIST NAME(#employs) FIELDS(#empno #givename #surname #STARTDTER #PHONEHME #salary) TYPE(*working) ENTRYS(100)
* OPEN JSM AND VERIFY STATUS
USE BUILTIN(jsmx_open) TO_GET(#jsmsts #jsmmsg #jsmxhdle1)
EXECUTE SUBROUTINE(CHECK_STS)
* BUILD THE SERVICE LOAD COMMAND
#jsmxcmd := 'SERVICE_LOAD SERVICE(ExcelReadService)'
USE BUILTIN(jsmx_command) WITH_ARGS(#jsmxhdle1 #jsmxcmd) TO_GET(#jsmsts #jsmmsg)
EXECUTE SUBROUTINE(CHECK_STS)
* 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 := 'OPEN FILE(training/iiiemp' + #datetimec + '.XLS) MODE(*WRITE)'
USE BUILTIN(jsmx_command) WITH_ARGS(#jsmxhdle1 #jsmxcmd) TO_GET(#jsmsts #jsmmsg)
EXECUTE SUBROUTINE(CHECK_STS)
* Write to Excel file
#jsmxcmd := 'WRITE'
USE BUILTIN(jsmx_command) WITH_ARGS(#jsmxhdle1 #jsmxcmd) TO_GET(#jsmsts #jsmmsg #employs)
EXECUTE SUBROUTINE(CHECK_STS)
* Close Excel
USE BUILTIN(jsmx_command) WITH_ARGS(#jsmxhdle1 CLOSE) TO_GET(#jsmsts #jsmmsg)
EXECUTE SUBROUTINE(CHECK_STS)
IF (#jsmsts = OK)
MESSAGE MSGTXT('Excel file ' + 'iiiemp' + #datetimec + '.xls produced')
ENDIF
* UNLOAD SERVICE
USE BUILTIN(jsmx_command) WITH_ARGS(#jsmxhdle1 SERVICE_UNLOAD) TO_GET(#jsmsts #jsmmsg)
EXECUTE SUBROUTINE(CHECK_STS)
* CLOSE JSM AND VERIFY STATUS
USE BUILTIN(jsmx_close) WITH_ARGS(#jsmxhdle1) TO_GET(#jsmsts #jsmmsg)
EXECUTE SUBROUTINE(CHECK_STS)
RETURN
*
SUBROUTINE NAME(CHECK_STS)
DEFINE FIELD(#MSGDTA) TYPE(*CHAR) LENGTH(132)
IF COND('#JSMSTS *NE OK')
USE BUILTIN(BCONCAT) WITH_ARGS('Error Status Code: ' #JSMSTS) TO_GET(#MSGDTA)
MESSAGE MSGID(DCM9899) MSGF(DC@M01) MSGDTA(#MSGDTA)
USE BUILTIN(BCONCAT) WITH_ARGS('Error Message: ' #JSMMSG) TO_GET(#MSGDTA)
MESSAGE MSGID(DCM9899) MSGF(DC@M01) MSGDTA(#MSGDTA)
ENDIF
ENDROUTINE
9. Compile function iiiFN12. If you are using the JSM server on your IBM i, check in and compile function iiiFN12.