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 #givename #surname #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(*max)

 

5.  Add an ADD command which formats the Start Date column as a Date. Your code should look like the following:

* Format Start Date Column

#std_num := #listcount + 3

#jsmxcmd := 'ADD OBJECT(*CELLSTYLE) 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 ADD command is before the WRITE command for the ExcelService.

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(*COLUMNWIDTH) on the SET command. Your code should look like the following:

* Set Column width - Salary

#jsmxcmd := 'SET OBJECT(*COLUMNWIDTH) RANGE(7) WIDTH(3260)'

USE BUILTIN(jsmx_command) WITH_ARGS(#jsmxhdle1 #jsmxcmd) TO_GET(#jsmsts #jsmmsg)

EXECUTE SUBROUTINE(CHECK_STS) With_Parms(#JSMXHDLE1)

7.  Recompile function iiiFN13 and retest. Your document should now look like the following: