[ABAP] ExcelOle – Seitenformat einstellen

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

...

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.
" Objekt wieder freigeben
FREE OBJECT o_interior.

" Objekt wieder freigeben
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_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 activieren
        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.

[ABAP] ExcelOle – Excel-Datei öffnen

DATA: o_excel TYPE ole2_object.
DATA: o_workbook TYPE ole2_object.
  
START-OF-SELECTION.
  " 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.
    " Workbook holen
    CALL METHOD OF o_excel 'WorkBooks' = o_workbook.
    " Excel-Datei öffnen
    CALL METHOD OF o_workbook 'Open'
      EXPORTING
        #1 = 'c:\test.xlsx'.

    ...

    " Workbook schließen
    CALL METHOD OF o_workbook 'Close'.
    " Excel schließen
    CALL METHOD OF o_excel 'Quit'.
    " Objekte freigeben
    FREE OBJECT o_workbook.
    FREE OBJECT o_excel.
  ENDIF.