TYPES: BEGIN OF ty_xl_sheet,
col1 TYPE string,
col2 TYPE string,
col3 TYPE string,
END OF ty_xl_sheet.
TYPES: ty_it_users TYPE STANDARD TABLE OF ty_xl_sheet WITH DEFAULT KEY.
TRY.
* leeres Excel-Dokument
DATA(o_xl) = xco_cp_xlsx=>document->empty( )->write_access( ).
* 1. Sheet holen
DATA(o_sheet) = o_xl->get_workbook( )->worksheet->at_position( 1 ).
IF abap_true = o_sheet->exists( ).
* neuen Namen für Sheet setzen
o_sheet->set_name( 'Test1' ).
* Daten
DATA(it_sheet_data) = VALUE ty_it_users( ( col1 = 'Name' col2 = 'Adr.' col3 = 'Age' )
( col1 = 'Ulf' col2 = 'Str. 1' col3 = '22' )
( col1 = 'Ida' col2 = 'Str. 2' col3 = '21' ) ).
* komplette Sheet auswählen
DATA(o_sel_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to( )->get_pattern( ).
* Daten einfügen
DATA(o_result) = o_sheet->select( o_sel_pattern
)->row_stream(
)->operation->write_from( REF #( it_sheet_data )
)->execute( ).
IF abap_true = o_result->succeeded.
* Daten als XSTRING (Excel-Datei holen)
DATA(lv_xl_xtsr) = o_xl->get_file_content( ).
* xstring -> itab (bytes)
DATA: lv_size TYPE i.
DATA: it_raw_data TYPE xml_rawdata.
* xstring (Bytes) -> RAW (iTab)
cl_scp_change_db=>xstr_to_xtab( EXPORTING im_xstring = lv_xl_xtsr
IMPORTING ex_size = lv_size
ex_xtab = it_raw_data ).
IF lines( it_raw_data ) > 0.
DATA: lv_action TYPE i.
DATA: lv_filename TYPE string.
DATA: lv_fullpath TYPE string.
DATA: lv_path TYPE string.
* SaveDialog aufrufen
cl_gui_frontend_services=>file_save_dialog( EXPORTING default_extension = 'xlsx'
default_file_name = 'export.xlsx'
file_filter = |Excel-Datei (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
prompt_on_overwrite = abap_true
CHANGING filename = lv_filename
path = lv_path
fullpath = lv_fullpath
user_action = lv_action ).
IF lv_action EQ cl_gui_frontend_services=>action_ok.
* iTab (bytes) -> lokale Datei
cl_gui_frontend_services=>gui_download( EXPORTING filename = lv_fullpath
filetype = 'BIN'
bin_filesize = lv_size " Size ist wichtig für das korrekte Schreiben der Excel-Datei
CHANGING data_tab = it_raw_data ).
* lokale Datei im Excel aufrufen
* parameter muss wegen möglicher Leerzeichen im Pfad mit "" quotiert werden
cl_gui_frontend_services=>execute( application = 'excel.exe'
parameter = |"{ lv_fullpath }"| ).
ENDIF.
ELSE.
* Fehlerbehandlung
LOOP AT o_result->messages INTO DATA(o_msg).
WRITE: / o_msg->get_text( ).
ENDLOOP.
ENDIF.
ENDIF.
ENDIF.
CATCH cx_root INTO DATA(e_text).
MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.
Links