DOWNLOAD DATA FROM TABLE TO EXCEL SHEET:
This program is used to download data from table to excel sheet.
PROGRAM:
REPORT Z_DB_TABLE_TO_EXCEL.
INCLUDE OLE2INCL.
DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object
H_MAPL TYPE OLE2_OBJECT, " list of workbooks
H_MAP TYPE OLE2_OBJECT, " workbook
H_ZL TYPE OLE2_OBJECT, " cell
H_F TYPE OLE2_OBJECT. " font
DATA H TYPE I.
"---------------------------------------------------------
TYPES: BEGIN OF LINE,
COL1 TYPE I,
COL2 TYPE I,
END OF LINE.
TYPES ITAB TYPE LINE OCCURS 10.
DATA: LIN TYPE LINE,
TAB TYPE ITAB.
DO 5 TIMES.
LIN-COL1 = SY-INDEX.
LIN-COL2 = SY-INDEX ** 2.
APPEND LIN TO TAB.
ENDDO.
"---------------------------------------------------------
ULINE (25).
WRITE: / SY-VLINE NO-GAP,
(11) 'Single'(001) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,
(11) 'Square'(002) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP.
ULINE /(25).
* display flights
LOOP AT TAB INTO LIN.
WRITE: / SY-VLINE NO-GAP,
LIN-COL1 COLOR COL_KEY NO-GAP, SY-VLINE NO-GAP,
LIN-COL2 COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP.
ENDLOOP.
ULINE /(25).
" tell user what is going on
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
* PERCENTAGE = 0
TEXT = TEXT-007
EXCEPTIONS
OTHERS = 1.
* start Excel
CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
PERFORM ERR_HDL.
SET PROPERTY OF H_EXCEL 'Visible' = 1.
PERFORM ERR_HDL.
* tell user what is going on
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
* PERCENTAGE = 0
TEXT = TEXT-008
EXCEPTIONS
OTHERS = 1.
* get list of workbooks, initially empty
CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.
PERFORM ERR_HDL.
* add a new workbook
CALL METHOD OF H_MAPL 'Add' = H_MAP.
PERFORM ERR_HDL.
* tell user what is going on
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
* PERCENTAGE = 0
TEXT = TEXT-009
EXCEPTIONS
OTHERS = 1.
PERFORM FILL_CELL USING 1 1 1 'Single'(001).
PERFORM FILL_CELL USING 1 2 1 'Square'(002).
LOOP AT TAB INTO LIN.
* copy flights to active EXCEL sheet
H = SY-TABIX + 1.
PERFORM FILL_CELL USING H 1 0 LIN-COL1.
PERFORM FILL_CELL USING H 2 0 LIN-COL2.
ENDLOOP.
* disconnect from Excel
FREE OBJECT H_EXCEL.
PERFORM ERR_HDL.
FORM FILL_CELL USING I J BOLD VAL.
CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = I #2 = J.
PERFORM ERR_HDL.
SET PROPERTY OF H_ZL 'Value' = VAL .
PERFORM ERR_HDL.
GET PROPERTY OF H_ZL 'Font' = H_F.
PERFORM ERR_HDL.
SET PROPERTY OF H_F 'Bold' = BOLD .
PERFORM ERR_HDL.
*************
**color property
*************
*DATA interior TYPE ole2_object.
*GET PROPERTY OF H_ZL 'Interior' = interior .
*SET PROPERTY OF interior 'ColorIndex' = 27.
**************************
ENDFORM.
FORM ERR_HDL.
IF SY-SUBRC <> 0.
WRITE: / 'Fehler bei OLE-Automation:'(010), SY-SUBRC.
STOP.
ENDIF.
ENDFORM.
OUTPUT:
General output:
XL Sheet output:
This program is used to download data from table to excel sheet.
PROGRAM:
REPORT Z_DB_TABLE_TO_EXCEL.
INCLUDE OLE2INCL.
DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object
H_MAPL TYPE OLE2_OBJECT, " list of workbooks
H_MAP TYPE OLE2_OBJECT, " workbook
H_ZL TYPE OLE2_OBJECT, " cell
H_F TYPE OLE2_OBJECT. " font
DATA H TYPE I.
"---------------------------------------------------------
TYPES: BEGIN OF LINE,
COL1 TYPE I,
COL2 TYPE I,
END OF LINE.
TYPES ITAB TYPE LINE OCCURS 10.
DATA: LIN TYPE LINE,
TAB TYPE ITAB.
DO 5 TIMES.
LIN-COL1 = SY-INDEX.
LIN-COL2 = SY-INDEX ** 2.
APPEND LIN TO TAB.
ENDDO.
"---------------------------------------------------------
ULINE (25).
WRITE: / SY-VLINE NO-GAP,
(11) 'Single'(001) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP,
(11) 'Square'(002) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP.
ULINE /(25).
* display flights
LOOP AT TAB INTO LIN.
WRITE: / SY-VLINE NO-GAP,
LIN-COL1 COLOR COL_KEY NO-GAP, SY-VLINE NO-GAP,
LIN-COL2 COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP.
ENDLOOP.
ULINE /(25).
" tell user what is going on
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
* PERCENTAGE = 0
TEXT = TEXT-007
EXCEPTIONS
OTHERS = 1.
* start Excel
CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
PERFORM ERR_HDL.
SET PROPERTY OF H_EXCEL 'Visible' = 1.
PERFORM ERR_HDL.
* tell user what is going on
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
* PERCENTAGE = 0
TEXT = TEXT-008
EXCEPTIONS
OTHERS = 1.
* get list of workbooks, initially empty
CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.
PERFORM ERR_HDL.
* add a new workbook
CALL METHOD OF H_MAPL 'Add' = H_MAP.
PERFORM ERR_HDL.
* tell user what is going on
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
* PERCENTAGE = 0
TEXT = TEXT-009
EXCEPTIONS
OTHERS = 1.
PERFORM FILL_CELL USING 1 1 1 'Single'(001).
PERFORM FILL_CELL USING 1 2 1 'Square'(002).
LOOP AT TAB INTO LIN.
* copy flights to active EXCEL sheet
H = SY-TABIX + 1.
PERFORM FILL_CELL USING H 1 0 LIN-COL1.
PERFORM FILL_CELL USING H 2 0 LIN-COL2.
ENDLOOP.
* disconnect from Excel
FREE OBJECT H_EXCEL.
PERFORM ERR_HDL.
FORM FILL_CELL USING I J BOLD VAL.
CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = I #2 = J.
PERFORM ERR_HDL.
SET PROPERTY OF H_ZL 'Value' = VAL .
PERFORM ERR_HDL.
GET PROPERTY OF H_ZL 'Font' = H_F.
PERFORM ERR_HDL.
SET PROPERTY OF H_F 'Bold' = BOLD .
PERFORM ERR_HDL.
*************
**color property
*************
*DATA interior TYPE ole2_object.
*GET PROPERTY OF H_ZL 'Interior' = interior .
*SET PROPERTY OF interior 'ColorIndex' = 27.
**************************
ENDFORM.
FORM ERR_HDL.
IF SY-SUBRC <> 0.
WRITE: / 'Fehler bei OLE-Automation:'(010), SY-SUBRC.
STOP.
ENDIF.
ENDFORM.
OUTPUT:
General output:
XL Sheet output: