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
* ===================================================================
*
* 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