Here, we are going to use the cell names to fill in the data for the document’s header.
The table will be filled using the XLSETLINE command.
Then, a macro will try to draw a border for the table.
Excel program source (in RPG language)
********************************************************************
* Files used : SP_CUST --> Customers file *
* SP_ORD_ID --> Logical file on ORDERS file *
* SP_DET_OR --> Logical file on DETAIL file *
********************************************************************
FSP_CUST if e k disk
FSP_ORD_ID if e k disk
FSP_DET_OR if e k disk
********************************************************************
* Declaration of the variables used by LAUNCHER/400. Take a look *
* at the user's guide to know how to use them. *
********************************************************************
DHANDLE s 50 inz('*ONLY')
DSVRADDR s 30 inz('*DEV')
DCCSID s 10 inz('*JOB')
DLNCCMD s 10 inz(*blanks)
DLNCPARM1 s 512 inz(*blanks)
DLNCPARM2 s 1024 inz(*blanks)
DLNCOPT s 1 inz(*blanks)
DLNCRESULT s 512 inz(*blanks)
********************************************************************
* Variables used by the program *
********************************************************************
DPREMIER s 9 inz(*blanks)
DDATECMD s d datfmt(*EUR)
DDATECMDS s 10 inz(*blanks)
DPRIXUP s 8p 0 inz(0)
DPRIXTP s 8p 0 inz(0)
DPRIXU s 8 inz(*blanks)
DPRIXT s 8 inz(*blanks)
DQTE s 10 inz(*blanks)
DRECORD s 9
********************************************************************
* Program entry point, defining parameters *
********************************************************************
C *ENTRY PLIST
c premier PARM record
*
* Show the screen and the data corresponding to the parameters
*
C PREMIER chain SP_CUST 90
*
* Connecting to LAUNCHER/400. The server is addressed by the variable
* SVRADDR. *DEV means that the server is the who emulates the
* screen (when using a 5250 emulation screen).
*
c call 'LNCOPEN'
c parm handle
c parm svraddr
c parm ccsid
*-------------------------------------------------------------------
* Edits a sales document that will be sent by e-mail
*-------------------------------------------------------------------
* Creates a new document based on a template
c eval lnccmd='XLOPENFILE'
c eval lncparm1=''+
c '\letter.xlt'
c exsr CALLLNC
c eval lnccmd='XLMINIMIZE'
c exsr CALLLNC
* Using cell names, data are set in the sheet
c eval lnccmd='XLSETTEXT'
c eval lncparm1='NAME'
c eval lncparm2= + lastname
c exsr CALLLNC
c eval lncparm1='ADDRESS'
c eval lncparm2=
c exsr CALLLNC
c eval lncparm1='ZIP'
c eval lncparm2=
c exsr CALLLNC
c eval lncparm1='CITY'
c eval lncparm2=
c exsr CALLLNC
c eval lncparm1='STATE'
c eval lncparm2=
c exsr CALLLNC
c eval lncparm1='PHONE'
c eval lncparm2=
c exsr CALLLNC
c eval lncparm1='FAX'
c eval lncparm2=
c exsr CALLLNC
c eval lncparm1='EMAIL'
c eval lncparm2=
c exsr CALLLNC
* Small routine to show the orders of the customer.
* We go on the order's file
c cust_id setll sp_ord_id
c cust_id reade sp_ord_id 91
c 91 goto Fin
* On the sheet, go to the cell which begin the table
c eval lnccmd='XLGOTOCELL'
c eval lncparm1='TABLE'
c eval lncparm2=
c exsr CALLLNC
c loop_order tag
c move ORDER_DATE datecmd
c movel datecmd datecmds
* Then, go to the order's detail
c order_id setll sp_det_or
c order_id reade sp_det_or 92
c 92 goto Suite
c loop_detail tag
c eval lnccmd='XLSETLINE'
c eval PRIXUP=
c movel prixup prixu
c movel(p) QUANTITY QTE
c eval PRIXTP=
c movel prixtp prixt
c eval lncparm1= + '%SEP%' +
c PART_DESC + '%SEP%' + prixu +
c '%SEP%' + QTE + '%SEP%' + PRIXT
c exsr CALLLNC
* We read next detail from the order
c order_id reade sp_det_or 92
c N92 goto loop_detail
c Suite tag
* And here, the next order
c cust_id reade sp_ord_id 93
c N93 goto loop_order
* All data are set, we can save and send it
c Fin tag
* Execute a macro to make a page setup
c eval lnccmd='XLEXEMACRO'
c eval lncparm1='PageSetup'
c exsr CALLLNC
c eval lnccmd='XLSAVEAS'
c eval lncparm1='%LNCDIR%\samples\'
c + firstname + '.xls'
c eval lncparm2=
c exsr CALLLNC
c eval lnccmd='XLSENDTO'
c eval lncparm1=
* The next line is not executed, preventing from abusive mail
* c exsr CALLLNC
c eval lnccmd='XLMAXIMIZE'
c exsr CALLLNC
c seton lr
* Exiting from the Launcher server
c eval lnccmd='END'
c exsr CALLLNC
c call 'LNCCLOSE'
c parm HANDLE