This example introduces some of Launcher’s functions with Excel.
Sheet management
We first insert a new sheet with the XLADDSHEET command, then we change its name with the XLSHNAME command. With LAUNCHER Office you can also copy or delete sheets from the workbook.
Data insertion
Among all the commands to fill and insert data in a sheet, the easiest and most practical is XLSETLINE witch fills the entire line in one operation.
The first parameter contains the values, separated by the %SEP% keyword.
The second parameter controls each cell’s formatting.
We first write a header line in bold characters then 2 value lines, the second one formatted with 2 decimal places with the NUMFMT(2).
Graph insertion
Once the table has been filled in, we create a graph using the XLDRAWGR command in a new sheet. We choose an AREA-type graph, but any of Excel’s graph types may be used.
Then we change some of the graph’s properties, its name and the name of the series, with the XLSETPROP command.
Finally, after all these operations have been performed, we can make Excel visible with the EXCELSHOW command.
This is the CL source of the example
PGM
DCL VAR(&SVRADDR) TYPE(*CHAR) LEN(30)
/* DECLARATION OF VARIABLES NEEDED FOR A CONNECTION TO THE SERVER */
DCL VAR(&HANDLE) TYPE(*CHAR) LEN(50)
DCL VAR(&CCSID) TYPE(*CHAR) LEN(10)
DCL VAR(&CMD) TYPE(*CHAR) LEN(10)
DCL VAR(&OPT) TYPE(*CHAR) LEN(1)
DCL VAR(&PARM1) TYPE(*CHAR) LEN(512)
DCL VAR(&PARM2) TYPE(*CHAR) LEN(1024)
DCL VAR(&RESULT) TYPE(*CHAR) LEN(512)
DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
DCL VAR(&MSG1) TYPE(*CHAR) LEN(80)
DCL VAR(&MSG2) TYPE(*CHAR) LEN(80)
CHGVAR VAR(&SVRADDR) VALUE('*DEV')
/* OPEN ONLY ONE CONVERSATION WITH THE SERVER */
CHGVAR VAR(&HANDLE) VALUE('*ONLY')
/* TRANSLATE THE CCSID ACCORDING TO THE JOB CCSOD */
CHGVAR VAR(&CCSID) VALUE('*JOB')
/* CONNECTION WITH THE SERVER */
CALL PGM(LNCOPEN) PARM(&HANDLE &SVRADDR &CCSID)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* OPENING EXCEL */
CHGVAR VAR(&CMD) VALUE('EXCELOPEN ')
CHGVAR VAR(&PARM1) VALUE(' ')
CHGVAR VAR(&PARM2) VALUE(' ')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* ADD A NEW SHEET */
CHGVAR VAR(&CMD) VALUE('XLADDSHEET ')
CHGVAR VAR(&PARM1) VALUE(' ')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* RENAME THE SHEET */
CHGVAR VAR(&CMD) VALUE('XLSHNAME ')
CHGVAR VAR(&PARM1) VALUE('VENTES 1ER TRIMESTRE')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* INSERT FIRST LINE WITH HEADERS */
CHGVAR VAR(&CMD) VALUE('XLSETLINE ')
CHGVAR VAR(&PARM1) VALUE('JANVIER%SEP%FEVRIER%SEP%MARS')
CHGVAR VAR(&PARM2) VALUE('PROP(FONT.BOLD)=
PROP(FONT.BOLD)=
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* FIRST DATA LINE */
CHGVAR VAR(&CMD) VALUE('XLSETLINE ')
CHGVAR VAR(&PARM1) VALUE('000123%SEP%22250%SEP%301')
CHGVAR VAR(&PARM2) VALUE('FORMAT()%SEP%NUMFMT(2)')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* SECOND DATA LINE */
CHGVAR VAR(&PARM1) VALUE('223%SEP%23250%SEP%285')
CHGVAR VAR(&PARM2) VALUE('FORMAT()%SEP%NUMFMT(2)')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* INSERT THE GRAPHIC */
CHGVAR VAR(&CMD) VALUE('XLDRAWGR ')
CHGVAR VAR(&PARM1) VALUE('A1:C3')
CHGVAR VAR(&PARM2) VALUE('''AREA'' ''ROWS'' ''1'' ''0'' ''+
MARKET'' ')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* CHANGING THE GRAPHS TITLE */
CHGVAR VAR(&CMD) VALUE('XLSETPROP')
CHGVAR VAR(&PARM1) VALUE('ACTIVECHART.CHARTTITLE.+
CHARACTERS.TEXT')
CHGVAR VAR(&PARM2) VALUE('VENTES TRIMESTRE 1')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* CHANGE FIRST SERIE NAME */
CHGVAR VAR(&CMD) VALUE('XLSETPROP')
CHGVAR VAR(&PARM1) VALUE('ACTIVECHART.+
SERIESCOLLECTION(1).NAME')
CHGVAR VAR(&PARM2) VALUE('PRODUIT A')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* SECOND SERIE */
CHGVAR VAR(&CMD) VALUE('XLSETPROP')
CHGVAR VAR(&PARM1) VALUE('ACTIVECHART.SERIESCOLLECTION(2)+
.NAME')
CHGVAR VAR(&PARM2) VALUE('PRODUIT B')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* MAKE EXCEL VISIBLE */
CHGVAR VAR(&CMD) VALUE('EXCELSHOW ')
CHGVAR VAR(&PARM1) VALUE(' ')
CHGVAR VAR(&PARM2) VALUE(' ')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* END */
CHGVAR VAR(&CMD) VALUE('END ')
CHGVAR VAR(&PARM1) VALUE(' ')
CHGVAR VAR(&PARM2) VALUE(' ')
CALL PGM(LNCCMD) PARM(&HANDLE &CMD &OPT +
&PARM1 &PARM2 &RESULT)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
/* END OF COMMUNICATION...*/
CALL PGM(LNCCLOSE) PARM(&HANDLE)
MONMSG MSGID(LNC0000) EXEC(GOTO CMDLBL(ERROR))
GOTO END
ERROR:
RCVMSG MSG(&MSG1) SECLVL(&MSG2) MSGID(&MSGID)
SNDPGMMSG MSGID(CPF9898) MSGF(QSYS/QCPFMSG) +
MSGDTA('ERROR !!!!! ' *CAT &MSGID *CAT ' +
' *CAT &MSG1 *CAT ' ' *CAT &MSG2) +
MSGTYPE(*ESCAPE)
END:
ENDPGM