S_208RME

LANSA

S_208RME
* ===================================================================
*
* Component : S_208RME
* Type : Reusable Component
* Ancestor : PRIM_PANL
*
* Description : Handle the extraction of cell values from a
* MS-Excel document and pass the details back
* to S_208FMF.
*
* Disclaimer : The following material is supplied as example material
* only. No warranty concerning this material or its use
* in any way whatsoever is expressed or implied.
*
* ===================================================================
FUNCTION OPTIONS(*DIRECT)
BEGIN_COM DISPLAYPOSITION(1) HEIGHT(34) LEFT(0) TABPOSITION(1) TOP(0) WIDTH(62)

* MS-Excel Object Definitions

DEFINE_COM CLASS(#va_excel) NAME(#MSExcel) REFERENCE(*DYNAMIC)
Define_Com Class(#va_excel.Worksheet) Name(#WorkSheet) Reference(*Dynamic)
Define_Com Class(#va_excel.Range) Name(#Range) Reference(*Dynamic)
Define_Com Class(#va_excel.Range) Name(#Cell) Reference(*Dynamic)

* The event that signals document information back to S_208FMF

Define_Evt InformationFound
Define_Map *input #S_208Name #WithName
Define_Map *input #S_208AVAL #WithValue

* Working variables

DEFINE_COM CLASS(#SYSVAR$AV) NAME(#CELLTEXT)

* -------------------------------------------------------------
* Handle an information extraction request coming from S_208FMF
* -------------------------------------------------------------

MthRoutine ExtractInformation
Define_Map *input #SysVar$av #FromDocument

* Define the limits for number of rows and columns to
* be checked for in any MS-Excel speadsheet

Define #Col_Limit Reffld(#Std_Num) Default(26)
Define #Row_Limit Reffld(#Std_Num) Default(50)

* Define the field used to form the cell identifier
* in the format CELL_rrrrrrr_ccccccc where rrrrrrr
* is the logical row number and ccccccc is the logical
* column number

Define #Cell_Idn *Char 20
* 1 2
* 12345678901234567890
Change #Cell_Idn CELL_0000000_0000000
Define #Cell_Row Reffld(#Date) Length(7) decimals(0) edit_code(4) default(0) To_Overlay(#Cell_Idn 6)
Define #Cell_Col Reffld(#Date) Length(7) decimals(0) edit_code(4) default(0) To_Overlay(#Cell_Idn 14)

* Create the MS-Excel application if it does not already exist

If_Ref #MSExcel is(*null)

Set_Ref #MSExcel (*Create_as #va_excel)

* Minimize the window containing MS-Excel and stop it showing error details

Change #Std_Num 2
Set #MSExcel WindowState(#Std_Num) DisplayAlerts(False)

Endif

* Open the specified MS-Excel document

Invoke #MSExcel.WorkBooks.Open FileName(#FromDocument.Value)

* Point to the first worksheet just opened

Set_Ref Com(#WorkSheet) To(*Dynamic #MSExcel.Worksheets.Item<1>)

* If it was opened successfully

If_ref #WorkSheet is_not(*null)

* Activate the worksheet

Invoke #WorkSheet.Activate

* Now extract the cell details and feed their values back to S_208FMF
* by issuing multiple "InformationFound" signals. S_208FMF listens
* for these signals, reformats the names and values passed and then
* stores the information in a series of working lists (ready to pass
* on to the AS/400 server for processing). Note that only non-blank
* cell details are signalled back.

Set_Ref #Range #WorkSheet.Cells

Begin_Loop from(1) to(#Row_Limit) Using(#Cell_Row)
Begin_Loop from(1) to(#Col_Limit) Using(#Cell_Col)

Set_Ref #Cell #Range.Cells<#Cell_Row #Cell_Col>

Set #CellText Value(#Cell.Text)

Continue '#CellText.Value = *Blanks'

Signal InformationFound WithName(#Cell_Idn) WithValue(#CellText.Value)

End_Loop
End_Loop

* After signalling all cell details close the open document

Invoke #MSExcel.WorkBooks.Close

Endif

* Finished, return control back to S_208FMF

Endroutine

* ----------------------------------------------
* Handle a shutdown request coming from S_208FMF
* ----------------------------------------------

MthRoutine Shutdown

* If a MS-Excel environment has been created close it down

If_ref #MSExcel is_Not(*null)

Invoke #MSExcel.Quit

Endif

* And then destroy all references to any part of it

Set_Ref (#WorkSheet #Range #Cell #MSExcel) *null

Endroutine

END_COM