ABAP将内表导出至Excel

ABAP将内表导出至Excel

方法一：GUI Download

---

• filename 文件名以xls命名
• codepages设置文件编码 SE11 TCP00A可以查看编码与编号对应关系
• 以DAT和ASC格式存储的文件实际为txt格式，EXCEL会有警告
• 遇到EXCEL乱码问题（未解决，可能与excel有关，txt打开正常）

  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
*     BIN_FILESIZE            =
      filename                = lv_filename1
      filetype                = 'DAT'
      "这里一般用DAT，如果用ASC则1000-不会显示为-1000，而dat会显示为-1000，如果"用DBF则不会有缩进，
      "即字符前面的空格会被除去,而且字符的前导0也会输出。
      "write_field_separator   = 'X'  "列由制表符分隔  该参数仅对文件类型值"ASC、DAT和IBM有意义；对于DAT，它是隐式设置的。
      no_auth_check           = 'X'
      codepage                = '8450'"8450 4310
    TABLES
      data_tab                = gt_data
      fieldnames              = t_fieldnames[]
    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.

方法二：SAP_CONVERT_TO_XLS_FORMAT

---

• 输入的文件名TYPE必须为rlgrap-filename
• 下载的文件只有数据内容，无表头

call function 'SAP_CONVERT_TO_XLS_FORMAT'
  exporting
*    i_field_seperator    = 
*    i_line_header        = 
    i_filename           = li_filename
*    i_appl_keep          = SPACE
  tables
    i_tab_sap_data       = gt_data_excel
*  changing
*    i_tab_converted_data = 
*  exceptions
*    conversion_failed    = 1
*    others               = 2
  .
if sy-subrc <> 0.
* message id sy-msgid type sy-msgty number sy-msgno
*            with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
endif.

方法三：SAP_CONVERT_TO_XLS_FORMAT

---

• 输入的文件名TYPE必须为rlgrap-filename
• 函数bug：两次或多次调用列名会进入数据区
• fielname数据类型会被强制转换为列的类型，转换失败程序会dump

call function 'MS_EXCEL_OLE_STANDARD_DAT'
  exporting
    file_name                 = li_filename
*    create_pivot              = 0
*    data_sheet_name           = SPACE
*    pivot_sheet_name          = SPACE
*    password                  = SPACE
*    password_option           = 0
*  tables
*    pivot_field_tab           = 
    data_tab                  = gt_data
    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_pivot_fields      = 6
*    download_problem          = 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.

相关功能实现

---

文件路径获取

*&---------------------------------------------------------------------*
*& Form FRM_GET_FULLPATH
*&---------------------------------------------------------------------*
*& 获取桌面路径并填入数据
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
form frm_get_fullpath changing pv_fullpath type string
                                 pv_path     type string
                                 pv_name     type string.

  data: lv_init_path  type string,
        lv_init_fname type string,
        lv_path       type string,
        lv_filename   type string,
        lv_fullpath   type string.
  lv_init_fname = 'Exportfile'."初始输出文件名

* 获取桌面路径
  call method cl_gui_frontend_services=>get_desktop_directory
    changing
      desktop_directory    = lv_init_path
    exceptions
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      others               = 4.
  if sy-subrc <> 0.
    exit.
  endif.

* 用戶选择文件名以及存储路径
  call method cl_gui_frontend_services=>file_save_dialog
    exporting
      default_extension    = 'XLS'
      default_file_name    = lv_init_fname
      initial_directory    = lv_init_path
      prompt_on_overwrite  = 'X'
    changing
      filename             = lv_filename "文件名
      path                 = lv_path
      fullpath             = lv_fullpath
    exceptions
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      others               = 4.
  if sy-subrc = 0.
    pv_fullpath = lv_fullpath.
    pv_path     = lv_path.
  endif.

endform. " FRM_GET_FULLPATH

通过显示ALV的GT_FIELDCAT获取列名

  FIELD-SYMBOLS:<fs_comp> TYPE abap_compdescr.
  FIELD-SYMBOLS <fs_name> TYPE ANY.
  DATA:cl_descr TYPE REF TO cl_abap_structdescr.
   cl_descr ?= cl_abap_typedescr=>describe_by_data( tem_out ).
   LOOP AT cl_descr->components ASSIGNING <fs_comp>.  "获取内表字段名称
     READ TABLE gt_fieldcat INTO DATA(ls_fieldcat) WITH KEY fieldname = <fs_comp>-name.
     t_fieldnames-name =  ls_fieldcat-seltext_l.             "字段名称
     APPEND t_fieldnames.
   ENDLOOP.