Step 6. Insert a Total Salary Formula

INT012 - Create Excel Document with Template and Formatting

In this step you will use the ADD command to insert a formula to total the salary column.

1.      Immediately following the SELECT/ENDSELECT loop which loads the list of employees add code to insert a total line. Your code should look like the following:

* Insert total line

#employs := *null

#surname := 'Salary Total:'

Add_Entry To_List(#employs)

2.      Following the WRITE to Excel logic, insert the following code:

* Add Total Salary Formula

#std_num := (#listcount + 3)

#listcount += 4

*

#jsmxcmd := 'ADD OBJECT(*FORMULA) FORMULA("SUM(G5:G' + #std_num.asstring + ')") R1C1(' + #listcount.asstring + ',7)'

Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg)

Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1) 

Note:

• STD_NUM provides the last row number for Excel SUM formula.

• LISTCOUNT provides the row number to insert the formula. The value of LISTCOUNT has been increased by 4, because the list is inserted into row five of the Excel document.

• The Excel formula must be enclosed in double quotes.

• The formula should not include the "=" symbol. This will be inserted automatically.

• The ADD command supports the insert of formula, image, cell style and hyperlink. See the LANSA Integrator Guide for further details.

3.      Recompile your function and test it to check that your formula has been correctly inserted.