ABAP:ALV导出EXCEL文件

转载

mob604756ffc089 2021-07-16 10:27:00

DATA: lo_excel          TYPE REF TO zcl_excel,
      lo_worksheet      TYPE REF TO zcl_excel_worksheet,
      lo_alv            TYPE REF TO cl_gui_alv_grid,
      lo_salv           TYPE REF TO cl_salv_table,
*      gr_events         TYPE REF TO lcl_handle_events,
      lr_events         TYPE REF TO cl_salv_events_table,
      gt_sbook          TYPE TABLE OF sbook,
      gt_listheader     TYPE slis_t_listheader,
      wa_listheader     LIKE LINE OF gt_listheader.
* create zcl_excel_worksheet object
DATA: l_path            TYPE string,  " local dir
      lv_workdir        TYPE string,
      lv_file_separator TYPE c,
      GO_ALV      TYPE REF TO CL_GUI_ALV_GRID.

"为ALV注册CALLER_EXIT事件
*&---------------------------------------------------------------------*
*&      Form  FRM_CALLER_EXIT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM FRM_CALLER_EXIT USING IR_GRID TYPE SLIS_DATA_CALLER_EXIT.

  CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
    IMPORTING
      E_GRID = GO_ALV.

  CALL METHOD GO_ALV->CHECK_CHANGED_DATA.
  CALL METHOD GO_ALV->REGISTER_EDIT_EVENT     "注册GRID事件
    EXPORTING
      I_EVENT_ID = CL_GUI_ALV_GRID=>MC_EVT_MODIFIED "事件：回车
    EXCEPTIONS
      ERROR      = 1
      OTHERS     = 2.

  CREATE OBJECT GO_REC.
  SET HANDLER GO_REC->HANDLE_DATA_CHANGED FOR GO_ALV.

ENDFORM.                    "FRM_CALLER_EXIT

导出EXCEL

PERFORM FRM_SAVE_EXCEL USING '测试数据'.

*&---------------------------------------------------------------------*
*&      Form  FRM_SAVE_EXCEL
*&---------------------------------------------------------------------*
*       下载EXCEL
*----------------------------------------------------------------------*
FORM FRM_SAVE_EXCEL USING P_PATH TYPE STRING.
  DATA:
    LV_FILENAME    TYPE STRING,
    LV_PATH        TYPE STRING,
    LV_FULLPATH    TYPE STRING,
    LV_DESTINATION LIKE RLGRAP-FILENAME,
    LS_OBJDATA     LIKE WWWDATATAB,
    LV_OBJID       TYPE WWWDATATAB-OBJID,
    LV_RC          TYPE SY-SUBRC,
    LV_TABIX       TYPE I.
  CONCATENATE P_PATH
               '_'
              SY-DATUM
               '_'
              SY-UZEIT
     INTO LV_FILENAME.

********************************选取存储地点***************************
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
    EXPORTING
      DEFAULT_EXTENSION    = 'XLSX'
      DEFAULT_FILE_NAME    = LV_FILENAME
      INITIAL_DIRECTORY    = LV_PATH
    CHANGING
      FILENAME             = LV_FILENAME
      PATH                 = LV_PATH
      FULLPATH             = L_PATH
    EXCEPTIONS
      CNTL_ERROR           = 1
      ERROR_NO_GUI         = 2
      NOT_SUPPORTED_BY_GUI = 3
      OTHERS               = 4.

*"excel表头设置
*  APPEND 'title-A' TO gt_hd.
*  APPEND 'title-B' TO gt_hd.
*
*  CALL FUNCTION 'GUI_DOWNLOAD'
*    EXPORTING
*      filename              = lv_fullpath
*      filetype              = 'ASC'
*      write_field_separator = 'X'
*    TABLES
*      data_tab              = gt_download
*      fieldnames            = gt_hd.

*  LV_DEFAULT_FILE_NAME = P_PATH.
*  CL_GUI_FRONTEND_SERVICES=>GET_SAPGUI_WORKDIR( CHANGING SAPWORKDIR = L_PATH ).
*  CL_GUI_CFW=>FLUSH( ).
*  CL_GUI_FRONTEND_SERVICES=>DIRECTORY_BROWSE(
*    EXPORTING INITIAL_FOLDER = L_PATH
*    CHANGING SELECTED_FOLDER = L_PATH ).
*
*  IF L_PATH IS INITIAL.
*    CL_GUI_FRONTEND_SERVICES=>GET_SAPGUI_WORKDIR(
*      CHANGING SAPWORKDIR = LV_WORKDIR ).
*    L_PATH = LV_WORKDIR.
*  ENDIF.
*
*  CL_GUI_FRONTEND_SERVICES=>GET_FILE_SEPARATOR(
*    CHANGING FILE_SEPARATOR = LV_FILE_SEPARATOR ).
*
*  CONCATENATE L_PATH LV_FILE_SEPARATOR LV_DEFAULT_FILE_NAME
*              INTO L_PATH.
  IF L_PATH IS NOT INITIAL.
    PERFORM EXPORT_TO_EXCEL.
  ENDIF.

ENDFORM.                    " FRM_SAVE_EXCEL

*--------------------------------------------------------------------*
* FORM EXPORT_TO_EXCEL
*--------------------------------------------------------------------*
* This subroutine is principal demo session
*--------------------------------------------------------------------*
FORM EXPORT_TO_EXCEL.

  CREATE OBJECT LO_EXCEL.
  LO_WORKSHEET = LO_EXCEL->GET_ACTIVE_WORKSHEET( ).

* get ALV object from screen

*  CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
*    IMPORTING
*      e_grid = GO_ALV.

* build list header

  WA_LISTHEADER-TYP = 'H'.
  WA_LISTHEADER-INFO = SY-TITLE.
  APPEND WA_LISTHEADER TO GT_LISTHEADER.

  WA_LISTHEADER-TYP = 'S'.
  WA_LISTHEADER-INFO =  'Created by: ABAP2XLSX Group'.
  APPEND WA_LISTHEADER TO GT_LISTHEADER.

  WA_LISTHEADER-TYP = 'A'.
  WA_LISTHEADER-INFO =
      'Project hosting at https://cw.sdn.sap.com/cw/groups/abap2xlsx'.
  APPEND WA_LISTHEADER TO GT_LISTHEADER.

* write to excel using method Bin_ALV

  LO_WORKSHEET->BIND_ALV_OLE2(
    EXPORTING
*      I_DOCUMENT_URL          = SPACE " excel template
*      I_XLS                   = 'X' " create in xls format?
      I_SAVE_PATH             = L_PATH
      IO_ALV                  = GO_ALV
    IT_LISTHEADER           = GT_LISTHEADER
      I_TOP                   = 2
      I_LEFT                  = 1
*      I_COLUMNS_HEADER        = 'X'
*      I_COLUMNS_AUTOFIT       = 'X'
*      I_FORMAT_COL_HEADER     =
*      I_FORMAT_SUBTOTAL       =
*      I_FORMAT_TOTAL          =
    EXCEPTIONS
      MISS_GUIDE              = 1
      EX_TRANSFER_KKBLO_ERROR = 2
      FATAL_ERROR             = 3
      INV_DATA_RANGE          = 4
      DIM_MISMATCH_VKEY       = 5
      DIM_MISMATCH_SEMA       = 6
      ERROR_IN_SEMA           = 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.

ENDFORM.                    "EXPORT_TO_EXCEL