**********************************************************************
* Types
**********************************************************************
TYPES: BEGIN OF ty_xl_line,
col1 TYPE string,
col2 TYPE string,
END OF ty_xl_line.
TYPES: ty_it_xl_lines TYPE STANDARD TABLE OF ty_xl_line WITH DEFAULT KEY.
**********************************************************************
* Selektionsbild
**********************************************************************
PARAMETERS: p_fname TYPE file_table-filename OBLIGATORY.
* wenn die F4-Hilfe für den Dateinamen aufgerufen wird
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
DATA: lv_rc TYPE i.
DATA: it_files TYPE filetable.
DATA: lv_action TYPE i.
* File-Tabelle leeren, da hier noch alte Einträge von vorherigen Aufrufen drin stehen können
CLEAR it_files.
* FileOpen-Dialog aufrufen
TRY.
cl_gui_frontend_services=>file_open_dialog( EXPORTING
file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
multiselection = abap_false
CHANGING
file_table = it_files
rc = lv_rc
user_action = lv_action ).
IF lv_action = cl_gui_frontend_services=>action_ok.
* wenn Datei ausgewählt wurde
IF lines( it_files ) > 0.
* ersten Tabelleneintrag lesen
p_fname = it_files[ 1 ]-filename.
ENDIF.
ENDIF.
CATCH cx_root INTO DATA(e_text).
MESSAGE e_text->get_text( ) TYPE 'I'.
ENDTRY.
START-OF-SELECTION.
DATA(it_xl) = VALUE ty_it_xl_lines( ).
TRY.
* Reader-Objekt erzeugen
DATA(o_reader) = CAST zif_excel_reader( NEW zcl_excel_reader_2007( ) ).
DATA(o_excel) = o_reader->load_file( p_fname ).
* Worksheet
DATA(o_worksheet) = o_excel->get_active_worksheet( ).
* max. Zeile und Spalte holen
DATA(lv_max_col) = o_worksheet->get_highest_column( ).
DATA(lv_max_row) = o_worksheet->get_highest_row( ).
WRITE: / lv_max_col, ',', lv_max_row.
DATA(lv_row) = 1.
* Worksheet zeilenweise durchlaufen
WHILE lv_row <= lv_max_row.
* Spaltennummer (1) in Excel-Spalten-Bezeichner (A) umwandeln
DATA(lv_col_str) = zcl_excel_common=>convert_column2alpha( 1 ).
* Zellinhalt Spalte 1 holen
o_worksheet->get_cell( EXPORTING
ip_column = lv_col_str
ip_row = lv_row
IMPORTING
ep_value = DATA(lv_value)
).
* Spaltennummer (2) in Excel-Spalten-Bezeichner (B) umwandeln
DATA(lv_col_str2) = zcl_excel_common=>convert_column2alpha( 2 ).
* Zellinhalt Spalte 2 holen
o_worksheet->get_cell( EXPORTING
ip_column = lv_col_str2
ip_row = lv_row
IMPORTING
ep_value = DATA(lv_value2)
).
* Werte an interne Tabelle anfügen
APPEND VALUE #(
col1 = lv_value
col2 = lv_value2
) TO it_xl.
lv_row = lv_row + 1.
ENDWHILE.
* Daten in SALV-Grid anzeigen
TRY.
* SALV-Table
DATA: o_salv TYPE REF TO cl_salv_table.
cl_salv_table=>factory( IMPORTING
r_salv_table = o_salv
CHANGING
t_table = it_xl ).
* Grundeinstellungen
o_salv->get_functions( )->set_all( abap_true ).
o_salv->get_columns( )->set_optimize( abap_true ).
o_salv->get_display_settings( )->set_list_header( |Excel-Werte ({ lines( it_xl ) })| ).
o_salv->get_display_settings( )->set_striped_pattern( abap_true ).
o_salv->get_selections( )->set_selection_mode( if_salv_c_selection_mode=>row_column ).
* Spaltenüberschriften: technischer Name und Beschreibungstexte, Short Text und Medium Text leer lassen für Autosize
LOOP AT o_salv->get_columns( )->get( ) ASSIGNING FIELD-SYMBOL(<c>).
DATA(o_col) = <c>-r_column.
o_col->set_short_text( || ).
o_col->set_medium_text( || ).
o_col->set_long_text( |{ o_col->get_columnname( ) } [{ o_col->get_long_text( ) }]| ).
ENDLOOP.
o_salv->display( ).
CATCH cx_root INTO DATA(e_txt).
WRITE: / e_txt->get_text( ).
ENDTRY.
CATCH cx_root INTO DATA(ex). " Exceptions for ABAP2XLSX
WRITE: / ex->get_text( ).
WRITE: / ex->get_longtext( ).
ENDTRY.