REPORTS

[Former Member]

HOW REPORT CAN BE AUTOMATICALLY DISPLAYED IN EXCEL WHEN EXECUTED

[Former Member]

hi thr,...

try using OLE(object linking and embedding) concept to do so...

jus copy paste the below mentioned code and execute it u wil get an idea..

-

---

*

REPORT zrosh_ole_format.

TYPE-POOLS ole2 .

DATA: count TYPE i,

num TYPE i,

application TYPE ole2_object,

workbook TYPE ole2_object,

excel TYPE ole2_object,

sheet TYPE ole2_object,

h_columns TYPE ole2_object,

h_rows TYPE ole2_object,

h_auto TYPE ole2_object,

h_border TYPE ole2_object,

cells TYPE ole2_object.

CONSTANTS: row_max TYPE i VALUE 256.

DATA index TYPE i.

DATA:

h_cell TYPE ole2_object, " cell

h_f TYPE ole2_object, " font

h_range TYPE ole2_object,

h_merge TYPE ole2_object,

h_int TYPE ole2_object.

CREATE OBJECT excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.

WRITE: / 'No EXCEL creation possible'.

STOP.

ENDIF.

SET PROPERTY OF excel 'DisplayAlerts' = 0.

CALL METHOD OF excel 'WORKBOOKS' = workbook .

SET PROPERTY OF excel 'VISIBLE' = 1.

• creating workbook

SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF workbook 'ADD'.

CALL METHOD OF excel 'WORKSHEETS' = sheet.

CALL METHOD OF sheet 'ADD'.

FREE OBJECT sheet.

CALL METHOD OF excel 'WORKSHEETS' = sheet

EXPORTING

#1 = 1.

SET PROPERTY OF sheet 'NAME' = 'name'.

CALL METHOD OF sheet 'ACTIVATE'.

DATA: col TYPE i VALUE 1,

row TYPE i VALUE 1,

col1 TYPE i VALUE 2,

col_real TYPE i VALUE 1.

num = 1.

********************************************************

• autofit the cell contents************************

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = 12

#2 = 4.

SET PROPERTY OF cells 'Value' = 'Object Linking and embedding'.

CALL METHOD OF excel 'Cells' = h_cell

EXPORTING

#1 = 12

#2 = 4.

CALL METHOD OF h_cell 'Columns' = h_columns .

CALL METHOD OF h_columns 'AutoFit' = h_auto .

CALL METHOD OF h_cell 'Rows' = h_rows .

CALL METHOD OF h_rows 'AutoFit' = h_auto .

FREE OBJECT h_cell.

***************************************************

• to color the entire row******

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = 16

#2 = 1.

SET PROPERTY OF cells 'Value' = 'object linking and embedding'.

CALL METHOD OF excel 'Rows' = h_rows

EXPORTING

#1 = '16:16'.

GET PROPERTY OF h_rows 'Interior' = h_int.

SET PROPERTY OF h_int 'ColorIndex' = 5.

*********************************************

• Save excel speadsheet to particular filename

GET PROPERTY OF excel 'ActiveSheet' = sheet.

CALL METHOD OF sheet 'SaveAs'

EXPORTING

#1 = 'C:\roshani.xls' "filename

#2 = 1. "fileFormat

• CALL METHOD OF workbook 'CLOSE'.

• CALL METHOD OF excel 'QUIT'.

*

• FREE OBJECT sheet.

• FREE OBJECT workbook.

• FREE OBJECT excel.

*****************************************************

Regards,

Roshani

[Former Member]

I THINK THERE IS A FUNCTION MODULE

[Former Member]

hey try the code tht i hav mentioned above...

a lot of formatting options are available with ole ...

Regards,

Roshani

[Former Member]

Using this method, you can download files to PC.

CALL METHOD cl_gui_frontend_services=>gui_download

EXPORTING

• BIN_FILESIZE =

filename =

• FILETYPE = 'ASC'

• APPEND = SPACE

• WRITE_FIELD_SEPARATOR = SPACE

• HEADER = '00'

• TRUNC_TRAILING_BLANKS = SPACE

• WRITE_LF = 'X'

• COL_SELECT = SPACE

• COL_SELECT_MASK = SPACE

• DAT_MODE = SPACE

• CONFIRM_OVERWRITE = SPACE

• NO_AUTH_CHECK = SPACE

• CODEPAGE = SPACE

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• WRITE_BOM = SPACE

• TRUNC_TRAILING_BLANKS_EOL = 'X'

• IMPORTING

• FILELENGTH =

changing

data_tab =

• EXCEPTIONS

• FILE_WRITE_ERROR = 1

• NO_BATCH = 2

• GUI_REFUSE_FILETRANSFER = 3

• INVALID_TYPE = 4

• NO_AUTHORITY = 5

• UNKNOWN_ERROR = 6

• HEADER_NOT_ALLOWED = 7

• SEPARATOR_NOT_ALLOWED = 8

• FILESIZE_NOT_ALLOWED = 9

• HEADER_TOO_LONG = 10

• DP_ERROR_CREATE = 11

• DP_ERROR_SEND = 12

• DP_ERROR_WRITE = 13

• UNKNOWN_DP_ERROR = 14

• ACCESS_DENIED = 15

• DP_OUT_OF_MEMORY = 16

• DISK_FULL = 17

• DP_TIMEOUT = 18

• FILE_NOT_FOUND = 19

• DATAPROVIDER_EXCEPTION = 20

• CONTROL_FLUSH_ERROR = 21

• NOT_SUPPORTED_BY_GUI = 22

• ERROR_NO_GUI = 23

• others = 24

.

IF sy-subrc <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Regards,

Pavan

[Former Member]

CALL FUNCTION 'DOWNLOAD'

EXPORTING

FILENAME = C_FILE

FILETYPE = 'DAT'

TABLES

DATA_TAB = TB_DLTAB

EXCEPTIONS

INVALID_FILESIZE = 1

INVALID_TABLE_WIDTH = 2

INVALID_TYPE = 3

NO_BATCH = 4

UNKNOWN_ERROR = 5

OTHERS = 6.

But This is obsolete....

Use class CL_GUI_FRONTEND_SERVICES.

Regards,

Pavan

[Former Member]

Hi,

REPORT z_test.

INCLUDE ole2incl.

• handles for OLE objects

DATA: h_excel TYPE ole2_object, " Excel object

h_mapl TYPE ole2_object, " list of workbooks

h_map TYPE ole2_object, " workbook

h_cell TYPE ole2_object, " cell

h_f TYPE ole2_object, " font

h_col TYPE ole2_object,

h_row TYPE ole2_object,

h_int TYPE ole2_object,

h_auto TYPE ole2_object,

h_select TYPE ole2_object,

h_range TYPE ole2_object,

h_merge TYPE ole2_object,

h_columns TYPE ole2_object,

h_rows TYPE ole2_object,

h_borders TYPE ole2_object,

h_protect TYPE ole2_object,

row TYPE i,

col TYPE i,

v_prog(70),

v_range1(10),

v_range2(10),

v_r1(10),

v_r2(10).

----

---

START-OF-SELECTION .

END-OF-SELECTION .

PERFORM f_start_excel .

PERFORM f_display_hdr .

PERFORM f_stop_excel .

&----

---

*& Form f_start_excel

&----

---

FORM f_start_excel .

• start Excel

CREATE OBJECT h_excel 'EXCEL.APPLICATION'.

SET PROPERTY OF h_excel 'Visible' = 0.

• 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.

ENDFORM. " f_start_excel

&----

---

*& Form f_stop_excel

&----

---

FORM f_stop_excel .

FREE OBJECT h_cell.

CALL METHOD OF h_excel 'Cells' = h_cell .

GET PROPERTY OF h_cell 'Select' = h_select.

CALL METHOD OF h_cell 'Columns' = h_columns .

CALL METHOD OF h_columns 'AutoFit' = h_auto .

CALL METHOD OF h_cell 'Rows' = h_rows .

CALL METHOD OF h_rows 'AutoFit' = h_auto .

FREE OBJECT h_cell.

CALL METHOD OF h_excel 'Cells' = h_cell

EXPORTING

#1 = 1

#2 = 1.

GET PROPERTY OF h_cell 'Select' = h_select.

• CALL METHOD OF h_excel 'Protect' = h_protect .

CALL METHOD OF h_excel 'Workbooks' = h_mapl.

CALL METHOD OF h_mapl 'Protect' = h_protect .

SET PROPERTY OF h_protect 'Structure' = 'True'.

SET PROPERTY OF h_protect 'Windows' = 'False'.

SET PROPERTY OF h_excel 'Visible' = 1.

FREE OBJECT h_excel.

PERFORM err_hdl.

ENDFORM. " f_stop_excel

&----

---

*& Form ERR_HDL

&----

---

FORM err_hdl.

IF sy-subrc <> 0.

WRITE: / 'OLE Error :'(010), sy-subrc.

STOP.

ENDIF.

ENDFORM. " ERR_HDL

&----

---

*& Form f_display_hdr

&----

---

FORM f_display_hdr .

row = 2 .

PERFORM fill_cell USING row 3 'OHO SHEET' 1 35 1 1 0 16 0 1.

PERFORM f_merge_cells USING 'A' 'E' -4108 .

row = row + 2 .

PERFORM fill_cell USING row 3 'Program Details' 1 43 1 1 0 12 0 1.

PERFORM f_merge_cells USING 'A' 'E' -4108 .

ENDFORM. " f_display_hdr

----

---

• FORM FILL_CELL *

----

---

FORM fill_cell USING p_row p_col p_val p_shrink p_bkclr p_pat

p_bold p_italic p_size p_fclr p_uline .

CALL METHOD OF h_excel 'Cells' = h_cell

EXPORTING

#1 = p_row

#2 = p_col.

SET PROPERTY OF h_cell 'Value' = p_val .

SET PROPERTY OF h_cell 'ShrinkToFit' = p_shrink .

GET PROPERTY OF h_cell 'Interior' = h_int.

SET PROPERTY OF h_int 'ColorIndex' = p_bkclr .

SET PROPERTY OF h_int 'Pattern' = p_pat.

GET PROPERTY OF h_cell 'Font' = h_f.

SET PROPERTY OF h_f 'Bold' = p_bold .

SET PROPERTY OF h_f 'Italic' = p_italic .

SET PROPERTY OF h_f 'Size' = p_size .

SET PROPERTY OF h_f 'ColorIndex' = p_fclr .

SET PROPERTY OF h_f 'Name' = 'Arial' .

SET PROPERTY OF h_f 'Underline' = p_uline .

ENDFORM. "FILL_CELL

&----

---

*& Form f_merge_cells

&----

---

FORM f_merge_cells USING p_r1 p_r2 p_val.

CLEAR : v_r1, v_r2, v_range1, v_range2 .

v_range1 = row .

v_range2 = row .

SHIFT v_range1 LEFT DELETING LEADING ' ' .

SHIFT v_range2 LEFT DELETING LEADING ' ' .

CONCATENATE p_r1 v_range1 INTO v_r1 .

CONCATENATE p_r2 v_range2 INTO v_r2 .

CALL METHOD OF h_excel 'Range' = h_range

EXPORTING

#1 = v_r1

#2 = v_r2.

CALL METHOD OF h_range 'Select' = h_select .

CALL METHOD OF h_range 'Merge' = h_merge .

SET PROPERTY OF h_range 'HorizontalAlignment' = p_val.

ENDFORM. " f_merge_cells

&----

---

*& Form f_progress_bar

&----

---

FORM f_progress_bar USING value(p_text) p_val.

WAIT UP TO p_val SECONDS.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

• PERCENTAGE = 0

text = p_text

EXCEPTIONS

OTHERS = 1.

CLEAR v_prog .

ENDFORM. " f_progress_bar

&----

---

*& Form f_make_border

&----

---

FORM f_make_border USING p_r2 .

v_range2 = row .

SHIFT v_range2 LEFT DELETING LEADING ' ' .

CONCATENATE p_r2 v_range2 INTO v_r2 .

CALL METHOD OF h_excel 'Range' = h_range

EXPORTING

#1 = v_range1

#2 = v_r2.

CALL METHOD OF h_range 'Select' = h_select .

*left

CALL METHOD OF h_range 'BORDERS' = h_borders

EXPORTING

#1 = '1'.

SET PROPERTY OF h_borders 'LineStyle' = '1'.

SET PROPERTY OF h_borders 'WEIGHT' = '3'. "4=max

FREE OBJECT h_borders.

• right

CALL METHOD OF h_range 'BORDERS' = h_borders

EXPORTING

#1 = '2'.

SET PROPERTY OF h_borders 'LineStyle' = '1'.

SET PROPERTY OF h_borders 'WEIGHT' = '3'.

FREE OBJECT h_borders.

• top

CALL METHOD OF h_range 'BORDERS' = h_borders

EXPORTING

#1 = '3'.

SET PROPERTY OF h_borders 'LineStyle' = '1'.

SET PROPERTY OF h_borders 'WEIGHT' = '3'.

FREE OBJECT h_borders.

• bottom

CALL METHOD OF h_range 'BORDERS' = h_borders

EXPORTING

#1 = '4'.

SET PROPERTY OF h_borders 'LineStyle' = '1'.

SET PROPERTY OF h_borders 'WEIGHT' = '3'.

FREE OBJECT h_borders.

ENDFORM. "f_make_border