OLE Programming for Excel and Word

A+ A-

Introduction: -

Display dynamic information (data) in three different formats.

  1. Simple ALV
  2. MS-Excel Sheet
  3. MS-Word Document

Topic Cover: -

  1. Dynamic Internal Table Creation
  2. Field-symbol Dynamic Programming
  3. OLE Automation Process on MS-Excel and MS-Document.

Step 1.

Enter a valid table name if Table input field and click “Show Columns”, button to get all columns of respective table.

All columns will display in below Table Control with their respective short description in column Name & Text.

Step 2.

Information would be display in three different formats based on the user selection.

By the “No of Rows” option user can restrict the information for display.

As well as user can display only those columns information which he/she wants to display with the help of the selection of column from table control.

 

Step 3.

After select some of the columns from Table control or not, by the clicking on “ALV Display” button, we will get the simple ALV Display type output in new screen.


 

In output user will get only those column records what they selected or it will display all columns records.

On top of the page the selected table description will display.

And by the selection of any one of the columns value, it will generate a pop-up message which will display the selected column with selected value.

 

Step 4: -

As the same way, by the clicking of “Excel Display” button a Browser will open to Select a folder, where the generated excel file would be save.

After selecting a folder, again a pop-up message will display, where user need to select a option to display the generated excel file or it will process in back ground.


By the selection of above pop-up message, excel will display in front end or process will be in background.

 

The Excel file will create with the respective information’s.

The OLE Process is little bit time taking process, so if we run the particular program for the large number of information it will take time to generate the final file.

Step 5: -

In the same way, by the clicking of “Word Display” button a Browser will open to select a folder, where the generated Word File would be saved.

After selecting a folder, again a pop-up message will display, where user need to select an option to display the generated word file or it will process in back ground.

By the selection of above pop-up message, word will display in front end or process will be in background.

 

Basically the current MS-Word file only support for 63 columns. If the selected column number is more then 63, then it will not process for word document.

Summary: -

Displaying and downloading the information in three different ways. Based on the Present scenario, it’s covering the customer main requirement in there won declared format, what they used to seek in output.

 

Source Code: -

It’s a Module Pool programming.

Two include are defined here.

  1. ZDATA_DISPLAY_TOP
  2. ZDATA_DISPLAY_PAI

In the ZDATA_DISPLAY_TOP Include, all global variables are declared.

*&---------------------------------------------------------------------*
*& Include ZDATA_DISPLAY_TOP                                 Module Pool      ZDATA_DISPLAY
*&
*&---------------------------------------------------------------------*

PROGRAM  ZDATA_DISPLAY.

TYPE-POOLS:   OLE2, ABAP, SLIS.
TABLES:       DD03L, DD02VV.

*&-----------------------------TYPE FOR DISPLAY THE SELECTED TABLE COLUMNS AND RESPECTIVE COLUMN TEXT
TYPES:       
BEGIN OF  S_INFO,
MARK,
FLDNAM 
TYPE DD03L-FIELDNAME,
FLDTXT 
TYPE AS4TEXT,
LEN    
TYPE I,
END OF    S_INFO.
*&-----------------------------TYPE FOR DISPLAY THE SELECTED TABLE COLUMNS AND RESPECTIVE COLUMN TEXT


*&-----------------------------TABLE CONTROL FOR DISPLAY THE SEELCTED TBALE COLUMNS AND TEXT
CONTROLS:     TB_MAIN
TYPE TABLEVIEW USING SCREEN '9000'.
*&-----------------------------TABLE CONTROL FOR DISPLAY THE SEELCTED TBALE COLUMNS AND TEXT


DATA:         G_TABNAME  
TYPE TABNAME.                   "TABLE NAME

DATA:         GIT_INFO   
TYPE TABLE OF S_INFO,           "INTERNAL TABLE TO STORE TABLE'S COLUMNS AND COLUMN TEXT
GFL_INFO   
LIKE LINE OF GIT_INFO.          "WORK AREA FOR READING THE GIT_INFO INTERNAL TABLE

DATA:         G_OK       
TYPE SY-UCOMM,                  "FOR READ THE USER INTERACTION
G_SAVE     
TYPE SY-UCOMM.

DATA:         GIT_TABINFO
TYPE ABAP_COMPDESCR_TAB,        "INTERNAL TABLE FOR STORE THE SELECTED TABLE COLUMNS INFORMATION
GFL_TABINFO
TYPE ABAP_COMPDESCR.            "WORK AREA FOR READ THE GIT_TABINFO INTERNAL TABLE
DATA:         CL_ABAP
TYPE REF TO CL_ABAP_STRUCTDESCR.    "CLASS OBJECT TO READ THE SELECTED TABLE COLUMNS INFORMATION

DATA:         G_RECS     
TYPE I.                         "GLOBAL INTEGER VARIABLE

DATA:         G_ROWS     
TYPE I.                         "ROW INFORMATION TO DISPLAY INFORMATION IN RESPECTIVE SELECTED METHOD

DATA:         GIT_FLDS   
TYPE LVC_T_FCAT,                "INTERNAL TABLE TO STORE THE COLUMNS & TEXT INFORMATION FOR CREATE THE DYNAMIC TABLE
GFL_FLDS   
TYPE LVC_S_FCAT.                "WORK AREA FOR READ THE GIT_FLDS INTERNAL TABLE


DATA:         GIT        
TYPE REF TO DATA,               "INTERNAL TABLE FOR DYNAMIC TABLE
GFL        
TYPE REF TO DATA.               "WORK AREA FOR THE DYNAMIC TABLE

FIELD-SYMBOLS:  <GIT>    
TYPE STANDARD TABLE,
<GFL>,
<VAL>.

DATA:         GIT_FCAT   
TYPE SLIS_T_FIELDCAT_ALV,     "INTERNAL TABLE FOR THE FIELD CATALOG INFORMATION FOR RESUE ALV GRID DISPLAY FUNCTION MODULE
GFL_FCAT   
TYPE SLIS_FIELDCAT_ALV.       "WORK AREA FOR THE GIT_FCAT INTERNAL TABLE.

DATA:         GIT_EVENT  
TYPE SLIS_T_EVENT,            "INTERNAL TABLE FOR ALV EVENT INFORMATION FOR TOP OF THE PAGE
GFL_EVENT  
TYPE SLIS_ALV_EVENT.          "WORK AREA FOR GIT_EVENT INTERNAL TABLE
DATA:         GIT_TOP    
TYPE SLIS_T_LISTHEADER,       "ALV TOP OF THE PAGE
GFL_TOP    
TYPE SLIS_LISTHEADER.         "WORK AREA FOR GIT_TOP

DATA:         G_SAVE_PATH
TYPE STRING.                  "PATH INFORMATION TO SAVE THE XL/DOC FILE
DATA:         G_VISB     
TYPE C.                       "VISIBLE OR INVISIBLE XL/DOC FILE

DATA:         OBJ_XCL    
TYPE OLE2_OBJECT,
OBJ_SHT    
TYPE OLE2_OBJECT,
OBJ_WBK    
TYPE OLE2_OBJECT,
OBJ_CEL    
TYPE OLE2_OBJECT,
OBJ_ROW    
TYPE OLE2_OBJECT,
OBJ_FNT    
TYPE OLE2_OBJECT,
OBJ_CLR    
TYPE OLE2_OBJECT,
OBJ_RNG    
TYPE OLE2_OBJECT,
OBJ_INT    
TYPE OLE2_OBJECT,
OBJ_BRD    
TYPE OLE2_OBJECT,
OBJ_COL    
TYPE OLE2_OBJECT.

TYPES:       
BEGIN OF S_CELL,
CELL 
TYPE STRING,
POS  
TYPE C,
END OF   S_CELL.
DATA:         GIT_CELL 
TYPE TABLE OF S_CELL,
GFL_CELL 
LIKE LINE OF GIT_CELL.

DATA:         OBJ_WRD    
TYPE OLE2_OBJECT,
OBJ_DOC    
TYPE OLE2_OBJECT,
OBJ_DCS    
TYPE OLE2_OBJECT,
OBJ_SLC    
TYPE OLE2_OBJECT,
OBJ_ADC    
TYPE OLE2_OBJECT,
OBJ_APP    
TYPE OLE2_OBJECT,
OBJ_ALG    
TYPE OLE2_OBJECT,
OBJ_TBS    
TYPE OLE2_OBJECT,
OBJ_TAB    
TYPE OLE2_OBJECT.

-------------------------------------------------------------------- ZDATA_DISPLAY_TOP-----------------------------------

In the ZDATA_DISPLAY_PAI Include all logic are declared with suitable understandable information’s.

*&---------------------------------------------------------------------*
*&  Include           ZDATA_DISPLAY_PAI
*&---------------------------------------------------------------------*

module STATUS_9000 output.
**  SET PF-STATUS 'xxxxxxxx'.
SET TITLEBAR 'INFO'.
DESCRIBE TABLE GIT_INFO LINES TB_MAIN-LINES.
endmodule.                
" STATUS_9000  OUTPUT


*----------------------------------------------------------------------*
***INCLUDE ZPRV_INFO_PAI .
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_9000  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
module USER_COMMAND_9000 input.
G_SAVE = G_OK.
CLEAR: G_OK.
CASE G_SAVE.
WHEN 'CLICK'.
PERFORM SUB_GET_TABLE_INFO USING G_TABNAME.
WHEN 'ALV'.
PERFORM SUB_CREATE_DYNAMIC_TABLE.
PERFORM SUB_SELECT_TABLE.
PERFORM SUB_DISPLAY_ALV.
WHEN 'XL'.
PERFORM SUB_CREATE_DYNAMIC_TABLE.
PERFORM SUB_SELECT_TABLE.
PERFORM SUB_DISPLAY_INFO_XL.
WHEN 'DOC'.
PERFORM SUB_CREATE_DYNAMIC_TABLE.
IF <GIT> IS NOT ASSIGNED.
EXIT.
ENDIF.
PERFORM SUB_SELECT_TABLE.
PERFORM SUB_DISPLAY_INFO_WORD.

ENDCASE.
endmodule.                
" USER_COMMAND_9000  INPUT
*&---------------------------------------------------------------------*
*&      Form  SUB_GET_TABLE_INFO
*&---------------------------------------------------------------------*
*       Fetching the Table information based on the Table Name
*----------------------------------------------------------------------*
*      -->P_TABNAME  TYPE TABNAME
*----------------------------------------------------------------------*
form SUB_GET_TABLE_INFO  using    p_tabname TYPE TABNAME.
PERFORM SUB_IS_VALID_TABLE USING P_TABNAME.
IF P_TABNAME EQ ''.
EXIT.
ENDIF.
REFRESH: GIT_TABINFO.
CLEAR: CL_ABAP.
CL_ABAP ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_NAME( p_tabname ).
GIT_TABINFO  = CL_ABAP->COMPONENTS[].
DESCRIBE TABLE GIT_TABINFO LINES G_RECS.
IF G_RECS EQ 0.
MESSAGE 'Not a Valid Table' TYPE 'I'.
CLEAR: P_TABNAME.
EXIT.
ENDIF.
REFRESH: GIT_INFO.
LOOP AT GIT_TABINFO INTO GFL_TABINFO.
CLEAR: GFL_INFO.
GFL_INFO-FLDNAM = GFL_TABINFO-NAME.
GFL_INFO-LEN    = GFL_TABINFO-LENGTH /
2.
PERFORM SUB_GET_FIELD_TEXT USING p_tabname GFL_TABINFO-NAME GFL_INFO-FLDTXT.
APPEND GFL_INFO TO GIT_INFO.
ENDLOOP.
endform.                   
" SUB_GET_TABLE_INFO
*&---------------------------------------------------------------------*
*&      Form  SUB_GET_FIELD_TEXT
*&---------------------------------------------------------------------*
*       Getting the Field Text based on the Field Name
*----------------------------------------------------------------------*
*      -->P_NAME
*      -->P_TXT
*----------------------------------------------------------------------*
form SUB_GET_FIELD_TEXT  using    P_TABLE TYPE ddobjname
p_name
TYPE FIELDNAME
p_txt
TYPE AS4TEXT.
DATA: LIT_DDT
TYPE DFIES OCCURS 0,
LFL_DDT
LIKE LINE OF LIT_DDT.
CALL FUNCTION 'DDIF_FIELDINFO_GET'
EXPORTING
tabname              = P_TABLE
FIELDNAME            = P_NAME
LANGU                = SY-LANGU
TABLES
DFIES_TAB            = LIT_DDT
EXCEPTIONS
NOT_FOUND            =
1
INTERNAL_ERROR       =
2
OTHERS               = 3
.
IF sy-subrc EQ 0.
LOOP AT LIT_DDT INTO LFL_DDT.
P_TXT = LFL_DDT-FIELDTEXT.
EXIT.
ENDLOOP.
ENDIF.
endform.                   
" SUB_GET_FIELD_TEXT
*&---------------------------------------------------------------------*
*&      Form  SUB_DISPLAY_ALV
*&---------------------------------------------------------------------*
*       Display the Selected table information in ALV Grid based on the
*       Selected Columns or Whole Table & Number of Rows Entered for Display
*----------------------------------------------------------------------*
form SUB_DISPLAY_ALV .
DESCRIBE TABLE <GIT> LINES G_RECS.
IF G_RECS EQ 0.
MESSAGE 'No Records Selected for Display' TYPE 'I'.
EXIT.
ENDIF.
PERFORM SUB_CREATE_FLD_CAT.
G_RECS =
1.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM                = SY-REPID
I_CALLBACK_TOP_OF_PAGE            =
'TOP_OF_PAGE'
I_CALLBACK_USER_COMMAND           =
'USER_COMMAND'
IT_FIELDCAT                       = GIT_FCAT
I_SAVE                            =
'A'
TABLES
t_outtab                          = <GIT>
EXCEPTIONS
PROGRAM_ERROR                     =
1
OTHERS                            = 2
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


endform.                   
" SUB_DISPLAY_ALV
*&---------------------------------------------------------------------*
*&      Form  SUB_CREATE_DYNAMIC_COLS
*&---------------------------------------------------------------------*
*       Creating the FieldCatalog based on the Field Name and Field Text
*----------------------------------------------------------------------*
*      -->P_FLDNAM  text
*      -->P_FLDTXT  text
*----------------------------------------------------------------------*
form SUB_CREATE_DYNAMIC_COLS  using    p_fldnam TYPE LVC_FNAME
p_fldtxt
P_LEN .
CLEAR: GFL_FLDS.
DATA: LFL_DD03L
TYPE DD03L.
GFL_FLDS-FIELDNAME  = p_fldnam.
GFL_FLDS-OUTPUTLEN  = P_LEN.
GFL_FLDS-COLTEXT    = p_fldtxt.
CLEAR: LFL_DD03L.
SELECT SINGLE * FROM DD03L INTO LFL_DD03L WHERE TABNAME EQ G_TABNAME AND FIELDNAME EQ p_fldnam.
IF SY-SUBRC EQ 0.
GFL_FLDS-DATATYPE      = LFL_DD03L-DATATYPE.
GFL_FLDS-ROLLNAME      = LFL_DD03L-ROLLNAME.
GFL_FLDS-INTTYPE       = LFL_DD03L-INTTYPE.
GFL_FLDS-DOMNAME       = LFL_DD03L-DOMNAME.
GFL_FLDS-CHECKTABLE    = LFL_DD03L-CHECKTABLE.
ENDIF.
APPEND GFL_FLDS TO GIT_FLDS.
endform.                   
" SUB_CREATE_DYNAMIC_COLS
*&---------------------------------------------------------------------*
*&      Form  SUB_CREATE_DYNAMIC_TABLE
*&---------------------------------------------------------------------*
*       Create Dynamic table based on the Selected column or All columns of the selected table
*----------------------------------------------------------------------*
form SUB_CREATE_DYNAMIC_TABLE .
REFRESH: GIT_FLDS.
LOOP AT GIT_INFO INTO GFL_INFO WHERE MARK EQ 'X'.
PERFORM SUB_CREATE_DYNAMIC_COLS USING GFL_INFO-FLDNAM GFL_INFO-FLDTXT GFL_INFO-LEN.
ENDLOOP.
DESCRIBE TABLE GIT_FLDS LINES G_RECS.
IF G_RECS EQ 0.
LOOP AT GIT_INFO INTO GFL_INFO.
PERFORM SUB_CREATE_DYNAMIC_COLS USING GFL_INFO-FLDNAM GFL_INFO-FLDTXT GFL_INFO-LEN.
ENDLOOP.
ENDIF.
DESCRIBE TABLE GIT_FLDS LINES G_RECS.
IF G_RECS GT 67.
MESSAGE 'MS-Word Only Support 67 Column in Table, Cannot Proceed.' TYPE 'I'.
EXIT.
ENDIF.
CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
EXPORTING
IT_FIELDCATALOG = GIT_FLDS
IMPORTING
EP_TABLE        = GIT.
IF <GIT> IS ASSIGNED.
UNASSIGN <GIT>.
ENDIF.
ASSIGN GIT->* TO <GIT>.
CREATE DATA GFL LIKE LINE OF <GIT>.
IF <GFL> IS ASSIGNED.
UNASSIGN <GFL>.
ENDIF.
ASSIGN GFL->* TO <GFL>.
endform.                   
" SUB_CREATE_DYNAMIC_TABLE
*&---------------------------------------------------------------------*
*&      Form  SUB_CREATE_FLD_CAT
*&---------------------------------------------------------------------*