*---------------------------------------------------------------------*
* Report  ZVEXCELOLE_TMP
* Author: Matthias Leitner
*---------------------------------------------------------------------*

REPORT zvexcelole_tmp NO STANDARD PAGE HEADING LINE-SIZE 132.

* ------------------------------------------------------------------- *
* datendeklaration                                                    *
* ------------------------------------------------------------------- *

DATA: it_filetable TYPE filetable.
DATA: wa_filetable TYPE file_table.

DATA: wa_filename     TYPE string,
      wa_upload       TYPE string,
      wa_download     TYPE string,
      wa_file(255)    TYPE c,
      wa_rc           TYPE i.

*** OLE-Definitionen
INCLUDE ole2incl.

DATA: wa_loesch type i,
      o_excel         TYPE ole2_object,
      o_workbook      TYPE ole2_object,
      o_columns       TYPE ole2_object,
      o_autofit       TYPE ole2_object,
      o_blatt         TYPE ole2_object.

* ------------------------------------------------------------------- *
* selection-screen                                                    *
* ------------------------------------------------------------------- *

SELECTION-SCREEN BEGIN OF BLOCK z3 WITH FRAME.
PARAMETERS: pa_dir(255) TYPE c LOWER CASE .
SELECTION-SCREEN END OF BLOCK z3.

* ------------------------------------------------------------------- *
* initialization                                                      *
* ------------------------------------------------------------------- *

INITIALIZATION.

* standardmässiges download-verzeichnis holen
  CALL METHOD cl_gui_frontend_services=>get_upload_download_path
    CHANGING
      upload_path                 = wa_upload
      download_path               = wa_download
    EXCEPTIONS
      cntl_error                  = 1
      error_no_gui                = 2
      not_supported_by_gui        = 3
      gui_upload_download_path    = 4
      upload_download_path_failed = 5
      OTHERS                      = 6.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ELSE.
    MOVE wa_download TO pa_dir.
  ENDIF.

* ------------------------------------------------------------------- *
* at selection-screen                                                 *
* ------------------------------------------------------------------- *

AT SELECTION-SCREEN.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_dir.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
*  EXPORTING
*    WINDOW_TITLE            =
*    DEFAULT_EXTENSION       =
*    DEFAULT_FILENAME        =
*    FILE_FILTER             =
*    INITIAL_DIRECTORY       =
*    MULTISELECTION          =
*    WITH_ENCODING           =
    CHANGING
      file_table              = it_filetable
      rc                      = wa_rc
*    USER_ACTION             =
*    FILE_ENCODING           =
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5
          .
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ELSE.
    READ TABLE it_filetable INTO wa_filetable INDEX 1.
    MOVE wa_filetable-filename TO pa_dir.
  ENDIF.

* ------------------------------------------------------------------- *
* start-of-selection                                                  *
* ------------------------------------------------------------------- *

START-OF-SELECTION.

  PERFORM excel_format.

  MOVE pa_dir TO wa_file.
  FORMAT HOTSPOT ON.
  WRITE: wa_file+0(132).
  FORMAT HOTSPOT OFF.
  HIDE wa_file.
  CLEAR wa_file.

* ------------------------------------------------------------------- *
* at line-selection                                                   *
* ------------------------------------------------------------------- *

AT LINE-SELECTION.

  CLEAR wa_file.
  READ CURRENT LINE.

  IF NOT wa_file IS INITIAL.
    MOVE wa_file TO wa_filename.
    CALL METHOD cl_gui_frontend_services=>execute
      EXPORTING
        document               = wa_filename
*      application            =
*      PARAMETER              =
*      DEFAULT_DIRECTORY      =
*      MAXIMIZED              =
*      MINIMIZED              =
*      SYNCHRONOUS            =
      EXCEPTIONS
        cntl_error             = 1
        error_no_gui           = 2
        bad_parameter          = 3
        file_not_found         = 4
        path_not_found         = 5
        file_extension_unknown = 6
        error_execute_failed   = 7
        OTHERS                 = 8
            .
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.
  ENDIF.

* ------------------------------------------------------------------- *
* form excel_format                                                   *
* ------------------------------------------------------------------- *
FORM excel_format.

* start excel
  CREATE OBJECT o_excel 'EXCEL.APPLICATION'.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. EXCEL.APPLICATION'.
  ENDIF.

* excel -> not visible
  SET PROPERTY OF o_excel 'Visible' = 0.                   "visible = 1
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. Visible'.
  ENDIF.

* Arbeitsblatt auswählen
  CALL METHOD OF o_excel 'Workbooks' = o_workbook.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. Workbooks'.
  ENDIF.

* open existing file
  CALL METHOD OF o_workbook 'OPEN'
    EXPORTING
    #1 = pa_dir.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. OPEN'.
  ENDIF.

* SUBTOTALS!!!

* bei allen spalten auf optimale breite setzen
  CALL METHOD OF o_excel 'Columns' = o_columns.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. Columns'.
  ENDIF.

  CALL METHOD OF o_columns 'Autofit' = o_autofit.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. Autofit'.
  ENDIF.

* save and close excel
  CALL METHOD OF o_excel 'Worksheets' = o_blatt
    EXPORTING
    #1 = 1.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. Worksheets'.
  ENDIF.

  CALL METHOD OF o_blatt 'SaveAs'
    EXPORTING
    #1 = pa_dir
    #2 = 1.                "fileFormat
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. SaveAs'.
  ELSE.

  ENDIF.

  CALL METHOD OF o_workbook 'CLOSE'.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. CLOSE'.
  ENDIF.

  CALL METHOD OF o_excel 'QUIT'.
  IF sy-subrc NE 0.
    WRITE: / 'OLE-Fehler. QUIT'.
  ENDIF.

  FREE o_excel.

ENDFORM.                                             "FORM excel_format