内表下载至excel

*&---------------------------------------------------------------------*
*& Report ZDMO_ALV_EXCEL
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zdmo_alv_excel.
TYPE-POOLS:ole2.


DATA:BEGIN OF gs_alv,
       matnr TYPE makt-matnr,
       maktx TYPE makt-maktx,
     END OF gs_alv.

DATA gt_alv LIKE TABLE OF gs_alv.
DATA:BEGIN OF gs_excel,
       matnr TYPE string,
       maktx TYPE string,
     END OF gs_excel.

DATA gt_excel LIKE TABLE OF gs_excel.
DATA gt_excel1 LIKE TABLE OF gs_excel.

TABLES: sscrfields.

DATA: go_excel   TYPE ole2_object,
      go_book    TYPE ole2_object,
      go_sheet   TYPE ole2_object,
      go_range   TYPE ole2_object,
      go_font    TYPE ole2_object,

      go_row     TYPE ole2_object,
      go_borders TYPE ole2_object,

      gv_visible TYPE i VALUE 0.           "0####1##
DATA: gv_file_name LIKE rlgrap-filename, "##############
      gv_file(255) TYPE c.                "Char##excel####
DATA: gv_doc_object_id(20) TYPE c, "####
      gv_doc_size          TYPE i,
      gv_doc_format(100)   TYPE c,
      gv_doc_type(100)     VALUE soi_doctype_word97_document,
      gt_doc_table         LIKE w3mime OCCURS 0.
DATA: gv_rc     TYPE i,
      gt_str_tb TYPE TABLE OF char1024 WITH HEADER LINE.

*&--------------------------------------------------------------------*
*&  声明ALV参数
*&--------------------------------------------------------------------*
DATA:gt_fieldcat TYPE lvc_t_fcat,
     gs_fieldcat TYPE lvc_s_fcat,
     gs_layout   TYPE lvc_s_layo.


SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE t0.
PARAMETERS:p_row TYPE i OBLIGATORY.

SELECTION-SCREEN END OF BLOCK blk.

INITIALIZATION.

START-OF-SELECTION.
  PERFORM frm_get_data.
  PERFORM frm_set_layout.
  PERFORM frm_set_fieldcat.
  PERFORM frm_diaplay_data.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form FRM_GET_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_data.
  SELECT
    matnr
    maktx
    FROM makt
    UP TO p_row ROWS
    INTO TABLE gt_alv.

ENDFORM.

*&--------------------------------------------------------------------*
*&  FRM_SET_LAYOUT
*&--------------------------------------------------------------------*
FORM frm_set_layout.
  gs_layout-sel_mode = 'A'.
  gs_layout-cwidth_opt = 'X'.
*  GS_LAYOUT-ZEBRA = 'X'.
ENDFORM.

FORM frm_set_fieldcat.

  CLEAR:gs_fieldcat.

  DEFINE fieldcatset.
    gs_fieldcat-fieldname = &1.
    gs_fieldcat-scrtext_l = &2.
    APPEND gs_fieldcat TO gt_fieldcat.
    CLEAR:gs_fieldcat.
  END-OF-DEFINITION.


  fieldcatset: 'MATNR' '物料编号',
               'MAKTX' '物料说明'.

ENDFORM.

*&--------------------------------------------------------------------*
*&  FRM_DISPLAY_ALV
*&--------------------------------------------------------------------*
FORM frm_diaplay_data.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
*     I_INTERFACE_CHECK        = ' '
*     I_BYPASSING_BUFFER       =
*     I_BUFFER_ACTIVE          =
      i_callback_program       = sy-repid
      i_callback_pf_status_set = 'FRM_SET_PF_STATUS'
      i_callback_user_command  = 'FRM_USER_COMMAND'
*     I_CALLBACK_TOP_OF_PAGE   = ' '
*     I_CALLBACK_HTML_TOP_OF_PAGE       = ' '
*     I_CALLBACK_HTML_END_OF_LIST       = ' '
*     I_STRUCTURE_NAME         =
*     I_BACKGROUND_ID          = ' '
*     I_GRID_TITLE             =
*     I_GRID_SETTINGS          =
      is_layout_lvc            = gs_layout
      it_fieldcat_lvc          = gt_fieldcat
*     IT_EXCLUDING             =
*     IT_SPECIAL_GROUPS_LVC    =
*     IT_SORT_LVC              =
*     IT_FILTER_LVC            =
*     IT_HYPERLINK             =
*     IS_SEL_HIDE              =
*     I_DEFAULT                = 'X'
      i_save                   = 'X'
*     IS_VARIANT               =
*     IT_EVENTS                =
*     IT_EVENT_EXIT            =
*     IS_PRINT_LVC             =
*     IS_REPREP_ID_LVC         =
*     I_SCREEN_START_COLUMN    = 0
*     I_SCREEN_START_LINE      = 0
*     I_SCREEN_END_COLUMN      = 0
*     I_SCREEN_END_LINE        = 0
*     I_HTML_HEIGHT_TOP        =
*     I_HTML_HEIGHT_END        =
*     IT_ALV_GRAPHICS          =
*     IT_EXCEPT_QINFO_LVC      =
*     IR_SALV_FULLSCREEN_ADAPTER        =
*   IMPORTING
*     E_EXIT_CAUSED_BY_CALLER  =
*     ES_EXIT_CAUSED_BY_USER   =
    TABLES
      t_outtab                 = gt_alv
    EXCEPTIONS
      program_error            = 1
      OTHERS                   = 2.

ENDFORM.

FORM frm_set_pf_status USING pt_extab TYPE slis_t_extab.
  SET PF-STATUS 'ZSTANDARD' .
ENDFORM.                   "_SET_PF_STATUS


FORM frm_user_command USING pv_ucomm LIKE sy-ucomm
                             ps_selfield TYPE slis_selfield.
  DATA: lv_grid  TYPE REF TO cl_gui_alv_grid.
  DATA: lv_flag  TYPE c.
  DATA lt_alv LIKE TABLE OF gs_alv.

*&将变更的数据刷新
  CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
    IMPORTING
      e_grid = lv_grid.
  CALL METHOD lv_grid->check_changed_data.
  ps_selfield-refresh = 'X'.
  CASE pv_ucomm.
    WHEN 'PRINT'.
      PERFORM f_download_data.
  ENDCASE.

  CALL METHOD lv_grid->refresh_table_display.
ENDFORM.

*&---------------------------------------------------------------------*
*& FORM f_download_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*


FORM f_download_data .

  MOVE-CORRESPONDING gt_alv TO gt_excel1.
  DO 50 TIMES.
    APPEND LINES OF gt_excel1 TO gt_excel.
  ENDDO.


  DATA: lv_filename LIKE rlgrap-filename VALUE 'default name'.
  DATA: lv_line TYPE i.

  lv_filename = '物料' && sy-datum && sy-uzeit.

  gv_doc_object_id = 'Z20144_TEST01'.

  PERFORM f_ole_filename_get USING lv_filename gv_file_name.
  PERFORM f_ole_download_template USING gv_file_name.

  CREATE OBJECT go_excel 'EXCEL.APPLICATION'.

  CALL METHOD OF
    go_excel
      'Workbooks' = go_book.
  CALL METHOD OF
    go_book
    'OPEN'
    EXPORTING
      #1 = gv_file.

*****************************

  CALL METHOD OF
       go_excel
       'WORKSHEETS' = go_sheet
     EXPORTING
       #1           = 'Sheet1'.
  CALL METHOD OF
    go_sheet
    'ACTIVATE'.
***************HEADER

  DATA:lv_index        TYPE sy-tabix,
       lv_border_start TYPE char20,
       lv_border_end   TYPE char20.

  lv_index = p_row + 1.
  DATA:
    col         TYPE string,
    col_str     TYPE string,
    col_str_wei TYPE string.
  .
  DO 88 TIMES.
    col = sy-index.
    IF sy-index = 1.
      col_str = col.
    ELSE.
      CONCATENATE col_str ',' col INTO col_str.
    ENDIF.
  ENDDO.

  PERFORM f_ole_export_table_cell TABLES gt_excel USING gt_excel 2 1
        col_str.


  lv_border_start = 'A2'.
  lv_border_end = 'B' && lv_index.


  PERFORM f_range_borders USING lv_border_start lv_border_end.

  gv_visible = 1.
  SET PROPERTY OF go_excel 'Visible' = gv_visible.
  SET PROPERTY OF go_excel 'DisplayAlerts' = 0.

  GET PROPERTY  OF  go_excel  'ACTIVEWORKBOOK'   =  go_book.
  GET PROPERTY OF go_excel 'ACTIVESHEET' = go_sheet.

  CALL METHOD OF
    go_sheet
    'SAVEAS'
    EXPORTING
      #1 = gv_file_name
      #2 = 1.

  CALL METHOD OF
    go_sheet
    'CLOSE'.
  CALL METHOD OF
    go_excel
    'QUIT'.

  FREE OBJECT go_excel.
  FREE OBJECT go_book.
  FREE OBJECT go_sheet.

ENDFORM.


*&---------------------------------------------------------------------*
*&      FORM  f_filename_get
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->i_filename text
*      -->e_filename text
*----------------------------------------------------------------------*
FORM f_ole_filename_get USING i_filename LIKE rlgrap-filename
                          e_filename LIKE rlgrap-filename.

  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_filename     = i_filename
      mask             = ',EXCEL(*.xlsx),*.xls,'
      mode             = 'S'                      " S###O##
      title            = ''
    IMPORTING
      filename         = e_filename
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.
ENDFORM. "f_filename_get

FORM f_ole_download_template USING i_filename LIKE rlgrap-filename.

  DATA: lv_fcheck TYPE c,        "#####
        lv_file   TYPE string,
        lv_return TYPE c.

  gv_file = i_filename.
  lv_file = i_filename.

  lv_fcheck = cl_gui_frontend_services=>file_exist( lv_file ).

  IF lv_fcheck IS NOT INITIAL.
    CALL FUNCTION 'TB_LIMIT_WS_FILE_DELETE'
      EXPORTING
        filename           = lv_file
      IMPORTING
        return             = lv_return
      EXCEPTIONS
        file_delete_failed = 1
        cntl_error         = 2
        error_no_gui       = 3
        file_not_found     = 4
        access_denied      = 5
        unknown_error      = 6
        OTHERS             = 7.
    IF sy-subrc <> 0.
      MESSAGE '######################' TYPE 'S'.
      EXIT.
    ENDIF.
  ENDIF.  " is file exist

  IF gv_file IS NOT INITIAL.
    PERFORM f_ole_download_doc.
  ENDIF.

ENDFORM. "f_download_template

*&---------------------------------------------------------------------*
*&      FORM  f_download_doc
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*


FORM f_ole_download_doc.

  DATA: lv_filename TYPE string.

  CALL FUNCTION 'SAP_OI_LOAD_MIME_DATA'
    EXPORTING
      object_id        = gv_doc_object_id
    IMPORTING
      data_size        = gv_doc_size
      document_format  = gv_doc_format
      document_type    = gv_doc_type
    TABLES
      data_table       = gt_doc_table
    EXCEPTIONS
      object_not_found = 1
      internal_error   = 2
      OTHERS           = 3.
  IF sy-subrc NE 0.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
         WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

  MOVE gv_file TO lv_filename.

  IF gv_doc_size NE 0.
    CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
        bin_filesize            = gv_doc_size
        filename                = lv_filename
        filetype                = 'BIN'
      TABLES
        data_tab                = gt_doc_table
      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.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
      WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.
  ELSE.
    MESSAGE 'No document selected' TYPE 'E'.
  ENDIF.

ENDFORM. " f_download_doc

*&---------------------------------------------------------------------*
*&      FORM  f_export_table_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->p_data         text
*      -->p_row          text
*      -->p_col          text
*      -->p_exclude_col  text
*----------------------------------------------------------------------*


FORM f_ole_export_table_cell TABLES p_table USING p_data p_row p_col p_exclude_col.

  DATA: lv_rc              TYPE i,
        lt_str_tb          TYPE TABLE OF char1024 WITH HEADER LINE,
        lv_str             TYPE string,
        lv_comp_count      TYPE i,
        lv_comp_type       TYPE c,
        lt_exclude_col     TYPE TABLE OF i WITH HEADER LINE,
        lt_exclude_col_str TYPE TABLE OF char10 WITH HEADER LINE.

  DATA:dy_table TYPE REF TO data.
  DATA:ls_line TYPE REF TO data.

  FIELD-SYMBOLS: <fs_table> TYPE ANY TABLE.
  FIELD-SYMBOLS: <fs_wa> TYPE any.
  FIELD-SYMBOLS: <fs_field> TYPE any.


  ASSIGN p_data TO <fs_table>.

  CREATE DATA ls_line LIKE LINE OF <fs_table>.

  ASSIGN ls_line->* TO <fs_wa>.

  DATA:lv_fs_field TYPE char30.


  DESCRIBE FIELD p_table TYPE lv_comp_type COMPONENTS lv_comp_count.

  IF p_exclude_col IS NOT INITIAL.
    SPLIT p_exclude_col AT ',' INTO TABLE lt_exclude_col_str.
    LOOP AT lt_exclude_col_str.
      MOVE lt_exclude_col_str TO lt_exclude_col.
      APPEND lt_exclude_col.
    ENDLOOP.
  ENDIF.

  LOOP AT <fs_table> ASSIGNING <fs_wa> .
    DO lv_comp_count TIMES.
      READ TABLE lt_exclude_col WITH KEY table_line = sy-index.
      IF sy-subrc NE 0.
        CONTINUE.
      ENDIF.
      ASSIGN COMPONENT sy-index OF STRUCTURE <fs_wa> TO <fs_field>.
      IF sy-subrc EQ 0.

        IF <fs_field> IS NOT INITIAL.
          MOVE <fs_field> TO lv_str.
        ELSE.
          MOVE space TO lv_str.
        ENDIF.
        IF sy-index NE lv_comp_count.
          CONCATENATE lv_str cl_abap_char_utilities=>horizontal_tab
                      INTO lv_str.
        ENDIF.
        CONCATENATE lt_str_tb lv_str INTO lt_str_tb.

      ENDIF.
    ENDDO.

    APPEND lt_str_tb.
    CLEAR lt_str_tb.
  ENDLOOP.

  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data       = lt_str_tb[]
    CHANGING
      rc         = lv_rc
    EXCEPTIONS
      cntl_error = 1
      OTHERS     = 4.

  CALL METHOD OF
      go_excel
      'Cells'  = go_range
    EXPORTING
      #1       = p_row
      #2       = p_col.

  CALL METHOD OF
    go_range
    'Select'.

  CALL METHOD OF
    go_sheet
    'Paste'.

ENDFORM. "_export_table_cell

FORM fm_fill_cell USING p_row p_col p_value .
  CLEAR:gt_str_tb[].
  gt_str_tb = p_value.
  APPEND gt_str_tb.

  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data = gt_str_tb[]
    CHANGING
      rc   = gv_rc.
  CALL METHOD OF
      go_excel
      'CELLS' = go_range
    EXPORTING
      #1      = p_row
      #2      = p_col.
  CALL METHOD OF
    go_range
    'Select'.
  CALL METHOD OF
    go_sheet
    'Paste'.
  SET PROPERTY OF go_excel 'VALUE' = p_value.
*  CLEAR go_excel.
ENDFORM.

FORM f_range_borders USING p_start
                           p_end.
  CALL METHOD OF go_excel 'Range' = go_range
    EXPORTING
      #1 = p_start
      #2 = p_end.
  CALL METHOD OF go_range 'Borders' = go_borders.
  SET PROPERTY OF go_borders 'LINESTYLE' = 1.
  SET PROPERTY OF go_borders 'WEIGHT' = 10.
ENDFORM.