ExcelReadService Example
* Uses Integrator Services: ExcelReadService
* This forms connects to the local JSM Server and allows Excel files to be read, with the resulting information displayed in a list,
* or written, based on the information currently in the list.
*
* The browse and working list definitions are defined with 5 columns to support the definition of an order.
* The following field must be defined in your repository:
* jsmstring string 1000
* jsmstatus string 20
* jsmsts alpha 20
* jsmmsg alpha 255
* jsmhdle char 4
* jsmcmd alpha 255
function options(*DIRECT)
begin_com role(*EXTENDS #PRIM_FORM) clientheight(573) clientwidth(692) framestyle(Dialog) height(607) left(441) top(129) width(700)
define_com class(#PRIM_PHBN) name(#Get) caption('Get') displayposition(1) left(8) parent(#PANL_2) tabposition(1) top(5) width(120)
define_com class(#PRIM_PHBN) name(#Read) caption('Read') displayposition(2) left(135) parent(#PANL_2) tabposition(2) top(5) width(120)
define_com class(#PRIM_PHBN) name(#Write) caption('Write') displayposition(3) left(265) parent(#PANL_2) tabposition(3) top(5) width(120)
define_com class(#PRIM_PHBN) name(#Clear) caption('Clear') displayposition(4) left(395) parent(#PANL_2) tabposition(4) top(5) width(120)
define_com class(#PRIM_STBR) name(#stbr_1) displayposition(1) height(24) left(0) messageposition(1) parent(#PANL_1) tabposition(1) tabstop(False) top(77) width(692)
define_com class(#PRIM_PANL) name(#PANL_1) displayposition(2) height(101) left(0) parent(#COM_OWNER) tabposition(2) tabstop(False) top(472) width(692)
define_com class(#PRIM_PANL) name(#PANL_2) displayposition(2) height(34) left(5) parent(#PANL_1) tabposition(2) tabstop(False) top(34) width(692)
define_com class(#PRIM_grid) name(#detaillst) columnbuttonheight(15) componentversion(1) displayposition(1) height(324) left(0) parent(#COM_OWNER) selectionstyle(Multiple) tabposition(1) top(136) width(692)
define_com class(#PRIM_gdcl) name(#linenum) caption('Line') captiontype(Caption) displayposition(1) parent(#detaillst) readonly(False) source(#STD_NUM) width(10)
define_com class(#PRIM_gdCL) name(#partnum) caption('Part') captiontype(Caption) displayposition(2) parent(#detaillst) readonly(False) source(#STD_OBJ) width(12)
define_com class(#PRIM_gdCL) name(#partdsc) caption('Description') captiontype(Caption) displayposition(3) parent(#detaillst) readonly(False) source(#STD_DESCL) widthtype(Remainder)
define_com class(#PRIM_gdCL) name(#partamt) caption('Unit Amount') captiontype(Caption) displayposition(4) parent(#detaillst) readonly(False) source(#STD_AMNT) width(15)
define_com class(#PRIM_gdCL) name(#partqty) caption('Quantity') captiontype(Caption) displayposition(5) parent(#detaillst) readonly(False) source(#STD_QTY) width(15)
define_com class(#JSMSTRING.Visual) name(#jsmfile) caption('Path and File Name') displayposition(4) height(19) labeltype(Caption) left(10) marginleft(100) parent(#COM_OWNER) tabposition(4) top(16) usepicklist(False) width(594)
define_com class(#JSMSTATUS.Visual) name(#jsmsheet) caption('Worksheet') displayposition(3) height(19) labeltype(Caption) left(10) marginleft(100) parent(#COM_OWNER) tabposition(3) top(40) usepicklist(False) width(263)
def_list name(#valueslst) fields(#std_num #std_obj #std_descl #std_amnt #std_qty) counter(#listcount) type(*working)
def_list name(#sheetlst) fields(#std_descl) type(*working)
evtroutine handling(#com_owner.Initialize)
#jsmhdle := *default
#com_owner.clearlists
#com_owner.setlist
#com_owner.Load
endroutine
evtroutine handling(#Get.Click)
#com_owner.GetExcelInfo
endroutine
evtroutine handling(#Read.Click)
#com_owner.ReadExcelFile
endroutine
evtroutine handling(#Write.Click)
#com_owner.WriteExcelFile
endroutine
evtroutine handling(#Clear.Click)
#com_owner.ClearLists
#com_owner.SetList
endroutine
evtroutine handling(#com_owner.closing)
#com_owner.Unload
endroutine
mthroutine name(Load)
if (#jsmhdle.IsNull)
* connect the JSMX client to the Java Service Manager and start a thread for the service
* Start default JSM server
use builtin(jsmx_open) to_get(#jsmsts #jsmmsg #jsmhdle)
#com_owner.check( #jsmsts #jsmmsg )
* Load the service
#jsmcmd := 'service_load service(ExcelReadService)'
use builtin(jsmx_command) with_args(#jsmhdle #jsmcmd) to_get(#jsmsts #jsmmsg)
#com_owner.check( #jsmsts #jsmmsg )
endif
endroutine
* Get EXCEL document information
mthroutine name(GetExcelInfo)
#com_owner.ClearLists
#std_num := 0
* open the document
#com_owner.OpenDocument( read )
* get details about the EXCEL file
#jsmcmd := 'get object(*sheets)'
use builtin(jsmx_command) with_args(#jsmhdle #jsmcmd) to_get(#jsmsts #jsmmsg #sheetlst)
#com_owner.check( #jsmsts #jsmmsg )
if (#jsmsts = OK)
#std_num := #std_num + 1
#std_descl := 'Current document has worksheets: '
add_entry to_list(#detaillst)
selectlist named(#sheetlst)
#std_num := #std_num + 1
add_entry to_list(#detaillst)
endselect
endif
* get rowcount for nominated wroksheet
#jsmcmd := 'get object(*rowcount) sheet(' + #jsmsheet + ')'
use builtin(jsmx_command) with_args(#jsmhdle #jsmcmd) to_get(#jsmsts #jsmmsg)
#com_owner.check( #jsmsts #jsmmsg )
if (#jsmsts = OK)
#std_num := #std_num + 1
if (#jsmfile = *blank)
#std_descl := 'Number of rows in worksheet sheet1 is ' + #jsmmsg
else
#std_descl := 'Number of rows in worksheet ' + #jsmsheet + ' is ' + #jsmmsg
endif
add_entry to_list(#detaillst)
endif
* close all documents
#com_owner.CloseDocuments
endroutine
* Read information from an EXCEL document
mthroutine name(ReadExcelFile)
* open the document
#com_owner.OpenDocument( read )
#com_owner.ClearLists
* read EXCEL file
#jsmcmd := 'read sheet(' + #jsmsheet + ')'
use builtin(jsmx_command) with_args(#jsmhdle #jsmcmd) to_get(#jsmsts #jsmmsg #valueslst)
#com_owner.check( #jsmsts #jsmmsg )
if (#jsmsts = OK)
selectlist named(#valueslst)
add_entry to_list(#detaillst)
endselect
endif
* add some blank entries to allow for additions to the file
#std_obj #std_descl #std_amnt #std_qty := *null
#std_num := #listcount
begin_loop using(#std_count) to(20)
#std_num := #std_num + 1
add_entry to_list(#detaillst)
end_loop
* close all documents
#com_owner.CloseDocuments
endroutine
* Write list details to the EXCEL document
mthroutine name(WriteExcelFile)
clr_list named(#valueslst)
selectlist named(#detaillst)
if (#std_obj <> *blank)
add_entry to_list(#valueslst)
endif
endselect
* open the document
#com_owner.OpenDocument( write )
* add variables for any additional keywords you need to modify
#jsmcmd := 'write sheet(' + #jsmsheet + ')'
use builtin(jsmx_command) with_args(#jsmhdle #jsmcmd) to_get(#jsmsts #jsmmsg #valueslst)
#com_owner.check( #jsmsts #jsmmsg )
* close all documents
#com_owner.CloseDocuments
endroutine
* Open an EXCEL document
mthroutine name(OpenDocument)
define_map for(*input) class(#prim_alph) name(#i_mode)
* open the nominated EXCEL file
#jsmcmd := 'open file(' + #jsmfile + ') mode(*' + #i_mode + ')'
use builtin(jsmx_command) with_args(#jsmhdle #jsmcmd) to_get(#jsmsts #jsmmsg)
#com_owner.check( #jsmsts #jsmmsg )
endroutine
* Close all open EXCEL documents
mthroutine name(CloseDocuments)
#jsmcmd := 'close'
use builtin(jsmx_command) with_args(#jsmhdle #jsmcmd) to_get(#jsmsts #jsmmsg)
#com_owner.check( #jsmsts #jsmmsg )
endroutine
* Clear all lists used for processing
mthroutine name(ClearLists)
#valueslst := *null
clr_list named(#sheetlst)
clr_list named(#valueslst)
clr_list named(#detaillst)
endroutine
* set default entries in list
mthroutine name(SetList)
#std_obj #std_descl #std_amnt #std_qty := *null
begin_loop using(#std_num) to(20)
add_entry to_list(#detaillst)
end_loop
endroutine
* Unload the service
mthroutine name(Unload)
if (#jsmhdle <> *blank)
* unload the service
use builtin(jsmx_command) with_args(#jsmhdle service_unload) to_get(#jsmsts #jsmmsg)
#com_owner.check( #jsmsts #jsmmsg )
* Close the thread
use builtin(jsmx_close) with_args(#jsmhdle) to_get(#jsmsts #jsmmsg)
endif
endroutine
* check the JSM return status
mthroutine name(check)
define_map for(*input) class(#jsmsts) name(#i_status)
define_map for(*input) class(#jsmmsg) name(#i_message)
message msgtxt(#i_status + ' : ' + #i_message)
endroutine
end_com
]