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.