Form VL_SAM081: MS-Excel Integration
Name: VL_SAM081
Description: The following RDMLX form is used to demonstrate how detailed and summarized information can be formated and displayed by MS-Excel. This form uses reusable component VL_SAM083.
FUNCTION OPTIONS(*DIRECT);
BEGIN_COM FORMPOSITION(ScreenCenter) HEIGHT(516) LEFT(308) TOP(122) WIDTH(635);
DEFINE_COM CLASS(#PRIM_TRVW) NAME(#TREEVIEW) DISPLAYPOSITION(1) HEIGHT(441) LEFT(8) PARENT(#DEPTBOX) TABPOSITION(1) TOP(16) WIDTH(225);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_1) KEYPOSITION(2) LEVEL(1) PARENT(#TREEVIEW) SOURCE(#DEPTMENT) VISIBLE(False);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_2) DISPLAYPOSITION(2) LEVEL(1) PARENT(#TREEVIEW) SOURCE(#DEPTDESC);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_3) KEYPOSITION(1) LEVEL(2) PARENT(#TREEVIEW) SOURCE(#SECTION) VISIBLE(False);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_4) DISPLAYPOSITION(1) LEVEL(2) PARENT(#TREEVIEW) SOURCE(#SECDESC);
DEFINE_COM CLASS(#PRIM_LTVW) NAME(#LISTVIEW) DISPLAYPOSITION(1) HEIGHT(281) LEFT(6) PARENT(#GROUPBOX) TABPOSITION(1) TOP(72) WIDTH(377);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_1) CAPTION('Number ') CAPTIONTYPE(Caption) DISPLAYPOSITION(1) PARENT(#LISTVIEW) SOURCE(#EMPNO) WIDTH(20);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_2) CAPTION('First Name') CAPTIONTYPE(Caption) DISPLAYPOSITION(2) PARENT(#LISTVIEW) SOURCE(#GIVENAME) WIDTH(25);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_3) CAPTION('Last Name ') CAPTIONTYPE(Caption) DISPLAYPOSITION(3) PARENT(#LISTVIEW) SOURCE(#SURNAME) WIDTH(30);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_4) CAPTION('Salary') CAPTIONTYPE(Caption) DISPLAYPOSITION(4) PARENT(#LISTVIEW) SOURCE(#SALARY) WIDTH(20) WIDTHTYPE(Remainder);
DEFINE_COM CLASS(#PRIM_GPBX) NAME(#GROUPBOX) CAPTION('Employees') DISPLAYPOSITION(1) HEIGHT(361) LEFT(240) PARENT(#COM_OWNER) TABPOSITION(1) TABSTOP(False) TOP(104) VISIBLE(False) WIDTH(389);
DEFINE_COM CLASS(#DEPTDESC.Visual) NAME(#DEPTDESC) CAPTION('In Department') DISPLAYPOSITION(2) HEIGHT(19) LABELTYPE(Caption) LEFT(7) MARGINLEFT(70) PARENT(#GROUPBOX) READONLY(True) TABPOSITION(2) TABSTOP(False) TOP(24) WIDTH(209);
DEFINE_COM CLASS(#SECDESC.Visual) NAME(#SECDESC) CAPTION('In Section ') DISPLAYPOSITION(3) HEIGHT(19) LABELTYPE(Caption) LEFT(7) MARGINLEFT(70) PARENT(#GROUPBOX) READONLY(True) TABPOSITION(3) TABSTOP(False) TOP(48) WIDTH(209);
DEFINE_COM CLASS(#VL_SAM083) NAME(#OS) DISPLAYPOSITION(3) LEFT(592) PARENT(#COM_OWNER) TABPOSITION(3) TOP(464) VISIBLE(False);
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#BTN_EMP) CAPTION('Section Salary Details') DISPLAYPOSITION(4) ENABLED(False) LEFT(448) PARENT(#COM_OWNER) TABPOSITION(4) TABSTOP(False) TOP(8) WIDTH(175);
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#BTN_DEP) CAPTION('Department Salary Summary') DISPLAYPOSITION(6) ENABLED(False) LEFT(448) PARENT(#COM_OWNER) TABPOSITION(6) TABSTOP(False) TOP(40) WIDTH(175);
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#BTN_ALL) CAPTION('All Departments Salary Summary') DISPLAYPOSITION(5) LEFT(448) PARENT(#COM_OWNER) TABPOSITION(5) TOP(72) WIDTH(175);
DEFINE_COM CLASS(#PRIM_STBR) NAME(#STBR_1) DISPLAYPOSITION(2) HEIGHT(25) LEFT(0) MESSAGEPOSITION(1) PARENT(#COM_OWNER) TABPOSITION(2) TOP(464) WIDTH(627);
DEFINE_COM CLASS(#PRIM_GPBX) NAME(#DEPTBOX) CAPTION('Departments and Sections ') DISPLAYPOSITION(7) HEIGHT(465) LEFT(0) PARENT(#COM_OWNER) TABPOSITION(7) TOP(0) WIDTH(241);
;
Define #FileName *char 100;
Define #RetCode *char 2;
Define #osErrCode RefFld(#Std_Num);
Define #Employees Reffld(#Std_Num) ;
Define #SalaryTot Reffld(#Salary) Length(*Reffld *plus 2);
Define #SalaryAve Reffld(#Salary) ;
;
* Fill tree view with department and section details ;
;
EVTROUTINE handling(#com_owner.Initialize);
Select (#Deptment #DeptDesc) From_File(DepTab);
Select (#Section #SecDesc) From_File(SecTab) With_Key(#Deptment);
Add_Entry #TreeView ;
EndSelect ;
EndSelect ;
ENDROUTINE ;
;
* Handle an entry in the tree view getting focus by making the employee details appear on the right (level 2);
* or by making them disappear and by disbling some options;
;
EVTROUTINE HANDLING(#TREEVIEW.ItemGotFocus) OPTIONS(*NOCLEARMESSAGES *NOCLEARERRORS);
If '#Treeview.CurrentItem.Level = 2';
Set #Btn_Dep Enabled(False);
Set #Btn_Emp Enabled(True);
Clr_List #ListView ;
Select (#empno #SurName #GiveName #salary) From_File(PslMst1) with_key(#Deptment #Section);
Add_Entry #ListView;
EndSelect ;
Set #GroupBox Visible(True);
Else ;
Set #Btn_Dep Enabled(True);
Set #Btn_Emp Enabled(False);
Set #GroupBox Visible(False);
EndIf ;
ENDROUTINE ;
;
* Handle request to send contents of employee list view to XL ;
;
EVTROUTINE HANDLING(#BTN_EMP.Click) ;
Invoke #OS.MakeTempFileName iSuffix(XLS) iNamePart1('Dept') iNamePart2(#deptment) iNamePart3('Section') iNamePart4(#section) iNamePart5('Details') oFileName(#FileName) ;
use builtin(transform_list) with_args(#listview #filename b) to_get(#retcode);
if '#RetCode = OK';
Invoke #OS.ExecuteFile iFileName(#FileName) oRetCode(#osErrCode) ;
Endif ;
ENDROUTINE ;
;
* Handle request to send summary of all sections in current department to XL ;
;
EVTROUTINE HANDLING(#BTN_DEP.Click) ;
Invoke #OS.MakeTempFileName iSuffix(XLS) iNamePart1('Dept') iNamePart2(#deptment) iNamePart3('Summary') oFileName(#FileName) ;
Def_List #DeptSum (#DeptDesc #secdesc #Employees #SalaryTot #SalaryAve) type(*working) entrys(9999);
Select fields(#Section #SecDesc) from_file(Sectab) with_key(#deptment);
Change (#Employees #SalaryTot #SalaryAve) 0 ;
Select Fields (#Salary) From_File(PslMst1) With_Key(#Deptment #Section);
Change #SalaryTot '#SalaryTot + #Salary';
Change #Employees '#Employees + 1';
EndSelect ;
if '#Employees > 0';
Change #SalaryAve '#SalaryTot / #Employees' ;
Endif ;
Add_Entry #DeptSum;
Endselect ;
Use builtin(Transform_List) with_args(#DeptSum #FileName b) to_get(#retcode);
Clr_List #DeptSum ;
if '#RetCode = OK';
Invoke #OS.ExecuteFile iFileName(#FileName) oRetCode(#osErrCode);
Endif ;
ENDROUTINE ;
;
* Handle request to send summary of all departments to XL ;
;
EVTROUTINE HANDLING(#BTN_ALL.Click) ;
Invoke #OS.MakeTempFileName iSuffix(XLS) iNamePart1('AllDeptSummary') oFileName(#FileName) ;
Def_List #AllSum (#DeptDesc #Employees #SalaryTot #SalaryAve) type(*working) entrys(9999);
Select fields(#Deptment #DeptDesc) from_file(DepTab) ;
Change (#Employees #SalaryTot #SalaryAve) 0 ;
Select Fields (#Salary) From_File(PslMst1) With_Key(#Deptment);
Change #SalaryTot '#SalaryTot + #Salary';
Change #Employees '#Employees + 1';
EndSelect ;
if '#Employees > 0';
Change #SalaryAve '#SalaryTot / #Employees' ;
Endif ;
Add_Entry #AllSum;
Endselect ;
Use builtin(Transform_List) with_args(#AllSum #FileName b) to_get(#retcode);
Clr_List #AllSum ;
if '#RetCode = OK';
Invoke #OS.ExecuteFile iFileName(#FileName) oRetCode(#osErrCode) ;
Endif ;
ENDROUTINE ;
;
END_COM ;
Name: VL_SAM081
Description: The following RDMLX form is used to demonstrate how detailed and summarized information can be formated and displayed by MS-Excel. This form uses reusable component VL_SAM083.
FUNCTION OPTIONS(*DIRECT);
BEGIN_COM FORMPOSITION(ScreenCenter) HEIGHT(516) LEFT(308) TOP(122) WIDTH(635);
DEFINE_COM CLASS(#PRIM_TRVW) NAME(#TREEVIEW) DISPLAYPOSITION(1) HEIGHT(441) LEFT(8) PARENT(#DEPTBOX) TABPOSITION(1) TOP(16) WIDTH(225);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_1) KEYPOSITION(2) LEVEL(1) PARENT(#TREEVIEW) SOURCE(#DEPTMENT) VISIBLE(False);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_2) DISPLAYPOSITION(2) LEVEL(1) PARENT(#TREEVIEW) SOURCE(#DEPTDESC);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_3) KEYPOSITION(1) LEVEL(2) PARENT(#TREEVIEW) SOURCE(#SECTION) VISIBLE(False);
DEFINE_COM CLASS(#PRIM_TVCL) NAME(#TVCL_4) DISPLAYPOSITION(1) LEVEL(2) PARENT(#TREEVIEW) SOURCE(#SECDESC);
DEFINE_COM CLASS(#PRIM_LTVW) NAME(#LISTVIEW) DISPLAYPOSITION(1) HEIGHT(281) LEFT(6) PARENT(#GROUPBOX) TABPOSITION(1) TOP(72) WIDTH(377);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_1) CAPTION('Number ') CAPTIONTYPE(Caption) DISPLAYPOSITION(1) PARENT(#LISTVIEW) SOURCE(#EMPNO) WIDTH(20);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_2) CAPTION('First Name') CAPTIONTYPE(Caption) DISPLAYPOSITION(2) PARENT(#LISTVIEW) SOURCE(#GIVENAME) WIDTH(25);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_3) CAPTION('Last Name ') CAPTIONTYPE(Caption) DISPLAYPOSITION(3) PARENT(#LISTVIEW) SOURCE(#SURNAME) WIDTH(30);
DEFINE_COM CLASS(#PRIM_LVCL) NAME(#LVCL_4) CAPTION('Salary') CAPTIONTYPE(Caption) DISPLAYPOSITION(4) PARENT(#LISTVIEW) SOURCE(#SALARY) WIDTH(20) WIDTHTYPE(Remainder);
DEFINE_COM CLASS(#PRIM_GPBX) NAME(#GROUPBOX) CAPTION('Employees') DISPLAYPOSITION(1) HEIGHT(361) LEFT(240) PARENT(#COM_OWNER) TABPOSITION(1) TABSTOP(False) TOP(104) VISIBLE(False) WIDTH(389);
DEFINE_COM CLASS(#DEPTDESC.Visual) NAME(#DEPTDESC) CAPTION('In Department') DISPLAYPOSITION(2) HEIGHT(19) LABELTYPE(Caption) LEFT(7) MARGINLEFT(70) PARENT(#GROUPBOX) READONLY(True) TABPOSITION(2) TABSTOP(False) TOP(24) WIDTH(209);
DEFINE_COM CLASS(#SECDESC.Visual) NAME(#SECDESC) CAPTION('In Section ') DISPLAYPOSITION(3) HEIGHT(19) LABELTYPE(Caption) LEFT(7) MARGINLEFT(70) PARENT(#GROUPBOX) READONLY(True) TABPOSITION(3) TABSTOP(False) TOP(48) WIDTH(209);
DEFINE_COM CLASS(#VL_SAM083) NAME(#OS) DISPLAYPOSITION(3) LEFT(592) PARENT(#COM_OWNER) TABPOSITION(3) TOP(464) VISIBLE(False);
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#BTN_EMP) CAPTION('Section Salary Details') DISPLAYPOSITION(4) ENABLED(False) LEFT(448) PARENT(#COM_OWNER) TABPOSITION(4) TABSTOP(False) TOP(8) WIDTH(175);
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#BTN_DEP) CAPTION('Department Salary Summary') DISPLAYPOSITION(6) ENABLED(False) LEFT(448) PARENT(#COM_OWNER) TABPOSITION(6) TABSTOP(False) TOP(40) WIDTH(175);
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#BTN_ALL) CAPTION('All Departments Salary Summary') DISPLAYPOSITION(5) LEFT(448) PARENT(#COM_OWNER) TABPOSITION(5) TOP(72) WIDTH(175);
DEFINE_COM CLASS(#PRIM_STBR) NAME(#STBR_1) DISPLAYPOSITION(2) HEIGHT(25) LEFT(0) MESSAGEPOSITION(1) PARENT(#COM_OWNER) TABPOSITION(2) TOP(464) WIDTH(627);
DEFINE_COM CLASS(#PRIM_GPBX) NAME(#DEPTBOX) CAPTION('Departments and Sections ') DISPLAYPOSITION(7) HEIGHT(465) LEFT(0) PARENT(#COM_OWNER) TABPOSITION(7) TOP(0) WIDTH(241);
;
Define #FileName *char 100;
Define #RetCode *char 2;
Define #osErrCode RefFld(#Std_Num);
Define #Employees Reffld(#Std_Num) ;
Define #SalaryTot Reffld(#Salary) Length(*Reffld *plus 2);
Define #SalaryAve Reffld(#Salary) ;
;
* Fill tree view with department and section details ;
;
EVTROUTINE handling(#com_owner.Initialize);
Select (#Deptment #DeptDesc) From_File(DepTab);
Select (#Section #SecDesc) From_File(SecTab) With_Key(#Deptment);
Add_Entry #TreeView ;
EndSelect ;
EndSelect ;
ENDROUTINE ;
;
* Handle an entry in the tree view getting focus by making the employee details appear on the right (level 2);
* or by making them disappear and by disbling some options;
;
EVTROUTINE HANDLING(#TREEVIEW.ItemGotFocus) OPTIONS(*NOCLEARMESSAGES *NOCLEARERRORS);
If '#Treeview.CurrentItem.Level = 2';
Set #Btn_Dep Enabled(False);
Set #Btn_Emp Enabled(True);
Clr_List #ListView ;
Select (#empno #SurName #GiveName #salary) From_File(PslMst1) with_key(#Deptment #Section);
Add_Entry #ListView;
EndSelect ;
Set #GroupBox Visible(True);
Else ;
Set #Btn_Dep Enabled(True);
Set #Btn_Emp Enabled(False);
Set #GroupBox Visible(False);
EndIf ;
ENDROUTINE ;
;
* Handle request to send contents of employee list view to XL ;
;
EVTROUTINE HANDLING(#BTN_EMP.Click) ;
Invoke #OS.MakeTempFileName iSuffix(XLS) iNamePart1('Dept') iNamePart2(#deptment) iNamePart3('Section') iNamePart4(#section) iNamePart5('Details') oFileName(#FileName) ;
use builtin(transform_list) with_args(#listview #filename b) to_get(#retcode);
if '#RetCode = OK';
Invoke #OS.ExecuteFile iFileName(#FileName) oRetCode(#osErrCode) ;
Endif ;
ENDROUTINE ;
;
* Handle request to send summary of all sections in current department to XL ;
;
EVTROUTINE HANDLING(#BTN_DEP.Click) ;
Invoke #OS.MakeTempFileName iSuffix(XLS) iNamePart1('Dept') iNamePart2(#deptment) iNamePart3('Summary') oFileName(#FileName) ;
Def_List #DeptSum (#DeptDesc #secdesc #Employees #SalaryTot #SalaryAve) type(*working) entrys(9999);
Select fields(#Section #SecDesc) from_file(Sectab) with_key(#deptment);
Change (#Employees #SalaryTot #SalaryAve) 0 ;
Select Fields (#Salary) From_File(PslMst1) With_Key(#Deptment #Section);
Change #SalaryTot '#SalaryTot + #Salary';
Change #Employees '#Employees + 1';
EndSelect ;
if '#Employees > 0';
Change #SalaryAve '#SalaryTot / #Employees' ;
Endif ;
Add_Entry #DeptSum;
Endselect ;
Use builtin(Transform_List) with_args(#DeptSum #FileName b) to_get(#retcode);
Clr_List #DeptSum ;
if '#RetCode = OK';
Invoke #OS.ExecuteFile iFileName(#FileName) oRetCode(#osErrCode);
Endif ;
ENDROUTINE ;
;
* Handle request to send summary of all departments to XL ;
;
EVTROUTINE HANDLING(#BTN_ALL.Click) ;
Invoke #OS.MakeTempFileName iSuffix(XLS) iNamePart1('AllDeptSummary') oFileName(#FileName) ;
Def_List #AllSum (#DeptDesc #Employees #SalaryTot #SalaryAve) type(*working) entrys(9999);
Select fields(#Deptment #DeptDesc) from_file(DepTab) ;
Change (#Employees #SalaryTot #SalaryAve) 0 ;
Select Fields (#Salary) From_File(PslMst1) With_Key(#Deptment);
Change #SalaryTot '#SalaryTot + #Salary';
Change #Employees '#Employees + 1';
EndSelect ;
if '#Employees > 0';
Change #SalaryAve '#SalaryTot / #Employees' ;
Endif ;
Add_Entry #AllSum;
Endselect ;
Use builtin(Transform_List) with_args(#AllSum #FileName b) to_get(#retcode);
Clr_List #AllSum ;
if '#RetCode = OK';
Invoke #OS.ExecuteFile iFileName(#FileName) oRetCode(#osErrCode) ;
Endif ;
ENDROUTINE ;
;
END_COM ;