Simple example of how to use the Excel Read Service

Date:15 April 2004
Product/Release:LANSA Integrator V10
Abstract:Simple example of how to use the Excel Read Service
Submitted By:LANSA Technical Support
Last Review:November 2010

This is a basic example of how to read data from and Excel Spread sheet using the Excel Read Service. This example could easily be modified to write to an Excel spread sheet and perform other more complex operations.

The example code below has been written in Visual LANSA. In order to execute the example it is required that you cut an paste the code supplied into a form, compile and run it.

It is recommended that cell formatting is avoided as much as possible. Further enhancements to this service are planned for the future to deal with various issues relating to various cell formats.

Example:

  • Cut and paste the following code into a form
  • To use this example you need an excel file with three columns with some data saved to "C:\Temp\book1.xls"
  • You can also refer to the excel document alone (i.e. with no path defined) if the excel document is saved to the "JSMinstance" directory
  • The following fields need to be defined in order to use this example
    • INT_ONE (A, 100)
    • INT_TRE (A, 100)
    • INT_TWO (A, 50)

* 
* 
* COMPONENT: STD_FORM
* 
* Example of how to use the Excel Read Service
* 
Function Options(*DIRECT)
Begin_Com Role(*EXTENDS #PRIM_FORM) Clientheight(591) Clientwidth(965) Height(619) Left(278) Top(201) Width(973)
Define_Com Class(#S_JSMCMD.Visual) Name(#S_JSMCMD) Displayposition(1) Height(19) Left(8) Parent(#COM_OWNER) Tabposition(1) Top(40) Usepicklist(False) Width(937)
Define_Com Class(#S_JSMMSG.Visual) Name(#S_JSMMSG) Displayposition(2) Height(19) Left(16) Parent(#COM_OWNER) Tabposition(2) Top(80) Usepicklist(False) Width(929)
Define_Com Class(#S_JSMSTS.Visual) Name(#S_JSMSTS) Displayposition(3) Height(19) Left(42) Parent(#COM_OWNER) Tabposition(3) Top(114) Usepicklist(False) Width(324)
Define_Com Class(#PRIM_PHBN) Name(#PHBN_1) Caption('Load Service') Displayposition(4) Left(40) Parent(#COM_OWNER) Tabposition(4) Top(200)
Define_Com Class(#PRIM_PHBN) Name(#PHBN_2) Caption('Read') Displayposition(5) Left(156) Parent(#COM_OWNER) Tabposition(5) Top(202)
Define_Com Class(#PRIM_LTVW) Name(#MYWRKLST) Componentversion(1) Displayposition(6) Fullrowselect(True) Height(321) Left(112) Parent(#COM_OWNER) Showsortarrow(True) Tabposition(6) Top(248) Width(545)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_1) Displayposition(1) Parent(#MYWRKLST) Source(#INT_ONE) Width(20)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_2) Displayposition(2) Parent(#MYWRKLST) Source(#INT_TRE) Width(28)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_3) Displayposition(3) Parent(#MYWRKLST) Source(#INT_TWO) Width(48)

Def_List Name(#MYWRKLST2) Fields(#INT_ONE #INT_TWO #INT_TRE) Type(*WORKING)

Evtroutine Handling(#com_owner.Initialize)
Set Com(#com_owner) Caption(*component_desc)
Define Field(#JSMSVR) Type(*CHAR) Length(20)

* Firstly set the filed value to be the local host
Change Field(#JSMSVR) To('localhost:4560')

* Open the JSM using the following argument
Use Builtin(JSM_OPEN) With_Args(#JSMSVR) To_Get(#S_JSMSTS #S_JSMMSG)
Endroutine

Evtroutine Handling(#PHBN_1.Click)
Use Builtin(JSM_COMMAND) With_Args('SERVICE_LOAD SERVICE(EXCELCLIENT)') To_Get(#S_JSMSTS #S_JSMMSG)
Endroutine

Evtroutine Handling(#PHBN_2.Click)
Change Field(#S_JSMCMD) To('''READ FILE(C:\Temp\book1.xls) service_list(INT_ONE,INT_TRE,INT_TWO)''')
Use Builtin(JSM_COMMAND) With_Args(#S_JSMCMD) To_Get(#S_JSMSTS #S_JSMMSG #MYWRKLST2)
Selectlist Named(#MYWRKLST2)
Add_Entry To_List(#MYWRKLST)
Endselect
Endroutine
End_Com