*&---------------------------------------------------------------------*
*& Report /SF0A0001/TABLE
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT /SF0A0001/TABLE.
INCLUDE ole2incl.
DATA:
gt_title TYPE string_table,
lr_type_descr type ref to cl_abap_typedescr,
lr_type_descr2 type ref to cl_abap_structdescr,
lr_table_descr type ref to cl_abap_tabledescr,
gv_filename TYPE string,
gv_path TYPE string,
lr_table TYPE REF TO data,
lt_comps type cl_abap_structdescr=>component_table,
ls_comp like line of lt_comps,
gv_fullpath TYPE string.
field-symbols: <lt_data> type table.
*&----------------------------------------------------*
*& SELECTION-SCREEN *
*&----------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1.
PARAMETERS: p_file TYPE string LOWER CASE OBLIGATORY,
p_tab LIKE DD02L-TABNAME DEFAULT '/SF0A0001/REPORT'.
SELECTION-SCREEN END OF BLOCK b1.
*&----------------------------------------------------*
*& START-OF-SELECTION *
*&----------------------------------------------------*
START-OF-SELECTION.
PERFORM get_titles.
PERFORM get_data.
PERFORM create_excel USING p_file 'Flights Info' gt_title.
WRITE: 'Export is finished successfully!'.
*&----------------------------------------------------*
*& AT SELECTION-SCREEN *
*&----------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = 'Select file'
default_extension = 'xls'
file_filter = '*.xls'
CHANGING
filename = gv_filename
path = gv_path
fullpath = gv_fullpath.
IF sy-subrc EQ 0.
p_file = gv_fullpath.
ENDIF.
*&----------------------------------------------------*
*& Macros *
*&----------------------------------------------------*
DEFINE handle_errors.
if sy-subrc <> 0.
write: 'Error in processing Excel file:', lv_step.
stop.
endif.
END-OF-DEFINITION.
*&----------------------------------------------------*
*& Form get_titles *
*&----------------------------------------------------*
FORM get_titles.
DATA: i_fieldcat TYPE LVC_T_FCAT.
FIELD-SYMBOLS: <item> LIKE LINE OF i_fieldcat.
CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
EXPORTING
I_STRUCTURE_NAME = p_tab
CHANGING
ct_fieldcat = i_fieldcat.
LOOP AT i_fieldcat ASSIGNING <item>.
APPEND <item>-FIELDNAME TO gt_title.
ENDLOOP.
ENDFORM. "get_titles
*&----------------------------------------------------*
*& Form get_data *
*&----------------------------------------------------*
FORM get_data.
"call method cl_abap_typedescr=>describe_by_name
CALL METHOD cl_abap_tabledescr=>describe_by_name
exporting
p_name = p_tab
receiving
p_descr_ref = lr_type_descr
exceptions
type_not_found = 1
others = 99.
if sy-subrc <> 0.
message 'dump' type 'X'.
endif.
ls_comp-type ?= lr_type_descr.
ls_comp-as_include = 'X'.
ls_comp-name = 'DATA'.
APPEND ls_comp TO lt_comps.
try.
lr_type_descr2 = cl_abap_structdescr=>create(
p_components = lt_comps ).
catch cx_sy_struct_creation.
message 'dump' type 'X'.
endtry.
try.
call method cl_abap_tabledescr=>create
exporting
p_line_type = lr_type_descr2
p_table_kind = cl_abap_tabledescr=>tablekind_std
p_unique = abap_false
p_key_kind = cl_abap_tabledescr=>keydefkind_default
receiving
p_result = lr_table_descr.
catch cx_sy_table_creation.
message 'dump' type 'X'.
endtry.
CREATE DATA lr_table TYPE HANDLE lr_table_descr.
ASSIGN lr_table->* TO <lt_data>.
ASSERT sy-subrc = 0.
SELECT * FROM (p_tab) INTO CORRESPONDING FIELDS OF TABLE <lt_data>.
check SY-SUBRC = 0.
data: TT type TABLE OF /SF0A0001/REPORT.
SELECT * FROM /SF0A0001/REPORT INTO CORRESPONDING FIELDS OF TABLE TT.
CHECK SY-SUBRC = 0.
ENDFORM. " get_data
*&----------------------------------------------------*
*& Form create_excel *
*&----------------------------------------------------*
FORM create_excel USING pv_file TYPE string
pv_sheet_name TYPE string
pt_title TYPE string_table.
DATA:
lv_row TYPE i,
lv_column TYPE i,
lv_total_columns TYPE i,
lv_field TYPE string,
lv_step TYPE string.
*In a buffered method call (addition: NO FLUSH) of OLE you cannot use local variables at result variables.
*Use global variables or static variables (STATICS) instead.
STATICS:lh_appl TYPE ole2_object,
lh_workbook_list TYPE ole2_object,
lh_workbook TYPE ole2_object,
lh_sheet TYPE ole2_object,
lh_columns TYPE ole2_object,
lh_cell TYPE ole2_object,
lh_interior TYPE ole2_object,
lh_font TYPE ole2_object.
FIELD-SYMBOLS:
<ls_itab_row> TYPE ANY,
<ls_title> TYPE string,
<ls_itab_cell> TYPE ANY.
* We will use 'NO FLUSH' addition in this example:
* Normally, OLE statements are buffered by the ABAP processor and executed at the frontend collectively before
* the first statement which is not of OLE context. Using the 'NO FLUSH' addition prevents this and postpones
* the execution till just before the first non-OLE statement coming after an OLE statement without NO FLUSH
* addition.
* Start application and set it to invisible
lv_step = 'Starting Excel...'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "Tell user what is going on
EXPORTING
percentage = 0
text = lv_step
.
CREATE OBJECT lh_appl 'Excel.Application' NO FLUSH. " If addition 'NO FLUSH' is used, the return code sy-subrc is not filled so it remains 0.
SET PROPERTY OF lh_appl 'SheetsInNewWorkbook' = 1 no flush.
SET PROPERTY OF lh_appl 'Visible' = 0 . "Flush is executed after this OLE statement, so return code sy-subrc will be filled. We evaluate this return code for error handling.
handle_errors.
* Add new workbook
lv_step = 'Create workbook...'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "Tell user what is going on
EXPORTING
percentage = 10
text = lv_step
.
GET PROPERTY OF lh_appl 'Workbooks' = lh_workbook_list no flush.
CALL METHOD OF lh_workbook_list 'Add' = lh_workbook.
handle_errors.
* Configure the active sheet
lv_step = 'Configure sheet...'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "Tell user what is going on
EXPORTING
percentage = 20
text = lv_step
.
GET PROPERTY OF lh_appl 'ActiveSheet' = lh_sheet no flush.
SET PROPERTY OF lh_sheet 'Name' = pv_sheet_name.
handle_errors.
lv_step = 'Writing title...'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "Tell user what is going on
EXPORTING
percentage = 30
text = lv_step
.
LOOP AT pt_title ASSIGNING <ls_title>.
lv_column = sy-tabix.
CALL METHOD OF lh_appl 'Cells' = lh_cell NO FLUSH "Get cell on the active worksheet in the application
EXPORTING
#1 = 1
#2 = lv_column .
handle_errors.
SET PROPERTY OF lh_cell 'Value' = <ls_title> no flush.
GET PROPERTY OF lh_cell 'Interior' = lh_interior no flush.
SET PROPERTY OF lh_interior 'ColorIndex' = 35 no flush.
GET PROPERTY OF lh_cell 'Font' = lh_font no flush.
SET PROPERTY OF lh_font 'Bold' = 1 no flush.
ENDLOOP.
CALL FUNCTION 'FLUSH' "Trigger the flush explicitly
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.
handle_errors.
* Write actual data
lv_step = 'Writing data...'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "Tell user what is going on
EXPORTING
percentage = 40
text = lv_step
.
lv_total_columns = lv_column.
lv_row = 1.
LOOP AT <lt_data> ASSIGNING <ls_itab_row>.
lv_row = lv_row + 1.
DO lv_total_columns TIMES.
lv_column = sy-index.
CALL METHOD OF lh_appl 'Cells' = lh_cell NO FLUSH "Get cell on the active worksheet in the application
EXPORTING
#1 = lv_row
#2 = lv_column.
handle_errors.
ASSIGN COMPONENT lv_column OF STRUCTURE <ls_itab_row> TO <ls_itab_cell>.
SET PROPERTY OF lh_cell 'Value' = <ls_itab_cell> no flush.
ENDDO.
ENDLOOP.
CALL FUNCTION 'FLUSH' "Trigger the flush explicitly
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.
handle_errors.
* Set column width
lv_step = 'Setting column width...'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "Tell user what is going on
EXPORTING
percentage = 50
text = lv_step
.
CALL METHOD OF lh_sheet 'Columns' = lh_columns NO FLUSH.
CALL METHOD OF lh_columns 'Autofit'.
handle_errors.
* Save and close
lv_step = 'Saving and closing workbook...'.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' "Tell user what is going on
EXPORTING
percentage = 60
text = lv_step
.
CALL METHOD OF lh_workbook 'SaveAs' NO FLUSH
EXPORTING
#1 = pv_file "File name
.
CALL METHOD OF lh_workbook 'Close' NO FLUSH.
CALL METHOD OF lh_appl 'Quit' NO FLUSH.
FREE OBJECT lh_appl.
handle_errors.
ENDFORM. " create_excel
|