Import | Die Codezentrale

SAP ABAP Excel Archive nach Kategorien "Import"

[ABAP] XLSX-Datei mit Klasse cl_ehfnd_xlsx einlesen und in SALV-Grid anzeigen

TRY.
    DATA: lv_rc TYPE i.
    DATA: it_files TYPE filetable.
    DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
    cl_gui_frontend_services=>file_open_dialog( EXPORTING file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                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
        DATA: lv_filesize TYPE w3param-cont_len.
        DATA: lv_filetype TYPE w3param-cont_type.
        DATA: it_bin_data TYPE w3mimetabtype.

* Excel-Datei auf Appl. Server hochladen (binary)
        cl_gui_frontend_services=>gui_upload( EXPORTING filename   = |{ it_files[ 1 ]-filename }|
                                                        filetype   = 'BIN'
                                              IMPORTING filelength = lv_filesize
                                              CHANGING  data_tab   = it_bin_data ).

* solix -> xstring
        DATA(lv_bin_data) = cl_bcs_convert=>solix_to_xstring( it_solix = it_bin_data ).

**********************************************************************
* XLSX Handling: Kapselt cl_xlsx_document
**********************************************************************
        DATA(o_excel) = cl_ehfnd_xlsx=>get_instance( ).

* XLSX Workbook (XString --> XML)
        DATA(o_doc) = o_excel->load_doc( iv_file_data = lv_bin_data ).

* XLSX Sheets des Workbooks holen
        DATA(it_sheets) = o_doc->get_sheets( ).

        IF lines( it_sheets ) > 0.
* XLSX Sheet: erste Sheet holen
          DATA(o_sheet) = o_doc->get_sheet_by_id( iv_sheet_id = 1 ).
          DATA(lv_sheet_name) = it_sheets[ 1 ]-name.

* max. Zeilenzahl der Sheet
          DATA(lv_max_rows) = o_sheet->get_last_row_number( ).
          DATA(lv_max_cols) = o_sheet->get_last_column_number_in_row( 1 ).

          IF lv_max_rows > 0 AND lv_max_cols > 0.
* Komponenten (Spalten) der Tabelle --> generische Stringtable bauen
            DATA(it_components) = VALUE cl_abap_structdescr=>component_table( ).

* Überschriften (Header) für ALV-Grid
            DATA(it_colnames) = VALUE stringtab( ).

            DO lv_max_cols TIMES.
* Spaltenbezeichner aus 1. Zeile (Header) der Excel-Tabelle holen
              DATA(lv_col_header) = o_sheet->get_cell_content( iv_row    = 1
                                                               iv_column = sy-index ).

* alle Vorkommen, die nicht [a-zA-Z0-9_] entsprechen, durch '_' ersetzen
              REPLACE ALL OCCURRENCES OF REGEX '([^\w]|[äöüÄÖÜß])+' IN lv_col_header WITH '_'.

* Tabelle mit Überschriften für ALV-Grid füllen
              APPEND lv_col_header TO it_colnames.

* Spalte vom Typ String mit generischem Namen zur Komponententabelle hinzufügen
              APPEND VALUE #( name   = |COL{ sy-index }|
                              type   = cl_abap_elemdescr=>get_string( )
                            ) TO it_components.
            ENDDO.

**********************************************************************
* generische interne Tabelle mit hilfe dynamischer Objekte erzeugen
**********************************************************************
* Strukturdeskriptor für Komponententabelle erzeugen
            DATA(o_struct_desc) = cl_abap_structdescr=>create( it_components ).

* Tabellendeskriptor erzeugen
            DATA(o_table_desc) = cl_abap_tabledescr=>create( p_line_type = o_struct_desc ).

* dynamisches Tabellenobjekt anhand des Tabellendeskriptors erstellen
            DATA: o_table TYPE REF TO data.
            CREATE DATA o_table TYPE HANDLE o_table_desc.

* Feldsymbol auf das Tabellenobjekt vom Typ STANDARD TABLE anlegen
            FIELD-SYMBOLS <table> TYPE STANDARD TABLE.
            ASSIGN o_table->* TO <table>.

* Inhalt (ohne Header) aus XLSX in interne Tabelle schreiben
            DATA(lv_row) = 2.

            DO lv_max_rows - 1 TIMES.
              DATA(lv_col) = 1.

* neue Ausgabezeile anfügen
              APPEND INITIAL LINE TO <table>.

* neue Ausgabezeile holen und Feldsymbol zuweisen
              DATA(lv_lc) = lines( <table> ).
              ASSIGN <table>[ lv_lc ] TO FIELD-SYMBOL(<row>).

              IF <row> IS ASSIGNED.
* für alle Spalten der Tabelle
                DO lv_max_cols TIMES.
* n-te Spalte <col> der akt. Tabellenzeile <row> ermitteln
                  ASSIGN COMPONENT lv_col OF STRUCTURE <row> TO FIELD-SYMBOL(<cell>).
                  IF <cell> IS ASSIGNED.
* Inhalt der akt. Zelle in die Zelle der internen Tabelle schreiben
                    <cell> = o_sheet->get_cell_content( iv_row    = lv_row
                                                        iv_column = lv_col ).


                  ENDIF.

                  lv_col = lv_col + 1.
                ENDDO.
              ENDIF.

              lv_row = lv_row + 1.
            ENDDO.

**********************************************************************
* Anzeige der gefüllten generischen Tabelle in einem SALV-Grid
**********************************************************************
            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      = <table> ).

*             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( CONV #( lv_sheet_name ) ).
                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(lv_idx) = sy-tabix.

                  DATA(o_col) = <c>-r_column.
                  o_col->set_short_text( || ).
                  o_col->set_medium_text( || ).
                  o_col->set_long_text( CONV #( it_colnames[ lv_idx ] ) ).
                ENDLOOP.

                o_salv->display( ).
              CATCH cx_root INTO DATA(e_txt).
                WRITE: / e_txt->get_text( ).
            ENDTRY.
          ENDIF.

        ENDIF.

      ENDIF.

    ENDIF.

  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.

[ABAP] MS-Excel-Datei mit Hilfe der Klasse cl_ehfnd_xlsx lesen

TRY.
    DATA: lv_rc TYPE i.
    DATA: it_files TYPE filetable.
    DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
    cl_gui_frontend_services=>file_open_dialog( EXPORTING file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                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
        DATA: lv_filesize TYPE w3param-cont_len.
        DATA: lv_filetype TYPE w3param-cont_type.
        DATA: it_bin_data TYPE w3mimetabtype.

* Excel-Datei auf Appl. Server hochladen (binary)
        cl_gui_frontend_services=>gui_upload( EXPORTING filename   = |{ it_files[ 1 ]-filename }|
                                                        filetype   = 'BIN'
                                              IMPORTING filelength = lv_filesize
                                              CHANGING  data_tab   = it_bin_data ).

* solix -> xstring
        DATA(lv_bin_data) = cl_bcs_convert=>solix_to_xstring( it_solix = it_bin_data ).

* XLSX Handling: Kapselt cl_xlsx_document
        DATA(o_excel) = cl_ehfnd_xlsx=>get_instance( ).

* XLSX Workbook (XString --> XML)
        DATA(o_doc) = o_excel->load_doc( iv_file_data = lv_bin_data ).

* XLSX Sheets des Workbooks holen
        DATA(it_sheets) = o_doc->get_sheets( ).

        LOOP AT it_sheets ASSIGNING FIELD-SYMBOL(<sheet>).
          WRITE: / <sheet>-name, <sheet>-sheet_id, <sheet>-rid.
        ENDLOOP.

        ULINE.

* XLSX Sheet: erste Sheet holen
        DATA(o_sheet) = o_doc->get_sheet_by_id( iv_sheet_id = 1 ).
* max. Zeilenzahl der Sheet
        DATA(lv_maxrow) = o_sheet->get_last_row_number( ).

        DATA(lv_row) = 1.

        DO lv_maxrow TIMES.
* max. Spaltenzahl der akt. Zeile
          DATA(lv_cols_in_row) = o_sheet->get_last_column_number_in_row( lv_row ).
          DATA(lv_col) = 1.

          DO lv_cols_in_row TIMES.
* Inhalt der akt. Zelle
            DATA(lv_value) = o_sheet->get_cell_content( EXPORTING iv_row    = lv_row
                                                                  iv_column = lv_col ).

            WRITE: lv_value.

            lv_col = lv_col + 1.
          ENDDO.

          lv_row = lv_row + 1.

          WRITE: /.
        ENDDO.

      ENDIF.
    ENDIF.

  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.

[ABAP] Beispiel für Verwendung der Klasse cl_xlsx_document

* Workbook
PARAMETERS: p_wb RADIOBUTTON GROUP grp DEFAULT 'X'.
* Sharedstrings
PARAMETERS: p_str RADIOBUTTON GROUP grp.
* Sheetpart
PARAMETERS: p_sh RADIOBUTTON GROUP grp.
* Tablepart
PARAMETERS: p_tab RADIOBUTTON GROUP grp.

START-OF-SELECTION.

  TRY.
      DATA: lv_rc TYPE i.
      DATA: it_files TYPE filetable.
      DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
      cl_gui_frontend_services=>file_open_dialog( EXPORTING file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                  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
          DATA: lv_filesize TYPE w3param-cont_len.
          DATA: lv_filetype TYPE w3param-cont_type.
          DATA: it_bin_data TYPE w3mimetabtype.

* Excel-Datei auf Appl. Server hochladen (binary)
          cl_gui_frontend_services=>gui_upload( EXPORTING filename   = |{ it_files[ 1 ]-filename }|
                                                          filetype   = 'BIN'
                                                IMPORTING filelength = lv_filesize
                                                CHANGING  data_tab   = it_bin_data ).

* solix -> xstring
          DATA(lv_bin_data) = cl_bcs_convert=>solix_to_xstring( it_solix = it_bin_data ).

**********************************************************************
* Excel-Objekt
**********************************************************************
          DATA(o_excel) = cl_xlsx_document=>load_document( lv_bin_data ).

          DATA(o_workbook) = o_excel->get_workbookpart( ).
          DATA(lv_workbook_xstr) = o_workbook->get_data( ).

          DATA(o_sharedstrings) = o_workbook->get_sharedstringspart( ).
          DATA(lv_sharedstrings_xstr) = o_sharedstrings->get_data( ).

          DATA(o_sheets) = o_workbook->get_worksheetparts( ).
          IF o_sheets->get_count( ) > 0.
            DATA(o_sheet) = CAST cl_xlsx_worksheetpart( o_sheets->get_part( 0 ) ).

            DATA(lv_sheet_xstr) = o_sheet->get_data( ).

            DATA(o_tables) = o_sheet->get_tableparts( ).
            IF o_tables->get_count( ) > 0.
              DATA(o_table) = CAST cl_xlsx_tablepart( o_tables->get_part( 0 ) ).
              DATA(lv_table_xstr) = o_table->get_data( ).
            ENDIF.
          ENDIF.

* XML Doc
          DATA(o_doc) = NEW cl_xml_document( ).

* xstring --> xml
          CASE abap_true.
            WHEN p_wb.
              o_doc->parse_xstring( lv_workbook_xstr ).
            WHEN p_str.
              o_doc->parse_xstring( lv_sharedstrings_xstr ).
            WHEN p_sh.
              o_doc->parse_xstring( lv_sheet_xstr ).
            WHEN p_tab.
              o_doc->parse_xstring( lv_table_xstr ).
          ENDCASE.

* Inhalt des XML-Documents in string wandeln
          o_doc->render_2_string( EXPORTING pretty_print = abap_true
                                  IMPORTING retcode      = DATA(lv_rc2)
                                            size         = DATA(lv_size2)
                                            stream       = DATA(lv_xml) ).

          IF lv_rc2 = 0.
* XML-String anzeigen
            cl_soap_xml_helper=>xml_show( sdoc  = lv_xml
                                          html  = abap_true
                                          title = 'XML Anzeige' ).
          ENDIF.

        ENDIF.
      ENDIF.

    CATCH cx_root INTO DATA(e_text).
      MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
  ENDTRY.

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

[ABAP] Excel-Datei (*.xlsx) mit Desktop-Office-Integration (DOI) laden und anzeigen

* Dummy-Screen für cl_gui_container=>default_screen deklarieren
SELECTION-SCREEN BEGIN OF SCREEN 100.
SELECTION-SCREEN END OF SCREEN 100.

START-OF-SELECTION.

  TRY.
      DATA: lv_rc TYPE i.
      DATA: it_files TYPE filetable.
      DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
      cl_gui_frontend_services=>file_open_dialog( EXPORTING
                                                    file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                  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.

          DATA: o_oic TYPE REF TO i_oi_container_control.
          DATA: o_oid TYPE REF TO i_oi_document_proxy.
          DATA: o_err TYPE REF TO i_oi_error.

          c_oi_container_control_creator=>get_container_control( IMPORTING
                                                                   control = o_oic
                                                                   error   = o_err ).

          IF abap_true = o_err->has_succeeded.
            o_oic->init_control( EXPORTING
                                   inplace_enabled     = abap_true
                                   no_flush            = abap_true
                                   r3_application_name = 'Excel Tabelle'
                                   parent              = cl_gui_container=>default_screen
                                 IMPORTING
                                   error               = o_err ).

            IF abap_true = o_err->has_succeeded.
              o_oic->get_document_proxy( EXPORTING
                                           document_type  = soi_doctype_excel_sheet " 'Excel.Sheet'
                                         IMPORTING
                                           document_proxy = o_oid
                                           error          = o_err ).

              IF abap_true = o_err->has_succeeded.
* URL muss für das Laden eines lokalen Files mit 'FILE://' beginnen
                DATA(lv_file) = 'FILE://' && it_files[ 1 ]-filename.
* *.xlsx von URL (File) laden und anzeigen
                o_oid->open_document( EXPORTING
                                        document_title = 'Excel'
                                        document_url   = CONV swk_url( lv_file )
                                        open_inplace   = abap_true
                                      IMPORTING
                                        error          = o_err ).

                IF abap_true = o_err->has_succeeded.
* leere Toolbar ausblenden
                  cl_abap_list_layout=>suppress_toolbar( ).

* cl_gui_container=>default_screen erzwingen
                  CALL SCREEN 100.

* Schließen eines Dokuments in der Office-Anwendung
                  o_oid->close_document( IMPORTING error = o_err ).
                  IF o_err->has_succeeded = abap_true.

* Schließen eines Visual-Basic-Dokuments
                    o_oid->close_activex_document( IMPORTING error = o_err ).
                    IF o_err->has_succeeded = abap_true.

* Freigabe des für das Dokument reservierten Speichers
                      o_oid->release_document( IMPORTING error = o_err ).

                      IF o_err->has_succeeded = abap_true.
                        FREE: o_oid.
                      ENDIF.
                    ENDIF.
                  ENDIF.

* baut das Control ab
                  o_oic->destroy_control( IMPORTING error = o_err ).

                  IF o_err->has_succeeded = abap_true.
                    FREE: o_oic.
                  ENDIF.

                ENDIF.
              ENDIF.
            ENDIF.
          ENDIF.
        ENDIF.
      ENDIF.

    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

[ABAP] Excel-Datei (*.xlsx) in interne Tabelle laden (cl_fdt_xl_spreadsheet)

Variante 1 (generische Ausgabe mit cl_demo_output)

* Mit Hilfe der Klasse cl_fdt_xl_spreadsheet können über XML-Transformationen
* aus XLSX-Dateien Daten extrahiert und in eine interne Tabelle konvertiert werden
TRY.
    DATA: lv_rc TYPE i.
    DATA: it_files TYPE filetable.
    DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
    cl_gui_frontend_services=>file_open_dialog( EXPORTING
                                                  file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                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
        DATA: lv_filesize TYPE w3param-cont_len.
        DATA: lv_filetype TYPE w3param-cont_type.
        DATA: it_bin_data TYPE w3mimetabtype.

* Bild auf Appl. Server hochladen (binary)
        cl_gui_frontend_services=>gui_upload( EXPORTING
                                                filename   = |{ it_files[ 1 ]-filename }|
                                                filetype   = 'BIN'
                                              IMPORTING
                                                filelength = lv_filesize
                                              CHANGING
                                                data_tab   = it_bin_data ).

* solix -> xstring
        DATA(lv_bin_data) = cl_bcs_convert=>solix_to_xstring( it_solix = it_bin_data ).

* Excel (itab) -> XML -> Ref-Objekt
* Achtung: Speicherintensiv und rel. langsam! Es sollten keine großen Datenmengen verarbeitet werden.
        DATA(o_excel) = NEW cl_fdt_xl_spreadsheet( document_name = CONV #( it_files[ 1 ]-filename )
                                                   xdocument     = lv_bin_data ).

        DATA: it_worksheet_names TYPE if_fdt_doc_spreadsheet=>t_worksheet_names.

* Worksheetnamen ermitteln
        o_excel->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = it_worksheet_names ).

        IF lines( it_worksheet_names ) > 0.
* erste Worksheet holen und -> REF to itab erstellen
          DATA(o_worksheet_itab) = o_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( it_worksheet_names[ 1 ] ).

* Referenz auf generisches Feldsymbol mappen
          ASSIGN o_worksheet_itab->* TO FIELD-SYMBOL(<worksheet>).

* Datenausgabe
          cl_demo_output=>write_data( <worksheet> ).

* HTML-Daten aus itab generieren
          DATA(lv_html) = cl_demo_output=>get( ).

* Daten im Inline-Browser im SAP-Fenster anzeigen
          cl_abap_browser=>show_html( EXPORTING
                                        title        = 'Excel-Worksheet'
                                        html_string  = lv_html
                                        container    = cl_gui_container=>default_screen ).

* cl_gui_container=>default_screen erzwingen
          WRITE: space.
        ENDIF.
      ENDIF.
    ENDIF.

  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.

Variante 2 (generische Listausgabe)

TYPES : BEGIN OF ty_s_col_width,
          width TYPE i,
        END OF ty_s_col_width.

TYPES: ty_it_col_width TYPE STANDARD TABLE OF ty_s_col_width WITH DEFAULT KEY.

* Spaltenbreiten für Tabellenausgabe
DATA(it_col_width) = VALUE ty_it_col_width(
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                            ( width = 30 )
                                          ).

* Mit Hilfe der Klasse cl_fdt_xl_spreadsheet können über XML-Transformationen
* aus XLSX-Dateien Daten extrahiert und in eine interne Tabelle konvertiert werden
TRY.
    DATA: lv_rc TYPE i.
    DATA: it_files TYPE filetable.
    DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
    cl_gui_frontend_services=>file_open_dialog( EXPORTING
                                                  file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                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
        DATA: lv_filesize TYPE w3param-cont_len.
        DATA: lv_filetype TYPE w3param-cont_type.
        DATA: it_bin_data TYPE w3mimetabtype.

* Bild auf Appl. Server hochladen (binary)
        cl_gui_frontend_services=>gui_upload( EXPORTING
                                                filename   = |{ it_files[ 1 ]-filename }|
                                                filetype   = 'BIN'
                                              IMPORTING
                                                filelength = lv_filesize
                                              CHANGING
                                                data_tab   = it_bin_data ).

* solix -> xstring
        DATA(lv_bin_data) = cl_bcs_convert=>solix_to_xstring( it_solix = it_bin_data ).

* Excel (itab) -> XML -> Ref-Objekt
* Achtung: Speicherintensiv und rel. langsam! Es sollten keine großen Datenmengen verarbeitet werden.
        DATA(o_excel) = NEW cl_fdt_xl_spreadsheet( document_name = CONV #( it_files[ 1 ]-filename )
                                                   xdocument     = lv_bin_data ).

        DATA: it_worksheet_names TYPE if_fdt_doc_spreadsheet=>t_worksheet_names.

* Worksheetnamen ermitteln
        o_excel->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = it_worksheet_names ).

        IF lines( it_worksheet_names ) > 0.
* erste Worksheet holen und -> REF to itab erstellen
          DATA(o_worksheet_itab) = o_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( it_worksheet_names[ 1 ] ).

* Referenz auf generisches Feldsymbol mappen
          FIELD-SYMBOLS: <worksheet> TYPE ANY TABLE.
          ASSIGN o_worksheet_itab->* TO <worksheet>.

* Tabellen-Zeile erzeugen
* muss hier erfolgen, damit man ein "greifbares" Tabellen-Zeilen-Objekt
* für die Strukturermittlung (describe_by_data) hat
          DATA: o_row TYPE REF TO data.
          CREATE DATA o_row LIKE LINE OF <worksheet>.
          ASSIGN o_row->* TO FIELD-SYMBOL(<row>).

* Komponenten (Spalten) einer Tabellenzeile ermitteln
          DATA(o_struct) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_data( <row> ) ).
          DATA(it_comp_tab) = o_struct->get_components( ).

* Anzahl Spalten der Tabellen-Zeile holen
          DATA(lv_colcnt) = lines( it_comp_tab ).

* Worksheet durchloopen
          LOOP AT <worksheet> ASSIGNING <row>.

* Spalten der akt. Zeile durchgehen
            DO lv_colcnt TIMES.
* Zelle: n-tes Element der akt. Zeile holen
              ASSIGN COMPONENT sy-index OF STRUCTURE <row> TO FIELD-SYMBOL(<cell>).

* Trennzeichen vor die Spalte einfügen, wenn nicht 1. Spalte
              IF sy-index > 1.
                WRITE: '|'.
              ENDIF.

* Achtung: Zell-Typ beachten! Es können hier nur flache Typen (Keine Strukturen, Tabellen) ausgegeben werden, der Rest muss gesondert behandelt werden
              IF CAST cl_abap_elemdescr( it_comp_tab[ sy-index ]-type )->kind = cl_abap_elemdescr=>kind_elem.
* Ausgabe Zellinhalt mit vordefinierter Spaltenbreite
                WRITE: |{ <cell> WIDTH = it_col_width[ sy-index ]-width }|.
              ENDIF.
            ENDDO.

            NEW-LINE.
          ENDLOOP.
        ENDIF.
      ENDIF.
    ENDIF.

  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.

Variante 3 (generische Ausgabe im SALV-Grid)

* Mit Hilfe der Klasse cl_fdt_xl_spreadsheet können über XML-Transformationen
* aus XLSX-Dateien Daten extrahiert und in eine interne Tabelle konvertiert werden
TRY.
    DATA: lv_rc TYPE i.
    DATA: it_files TYPE filetable.
    DATA: lv_action TYPE i.

* FileOpen-Dialog aufrufen
    cl_gui_frontend_services=>file_open_dialog( EXPORTING
                                                  file_filter = |xlsx (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                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
        DATA: lv_filesize TYPE w3param-cont_len.
        DATA: lv_filetype TYPE w3param-cont_type.
        DATA: it_bin_data TYPE w3mimetabtype.

* Bild auf Appl. Server hochladen (binary)
        cl_gui_frontend_services=>gui_upload( EXPORTING
                                                filename   = |{ it_files[ 1 ]-filename }|
                                                filetype   = 'BIN'
                                              IMPORTING
                                                filelength = lv_filesize
                                              CHANGING
                                                data_tab   = it_bin_data ).

* solix -> xstring
        DATA(lv_bin_data) = cl_bcs_convert=>solix_to_xstring( it_solix = it_bin_data ).

* Excel (itab) -> XML -> Ref-Objekt
* Achtung: Speicherintensiv und rel. langsam! Es sollten keine großen Datenmengen verarbeitet werden.
        DATA(o_excel) = NEW cl_fdt_xl_spreadsheet( document_name = CONV #( it_files[ 1 ]-filename )
                                                   xdocument     = lv_bin_data ).

        DATA: it_worksheet_names TYPE if_fdt_doc_spreadsheet=>t_worksheet_names.

* Worksheetnamen ermitteln
        o_excel->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = it_worksheet_names ).

        IF lines( it_worksheet_names ) > 0.
* erste Worksheet holen und -> REF to itab erstellen
          DATA(o_worksheet_itab) = o_excel->if_fdt_doc_spreadsheet~get_itab_for_alv_update( ).

* Referenz auf generisches Feldsymbol mappen
          ASSIGN o_worksheet_itab->* TO FIELD-SYMBOL(<worksheet>).

          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        = <worksheet> ).

* 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( 'Worksheet' ).
              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
              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( ) }| ).
              ENDLOOP.

              o_salv->display( ).
            CATCH cx_root INTO DATA(e_txt).
              WRITE: / e_txt->get_text( ).
          ENDTRY.
        ENDIF.
      ENDIF.
    ENDIF.

  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'S' DISPLAY LIKE 'E'.
ENDTRY.

[ABAP] ABAP2XLSX: Interne Tabelle als Excel-Datei (*.xlsx) aufbereiten und mit Desktop Office Integration (DOI) als Excel inplace anzeigen

Variante 1 (Daten aus der Tabelle SFLIGHT anzeigen)

* Dummy-Screen für cl_gui_container=>default_screen deklarieren
SELECTION-SCREEN BEGIN OF SCREEN 100.
SELECTION-SCREEN END OF SCREEN 100.

START-OF-SELECTION.

* Daten holen
  SELECT * FROM sflight INTO TABLE @DATA(it_sflight).

  TRY.
* ABAP2XLSX
      DATA: o_xl TYPE REF TO zcl_excel.

* Converter itab->ABAP2XLSX
      DATA(o_converter) = NEW zcl_excel_converter( ).

      o_converter->convert( EXPORTING
                              it_table = it_sflight
                            CHANGING
                              co_excel = o_xl ).

* aktive Worksheet
      DATA(o_xl_ws) = o_xl->get_active_worksheet( ).
* oberste Zeile einfrieren -> beim Scrollen immer sichtbar
      o_xl_ws->freeze_panes( ip_num_rows = 1 ).

* Binärdaten (xstring) generieren
      DATA(o_xlwriter) = CAST zif_excel_writer( NEW zcl_excel_writer_2007( ) ).
      DATA(lv_xl_xdata) = o_xlwriter->write_file( o_xl ).

* Daten mit DOI (Excel inplace) anzeigen
      DATA: o_oic TYPE REF TO i_oi_container_control.
      DATA: o_oid TYPE REF TO i_oi_document_proxy.
      DATA: o_err TYPE REF TO i_oi_error.
      DATA: it_errors TYPE STANDARD TABLE OF REF TO i_oi_error WITH NON-UNIQUE DEFAULT KEY.

      c_oi_container_control_creator=>get_container_control( IMPORTING
                                                               control = o_oic
                                                               error   = o_err ).

      APPEND o_err TO it_errors.

      IF abap_true = o_err->has_succeeded.
        o_oic->init_control( EXPORTING
                               inplace_enabled     = abap_true
                               no_flush            = abap_true
                               r3_application_name = 'Flugverbindungen'
                               parent              = cl_gui_container=>default_screen
                             IMPORTING
                               error               = o_err ).

        APPEND o_err TO it_errors.

        IF abap_true = o_err->has_succeeded.
          o_oic->get_document_proxy( EXPORTING
                                       document_type  = soi_doctype_excel_sheet " 'Excel.Sheet'
                                     IMPORTING
                                       document_proxy = o_oid
                                       error          = o_err ).
          APPEND o_err TO it_errors.

          IF abap_true = o_err->has_succeeded.
            DATA(it_raw) = cl_bcs_convert=>xstring_to_solix( iv_xstring = lv_xl_xdata ).
            DATA(lv_rawlen) = xstrlen( lv_xl_xdata ).

* Binärdaten des *.xlsx im Hintergrund an DOI-Objekt senden und anzeigen
            o_oid->open_document_from_table( EXPORTING
                                               document_size  = lv_rawlen
                                               document_table = it_raw
                                               open_inplace   = abap_true
                                             IMPORTING
                                               error          = o_err ).

            APPEND o_err TO it_errors.

            IF abap_true = o_err->has_succeeded.

* leere Toolbar ausblenden
              cl_abap_list_layout=>suppress_toolbar( ).

* cl_gui_container=>default_screen erzwingen
              CALL SCREEN 100.

* Schließen eines Dokuments in der Office-Anwendung
              o_oid->close_document( IMPORTING error = o_err ).

              APPEND o_err TO it_errors.

              IF o_err->has_succeeded = abap_true.

* Schließen eines Visual-Basic-Dokuments
                o_oid->close_activex_document( IMPORTING error = o_err ).

                APPEND o_err TO it_errors.

                IF o_err->has_succeeded = abap_true.

* Freigabe des für das Dokument reservierten Speichers
                  o_oid->release_document( IMPORTING error = o_err ).

                  APPEND o_err TO it_errors.

                  IF o_err->has_succeeded = abap_true.
                    FREE: o_oid.
                  ENDIF.
                ENDIF.
              ENDIF.
            ENDIF.

* baut das Control ab
            o_oic->destroy_control( IMPORTING error = o_err ).

            APPEND o_err TO it_errors.

            IF o_err->has_succeeded = abap_true.
              FREE: o_oic.
            ENDIF.

          ENDIF.
        ENDIF.
      ENDIF.

* Ausgabe Fehlerprotokoll
      DATA: lv_message_id TYPE sy-msgid.
      DATA: lv_message_number TYPE sy-msgno.
      DATA: lv_param1 TYPE sy-msgv1.
      DATA: lv_param2 TYPE sy-msgv2.
      DATA: lv_param3 TYPE sy-msgv3.
      DATA: lv_param4 TYPE sy-msgv4.

      LOOP AT it_errors ASSIGNING FIELD-SYMBOL(<e>).
        <e>->get_message( IMPORTING
                            message_id     = lv_message_id
                            message_number = lv_message_number
                            param1         = lv_param1
                            param2         = lv_param2
                            param3         = lv_param3
                            param4         = lv_param4
                        ).
        WRITE: / <e>->error_code, lv_message_id, lv_message_number, lv_param1, lv_param2, lv_param3, lv_param4.
      ENDLOOP.

    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

Variante 2 (Daten aus der Tabelle SFLIGHT anzeigen, Styles festlegen)

* Dummy-Screen für cl_gui_container=>default_screen deklarieren
SELECTION-SCREEN BEGIN OF SCREEN 100.
SELECTION-SCREEN END OF SCREEN 100.

START-OF-SELECTION.

* Daten holen
  SELECT * FROM sflight INTO TABLE @DATA(it_sflight).

  TRY.
* ABAP2XLSX
      DATA(o_xl) = NEW zcl_excel( ).
* aktive Worksheet
      DATA(o_xl_ws) = o_xl->get_active_worksheet( ).
* Name der Sheet
      o_xl_ws->set_title( ip_title = 'Flights' ).

* Tabelle übergeben und Style festlegen
      o_xl_ws->bind_table( ip_table          = it_sflight
                           is_table_settings = VALUE #(
                                                        table_style      = zcl_excel_table=>builtinstyle_medium2
*                                                  show_row_stripes = abap_true
                                                      )
                         ).

* oberste Zeile einfrieren -> beim Scrollen immer sichtbar
      o_xl_ws->freeze_panes( ip_num_rows = 1 ).

* Binärdaten (xstring) generieren
      DATA(o_xlwriter) = CAST zif_excel_writer( NEW zcl_excel_writer_2007( ) ).
      DATA(lv_xl_xdata) = o_xlwriter->write_file( o_xl ).

* Daten mit DOI (Excel inplace) anzeigen
      DATA: o_oic TYPE REF TO i_oi_container_control.
      DATA: o_oid TYPE REF TO i_oi_document_proxy.
      DATA: o_err TYPE REF TO i_oi_error.
      DATA: it_errors TYPE STANDARD TABLE OF REF TO i_oi_error WITH NON-UNIQUE DEFAULT KEY.

      c_oi_container_control_creator=>get_container_control( IMPORTING
                                                               control = o_oic
                                                               error   = o_err ).

      APPEND o_err TO it_errors.

      IF abap_true = o_err->has_succeeded.
        o_oic->init_control( EXPORTING
                               inplace_enabled     = abap_true
                               no_flush            = abap_true
                               r3_application_name = 'Flugverbindungen'
                               parent              = cl_gui_container=>default_screen
                             IMPORTING
                               error               = o_err ).

        APPEND o_err TO it_errors.

        IF abap_true = o_err->has_succeeded.
          o_oic->get_document_proxy( EXPORTING
                                       document_type  = soi_doctype_excel_sheet " 'Excel.Sheet'
                                     IMPORTING
                                       document_proxy = o_oid
                                       error          = o_err ).

          APPEND o_err TO it_errors.

          IF abap_true = o_err->has_succeeded.
            DATA(it_raw) = cl_bcs_convert=>xstring_to_solix( iv_xstring = lv_xl_xdata ).
            DATA(lv_rawlen) = xstrlen( lv_xl_xdata ).

* Binärdaten des *.xlsx im Hintergrund an DOI-Objekt senden und anzeigen
            o_oid->open_document_from_table( EXPORTING
                                               document_size    = lv_rawlen
                                               document_table   = it_raw
                                               open_inplace     = abap_true
                                             IMPORTING
                                               error          = o_err ).

            APPEND o_err TO it_errors.

            IF abap_true = o_err->has_succeeded.

* leere Toolbar ausblenden
              cl_abap_list_layout=>suppress_toolbar( ).

* cl_gui_container=>default_screen erzwingen
              CALL SCREEN 100.

* Schließen eines Dokuments in der Office-Anwendung
              o_oid->close_document( IMPORTING error = o_err ).

              APPEND o_err TO it_errors.

              IF o_err->has_succeeded = abap_true.

* Schließen eines Visual-Basic-Dokuments
                o_oid->close_activex_document( IMPORTING error = o_err ).

                APPEND o_err TO it_errors.

                IF o_err->has_succeeded = abap_true.

* Freigabe des für das Dokument reservierten Speichers
                  o_oid->release_document( IMPORTING error = o_err ).

                  APPEND o_err TO it_errors.

                  IF o_err->has_succeeded = abap_true.
                    FREE: o_oid.
                  ENDIF.
                ENDIF.
              ENDIF.
            ENDIF.

* baut das Control ab
            o_oic->destroy_control( IMPORTING error = o_err ).

            APPEND o_err TO it_errors.

            IF o_err->has_succeeded = abap_true.
              FREE: o_oic.
            ENDIF.

          ENDIF.
        ENDIF.
      ENDIF.

* Ausgabe Fehlerprotokoll
      DATA: lv_message_id TYPE sy-msgid.
      DATA: lv_message_number TYPE sy-msgno.
      DATA: lv_param1 TYPE sy-msgv1.
      DATA: lv_param2 TYPE sy-msgv2.
      DATA: lv_param3 TYPE sy-msgv3.
      DATA: lv_param4 TYPE sy-msgv4.

      LOOP AT it_errors ASSIGNING FIELD-SYMBOL(<e>).
        <e>->get_message( IMPORTING
                            message_id     = lv_message_id
                            message_number = lv_message_number
                            param1         = lv_param1
                            param2         = lv_param2
                            param3         = lv_param3
                            param4         = lv_param4
                        ).
        WRITE: / <e>->error_code, lv_message_id, lv_message_number, lv_param1, lv_param2, lv_param3, lv_param4.
      ENDLOOP.

    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

Variante 3 (Daten aus der Tabelle SFLIGHT anzeigen, Komplexbeispiel)

* Dummy-Screen für cl_gui_container=>default_screen deklarieren
SELECTION-SCREEN BEGIN OF SCREEN 100.
SELECTION-SCREEN END OF SCREEN 100.

START-OF-SELECTION.

* Daten holen
  SELECT * FROM sflight INTO TABLE @DATA(it_sflight).

  TRY.
* ABAP2XLSX
      DATA(o_xl) = NEW zcl_excel( ).
* aktive Worksheet
      DATA(o_xl_ws) = o_xl->get_active_worksheet( ).
* Name der Sheet
      o_xl_ws->set_title( ip_title = 'Flights' ).
* Gitterlinien ausschalten
      o_xl_ws->set_show_gridlines( i_show_gridlines = abap_false ).
* oberste zwei Zeilen einfrieren -> beim Scrollen immer sichtbar
      o_xl_ws->freeze_panes( ip_num_rows = 2 ).

* RTTS: Table: Beschreibung
      DATA(o_tdesc) = CAST cl_abap_tabledescr( cl_abap_tabledescr=>describe_by_data( it_sflight ) ).
* RTTS: Row: Beschreibung
      DATA(o_sdesc) = CAST cl_abap_structdescr( o_tdesc->get_table_line_type( ) ).
* RTTS: Col: Komponenten (Felder) -> DFIES: DD-Schnittstelle: Tabellenfelder für DDIF_FIELDINFO_GET
      DATA(it_com) = o_sdesc->get_components( ).

* Header einfügen
      LOOP AT it_com ASSIGNING FIELD-SYMBOL(<c>).
        DATA(lv_col_head) = sy-tabix.
* Prüfen, ob DDIC-Typ, damit die Metadaten ausgelesen werden können
        DATA(o_edesc_head) = CAST cl_abap_elemdescr( <c>-type ).
        IF o_edesc_head->is_ddic_type( ) EQ abap_true.
          DATA(lv_dfies_head) = o_edesc_head->get_ddic_field( ).
* technischer Name in die erste Zeile
          o_xl_ws->set_cell( ip_column = lv_col_head
                             ip_row    = 1
                             ip_value  = <c>-name ).
* Langtext des Feldes in die zweite Zeile
          o_xl_ws->set_cell( ip_column = lv_col_head
                             ip_row    = 2
                             ip_value  = lv_dfies_head-scrtext_l ).

* Spaltenbreite setzen
          o_xl_ws->get_column( lv_col_head )->set_width( strlen( lv_dfies_head-scrtext_l ) + 5 ).
        ENDIF.

      ENDLOOP.

* Daten einfügen
      LOOP AT it_sflight ASSIGNING FIELD-SYMBOL(<row>).
        DATA(lv_row) = sy-tabix + 2.

        LOOP AT it_com ASSIGNING FIELD-SYMBOL(<col>).
          DATA(lv_col) = sy-tabix.

* Prüfen, ob DDIC-Typ, damit die Metadaten ausgelesen werden können
          DATA(o_edesc) = CAST cl_abap_elemdescr( <col>-type ).
          IF o_edesc->is_ddic_type( ) EQ abap_true.
* wenn DDIC-Typ, dann Metainformationen auslesen
            DATA(lv_dfies) = o_edesc->get_ddic_field( ).
* anhand des Komponentennamens den akt. Zellinhalt einer Zeile lesen
            ASSIGN COMPONENT <col>-name OF STRUCTURE <row> TO FIELD-SYMBOL(<cell>).
            IF <cell> IS ASSIGNED.
              o_xl_ws->set_cell( ip_column = lv_col
                                 ip_row    = lv_row
                                 ip_value  = <cell> ).
            ENDIF.
          ENDIF.
        ENDLOOP.

* Zeilenhöhe
        o_xl_ws->get_row( ip_row = lv_row )->set_row_height( 15 ).
      ENDLOOP.

* Autofilter setzen
      DATA(o_autofilter) = o_xl->add_new_autofilter( io_sheet = o_xl_ws ).
      o_autofilter->set_filter_area( is_area = VALUE #( row_start = 2
                                                        col_start = 1
                                                        row_end   = o_xl_ws->get_highest_row( )
                                                        col_end   = o_xl_ws->get_highest_column( ) ) ).

* Binärdaten (xstring) generieren
      DATA(o_xlwriter) = CAST zif_excel_writer( NEW zcl_excel_writer_2007( ) ).
      DATA(lv_xl_xdata) = o_xlwriter->write_file( o_xl ).

* Daten mit DOI (Excel inplace) anzeigen
      DATA: o_oic TYPE REF TO i_oi_container_control.
      DATA: o_oid TYPE REF TO i_oi_document_proxy.
      DATA: o_err TYPE REF TO i_oi_error.
      DATA: it_errors TYPE STANDARD TABLE OF REF TO i_oi_error WITH NON-UNIQUE DEFAULT KEY.

      c_oi_container_control_creator=>get_container_control( IMPORTING
                                                               control = o_oic
                                                               error   = o_err ).

      APPEND o_err TO it_errors.

      IF abap_true = o_err->has_succeeded.
        o_oic->init_control( EXPORTING
                               inplace_enabled     = abap_true
                               no_flush            = abap_true
                               r3_application_name = 'Flugverbindungen'
                               parent              = cl_gui_container=>default_screen
                             IMPORTING
                               error               = o_err ).

        APPEND o_err TO it_errors.

        IF abap_true = o_err->has_succeeded.
          o_oic->get_document_proxy( EXPORTING
                                       document_type  = soi_doctype_excel_sheet " 'Excel.Sheet'
                                     IMPORTING
                                       document_proxy = o_oid
                                       error          = o_err ).

          APPEND o_err TO it_errors.

          IF abap_true = o_err->has_succeeded.
            DATA(it_raw) = cl_bcs_convert=>xstring_to_solix( iv_xstring = lv_xl_xdata ).
            DATA(lv_rawlen) = xstrlen( lv_xl_xdata ).

* Binärdaten des *.xlsx im Hintergrund an DOI-Objekt senden und anzeigen
            o_oid->open_document_from_table( EXPORTING
                                               document_size  = lv_rawlen
                                               document_table = it_raw
                                               open_inplace   = abap_true
                                             IMPORTING
                                               error          = o_err ).

            APPEND o_err TO it_errors.

            IF abap_true = o_err->has_succeeded.
* leere Toolbar ausblenden
              cl_abap_list_layout=>suppress_toolbar( ).

* cl_gui_container=>default_screen erzwingen
              CALL SCREEN 100.

* Schließen eines Dokuments in der Office-Anwendung
              o_oid->close_document( IMPORTING error = o_err ).

              APPEND o_err TO it_errors.

              IF o_err->has_succeeded = abap_true.

* Schließen eines Visual-Basic-Dokuments
                o_oid->close_activex_document( IMPORTING error = o_err ).

                APPEND o_err TO it_errors.

                IF o_err->has_succeeded = abap_true.

* Freigabe des für das Dokument reservierten Speichers
                  o_oid->release_document( IMPORTING error = o_err ).

                  APPEND o_err TO it_errors.

                  IF o_err->has_succeeded = abap_true.
                    FREE: o_oid.
                  ENDIF.
                ENDIF.
              ENDIF.
            ENDIF.

* baut das Control ab
            o_oic->destroy_control( IMPORTING error = o_err ).

            APPEND o_err TO it_errors.

            IF o_err->has_succeeded = abap_true.
              FREE: o_oic.
            ENDIF.

          ENDIF.
        ENDIF.
      ENDIF.

* Ausgabe Fehlerprotokoll
      DATA: lv_message_id TYPE sy-msgid.
      DATA: lv_message_number TYPE sy-msgno.
      DATA: lv_param1 TYPE sy-msgv1.
      DATA: lv_param2 TYPE sy-msgv2.
      DATA: lv_param3 TYPE sy-msgv3.
      DATA: lv_param4 TYPE sy-msgv4.

      LOOP AT it_errors ASSIGNING FIELD-SYMBOL(<e>).
        <e>->get_message( IMPORTING
                            message_id     = lv_message_id
                            message_number = lv_message_number
                            param1         = lv_param1
                            param2         = lv_param2
                            param3         = lv_param3
                            param4         = lv_param4
                        ).
        WRITE: / <e>->error_code, lv_message_id, lv_message_number, lv_param1, lv_param2, lv_param3, lv_param4.
      ENDLOOP.

    CATCH cx_root INTO DATA(e_txt).
      WRITE: / e_txt->get_text( ).
  ENDTRY.

[ABAP] Desktop-Office-Integration (DOI) (Listausgabe / MS Excel inplace)

Vorteile

Einfacher unkomplizierter Zugriff auf MS Excel-Dateien

Nachteile

Office muss auf dem Clientrechner installiert sein, nicht zur Ausführung per Job geeignet
Einbettung in SAP Document Container Control: Kapselung von ActiveX / OLE2, daher rel. langsam
Interface unterstützt nur 9999 Zeilen und Spalten, da row und column vom Typ C(4)
kann alles besser über OLE2-Objekte manuell abgebildet werden -> mehr Freiheitsgrade

* Demoprogramme:
*   SAPRDEMO_DOI_BDS
*   SAPRDEMO_FORM_INTERFACE
*   SAPRDEMO_SPREADSHEET_INTERFACE
*   SAPRDEMO_MAILMERGE_INTERFACE
* Interface:
*   I_OI_DOCUMENT_PROXY

DATA: lv_has_activex TYPE abap_bool.

CALL FUNCTION 'GUI_HAS_ACTIVEX'
  IMPORTING
    return = lv_has_activex.

IF lv_has_activex = abap_true.
  DATA: lv_error TYPE REF TO i_oi_error.
  DATA: lv_size TYPE i.
  DATA: it_xl_upload TYPE umb_bds_content.
  DATA: o_control TYPE REF TO i_oi_container_control.
  DATA: o_document TYPE REF TO i_oi_document_proxy.
  DATA: o_spreadsheet TYPE REF TO i_oi_spreadsheet.

  cl_gui_frontend_services=>gui_upload( EXPORTING
                                          filename     = 'c:\temp\Excel.xlsx'
                                          filetype     = 'BIN'
                                          read_by_line = space
                                        IMPORTING
                                          filelength   = lv_size
                                        CHANGING
                                          data_tab     = it_xl_upload ).

* Erzeugt eine Container-Control-Instanz
  c_oi_container_control_creator=>get_container_control( IMPORTING
                                                           control = o_control
                                                           error   = lv_error ).

  IF lv_error->has_succeeded = abap_true.
* erzeugt und initializiert das Control
    o_control->init_control( EXPORTING
                               no_flush                 = abap_true
                               r3_application_name      = 'DOI Beispiel'
                               parent                   = cl_gui_container=>default_screen
                               inplace_enabled          = abap_true
                               inplace_show_toolbars    = abap_false
                               inplace_scroll_documents = abap_true
                               register_on_close_event  = abap_true
                               register_on_custom_event = abap_true
                             IMPORTING
                               error                 = lv_error ).

    IF lv_error->has_succeeded = abap_true.
* erzeugt eine Instanz für die Verwaltung eines Dokuments
      o_control->get_document_proxy( EXPORTING
                                       document_type  = soi_doctype_excel_sheet
                                       no_flush       = abap_true
                                     IMPORTING
                                       document_proxy = o_document
                                       error          = lv_error ).

      IF lv_error->has_succeeded = abap_true.
* Öffnen eines bereits existierenden Dokuments
        o_document->open_document_from_table( EXPORTING
                                                 document_size    = lv_size
                                                 document_table   = it_xl_upload
                                                 document_title   = 'DOI Beispiel'
                                                 open_inplace     = abap_true " SAP Inplace anzeigen oder extern in MS Excel
                                                 protect_document = ' '
                                               IMPORTING
                                                 error            = lv_error ).

        IF lv_error->has_succeeded = abap_true.
* liefert eine Interface-Referenz zurück
          o_document->get_spreadsheet_interface( IMPORTING
                                                   sheet_interface = o_spreadsheet
                                                   error           = lv_error ).

          IF lv_error->has_succeeded = abap_true.
            DATA: it_cells TYPE soi_generic_table.
            DATA: it_ranges TYPE soi_range_list.
            DATA: lv_retcode TYPE soi_ret_string.

            it_ranges = VALUE #( ( name = 'MyRange' ) ).

* Einfügen eines Bereiches ausgehend von den Dimensionen
            o_spreadsheet->insert_range_dim( EXPORTING
                                               name = it_ranges[ 1 ]-name
                                               top = 1
                                               left = 1
                                               rows = 10
                                               columns = 10
                                               no_flush = abap_true ).

* Holen der Namen und der Daten der Bereiche
            o_spreadsheet->get_ranges_data( EXPORTING
                                              all      = abap_false
                                            IMPORTING
                                              contents = it_cells
                                              error    = lv_error
                                              retcode  = lv_retcode
                                            CHANGING
                                              ranges   = it_ranges ).

            LOOP AT it_cells ASSIGNING FIELD-SYMBOL(<fs_cells>) GROUP BY <fs_cells>-row.
              DATA(lv_line) = ||.

              LOOP AT GROUP <fs_cells> ASSIGNING FIELD-SYMBOL(<fs_col>).
                IF lv_line IS INITIAL.
                  lv_line = |{ <fs_col>-value WIDTH = 20 }|.
                ELSE.
                  lv_line = |{ lv_line }{ <fs_col>-value WIDTH = 20 }|.
                ENDIF.
              ENDLOOP.

              WRITE: / <fs_cells>-row, lv_line.
            ENDLOOP.

* Schließen eines Dokuments in der Office-Anwendung
            o_document->close_document( IMPORTING error = lv_error ).
            IF lv_error->has_succeeded = abap_true.

* Schließen eines Visual-Basic-Dokuments
              o_document->close_activex_document( IMPORTING error = lv_error ).
              IF lv_error->has_succeeded = abap_true.

* Freigabe des für das Dokument reservierten Speichers
                o_document->release_document( IMPORTING error = lv_error ).

                IF lv_error->has_succeeded = abap_true.
                  FREE: o_document.
                ENDIF.
              ENDIF.
            ENDIF.

* baut das Control ab
            o_control->destroy_control( IMPORTING error = lv_error ).

            IF lv_error->has_succeeded = abap_true.
              FREE: o_control.
            ENDIF.

          ENDIF.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDIF.
ENDIF.

[ABAP] Excel-Daten in interne Tabelle einlesen

* alternative Funktionsbausteine:
* ALSM_EXCEL_TO_INTERNAL_TABLE -> nur Zellen mit 32 Zeichen Länge
* KCD_EXCEL_OLE_TO_INT_CONVERT -> nur Zellen mit 50 Zeichen Länge
* UPLOAD_XLS_FILE_2_ITAB
* FILE_READ_AND_CONVERT_SAP_DATA
* IMPORT_FROM_SPREADSHEET

TYPES: BEGIN OF ty_data,
         name    TYPE char255,
         gewicht TYPE f,
       END OF ty_data.

DATA: it_raw TYPE truxs_t_text_data.

PARAMETERS: p_file TYPE file_table-filename OBLIGATORY.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  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 = |TXT (*.txt)\|*.txt\|XLS (*.xls)\|*.xls\|XLSX (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                  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_file = 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_datatab TYPE STANDARD TABLE OF ty_data.

* Import von Excel-Daten in interne Tabelle über die Ole-Schnittstelle
* Format der Tabelle ist in ty_data definiert
* Unterstützte Dateiformate: *.csv (*.txt), *.xls, *.xlsx
* Das Format der Fließkommazahlen bei CSV muß dem Gebietsschema
* von Excel entsprechen: 1,23 (Dezimalseparator = ',')
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
      i_field_seperator    = ';'                            " Trennzeichen für CSV-Datei
      i_line_header        = abap_true                      " Überschrift in der Tabelle
      i_tab_raw_data       = it_raw
      i_filename           = CONV rlgrap-filename( p_file ) " i_filename -> nur 128 Zeichen für Dateinamenlänge erlaubt
    TABLES
      i_tab_converted_data = it_datatab
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.

  IF sy-subrc = 0.
    LOOP AT it_datatab ASSIGNING FIELD-SYMBOL(<fs_line>).
      WRITE: / <fs_line>-name, <fs_line>-gewicht.
    ENDLOOP.
  ELSE.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

Weiterführende Infos: Link

Kategorie: Import

[ABAP] XLSX-Datei mit Klasse cl_ehfnd_xlsx einlesen und in SALV-Grid anzeigen

[ABAP] MS-Excel-Datei mit Hilfe der Klasse cl_ehfnd_xlsx lesen

[ABAP] Beispiel für Verwendung der Klasse cl_xlsx_document

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

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

[ABAP] Excel-Datei (*.xlsx) mit Desktop-Office-Integration (DOI) laden und anzeigen

[ABAP] Excel-Datei (*.xlsx) in interne Tabelle laden (cl_fdt_xl_spreadsheet)

Variante 1 (generische Ausgabe mit cl_demo_output)

Variante 2 (generische Listausgabe)

Variante 3 (generische Ausgabe im SALV-Grid)

[ABAP] ABAP2XLSX: Interne Tabelle als Excel-Datei (*.xlsx) aufbereiten und mit Desktop Office Integration (DOI) als Excel inplace anzeigen

Variante 1 (Daten aus der Tabelle SFLIGHT anzeigen)

Variante 2 (Daten aus der Tabelle SFLIGHT anzeigen, Styles festlegen)

Variante 3 (Daten aus der Tabelle SFLIGHT anzeigen, Komplexbeispiel)

[ABAP] Desktop-Office-Integration (DOI) (Listausgabe / MS Excel inplace)

Vorteile

Nachteile

Links

[ABAP] Excel-Daten in interne Tabelle einlesen

Kategorien

Neueste Beiträge

Archiv

Meta

Variante 1 (generische Ausgabe mit cl_demo_output)

Variante 2 (generische Listausgabe)

Variante 3 (generische Ausgabe im SALV-Grid)

Variante 1 (Daten aus der Tabelle SFLIGHT anzeigen)

Variante 2 (Daten aus der Tabelle SFLIGHT anzeigen, Styles festlegen)

Variante 3 (Daten aus der Tabelle SFLIGHT anzeigen, Komplexbeispiel)

Vorteile

Nachteile

Links

Kategorien

Neueste Beiträge

Archiv

Meta

Related Posts

Tags