Step 5. Format Start Date Column
INT012 - Create Excel Document with Template and Formatting
One problem with the current solution is the lack of formatting of the Start Date column. Start Date is based on a signed numeric field, 6 digits long, In this step you will convert it to a Date type field and include this in the working list. You will also then be able to format the Start Date column in Excel as Date cells.
1. You will now be reading STARTDTER from the file, converting it and including a date work field in the working list. Change your function to include a GROUP_BY for the fields to be read and use this on the SELECT command. Your code should look like the following:
GROUP_BY NAME(#EMP_GRP) FIELDS(#empno #surname #givename #startdter #phonehme #salary)
. . . . .
SELECT FIELDS(#EMP_GRP) FROM_FILE(pslmst1) WITH_KEY(#deptment #section) NBR_KEYS(*compute)
2. In the working list, replace field STARTDTER with STD_DATEX
3. Within the SELECT loop add logic to:
- Check that STARTDTER is a date (of YYMMDD format)
- If so, convert STARTDTER to field STD_DATEX
- If not, set STD_DATEX to *SQLNULL
Your code should look like the following:
CLR_LIST NAMED(#employs)
SELECT FIELDS(#EMP_GRP) FROM_FILE(pslmst1) WITH_KEY(#deptment #section) NBR_KEYS(*compute)
IF (#STARTDTER.IsDate( YYMMDD ))
#Std_Datex := #STARTDTER.AsDate( YYMMDD )
ELSE
#Std_Datex := *SQLNULL
ENDIF
ADD_ENTRY TO_LIST(#employs)
ENDSELECT
4. Change the DEF_LIST command for EMPLOYS to include a counter using field LISTCOUNT. Your code should look like the following:
Def_List Name(#employs) Fields(#empno #givename #surname #STD_DATEX #PHONEHME #salary) Counter(#listcount) Type(*working) Entrys(100)
5. Add a DEFINE command which formats the Start Date column as a Date. Your code should look like the following:
* Format Start Date Column
#std_num := #listcount + 4
#jsmxcmd := 'DEFINE OBJECT(*CELLFORMAT) COLUMN(5) RANGE(5,' + #std_num.asstring + ') TYPE(*DATE) FORMAT(*FORMAT2)'
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
Once again, ensure the DEFINE command is before the WRITE command for the .
6. You may have noticed when opening the document in Excel that the width of the Salary column needs adjusting because of the size of one or more salaries (e.g. 1,234,456.50). To set the width you need to use OBJECT(*COLUMNVIEW) on the DEFINE command. Your code should look like the following:
* Set Column width - Salary
#jsmxcmd := 'DEFINE OBJECT(*COLUMNVIEW) COLUMN(7) WIDTH(12)'
USE BUILTIN(jsmx_command) WITH_ARGS(#jsmxhdle1 #jsmxcmd) TO_GET(#jsmsts #jsmmsg)
EXECUTE SUBROUTINE(CHECK_STS) With_Parms(#JSMXHDLE1)
7. Recompile function iiiFN14 and retest. Your document should now look like the following:
