Simple example of how to use the Excel Read Service

Description:
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