EXCEL Export aus SAP mit Excel 2003 / 2007

Code: Alles auswählen.

*---------------------------------------------------------------------*
*       FORM f_hex_ext_down_file
*---------------------------------------------------------------------*
* Downloadroutine für die bearbeiteten Daten
* Auswahl einer Datei und nachfolgend Download der Daten
* pd_dir liefert den aktuelle gewählten Sicherungspfad zurück
*---------------------------------------------------------------------*
FORM f_hex_ext_down_file USING pd_filename
                               pd_convert
                               pd_showexcel
                      CHANGING pd_file_error
                               pd_dir.
  DATA: ld_goon.
  DATA: ld_fileas_c(1024).
  DATA: ld_o_excel    TYPE ole2_object,
        ld_o_workbook TYPE ole2_object,
        ld_o_winstate TYPE ole2_object,
        ld_o_wrksheet TYPE ole2_object,
        ld_o_actbook  TYPE ole2_object.
  DATA: ld_o_cell1    TYPE ole2_object,
        ld_o_cell2    TYPE ole2_object,
        ld_o_range    TYPE ole2_object,
        ld_o_column   TYPE ole2_object.
  DATA: ld_o_window   TYPE ole2_object.
  DATA: lt_tx_output TYPE TABLE OF gd_outline_typ.
  DATA: ld_subrc TYPE sysubrc, ld_recalc_filename, ld_append.
  DATA: ld_max_win.

* Filenameübernahme falls noch nicht vorgegeben
  IF gd_x_fileas_str IS INITIAL.
    gd_x_fileas_str  = pd_filename.
  ENDIF.
  CLEAR ld_recalc_filename.
  IF gd_x_fileas_str IS INITIAL OR gd_x_file_was_selected IS INITIAL.
*   Dateiabfrage falls noch keine Datei bekannt
    PERFORM f_hex_ext_sel_file_save USING space
      CHANGING gd_x_fileas_str pd_dir.
  ENDIF.
  IF pd_convert = 'P'.
*   Aktuell erfolgt der Extrakt von Ausschnittsdaten; auch bei eventuell
*   späterem File-Splitt eine Namensveränderung verhindern!
    gd_x_partaktiv = 'X'.
*   Eventuell muß Dateiname geprüft werden da zuvor Gesamtsicherung
*   eines Filesplitts stattfand!
    ld_recalc_filename = gd_x_recalc_filename.
  ENDIF.
  IF NOT gd_x_splitaktiv IS INITIAL.
    CLEAR gd_x_start_excel.  "Keine Excelanzeige da mehrere Dateien!
    IF gd_x_partaktiv IS INITIAL.
*     bei nicht aktiven Teilextrakt wird die Dateiendung eventuell um
*     weitere Nummer ergänzt
      ld_recalc_filename = 'X'.
    ENDIF.
  ENDIF.
  IF NOT ld_recalc_filename IS INITIAL.
*   Anpassungs-Prüfung für die Dateiendung => weitere Nummer ergänzen
    IF gd_x_fileas_org IS INITIAL.
      gd_x_fileas_org = gd_x_fileas_str.
    ENDIF.
    gd_x_fileas_str = gd_x_fileas_org.
    PERFORM f_hex_ext_file_exist_nr
      USING '00' 30 '~' space CHANGING gd_x_fileas_str.
*   Zurücksetzen des globalen Steuerungsparameters für Dateinamenprüfung
    CLEAR gd_x_recalc_filename.
  ENDIF.
  CHECK NOT gd_x_fileas_str IS INITIAL.
  PERFORM f_hex_ext_msgline USING 10 5 1 text-x04.

* Hinweise zum nachfolgenden Extrakt: Dieser läuft unter Aufteilung der
* Gesamtmenge um entdeckte Speicherungsprobleme zu verhindern.
* Auftrielung erfolgt in 10000'er Paketen der Extraktzeilen!
  ld_subrc = 1.
  WHILE ld_subrc > 0.
    FREE lt_tx_output.
    APPEND LINES OF gt_tx_output FROM 1 TO 10000 TO lt_tx_output.
    IF sy-tabix <= 0.
*     Alle Informationen sind extrahiert, Info setzen und Abbrechen
      ld_subrc = 0.
      FREE gt_tx_output.
      EXIT.
    ENDIF.
    DELETE gt_tx_output FROM 1 TO 10000.
*   Prüfung ob Datei bereits existiert, dann wird nachfolgend angehängt;
*   Hinweis: es zeigte sich dass in manchen Systemen Append auch bei
*   nicht existenter Datei funktionierte und in anderen nicht!
    PERFORM f_hex_ext_file_exist
      USING gd_x_fileas_str CHANGING ld_append.
    CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
        filename                = gd_x_fileas_str
        filetype                = 'ASC'
        append                  = ld_append
      TABLES
        data_tab                = lt_tx_output
      EXCEPTIONS
        file_write_error        = 1
        no_batch                = 2
        gui_refuse_filetransfer = 3
        invalid_type            = 4
        no_authority            = 5
        unknown_error           = 6
        header_not_allowed      = 7
        separator_not_allowed   = 8
        filesize_not_allowed    = 9
        header_too_long         = 10
        dp_error_create         = 11
        dp_error_send           = 12
        dp_error_write          = 13
        unknown_dp_error        = 14
        access_denied           = 15
        dp_out_of_memory        = 16
        disk_full               = 17
        dp_timeout              = 18
        file_not_found          = 19
        dataprovider_exception  = 20
        control_flush_error     = 21
        OTHERS                  = 22.
    IF sy-subrc <> 0.
*     Problemfall => Info setzen und Meldung, nachfolgend Abbrechen
      ld_subrc = -1.
      MESSAGE i646(db) WITH
        text-x05 gd_x_fileas_str space space.
      pd_file_error = sy-subrc.
    ENDIF.
  ENDWHILE.
  FREE: lt_tx_output.
  pd_file_error = ld_subrc.
  IF pd_convert = 'P'.
*   Aktuell erfolgt der Extrakt von Ausschnittsdaten => keine
*   Konvertierung ...
    EXIT.
  ENDIF.
  IF ld_subrc = 0.
    IF NOT pd_convert IS INITIAL.
*     Konvertierung HTM nach XLS durchführen
      CREATE OBJECT ld_o_excel 'EXCEL.APPLICATION'.
      macx_alv_down_err.
      CHECK sy-subrc = 0.
*     Mauspoiter aktiv setzen
      SET PROPERTY OF ld_o_excel 'DisplayAlerts' = 0.
      CALL METHOD OF ld_o_excel 'Workbooks' = ld_o_workbook.
      macx_alv_down_err.
*     Excel Vordergrund
      IF pd_showexcel = 'X'.
        SET PROPERTY OF ld_o_excel 'Cursor' = 2.            "#EC NOTEXT
        SET PROPERTY OF ld_o_excel 'VISIBLE' = 1.
*       aktuelle Windoweinstellung holen
        GET PROPERTY OF  ld_o_excel 'WindowState' = ld_o_winstate.
        IF ld_o_winstate-header = '-413' AND
           ld_o_winstate-type   = '7'.
          ld_max_win = 'X'.
        ENDIF.
*       Window Normal anzeigen; Maximize wäre -4137, Minimize -4140
        SET PROPERTY OF  ld_o_excel 'WindowState' = -4143.
      ELSE.
        SET PROPERTY OF ld_o_excel 'VISIBLE' = 0.
      ENDIF.
      gd_x_fileas_char = gd_x_fileas_str.
      CALL METHOD OF ld_o_workbook 'Open'
        EXPORTING #1 = gd_x_fileas_char.
      macx_alv_down_err.
      CALL METHOD OF ld_o_excel 'ActiveWorkbook' = ld_o_actbook.
      macx_alv_down_err.
      GET PROPERTY OF ld_o_excel 'ActiveWindow' = ld_o_window.
      macx_alv_down_err.
*     aktuelles Sheet ermitteln
      GET PROPERTY OF ld_o_excel 'ActiveSheet' = ld_o_wrksheet.
      macx_alv_down_err.
      IF NOT gd_x_zoom IS INITIAL.
*       Zoom im Excel auf Vorgabe setzen
        SET PROPERTY OF ld_o_window 'Zoom' = gd_x_zoom.     "#EC NOTEXT
        macx_alv_down_err.
      ENDIF.
      IF gd_x_fixz > 0 AND gd_x_fixsp > 0.
*       Fensterfixierung in Excel durchführen bei Zeile/Spalte
        PERFORM f_hex_ext_free_obj USING ld_o_range.
        PERFORM f_hex_ext_free_obj USING ld_o_cell1.
*       Passende Zelle markieren und danch Fixierung durchführen
        CALL METHOD OF ld_o_wrksheet 'Cells' = ld_o_cell1
          EXPORTING #1 = gd_x_fixz #2 = gd_x_fixsp.
        macx_alv_down_err.
        CALL METHOD OF ld_o_wrksheet 'Range' = ld_o_range
          EXPORTING #1 = ld_o_cell1 #2 = ld_o_cell1.
        macx_alv_down_err.
        CALL METHOD OF ld_o_range 'SELECT'.
        macx_alv_down_err.
        SET PROPERTY OF ld_o_window 'FreezePanes' = 1.
        macx_alv_down_err.
      ENDIF.
      IF gd_x_autof_znr > 0.
*       Autofilter realisieren bei gewünschter Zeile
        PERFORM f_hex_ext_free_obj USING ld_o_range.
        PERFORM f_hex_ext_free_obj USING ld_o_cell1.
        PERFORM f_hex_ext_free_obj USING ld_o_cell2.
        CALL METHOD OF ld_o_wrksheet 'Cells' = ld_o_cell1
          EXPORTING #1 = gd_x_autof_znr #2 = 1.
        CALL METHOD OF ld_o_wrksheet 'Cells' = ld_o_cell2
          EXPORTING #1 = gd_x_autof_znr #2 = 80.
        macx_alv_down_err.
        CALL METHOD OF ld_o_wrksheet 'Range' = ld_o_range
          EXPORTING #1 = ld_o_cell1 #2 = ld_o_cell2.
        macx_alv_down_err.
        CALL METHOD OF ld_o_range 'Autofilter'.
      ENDIF.
      IF gd_x_zeilenumbr = 0 OR gd_x_zeilenumbr = 1.
        PERFORM f_hex_ext_free_obj USING ld_o_range.
        PERFORM f_hex_ext_free_obj USING ld_o_cell1.
        PERFORM f_hex_ext_free_obj USING ld_o_cell2.
*       Range festlegen (Alles im Sheet) für Zeilenumbruch!
        CALL METHOD OF ld_o_wrksheet 'Cells' = ld_o_cell1
          EXPORTING #1 = 1 #2 = 1.
        macx_alv_down_err.
*       Zugriff aus Rangeobjekt herstellen mit obigem Zellenbezug
        CALL METHOD OF ld_o_wrksheet 'Range' = ld_o_range
          EXPORTING #1 = ld_o_cell1 #2 = ld_o_cell1.
        macx_alv_down_err.
*       Letze verwendete Zelle im Worksheet ermitteln über eine
*       Spezielmethode des Rangeobjektes
        CALL METHOD OF ld_o_range 'SpecialCells' = ld_o_cell2
          EXPORTING #1 = '11'.
        macx_alv_down_err.
*       Markierung (Range) über die 2 ermittelten Zellenobj. festlegen
        PERFORM f_hex_ext_free_obj USING ld_o_range.
        CALL METHOD OF ld_o_wrksheet 'Range' = ld_o_range
          EXPORTING #1 = ld_o_cell1 #2 = ld_o_cell2.
        macx_alv_down_err.
        SET PROPERTY OF ld_o_range 'WrapText' = gd_x_zeilenumbr.
        macx_alv_down_err.
        GET PROPERTY OF ld_o_range 'Columns' = ld_o_column. "#EC NOTEXT
        macx_alv_down_err.
        CALL METHOD OF ld_o_column 'AutoFit'.
        macx_alv_down_err.
      ENDIF.
      CALL METHOD OF ld_o_actbook 'SaveAs'
        EXPORTING #01 = gd_x_fileas_char #2 = 1.
      SET PROPERTY OF ld_o_excel 'Cursor' = -4143.          "#EC NOTEXT
      CALL METHOD OF ld_o_workbook 'CLOSE'.
      CALL METHOD OF ld_o_excel 'QUIT'.
      PERFORM f_hex_ext_free_obj USING ld_o_cell1.
      PERFORM f_hex_ext_free_obj USING ld_o_cell2.
      PERFORM f_hex_ext_free_obj USING ld_o_column.
      PERFORM f_hex_ext_free_obj USING ld_o_range.
      PERFORM f_hex_ext_free_obj USING ld_o_wrksheet.
      PERFORM f_hex_ext_free_obj USING ld_o_actbook.
      PERFORM f_hex_ext_free_obj USING ld_o_workbook.
      PERFORM f_hex_ext_free_obj USING ld_o_window.
      PERFORM f_hex_ext_free_obj USING ld_o_winstate.
      PERFORM f_hex_ext_free_obj USING ld_o_excel.
      IF NOT ld_max_win IS INITIAL.
*       Originalen Windowstatus wieder herstellen
*       =>Hier wird auf Maximierung geschaltet
        CREATE OBJECT ld_o_excel 'EXCEL.APPLICATION'.
        SET PROPERTY OF  ld_o_excel 'WindowState' = -4137.
        CALL METHOD OF ld_o_excel 'QUIT'.
        PERFORM f_hex_ext_free_obj USING ld_o_winstate.
        PERFORM f_hex_ext_free_obj USING ld_o_excel.
      ENDIF.
    ENDIF.
    IF gd_x_start_excel = 'X' AND NOT gd_x_fileas_str IS INITIAL.
      IF gd_x_info = 'X' AND pd_convert IS INITIAL.
        PERFORM f_hex_ext_info CHANGING ld_goon.
        CHECK  ld_goon = 'X'.
      ENDIF.
      CONCATENATE '"' gd_x_fileas_str '"'
              INTO ld_fileas_c.
*     Excel starten und Datei anzeigen
      CALL FUNCTION 'WS_EXECUTE'
        EXPORTING
          document       = 'X'
          commandline    = ld_fileas_c
          program        = 'EXCEL'
        EXCEPTIONS
          frontend_error = 1
          OTHERS         = 6.
      IF sy-subrc <> 0.
        CLEAR ld_fileas_c.
      ENDIF.
    ENDIF.
  ENDIF.
ENDFORM.                    "f_hex_ext_down_file