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. Before the WRITE to Excel logic, add a DEFINE command to format cells in the total line. This needs to format the cell in the Start Date column, so that it is blank on the total line. Add the following code:
* Format total line
#std_num := #listcount + 4
#jsmxcmd := 'DEFINE OBJECT(*CELLFORMAT) COLUMN(5) RANGE(' + #std_num.asstring + ',' + #std_num.asstring + ') TYPE(*BLANK)'
Use Builtin(jsmx_command) With_Args(#jsmxhdle1 #jsmxcmd) To_Get(#jsmsts #jsmmsg)
Execute Subroutine(CHECK_STS) With_Parms(#JSMXHDLE1)
Review the above code.
- The RANGE parameter needs to contain the range of rows to be formatted. It must be of the form RANGE(n,n)
- The total row position needs to be calculated based on the number of lines added to the employees working list
3. 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)
- 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, setting and hyperlink. See the for further details.
4. Recompile your function and test it to check that your formula has been correctly inserted.