Startseite SAP ABAP Excel Archive nach Kategorien "ExcelOle"

Kategorie: ExcelOle

[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] ExcelOle – Seitenformat einstellen 

DATA: o_excel TYPE ole2_object.
DATA: o_sheet TYPE ole2_object.
DATA: o_page TYPE ole2_object.

...

* Page-Property holen
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'.

* Objekt wieder freigeben
FREE OBJECT: o_page.
FREE OBJECT: o_sheet.

[ABAP] ExcelOle – Zelle formatieren 

DATA: o_excel TYPE ole2_object.
DATA: o_cell TYPE ole2_object.
DATA: o_font TYPE ole2_object.

...

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

* Font der Zelle holen
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

* Objekte wieder freigeben
FREE OBJECT: o_font.
FREE OBJECT: o_cell.

[ABAP] ExcelOle – Range (Bereich) definieren 

DATA: o_excel TYPE ole2_object.
DATA: o_range TYPE ole2_object.
DATA: o_font TYPE ole2_object.
DATA: o_interior TYPE ole2_object.

...

* Range (Bereich) 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.

CALL METHOD OF o_range 'Select' .
CALL METHOD OF o_range 'BorderAround'
  EXPORTING
	#1 = 1  " Stil
	#2 = 2. " Dicke (1..4)

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

* Objekte wieder freigeben
FREE OBJECT: o_interior.
FREE OBJECT: o_font.
FREE OBJECT: o_range.

[ABAP] ExcelOle – Zelle lesen / schreiben 

DATA: o_excel TYPE ole2_object.
DATA: o_cell TYPE ole2_object.
DATA: cell_value TYPE string.

...

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

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

* Zellwert ausgeben
WRITE: / cell_value.

* Objekt wieder freigeben
FREE OBJECT: o_cell.

[ABAP] ExcelOle – Excel-Datei erstellen und speichern 

DATA: o_excel TYPE ole2_object.
DATA: o_workbook TYPE ole2_object.
DATA: o_sheet TYPE ole2_object.
  
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 aktivieren
        CALL METHOD OF o_sheet 'Activate'.
* Sheet benennen
        SET PROPERTY OF o_sheet 'Name' = 'Tabelle mit Testwerten'.

        ...

* 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).
      MESSAGE e_text->get_text( ) TYPE 'I'.
  ENDTRY.