[ABAP] ABAP2XLSX: Excel-Daten (*.xlsx) einlesen und Inhalt anzeigen

**********************************************************************
* 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
**********************************************************************
START-OF-SELECTION.

  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( ).
* alternativer Zugriff über Worksheet-Iterator
*      DATA(o_worksheet) = CAST zcl_excel_worksheet( o_excel->get_worksheets_iterator( )->get_next( ) ).

* Inhalt der Worksheet
      LOOP AT o_worksheet->sheet_content ASSIGNING FIELD-SYMBOL(<cell>) GROUP BY <cell>-cell_row ASSIGNING FIELD-SYMBOL(<row>).
* Zeile
        LOOP AT GROUP <row> ASSIGNING FIELD-SYMBOL(<cell_data>).
* Zellinfos
          WRITE: / <cell_data>-cell_coords, <cell_data>-cell_column, <cell_data>-cell_row, <cell_data>-data_type, <cell_data>-cell_value, <cell_data>-cell_formula, <cell_data>-cell_style.
        ENDLOOP.
      ENDLOOP.
    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

[ABAP] ABAP2XLSX: Excel-Daten (*.xlsx) einlesen und anzeigen

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