Startseite SAP ABAP Archive nach Kategorien "Excel"

Kategorie: Excel

[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-Version aus der Registry auslesen 

DATA(lv_xl_current_version) = ||.

TRY.
    cl_gui_frontend_services=>registry_get_value( EXPORTING
                                                    root      = 0 " HKEY_CLASSES_ROOT
                                                    key       = 'EXCEL.APPLICATION\CURVER'
                                                  IMPORTING
                                                    reg_value = lv_xl_current_version ).

*Excel 97   = 8
*Excel 2000 = 9
*Excel 2002 = 10
*Excel 2003 = 11
*Excel 2007 = 12
*Excel 2010 = 14
*Excel 2013 = 15
*Excel 2016 = 16
    WRITE: / lv_xl_current_version.

  CATCH cx_root INTO DATA(e_txt).
ENDTRY.

[ABAP] Excel-Ole Komplexbeispiel 

DATA: o_excel TYPE ole2_object.
DATA: o_workbook TYPE ole2_object.
DATA: o_sheet TYPE ole2_object.
DATA: o_cell TYPE ole2_object.
DATA: o_range TYPE ole2_object.
DATA: o_font TYPE ole2_object.
DATA: o_border TYPE ole2_object.
DATA: o_column TYPE ole2_object.
DATA: o_interior TYPE ole2_object.
DATA: o_page TYPE ole2_object.

DATA: cell_value TYPE string.

PARAMETERS: p_fname TYPE file_table-filename OBLIGATORY DEFAULT 'c:\temp\Test.xlsx'.

* wenn die F4-Hilfe für den Dateinamen aufgerufen wird
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
  DATA: lv_action TYPE i.
  DATA: lv_filename TYPE string.
  DATA: lv_fullpath TYPE string.
  DATA: lv_path TYPE string.

* FileOpen-Dialog aufrufen
  TRY.
      cl_gui_frontend_services=>file_save_dialog( EXPORTING
                                                    default_extension   = 'xlsx'
                                                    default_file_name   = |{ p_fname }|
                                                    file_filter         = |MS Excel (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                    prompt_on_overwrite = abap_true
                                                  CHANGING
                                                    filename          = lv_filename     " Dateiname
                                                    path              = lv_path         " Pfad
                                                    fullpath          = lv_fullpath     " Pfad + Dateiname
                                                    user_action       = lv_action ).    " Benutzeraktion

      IF lv_action EQ cl_gui_frontend_services=>action_ok.
        p_fname = lv_fullpath.
      ENDIF.

    CATCH cx_root INTO DATA(e_text).          " Oberklasse für Exceptions abfangen und Kurztext übergeben
      MESSAGE e_text->get_text( ) TYPE 'I'.   " Exception Kurztext ausgeben
  ENDTRY.

START-OF-SELECTION.
  TRY.
* Excel starten
      CREATE OBJECT o_excel 'Excel.Application'.

      IF sy-subrc NE 0.
        WRITE : / 'Fehler.'.
      ELSE.
* Excel sichtbar
        SET PROPERTY OF o_excel 'Visible' = 1.

* Anzahl Sheets setzen
        SET PROPERTY OF o_excel 'SheetsInNewWorkbook' = 1.

* Workbook holen
        CALL METHOD OF o_excel 'WorkBooks' = o_workbook.

* neues Workbook mit einer Sheet hinzufügen
        CALL METHOD OF o_workbook 'Add'.

* 1. Sheet holen
        CALL METHOD OF o_excel 'Worksheets' = o_sheet
          EXPORTING #1 = 1.

* Sheet activieren
        CALL METHOD OF o_sheet 'Activate'.

* Sheet benennen
        SET PROPERTY OF o_sheet 'Name' = 'Tabelle mit Testwerten'.

* Zelle 1,1 ermitteln
        CALL METHOD OF o_excel 'Cells' = o_cell
           EXPORTING
             #1 = 1   " Zeile
             #2 = 1.  " Spalte

* Wert schreiben
        SET PROPERTY OF o_cell 'Value' = 'Text 1_1'.

* Zellen-Objekt wieder freigeben
        FREE OBJECT o_cell.

* Zelle 1,2 ermitteln
        CALL METHOD OF o_excel 'Cells' = o_cell
           EXPORTING
             #1 = 1   " Zeile
             #2 = 2.  " Spalte

* Wert schreiben
        SET PROPERTY OF o_cell 'Value' = 'Text 1_2'.

* Zellen-Objekt wieder freigeben
        FREE OBJECT o_cell.

* Zelle 2,1 ermitteln
        CALL METHOD OF o_excel 'Cells' = o_cell
           EXPORTING
             #1 = 2   " Zeile
             #2 = 1.  " Spalte

* Wert schreiben
        SET PROPERTY OF o_cell 'Value' = 'Text 2_1'.

* Zellen-Objekt wieder freigeben
        FREE OBJECT o_cell.

* Zelle 2,2 ermitteln
        CALL METHOD OF o_excel 'Cells' = o_cell
           EXPORTING
             #1 = 2   " Zeile
             #2 = 2.  " Spalte

* Wert schreiben
        SET PROPERTY OF o_cell 'Value' = 'Text 2_2'.

* Zellen-Objekt wieder freigeben
        FREE OBJECT o_cell.

* Range (Bereich) [A2:B2] definieren
        CALL METHOD OF o_excel 'Range' = o_range
          EXPORTING
            #1 = 'A2'
            #2 = 'B2'.

* Range Schrift setzen
        GET PROPERTY OF o_range 'Font' = o_font.
        SET PROPERTY OF o_font 'Bold' = 1.
        SET PROPERTY OF o_font 'ColorIndex' = 4.
        SET PROPERTY OF o_font 'Size' = 12.

* Font-Objekt wieder freigeben
        FREE OBJECT o_font.

* Rahmen für Range setzen
        CALL METHOD OF o_range 'Select' .
        CALL METHOD OF o_range 'BorderAround'
          EXPORTING
            #1 = 1  " Stil
            #2 = 2. " Dicke (1..4)

* Zellhintergrund für Range setzen
        CALL METHOD OF o_range 'Interior' = o_interior.
        SET PROPERTY OF o_interior 'ColorIndex' = 6. " gelb
        SET PROPERTY OF o_interior 'Pattern' = 1.

* Hintergrund-Objekt wieder freigeben
        FREE OBJECT o_interior.

* Range-Objekt wieder freigeben
        FREE OBJECT o_range.

* Zelle ermitteln
        CALL METHOD OF o_excel 'Cells' = o_cell
           EXPORTING
             #1 = 1   " Zeile
             #2 = 2.  " Spalte

* Zellwert holen
        GET PROPERTY OF o_cell 'Value' = cell_value.

* Zell-Objekt wieder freigeben
        FREE OBJECT o_cell.

* Zellwert ausgeben
        WRITE: / cell_value.

* Zelle ermitteln
        CALL METHOD OF o_excel 'Cells' = o_cell
           EXPORTING
             #1 = 2   " Zeile
             #2 = 2.  " Spalte

* Font der Zelle holen und Darstellung ändern
        GET PROPERTY OF o_cell 'Font' = o_font.
        SET PROPERTY OF o_font 'Name' = 'Courier New'. " Schriftart
        SET PROPERTY OF o_font 'Bold' = 0.             " Schriftstil: 0 - normal, 1 - fett
        SET PROPERTY OF o_font 'Italic' = 1.           " Schriftstil: 0 - normal, 1 - Italic
        SET PROPERTY OF o_font 'ColorIndex' = 5.       " Farbindex: 1=Schwarz, 2=weiss, 3=rot, 4=grün, 5=blau, 6=gelb
        SET PROPERTY OF o_font 'Size' = 16.            " Schriftgröße [px]
        SET PROPERTY OF o_font 'Underline' = 2.        " xlUnderlineStyleSingle

* Font-Objekt wieder freigeben
        FREE OBJECT o_font.
* Zell-Objekt wieder freigeben
        FREE OBJECT o_cell.

* Spaltenbreite automatisch anpassen
        CALL METHOD OF o_excel 'Columns' = o_column.
        CALL METHOD OF o_column 'Autofit'.

* Spalten-Objekt wieder freigeben
        FREE OBJECT o_column.

* Spalte 1 holen
        CALL METHOD OF o_excel 'Columns' = o_column
          EXPORTING
            #1 = 1. " erste Spalte

* Spaltenformat festlegen
        SET PROPERTY OF o_column 'NumberFormat' = '@'. " Format: "Text"

* Spalten-Objekt wieder freigeben
        FREE OBJECT o_column.

*        " Inhalt der Zwischenablage in die akt. Sheet Zelle 'A1' kopieren
*        CALL METHOD OF o_excel 'Cells' = o_cell
*          EXPORTING
*            #1 = 1  " Zeile
*            #2 = 1. " Spalte
*        CALL METHOD OF o_cell 'SELECT'.
*        CALL METHOD OF o_sheet 'Paste'.
*        " Objekt wieder freigeben
*        FREE OBJECT o_cell.

* Seitenausrichtung -> Querformat
        GET PROPERTY OF o_sheet 'PageSetup' = o_page.
        SET PROPERTY OF o_page 'Orientation' = 2.     " xlLandscape
        SET PROPERTY OF o_page 'PrintGridlines' = 1.  " Gitterlinien drucken
        SET PROPERTY OF o_page 'LeftMargin' = '5.00'. " Seitenränder
        SET PROPERTY OF o_page 'RightMargin' = '2.00'.
        SET PROPERTY OF o_page 'TopMargin' = '2.00'.
        SET PROPERTY OF o_page 'BottomMargin'= '2.00'.

* Seiten-Objekt wieder freigeben
        FREE OBJECT o_page.

* Drucken
*        CALL METHOD OF o_workbook 'PrintOut' .

* MessageDialog für Überschreiben einer bestehenden Datei unterdrücken
        SET PROPERTY OF o_excel 'DisplayAlerts' = 0.

* Datei speichern unter ...
        CALL METHOD OF o_sheet 'SaveAs'
          EXPORTING
            #1 = p_fname.
*            #2 = 1. " Fileformat

* Alle Messages in Excel wieder zulassen
        SET PROPERTY OF o_excel 'DisplayAlerts' = -1.

* Workbook schließen
        CALL METHOD OF o_workbook 'Close'.

* Excel schließen
        CALL METHOD OF o_excel 'Quit'.

* Objekte freigeben
        FREE OBJECT: o_sheet,
                     o_workbook,
                     o_excel.
      ENDIF.
    CATCH cx_root INTO DATA(e_text).          " Oberklasse für Exceptions abfangen und Kurztext übergeben
      MESSAGE e_text->get_text( ) TYPE 'I'.   " Exception Kurztext ausgeben
  ENDTRY.

[ABAP] Desktop Office Integration (DOI): Eventhandler, leeres Dokument erzeugen, Zugriff auf Ole-Funktionen für Ole-Automation 

* https://www.consolut.com/fileadmin/user_upload/SAP_PrintDoku/de/BCCIOFFI/BCCIOFFI.PDF
* https://www.saptechpro.com/post/SAPRDEMO_MS_DEMO
* https://www.saptechpro.com/post/SAPRDEMODOCUMENTCONTAINER2

* Eventhandler für Excel on_closed
CLASS lcl_events DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS: on_close FOR EVENT on_close_document OF i_oi_document_proxy
      IMPORTING
          document_proxy
          has_changed.
ENDCLASS.

CLASS lcl_events IMPLEMENTATION.
  METHOD on_close.
    MESSAGE 'Excel closed.' TYPE 'S' DISPLAY LIKE 'W'.
  ENDMETHOD.
ENDCLASS.

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

* 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_false
                               no_flush                = abap_true
                               r3_application_name     = 'Excel Daten'
                               parent                  = cl_gui_container=>default_screen
                               register_on_close_event = abap_true
                             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.
* neues, leeres Workbook einfügen
            o_oid->create_document( EXPORTING
                                      document_title = 'Excel Test'
                                      open_inplace   = abap_false
                                    IMPORTING
                                      error          = o_err ).

* Event-Handler für on_close Event
            SET HANDLER lcl_events=>on_close FOR o_oid.

* Abfrage nach dem Automation-Handle des Dokumentes
            DATA: lv_cntl_handle TYPE cntl_handle.
            o_oid->get_document_handle( IMPORTING
                                          handle  = lv_cntl_handle
                                          error   = o_err ).

            APPEND o_err TO it_errors.

            IF abap_true = o_err->has_succeeded.

* Ole-Funktionen Direkt aufrufen
              DATA: o_xl_ole TYPE ole2_object.
              DATA: o_cell_ole TYPE ole2_object.
              GET PROPERTY OF lv_cntl_handle-obj 'Application' = o_xl_ole.
              CALL METHOD OF o_xl_ole 'Cells' = o_cell_ole
                EXPORTING
                  #1 = 1
                  #2 = 1.
              SET PROPERTY OF o_cell_ole 'Value' = 'Test'.
              FREE OBJECT o_cell_ole.
              FREE OBJECT o_xl_ole.

* 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] 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] Interne Tabelle als Excel-Datei (*.xlsx) speichern (cl_fdt_xl_spreadsheet) 

* Mit Hilfe der Klasse cl_fdt_xl_spreadsheet können über XML-Transformationen
* Daten aus SAP in XLSX-Dateien geschrieben werden
TRY.
* Testdaten lesen
    SELECT * FROM t001 INTO TABLE @DATA(it_t001).

    IF sy-subrc = 0.
* Header erzeugen
      DATA: it_columns TYPE if_fdt_doc_spreadsheet=>t_column.
      DATA: lv_head TYPE t001.
      DATA(o_desc) = CAST cl_abap_structdescr( cl_abap_structdescr=>describe_by_data( lv_head ) ).

      LOOP AT o_desc->get_components( ) ASSIGNING FIELD-SYMBOL(<c>).
        IF <c> IS ASSIGNED.
          IF <c>-type->kind = cl_abap_typedescr=>kind_elem.
            APPEND VALUE #( id           = sy-tabix
                            name         = <c>-name
                            display_name = <c>-name
                            is_result    = abap_true
                            type         = <c>-type ) TO it_columns.
          ENDIF.
        ENDIF.
      ENDLOOP.

* itab + header -> XML -> xstring
* Achtung: Speicherintensiv und rel. langsam! Es sollten keine großen Datenmengen verarbeitet werden.
      DATA(lv_bin_data) = cl_fdt_xl_spreadsheet=>if_fdt_doc_spreadsheet~create_document( columns      = it_columns " optional
                                                                                         itab         = REF #( it_t001 )
                                                                                         iv_call_type = if_fdt_doc_spreadsheet=>gc_call_dec_table ).
      IF xstrlen( lv_bin_data ) > 0.
        DATA: lv_action TYPE i.
        DATA: lv_filename TYPE string.
        DATA: lv_fullpath TYPE string.
        DATA: lv_path TYPE string.

* Save-Dialog
        cl_gui_frontend_services=>file_save_dialog( EXPORTING
                                                      default_file_name = 'Excel.xlsx'
                                                      default_extension = 'xlsx'
                                                      file_filter       = |Excel-Datei (*.xlsx)\|*.xlsx\|{ cl_gui_frontend_services=>filetype_all }|
                                                    CHANGING
                                                      filename          = lv_filename
                                                      path              = lv_path
                                                      fullpath          = lv_fullpath
                                                      user_action       = lv_action ).

        IF lv_action EQ cl_gui_frontend_services=>action_ok.
* XSTRING -> SOLIX (RAW)
          DATA(it_raw_data) = cl_bcs_convert=>xstring_to_solix( EXPORTING iv_xstring = lv_bin_data ).

* Datei lokal speichern
          cl_gui_frontend_services=>gui_download( EXPORTING
                                                    filename     = lv_fullpath
                                                    filetype     = 'BIN'
                                                    bin_filesize = xstrlen( lv_bin_data )
                                                  CHANGING
                                                    data_tab     = it_raw_data ).


        ENDIF.
      ENDIF.
    ENDIF.
  CATCH cx_root INTO DATA(e_text).
    MESSAGE e_text->get_text( ) TYPE 'I'.
ENDTRY.