Step 4. Add ADD Command to Format Output

INT012 - Create Excel Document with Template and Formatting

1.  Review the ADD command of the ExcelService in the LANSA Integrator Guide:

     Note that the ADD command must be used to set the cell format before using the WRITE command.

2.  Based on the information provided in the LANSA Integrator Guide, consider how to format the Salary column as a number with 2 decimal places. You will usually need to use a number of ADD commands to format different aspects of your Excel document. In this case you will need to use keywords OBJECT, COLUMN, RANGE, TYPE and FORMAT. Your added code in iiiFN13 should look like the following:

#jsmxcmd := 'ADD OBJECT(*CELLSTYLE) COLUMN(7) RANGE(5, 38) TYPE(*NUMBER) FORMAT(*FORMAT39)'

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

Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)  

     Remember to add this code before the ExcelService WRITE command is processed.

3.  Recompile function iiiFN13 and retest.

4.  Open the document with Excel and check your results. Your results should look like the following:

     Note that the Salary column is now formatted correctly, but the right hand border for the Salary column has been lost.

     You can check the format of the Salary column by selecting the cells and using the right mouse menu / Format Cells.

5.  You can extend the existing ADD command to add a thin border to the right hand side of the Salary column. To do this add BORDERTYPE and BORDER keywords to the ADD command. Your code should look like the following:

#jsmxcmd := 'ADD OBJECT(*CELLSTYLE) COLUMN(7) RANGE(5,38) TYPE(*NUMBER) FORMAT(*FORMAT39) BORDERSTYLE(*THIN) BORDER(*RIGHT)'

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

EXECUTE SUBROUTINE(CHECK_STS) With_Parms(#JSMXHDLE1)