01/03/2010

Criando Documento Excel com OLE

Necessidade: Criar uma planilha Excel formatada e que o usuário possa visualizar o processo de execução de criação da mesma.

Solução: Vamos utilizar Object Linking and Embedding (OLE), uma tecnologia desenvolvida pela Microsoft que permite incorporar e linkar a documentos e outros objetos.


*-----------------------------------------------------------------------
* | REPORT DESCRIPTION : EXCEL SHEET USING OLE AUTOMATION
*-----------------------------------------------------------------------
REPORT ZRR_SMAPLE NO STANDARD PAGE HEADING.

*-----------------------------------------------------------------------
* | S.T.A.N.D.A.R.D T.Y.P.E P.O.O.L.S D.E.C.L.A.R.A.T.I.O.N.S
*-----------------------------------------------------------------------

TYPE-POOLS ole2 .

*-----------------------------------------------------------------------
* | O.L.E O.B.J.E.C.T.S D.E.C.L.A.R.A.T.I.O.N.S
*-----------------------------------------------------------------------

DATA: obj_actwindow TYPE ole2_object, " Active Window
obj_excel TYPE ole2_object, " Excel Object
obj_books TYPE ole2_object, " List Of Workbooks
obj_book TYPE ole2_object, " Workbook
obj_cell TYPE ole2_object, " Cell
obj_font TYPE ole2_object. " Font

*-----------------------------------------------------------------------
* | I.N.T.E.R.N.A.L T.A.B.L.E.S T.Y.P.E.S D.E.C.L.A.R.A.T.I.O.N.S
*-----------------------------------------------------------------------

TYPES : ty_spfli TYPE STANDARD TABLE OF spfli.

*-----------------------------------------------------------------------
* | I.N.T.E.R.N.A.L T.A.B.L.E.S D.E.C.L.A.R.A.T.I.O.N.S
*-----------------------------------------------------------------------

DATA : it_spfli TYPE ty_spfli WITH HEADER LINE.

*-----------------------------------------------------------------------
* | V.A.R.I.A.B.L.E.S D.E.C.L.A.R.A.T.I.O.N.S
*-----------------------------------------------------------------------

DATA : row_idx TYPE i. " ROW INDEX

*-----------------------------------------------------------------------
* | S.T.A.R.T O.F S.E.L.E.C.T.I.O.N
*-----------------------------------------------------------------------

START-OF-SELECTION.

* |-> BIND SPFLI INTERNAL TABLE

SELECT * FROM spfli INTO TABLE it_spfli UP TO 10 ROWS.

* |-> DISPLAY HEADER LIST

ULINE (61).

WRITE: / sy-vline NO-GAP,
(3) 'Flg'(001) COLOR COL_HEADING NO-GAP, sy-vline NO-GAP,
(4) 'Nº'(002) COLOR COL_HEADING NO-GAP, sy-vline NO-GAP,
(20) 'De'(003) COLOR COL_HEADING NO-GAP, sy-vline NO-GAP,
(20) 'Para'(004) COLOR COL_HEADING NO-GAP, sy-vline NO-GAP,
(8) 'Hora'(005) COLOR COL_HEADING NO-GAP, sy-vline NO-GAP.
ULINE (61).

* |-> DISPLAY FLIGHTS

LOOP AT it_spfli.
WRITE: / sy-vline NO-GAP,
it_spfli-carrid COLOR COL_KEY NO-GAP, sy-vline NO-GAP,
it_spfli-connid COLOR COL_NORMAL NO-GAP, sy-vline NO-GAP,
it_spfli-cityfrom COLOR COL_NORMAL NO-GAP, sy-vline NO-GAP,
it_spfli-cityto COLOR COL_NORMAL NO-GAP, sy-vline NO-GAP,
it_spfli-deptime COLOR COL_NORMAL NO-GAP, sy-vline NO-GAP.
ENDLOOP.
ULINE (61).

* |-> CALL SAPGUI_PROGRESS_INDICATOR

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = text-007 " Excel executado ...
EXCEPTIONS
OTHERS = 1.

* |-> START EXCEL OBJECT

CREATE OBJECT obj_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.

* |-> DEFINE WITH VISIBLE

SET PROPERTY OF obj_excel 'Visible' = 1.
PERFORM err_hdl.

* |-> CALL SAPGUI_PROGRESS_INDICATOR

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = text-008 " Abrir nova pasta trabalho...
EXCEPTIONS
OTHERS = 1.

* |-> GET LIST OF WORKBOOKS, INITIALLY EMPTY

CALL METHOD OF obj_excel 'Workbooks' = obj_books.
PERFORM err_hdl.

* |-> ADD A NEW WORKBOOK

CALL METHOD OF obj_books 'Add' = obj_book.
PERFORM err_hdl.

* |-> CALL SAPGUI_PROGRESS_INDICATOR

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = text-009 " Preencher células com tabela interna...
EXCEPTIONS
OTHERS = 1.

* |-> OUTOUT COLUMN HEADINGS TO ACTIVE EXCEL SHEET

PERFORM fill_cell USING 1 1 1 'Flg'(001).
PERFORM fill_cell USING 1 2 1 'Nº'(002).
PERFORM fill_cell USING 1 3 1 'De'(003).
PERFORM fill_cell USING 1 4 1 'Para'(004).
PERFORM fill_cell USING 1 5 1 'Hora'(005).

* |-> COPY FLIGHTS TO ACTIVE EXCEL SHEET

LOOP AT it_spfli.

row_idx = sy-tabix + 1.

PERFORM fill_cell USING row_idx 1 0 it_spfli-carrid.
PERFORM fill_cell USING row_idx 2 0 it_spfli-connid.
PERFORM fill_cell USING row_idx 3 0 it_spfli-cityfrom.
PERFORM fill_cell USING row_idx 4 0 it_spfli-cityto.
PERFORM fill_cell USING row_idx 5 0 it_spfli-deptime.

ENDLOOP.

* |-> SAVE CURRENT DOCUMENT

CALL METHOD OF obj_book 'SaveAs'
EXPORTING
#1 = 'C:\OLE_RR_SAMPLES.xls'.
PERFORM err_hdl.

* |-> CLOSE THE CURREENT WINDOW DOCUMENT

GET PROPERTY OF obj_excel 'ActiveWindow' = obj_actwindow .
CALL METHOD OF obj_actwindow 'Close' .

* |-> CLOSE THE CURREENT EXCEL THREAD

CALL METHOD OF obj_excel 'Quit' .

PERFORM err_hdl.

* |-> DISCONNECT FROM EXCEL

FREE OBJECT : obj_actwindow, " Active Window
obj_excel, " Excel Object
obj_books, " List Of Workbooks
obj_book, " Workbook
obj_cell, " Cell
obj_font. " Font

PERFORM err_hdl.

*-----------------------------------------------------------------------
* |-> FORM : FILL_CELL
*-----------------------------------------------------------------------
* |-> TEXT : SETS CELL AT COORDINATES i,j TO VALUE val BOLDTYPE bold
*-----------------------------------------------------------------------
FORM fill_cell USING i j bold val.

CALL METHOD OF obj_excel 'Cells' = obj_cell
EXPORTING
#1 = i
#2 = j.

PERFORM err_hdl.

SET PROPERTY OF obj_cell 'Value' = val .
PERFORM err_hdl.

GET PROPERTY OF obj_cell 'Font' = obj_font.
PERFORM err_hdl.

SET PROPERTY OF obj_font 'Bold' = bold .
PERFORM err_hdl.

ENDFORM. "FILL_CELL
*-----------------------------------------------------------------------
* |-> FORM : ERR_HDL
*-----------------------------------------------------------------------
* |-> TEXT : OUTPUTS OLE ERROR IF ANY
*-----------------------------------------------------------------------
FORM err_hdl.

IF sy-subrc <> 0.

WRITE: / 'Erro OLE: returncode = '(010), sy-subrc.
STOP.

ENDIF.

ENDFORM. " ERR_HDL


Para mais detalhes, accesse: OLE Automation

Nenhum comentário:

Postar um comentário