Interne Tabelle als xls Datei per E-Mail verschicken

Code: Alles auswählen.

REPORT  y_alv_excel.

TABLES: vbak.

*------------------------------*
* Definitionen + Deklarationen *
*------------------------------*

INCLUDE y_alv_excel_top.

*------------------*
* Selektion-Screen *
*------------------*

SELECTION-SCREEN: BEGIN OF BLOCK param WITH FRAME TITLE text-001.
  SELECT-OPTIONS: s_erdat FOR vbak-erdat.
SELECTION-SCREEN: END OF BLOCK param.

SELECTION-SCREEN: BEGIN OF BLOCK limit WITH FRAME TITLE text-002.
PARAMETERS: p_limit TYPE p_dbacc DEFAULT 2500.
SELECTION-SCREEN: END OF BLOCK limit.

SELECTION-SCREEN: BEGIN OF BLOCK form WITH FRAME TITLE text-003.
PARAMETERS: rb_excel RADIOBUTTON GROUP typ,
            rb_list  RADIOBUTTON GROUP typ DEFAULT 'X'.
SELECTION-SCREEN: END OF BLOCK form.

*----------------*
* Datenselektion *
*----------------*

CLEAR gt_vbelege.

SELECT vbeln erdat
INTO CORRESPONDING FIELDS OF TABLE gt_vbelege
FROM vbak
UP TO p_limit ROWS
WHERE erdat IN s_erdat
ORDER BY erdat vbeln.

IF sy-subrc = 0.

* Wahl des Ausgabemediums
  CASE 'X'.
    WHEN rb_excel.
      PERFORM excel_download.
    WHEN rb_list.
      PERFORM display_alv.
  ENDCASE.

ENDIF.

Code: Alles auswählen.

*-------*
* TYPES *
*-------*

TYPES: BEGIN OF ty_vbelege,
  vbeln TYPE vbeln_va,
  erdat	TYPE erdat,
END OF ty_vbelege.

TYPES: BEGIN OF ty_vbelege_excel,
  vbeln TYPE vbeln,
  erdat TYPE string,
END OF ty_vbelege_excel.

*---------*
* Klassen *
*---------*

* Eventhandler definieren
CLASS lcl_handle_events DEFINITION.
  PUBLIC SECTION.
  METHODS:

  on_link_click
  FOR EVENT link_click OF cl_salv_events_table
  IMPORTING row column.

ENDCLASS.                    "lcl_handle_events DEFINITION

*------*
* DATA *
*------*

* Datentabellen
DATA: gt_vbelege TYPE STANDARD TABLE OF ty_vbelege,
      gt_vbelege_excel TYPE STANDARD TABLE OF ty_vbelege_excel.

* Workareas
DATA: wa_vbelege TYPE ty_vbelege,
      wa_vbelege_excel TYPE ty_vbelege_excel.

* Hilfsvariablen
DATA: h_datum     TYPE  string.            " zum formattieren der Datümer

* Excel Download
DATA: h_offset   TYPE I,
      h_path     TYPE string,
      h_fullpath TYPE string,
      h_filename TYPE string,
      h_file     TYPE rlgrap-filename,
      h_filepath TYPE rlgrap-filename.

DATA: BEGIN OF t_fieldnames OCCURS 0,
  fnames(195),
END OF t_fieldnames.

DEFINE append_fname.
  CLEAR t_fieldnames.
  t_fieldnames = &1.
  APPEND t_fieldnames.
  CLEAR t_fieldnames.
END-OF-DEFINITION.

* alv Listausgabe
DATA: go_alv              TYPE REF TO  cl_salv_table,
      go_column           TYPE REF TO  cl_salv_column_table,
      go_columns          TYPE REF TO  cl_salv_columns_table,
      go_functions        TYPE REF TO  cl_salv_functions_list,      " toolbar
      go_display          TYPE REF TO  cl_salv_display_settings,
      go_layout           TYPE REF TO  cl_salv_layout,
      go_selections       TYPE REF TO  cl_salv_selections,          " multiselect
      go_sort             TYPE REF TO  cl_salv_sorts,               " von Anfang an sortiert ausgeben
      go_events           TYPE REF TO  cl_salv_events_table,        " Events
      go_event_handler    TYPE REF TO  lcl_handle_events,
      gx_salv_msg         TYPE REF TO  cx_salv_msg,                 " exceptions
      gx_salv_not_found   TYPE REF TO  cx_salv_not_found,
      gx_salv_existing    TYPE REF TO  cx_salv_existing,
      gx_salv_data_error  TYPE REF TO  cx_salv_data_error,
      gs_layoutkey        TYPE         salv_s_layout_key.

Code: Alles auswählen.

FORM excel_download.

* Excel-Ausgabetabelle füllen


  LOOP AT gt_vbelege INTO wa_vbelege.

    wa_vbelege_excel-bukrs = wa_vbelege-bukrs.
    wa_vbelege_excel-ekorg = wa_vbelege-ekorg.

*   Datümer formattieren
    IF wa_vbelege-erdat IS NOT INITIAL.
      CONCATENATE wa_vbelege-erdat+6(2) '.' wa_vbelege-erdat+4(2) '.' wa_vbelege-erdat(4) INTO h_datum.
      wa_vbelege_excel-erdatr = h_datum.
      CLEAR h_datum.
    ENDIF.

    ENDIF.

    APPEND wa_vbelege_excel TO gt_vbelege_excel.

  ENDLOOP.

* Header hinzufügen
  append_fname 'Vertriebsbeleg'.
  append_fname 'Datum'.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title = 'Speichern'
      file_filter  = cl_gui_frontend_services=>filetype_excel
    CHANGING
      filename     = h_filename
      path         = h_path
      fullpath     = h_fullpath.

* Dateiendung abschneiden
  FIND ALL OCCURRENCES OF '.' IN h_fullpath MATCH OFFSET h_offset.
  h_filepath = h_fullpath+0(h_offset).
  CLEAR h_offset.
  FIND ALL OCCURRENCES OF '.' IN h_filename MATCH OFFSET h_offset.
  h_file = h_filename+0(h_offset).

  CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'
    EXPORTING
      file_name                 = h_filepath
      data_sheet_name           = h_file
      password_option           = 0
    TABLES
      data_tab                  = gt_vbelege_excel
      fieldnames                = t_fieldnames
    EXCEPTIONS
      file_not_exist            = 1
      filename_expected         = 2
      communication_error       = 3
      ole_object_method_error   = 4
      ole_object_property_error = 5
      invalid_filename          = 6
      invalid_pivot_fields      = 7
      download_problem          = 8
      OTHERS                    = 9.

  CASE sy-subrc.
    WHEN 1.
      MESSAGE 'File does not exist' TYPE 'E'.
    WHEN 2.
      MESSAGE 'Filename expected' TYPE 'E'.
    WHEN 3.
      MESSAGE 'Communication error' TYPE 'E'.
    WHEN 4.
      MESSAGE 'OLE object method error' TYPE 'E'.
    WHEN 5.
      MESSAGE 'OLE object property error' TYPE 'E'.
    WHEN 6.
      MESSAGE 'Invalid filename' TYPE 'E'.
    WHEN 7.
      MESSAGE 'Invalid pivot fields' TYPE 'E'.
    WHEN 8.
      MESSAGE 'Download problem' TYPE 'E'.
    WHEN 9.
      MESSAGE 'Other problem' TYPE 'E'.
  ENDCASE.

ENDFORM.