Functional ALV系列 (06) - 数据导出至Excel

方法1：利用cl_salv_export_tool_xls 类实现导出

ALV 数据导出至 Excel，其实就是将 ALV 对应的内表数据导出至 Excel。大家常见的 GUI_DOWNLOAD 函数导出是将内表导出为文本文件，所以每次打开的时候提示文件格式不相符错误。本文提供基于 cl_salv_export_tool_xls 类实现导出的方法。为了方便调用，我将导出功能写在一个子例程中：

form frm_export_excel using p_itab type standard table.
  data: file_length type i.
  data: lt_stream   type salv_xml_xline_tabtype.
  data: file_name   type string.

  data: xls_export_tool  type ref to   cl_salv_export_tool_xls,
        export_config    type ref to   if_salv_export_configuration,
        file_content     type          cl_salv_export_tool=>y_file_content,
        export_exception type ref to   cx_salv_export_error.

  " Create an instance of the Excel export tool
  get reference of p_itab into gr_data.
  xls_export_tool = cl_salv_export_tool=>create_for_excel( gr_data ).

  " Configure export properties
  export_config = xls_export_tool->configuration( ).

  " Populate header
  loop at gt_fieldcat.
    export_config->add_column( header_text  = |{ gt_fieldcat-coltext }|
                               field_name   = |{ gt_fieldcat-fieldname }|
                               display_type  = if_salv_export_column_conf=>display_types-text_view ).
    clear gt_fieldcat.
  endloop.

  " exports R_DATA to requested format
  try.
      xls_export_tool->read_result(
        importing content = file_content ).
    catch cx_salv_export_error into export_exception.
      message id export_exception->if_t100_message~t100key-msgid
              type 'E'
              number export_exception->if_t100_message~t100key-msgno .
  endtry.

  " Set Filename
  file_name = 'D:\Downloads\spfli.xlsx'.

  " Convert to Binary
  call function 'SCMS_XSTRING_TO_BINARY'
    exporting
      buffer        = file_content
    importing
      output_length = file_length
    tables
      binary_tab    = lt_stream.

  " Download file using binary format
  cl_gui_frontend_services=>gui_download(
    exporting
      bin_filesize = file_length
      filetype     = 'BIN'
      filename     = file_name
    changing
      data_tab     = lt_stream
    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
      not_supported_by_gui    = 22
      error_no_gui            = 23
      others                  = 24 ).
  if sy-subrc <> 0.
    message 'Fail to download the file.' type 'E'.
  else.
    message 'Download the file successfully.' type 'S'.
  endif.
endform.

完整的代码放在源码中，请自行参考。

方法二：利用 XXL_SIMPLE_API 函数

下面利用 xxl_simple_api 函数，实现通用的将内表导出到 Excel 功能。

创建一个新的函数，名为 zitab_to_excel， 在函数组中，编写一个 form 获取内表所有的字段：

form frm_get_fields using    pt_data     type any table
                    changing pt_fields   type ddfields.

  data: lr_tabdescr  type ref to cl_abap_structdescr,
        lr_data      type ref to data,
        lt_fields    type ddfields.

  create data lr_data like line of pt_data.

  lr_tabdescr ?= cl_abap_structdescr=>describe_by_data_ref( lr_data ).
  lt_fields    = cl_salv_data_descr=>read_structdescr( lr_tabdescr ).

  pt_fields = lt_fields.
endform.    

利用 XXL_SIMPLE_API 函数实现内表导出：

function zitab_to_excel.
*"--------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(INTERNAL_TAB) TYPE  ANY TABLE
*"  EXCEPTIONS
*"      DIM_MISMATCH_DATA
*"      FILE_OPEN_ERROR
*"      FILE_WRITE_ERROR
*"      INV_WINSYS
*"      INV_XXL
*"--------------------------------------------------------------------
  data: lt_ddfields type ddfields,
          ls_fields   type dfies.

  perform frm_get_fields using internal_tab[] changing lt_ddfields[].

  field-symbols <fs> type standard table.

  data: t_heading type table of gxxlt_v with header line,
        t_online  type table of gxxlt_o,
        t_print   type table of gxxlt_p.

  loop at lt_ddfields into ls_fields.
    t_heading-col_no   = sy-tabix.
    t_heading-col_name = ls_fields-scrtext_m.
    append t_heading.
  endloop.

  assign internal_tab to <fs>.

  call function 'XXL_SIMPLE_API'
    tables
      col_text          = t_heading " heading, column text
      data              = <fs>
      online_text       = t_online
      print_text        = t_print
    exceptions
      dim_mismatch_data = 1
      file_open_error   = 2
      file_write_error  = 3
      inv_winsys        = 4
      inv_xxl           = 5
      others            = 6.

  case sy-subrc.
    when 1. raise dim_mismatch_data.
    when 2. raise file_open_error.
    when 3. raise file_write_error.
    when 4. raise inv_winsys.
    when 5. raise inv_xxl.
  endcase.
endfunction.

调用示例：

form frm_user_command using p_ucomm    type sy-ucomm        " user command
                            p_selfield type slis_selfield.  " select field

  case p_ucomm.
    when 'EXCEL'.
      call function 'ZITAB_TO_EXCEL'
        exporting
          internal_tab = gt_spfli[].
  endcase.
endform.                    "user_command

源码

06-Download ALV to Excel