ExcelReadService Example
This example program will perform the following steps:
1. It performs a series of calls necessary to load the ExcelReadService.
2. It opens, writes and closes an Excel workbook file containing order line data to file XLREAD.XLS in the JSM instance folder – in this simple example, the data comes from compile-time array data coded in the RPG program, but it could equally well have come from a database or have been received in various formats through another LANSA Integrator service call.
3. It unloads the service and closes the connection to the JSM server.
Refer to the comments and code in the example for more information.
There are three steps required to make this application work:
1. Create the structure XML
The calls to the WRITE command of the ExcelReadService pass a multiple occurrence data structure containing the order line items that will be written to the workbook. For this to work, the LANSA Integrator service needs to know the characteristics of the structure. This is accomplished by supplying an XML file that describes the structure.
For this example, two structures are used – one is used to write column headings one cell at a time while the second is used to write the order line data. The required XML is supplied below. To install this XML you need to perform the following steps:
a. Locate the structure folder in the JSM instance folder for your JSM server
b. Create files called XLCell.xml and XLOrderLine.xml
c. Edit the XLCell.xml file with a text editor and paste into it the xml supplied below
<?xml version="1.0" encoding="UTF-8"?>
<rdml:structure xmlns:rdml="http://www.lansa.com/2000/XML/Function">
<rdml:field name="XLCELL" type="A" length="256" />
</rdml:structure>
d. Edit the XLOrderLine.xml file with a text editor and paste into it the xml supplied below
<?xml version="1.0" encoding="UTF-8"?>
<rdml:structure xmlns:rdml="http://www.lansa.com/2000/XML/Function">
<rdml:field name="LINENUM" type="S" length="7" />
<rdml:field name="PARTNUM" type="A" length="7" />
<rdml:field name="PARTDSC" type="A" length="30" />
<rdml:field name="PARTAMT" type="S" length="9" decimal="2" />
<rdml:field name="PARTQTY" type="S" length="7" />
</rdml:structure>
Note that the field names used in the structure XML do not need to match the variable names used in the RPG program. It is their order, types and length that are important – not their names.
2. Register the structure XML with the JSM Server
The example program refers to the structure XML supplied above with the symbolic names XL.XLCell and XL.XLOrderLine by specifying those names in the SERVICE_STRUCTURE keyword of the WRITE command.
We need to give the JSM Server a link between those symbolic names and the actual names and locations of the structure XML files created in step 1. To do this you need to perform the following steps:
a. Locate the system folder in the JSM instance folder for your JSM server
b. Edit the file structure.properties with a text editor and paste into it the entries supplied below (make sure the new entries are each on separate lines by themselves)
c. Save your changes
d. Restart or refresh the JSM Server instance (refer to Java Service Manager Refresh).
structure.XL.XLCell=structure/XLCell.xml
structure.XL.XLOrderLine=structure/XLOrderLine.xml
3. Create and run the ILE RPG example program
Copy and paste the source provided below into a source file member.
To create the program, you need to use the CRTRPGMOD and CRTPGM commands. Make sure that you use the parameter values specified in the source member.
*************************************************
* EXCELREAD: example in RPG ILE of using the LANSA Integrator
* ExcelReadService to write an Excel workbook.
*
* Note: This is an example program containing only
* rudimentary exception handling
*
* To create this program you must execute the following commands,
* supplying the indicated parameter values and any others that are
* necessary in your installation:
*
* CRTRPGMOD MODULE(<modlib>/EXCELREAD)
* SRCFILE(<srclib>/<srcfil>)
*
* CRTPGM PGM(<pgmlib>/EXCELREAD)
* MODULE(<modlib>/EXCELREAD)
* BNDSRVPGM(<jsmpgmlib>/DCXS882X)
* ACTGRP(*CALLER)
*************************************************
* Path of the Excel workbook created by this program
* - because no folder path is specified, the file will be created
* in the JSM instance folder by default
d xlfilepath c const('XLREAD.XLS')
* Declare variables for the JSM calls
d jsmsrv s 50a inz(*blanks)
d jsmsts s 20a inz(*blanks)
d jsmmsg s 255a inz(*blanks)
d jsmcmd s 255a inz(*blanks)
d bytelength s 10i 0 inz(*zero)
* Declare structure to send order line data to be written to the
* Excel workbook:
* - in this simple example, the data comes from the compile-time
* array data, but it could equally well have come from a database
* or received through another LANSA Integrator service call
* NB: This MUST match the structure xml provided to the JSM Server!
d xllist ds occurs(xlocur) based(xllistptr)
d linenum 7s 0
d partnum 7a
d partdsc 30a
d partamt 9s 2
d partqty 7s 0
d xlocur c const(2)
d xlsize c const(%size(xllist))
* Declare the compile-time array that provides the data for
* this simple example
d xldata s 60a dim(xlocur) perrcd(1) ctdata
* Completion messages
d CompMsg01 c 'JSMOPEN call completed.'
d CompMsg02 c ' SERVICE_LOAD call completed.'
d CompMsg10 c ' OPEN call completed.'
d CompMsg20 c ' WRITE call completed.'
d CompMsg30 c ' CLOSE call completed.'
d CompMsg98 c ' SERVICE_UNLOAD call completed.'
d CompMsg99 c 'JSMCLOSE call completed.'
* Procedure prototypes
d WriteCell pr
d wcR1C1 10a value
d wcCell 256a value
d CheckResult pr
d crjsts const like(jsmsts)
d crjmsg const like(jsmmsg)
d SendMessage pr
d smText 512a VALUE
d smType 10a VALUE
* Prototypes for the JSM calls
/COPY QRPGLESRC,JSM_PROC.H
* Open a connection to the default JSM server
* - because the server parameter is blank, details of the default
* JSM server are obtained from the data area JSMCLTDTA on IBM i
* or from the file jsmcltdta.txt on other supported platforms)
c callp p_jsmopen(jsmsrv:jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg01:'*COMP')
* Load the ExcelReadService
* - this example explicitly turns tracing on, overriding the
* settings in the manager.properties file
c eval jsmcmd = 'SERVICE_LOAD'
c + ' SERVICE(EXCELREADSERVICE) TRACE(*YES)'
c callp p_jsmcmd(jsmcmd:jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg02:'*COMP')
* Open the Excel workbook for writing
c eval jsmcmd = 'OPEN'
c + ' FILE(' + %trim(xlfilepath) + ')'
c + ' MODE(*WRITE) REPLACE(*YES)'
c callp p_jsmcmd(jsmcmd:jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg10:'*COMP')
* Write some column headings
c callp WriteCell('3,3':'Line Number')
c callp WriteCell('3,4':'Part Number')
c callp WriteCell('3,5':'Part Description')
c callp WriteCell('3,6':'Unit Amount')
c callp WriteCell('3,7':'Unit Quantity')
* Populate the list to be written to the Excel workbook
* - in this simple example, the data comes from the compile-time
* array data, but it could equally well have come from a database
* or received through another LANSA Integrator service call
c eval xllistptr = %addr(xldata)
* Write the list of items from the compile-time array to the specified
* position in the specified worksheet:
* - this passes the multiple occurrence data structure
* (xllist) containing the items
* - the structure is described to the ExcelReadService by the
* structure XML identified by the SERVICE_STRUCTURE keyword - there
* must be a matching entry in the structure.properties file and a
* corresponding structure XML file
* NOTE: this call uses the JSMCMDX api in order to be able to send
* variable data (in this case the structure/list)
c eval jsmcmd = 'WRITE'
c + ' SHEET(ORDERLINES) R1C1(5,3)'
c + ' SERVICE_STRUCTURE(XL.XLOrderLine)'
c + ' COUNT(' + %char(xlocur) + ')'
c + ' OCCURS(' + %char(xlocur) + ')'
c + ' SIZE(' + %char(xlsize) + ')'
c eval bytelength = xlocur * xlsize
c callp p_jsmcmdx(jsmcmd:xllist:bytelength:
c jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg20:'*COMP')
* Close the Excel workbook
c eval jsmcmd = 'CLOSE'
c callp p_jsmcmd(jsmcmd:jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg30:'*COMP')
* Unload the ExcelReadService
c eval jsmcmd = 'SERVICE_UNLOAD'
c callp p_jsmcmd(jsmcmd:jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg98:'*COMP')
* Close the connection to the JSM server and finish
c callp p_jsmclose(jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg99:'*COMP')
c eval *inlr = *on
c return
*************************************************
* Procedure to write a single cell at a time to the workbook
*************************************************
p WriteCell b
d WriteCell pi
d wcR1C1 10a value
d wcCell 256a value
c eval jsmcmd = 'WRITE'
c + ' R1C1(' + %trim(wcR1C1) + ')'
c + ' SHEET(ORDERLINES)'
c + ' SERVICE_STRUCTURE(XL.XLCell)'
c + ' COUNT(1) OCCURS(1)'
c + ' SIZE(' + %char(%size(wcCell)) + ')'
c eval bytelength = %size(wcCell)
c callp p_jsmcmdx(jsmcmd:wcCell:bytelength:
c jsmsts:jsmmsg)
c callp CheckResult(jsmsts:jsmmsg)
c callp SendMessage(CompMsg20:'*COMP')
p WriteCell e
*************************************************
* Procedure to check the result of a Java Service Manager call
*************************************************
p CheckResult b
d CheckResult pi
d crjsts const like(jsmsts)
d crjmsg const like(jsmmsg)
d crText s 512a
d crMsg1 c const('JSM Status : ')
d crMsg2 c const('JSM Message: ')
d crMsg3 c const('JSM Service error has +
d occurred')
c if crjsts <> 'OK'
c eval crText = crMsg1 + crjsts
c callp SendMessage(crText:'*DIAG')
c eval crText = crMsg2 + crjmsg
c callp SendMessage(crText:'*DIAG')
c callp SendMessage(crMsg3:'*ESCAPE')
c endif
p CheckResult e
*************************************************
* Procedure to send a program message
*************************************************
p SendMessage b
d SendMessage pi
d smText 512a VALUE
d smMsgT 10a VALUE
d smMsgI s 7a inz('CPF9897')
d smMsgF s 20a inz('QCPFMSG *LIBL ')
d smDtaL s 10i 0 inz(%size(smText))
d smStkE s 10a inz('*')
d smStkC s 10i 0 inz(1)
d smMsgK s 4a
d smErrC s 10i 0 inz(0)
c if smMsgT = '*ESCAPE'
c eval smMsgI = 'CPF9898'
c endif
c call 'QMHSNDPM'
c parm smMsgI
c parm smMsgF
c parm smText
c parm smDtaL
c parm smMsgT
c parm smStkE
c parm smStkC
c parm smMsgK
c parm smErrC
p e
**CTDATA xldata
0000001123 Gasket Paper 0000009950000010
0000002456 Gasket polymer glue 0000013270000005