*&---------------------------------------------------------------------*
*& Report ZTEST_CODE_EXCEL_OLE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZTEST_CODE_EXCEL_OLE.
TYPE-POOLS ole2.
DATA: wf_cell_from TYPE ole2_object,
wf_cell_from1 TYPE ole2_object,
wf_cell_to TYPE ole2_object,
wf_cell_to1 TYPE ole2_object,
wf_excel TYPE ole2_object, " Excel object
wf_mapl TYPE ole2_object, " list of workbooks
wf_map TYPE ole2_object, " workbook
wf_worksheet TYPE ole2_object, " Worksheet
wf_cell TYPE ole2_object, " Cell Range
wf_cell1 TYPE ole2_object,
wf_range TYPE ole2_object, " Range of cells to be formatted
wf_range2 TYPE ole2_object,
wf_column1 TYPE ole2_object. " Column to be Autofit
DATA: BEGIN OF t_hex,
l_tab TYPE x,
END OF t_hex.
DATA: wf_deli(1) TYPE c, "delimiter
wf_action TYPE i,
wf_file TYPE string,
wf_path TYPE string,
wf_fullpath TYPE string.
TYPES: t_data1(1500) TYPE c,
int_ty TYPE TABLE OF t_data1. "line type internal table
*All the data was prepared as line type internal tables for faster
*download
DATA: int_matl TYPE int_ty ,
int_matl1 TYPE int_ty ,
wa_matl TYPE t_data1.
TYPES: BEGIN OF ty_mara,
matnr TYPE matnr,
mtart TYPE mtart,
matkl TYPE matkl,
meins TYPE meins,
END OF ty_mara.
DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
wa_mara TYPE ty_mara.
FIELD-SYMBOLS: <fs> .
DATA: wc_sheets LIKE sy-index. "no.of sheets
DATA: it_tabemp TYPE filetable,
gd_subrcemp TYPE i.
CONSTANTS wl_c09(2) TYPE n VALUE 09.
CLEAR wc_sheets.
DEFINE ole_check_error.
if &1 ne 0.
message e001(zz) with &1.
exit.
endif.
END-OF-DEFINITION.
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
REFRESH: it_tabemp.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = 'Select File'
* default_extension = 'xls'
default_file_name = 'Material Details'
* with_encoding =
file_filter = '*.xls'
initial_directory = 'C:\'
prompt_on_overwrite = ' '
CHANGING
filename = wf_file
path = wf_path
fullpath = wf_fullpath
user_action = wf_action
* file_encoding =
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
IF wf_action EQ 9.
MESSAGE 'No File have been Selected' TYPE 'S'.
ELSE.
p_file = wf_fullpath.
PERFORM create_excel.
ENDIF.
*&---------------------------------------------------------------------*
*& Form create_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM create_excel.
LOOP AT it_tabemp INTO p_file.
ENDLOOP.
* START THE EXCEL APPLICATION
CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.
* PUT EXCEL IN FRONT
SET PROPERTY OF wf_excel 'VISIBLE' = 1.
PERFORM err_hdl.
* CREATE AN EXCEL WORKBOOK OBJECT
CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
PERFORM err_hdl.
SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
PERFORM err_hdl.
CALL METHOD OF wf_mapl 'ADD' = wf_map.
PERFORM err_hdl.
*Assign the Delimiter to field symbol.
ASSIGN wf_deli TO <fs> TYPE 'X'.
t_hex-l_tab = wl_c09.
<fs> = t_hex-l_tab.
CLEAR int_matl.
REFRESH int_matl.
SELECT matnr
mtart
matkl
meins
FROM mara
INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed in the excel sheet
CONCATENATE 'Material Number'
'Material type'
'Material Group'
'Base Unit of Measure'
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
LOOP AT int_mara INTO wa_mara.
CONCATENATE wa_mara-matnr
wa_mara-mtart
wa_mara-matkl
wa_mara-meins
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
CLEAR wa_matl.
ENDLOOP.
*Copyng thae same contents to another table to display in
*new sheet
MOVE int_matl TO int_matl1.
PERFORM f_material_details
TABLES int_matl
USING 1.
PERFORM f_material_details
TABLES int_matl
USING 2.
GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.
CALL FUNCTION 'FLUSH'
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.
IF sy-subrc = 0.
CALL METHOD OF wf_map 'SAVEAS'
EXPORTING #1 = p_file.
ENDIF.
CALL METHOD OF wf_mapl 'CLOSE'.
CALL METHOD OF wf_excel 'QUIT'.
FREE OBJECT wf_mapl.
FREE OBJECT wf_map.
FREE OBJECT wf_excel.
ENDFORM. "create_excel
*&---------------------------------------------------------------------*
*& Form ERR_HDL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM err_hdl.
IF sy-subrc <> 0.
WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
STOP.
ENDIF.
ENDFORM. "ERR_HDL
*-- End of Program
*&---------------------------------------------------------------------*
*& Form f_material_details
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM f_material_details
TABLES lint_matl
USING l_sheet_no TYPE i.
DATA: lv_lines TYPE i,
lv_sheet_name(50) TYPE c.
wc_sheets = l_sheet_no.
CASE l_sheet_no.
WHEN 1.
lv_sheet_name = 'Material_sheet1'.
WHEN 2.
lv_sheet_name = 'Material_sheet2'.
ENDCASE.
*-- activating the worksheet and giving a name to it
CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
EXPORTING
#1 = wc_sheets.
CALL METHOD OF wf_worksheet 'ACTIVATE'.
SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to
EXPORTING
#1 = lv_lines
#2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
CALL METHOD OF wf_excel 'Range' = wf_cell
EXPORTING
#1 = wf_cell_from
#2 = wf_cell_to.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
EXPORTING
#1 = lv_lines
#2 = 1.
CALL METHOD OF wf_excel 'Range' = wf_cell1 " Cell range for first
" column(Material)
EXPORTING
#1 = wf_cell_from1
#2 = wf_cell_to1.
SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
"in Material number
DATA l_rc TYPE i.
*DATA download into excel first sheet
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = lint_matl[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
OTHERS = 4.
CALL METHOD OF wf_worksheet 'Paste'.
CALL METHOD OF wf_excel 'Columns' = wf_column1.
CALL METHOD OF wf_column1 'Autofit'.
FREE OBJECT wf_column1.
ENDFORM. " f_material_details
*& Report ZTEST_CODE_EXCEL_OLE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZTEST_CODE_EXCEL_OLE.
TYPE-POOLS ole2.
DATA: wf_cell_from TYPE ole2_object,
wf_cell_from1 TYPE ole2_object,
wf_cell_to TYPE ole2_object,
wf_cell_to1 TYPE ole2_object,
wf_excel TYPE ole2_object, " Excel object
wf_mapl TYPE ole2_object, " list of workbooks
wf_map TYPE ole2_object, " workbook
wf_worksheet TYPE ole2_object, " Worksheet
wf_cell TYPE ole2_object, " Cell Range
wf_cell1 TYPE ole2_object,
wf_range TYPE ole2_object, " Range of cells to be formatted
wf_range2 TYPE ole2_object,
wf_column1 TYPE ole2_object. " Column to be Autofit
DATA: BEGIN OF t_hex,
l_tab TYPE x,
END OF t_hex.
DATA: wf_deli(1) TYPE c, "delimiter
wf_action TYPE i,
wf_file TYPE string,
wf_path TYPE string,
wf_fullpath TYPE string.
TYPES: t_data1(1500) TYPE c,
int_ty TYPE TABLE OF t_data1. "line type internal table
*All the data was prepared as line type internal tables for faster
*download
DATA: int_matl TYPE int_ty ,
int_matl1 TYPE int_ty ,
wa_matl TYPE t_data1.
TYPES: BEGIN OF ty_mara,
matnr TYPE matnr,
mtart TYPE mtart,
matkl TYPE matkl,
meins TYPE meins,
END OF ty_mara.
DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
wa_mara TYPE ty_mara.
FIELD-SYMBOLS: <fs> .
DATA: wc_sheets LIKE sy-index. "no.of sheets
DATA: it_tabemp TYPE filetable,
gd_subrcemp TYPE i.
CONSTANTS wl_c09(2) TYPE n VALUE 09.
CLEAR wc_sheets.
DEFINE ole_check_error.
if &1 ne 0.
message e001(zz) with &1.
exit.
endif.
END-OF-DEFINITION.
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
REFRESH: it_tabemp.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = 'Select File'
* default_extension = 'xls'
default_file_name = 'Material Details'
* with_encoding =
file_filter = '*.xls'
initial_directory = 'C:\'
prompt_on_overwrite = ' '
CHANGING
filename = wf_file
path = wf_path
fullpath = wf_fullpath
user_action = wf_action
* file_encoding =
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
IF wf_action EQ 9.
MESSAGE 'No File have been Selected' TYPE 'S'.
ELSE.
p_file = wf_fullpath.
PERFORM create_excel.
ENDIF.
*&---------------------------------------------------------------------*
*& Form create_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM create_excel.
LOOP AT it_tabemp INTO p_file.
ENDLOOP.
* START THE EXCEL APPLICATION
CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.
* PUT EXCEL IN FRONT
SET PROPERTY OF wf_excel 'VISIBLE' = 1.
PERFORM err_hdl.
* CREATE AN EXCEL WORKBOOK OBJECT
CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
PERFORM err_hdl.
SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
PERFORM err_hdl.
CALL METHOD OF wf_mapl 'ADD' = wf_map.
PERFORM err_hdl.
*Assign the Delimiter to field symbol.
ASSIGN wf_deli TO <fs> TYPE 'X'.
t_hex-l_tab = wl_c09.
<fs> = t_hex-l_tab.
CLEAR int_matl.
REFRESH int_matl.
SELECT matnr
mtart
matkl
meins
FROM mara
INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed in the excel sheet
CONCATENATE 'Material Number'
'Material type'
'Material Group'
'Base Unit of Measure'
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
LOOP AT int_mara INTO wa_mara.
CONCATENATE wa_mara-matnr
wa_mara-mtart
wa_mara-matkl
wa_mara-meins
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
CLEAR wa_matl.
ENDLOOP.
*Copyng thae same contents to another table to display in
*new sheet
MOVE int_matl TO int_matl1.
PERFORM f_material_details
TABLES int_matl
USING 1.
PERFORM f_material_details
TABLES int_matl
USING 2.
GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.
CALL FUNCTION 'FLUSH'
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.
IF sy-subrc = 0.
CALL METHOD OF wf_map 'SAVEAS'
EXPORTING #1 = p_file.
ENDIF.
CALL METHOD OF wf_mapl 'CLOSE'.
CALL METHOD OF wf_excel 'QUIT'.
FREE OBJECT wf_mapl.
FREE OBJECT wf_map.
FREE OBJECT wf_excel.
ENDFORM. "create_excel
*&---------------------------------------------------------------------*
*& Form ERR_HDL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM err_hdl.
IF sy-subrc <> 0.
WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
STOP.
ENDIF.
ENDFORM. "ERR_HDL
*-- End of Program
*&---------------------------------------------------------------------*
*& Form f_material_details
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM f_material_details
TABLES lint_matl
USING l_sheet_no TYPE i.
DATA: lv_lines TYPE i,
lv_sheet_name(50) TYPE c.
wc_sheets = l_sheet_no.
CASE l_sheet_no.
WHEN 1.
lv_sheet_name = 'Material_sheet1'.
WHEN 2.
lv_sheet_name = 'Material_sheet2'.
ENDCASE.
*-- activating the worksheet and giving a name to it
CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
EXPORTING
#1 = wc_sheets.
CALL METHOD OF wf_worksheet 'ACTIVATE'.
SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to
EXPORTING
#1 = lv_lines
#2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
CALL METHOD OF wf_excel 'Range' = wf_cell
EXPORTING
#1 = wf_cell_from
#2 = wf_cell_to.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
EXPORTING
#1 = lv_lines
#2 = 1.
CALL METHOD OF wf_excel 'Range' = wf_cell1 " Cell range for first
" column(Material)
EXPORTING
#1 = wf_cell_from1
#2 = wf_cell_to1.
SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
"in Material number
DATA l_rc TYPE i.
*DATA download into excel first sheet
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = lint_matl[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
OTHERS = 4.
CALL METHOD OF wf_worksheet 'Paste'.
CALL METHOD OF wf_excel 'Columns' = wf_column1.
CALL METHOD OF wf_column1 'Autofit'.
FREE OBJECT wf_column1.
ENDFORM. " f_material_details