[ABAP] JavaScript-Code über MSScriptControl ausführen

* http://www.thescarms.com/VBasic/Scripting.aspx

* JS-Code mit Beispielfunktionen einfügen
DATA(it_js_code) = VALUE stringtab( ( |var oJSON = \{| )
                                    ( |  "name" : "Heinz",| )
                                    ( |  "age"  : 56,| )
                                    ( |  "city" : "Hamburg"| )
                                    ( |\};| )
                                    ( |function add(a, b) \{| )
                                    ( |  return a + b;| )
                                    ( |\};| )
                                    ( |function getJSONString() \{| )
                                    ( |  return oJSON["name"];| )
                                    ( |\};| )
                                   ).

DATA: lv_jscode TYPE string.

* VBA-Code in String wandeln
LOOP AT it_js_code ASSIGNING FIELD-SYMBOL(<c>).
  DATA(lv_codeline) = condense( <c> ).
  IF lv_jscode IS INITIAL.
    lv_jscode = lv_codeline.
  ELSE.
    lv_jscode = |{ lv_jscode }{ cl_abap_char_utilities=>cr_lf }{ lv_codeline }|.
  ENDIF.
ENDLOOP.

* Scriptcontrol-Objekt erzeugen
DATA: o_scr TYPE ole2_object.
* Variablen für Fehleranalyse
DATA: o_error TYPE ole2_object.
DATA: lv_line TYPE string.
DATA: lv_number TYPE string.
DATA: lv_desc TYPE string.
DATA: lv_source TYPE string.

CREATE OBJECT o_scr 'MSScriptControl.ScriptControl'.

* wenn Erzeugung ok
IF sy-subrc = 0 AND o_scr-handle <> 0 AND o_scr-type = 'OLE2'.
  WRITE: / 'Header', o_scr-header.
  WRITE: / 'Type', o_scr-type.
  WRITE: / 'Handle', o_scr-handle.
  WRITE: / 'CB-Index', o_scr-cb_index.
  WRITE: / 'CLSID', o_scr-clsid.

* GUI anzeigen
  SET PROPERTY OF o_scr 'AllowUI' = 1.
* Sprache ist 'JScript'
  SET PROPERTY OF o_scr 'Language' = 'JScript'.

* Code hinzufügen und Syntaxcheck
  CALL METHOD OF o_scr 'AddCode'
    EXPORTING
      #1 = lv_jscode.

* wenn Syntax ok
  IF sy-subrc = 0.
* Funktionsaufruf mit Übergabeparametern
    DATA: lv_res_i TYPE i.
    CALL METHOD OF o_scr 'Eval' = lv_res_i
      EXPORTING
        #1 = 'add(1, 2)'.

    WRITE: / 'add(1, 2) =', lv_res_i.

* Aufruf Stringfunktion
    DATA: lv_res_str TYPE string.
    CALL METHOD OF o_scr 'Eval' = lv_res_str
      EXPORTING
        #1 = 'getJSONString()'.

    WRITE: / 'getJSONString() =', lv_res_str.

* Aufruf einer nicht vorhandenen Funktion --> Fehlerauswertung
    DATA: lv_res_input TYPE string.
    CALL METHOD OF o_scr 'Eval' = lv_res_input
      EXPORTING
        #1 = 'getInput()'.

    IF sy-subrc = 0.
      WRITE: / 'getInput() =', lv_res_input.
    ELSE.
* Fehlerauswertung
      GET PROPERTY OF o_scr 'Error' = o_error.
      GET PROPERTY OF o_error 'Line' = lv_line.
      GET PROPERTY OF o_error 'Number' = lv_number.
      GET PROPERTY OF o_error 'Description' = lv_desc.
      GET PROPERTY OF o_error 'Source' = lv_source.

      WRITE: / 'Line:', lv_line.
      WRITE: / 'Number:', lv_number.
      WRITE: / 'Desc:', lv_desc.
      WRITE: / 'Source:', lv_source.
    ENDIF.
  ELSE.
    WRITE: / 'Fehler im Code: ', sy-subrc.
  ENDIF.

* Objekt zerstören
  FREE OBJECT o_scr.
ENDIF.

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