EXCEL几种输出

*&---------------------------------------------------------------------*
*& Report  ZTEST_7
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ztest_7.
TYPE-POOLS: cxtab, kkblo.
TYPES: BEGIN OF zstr_outdata,
          col1  TYPE string,
          col2  TYPE string,
          col3  TYPE string,
          col4  TYPE string,
        END OF zstr_outdata.

DATA: lt_outdata TYPE TABLE OF zstr_outdata,
      wa_outdata TYPE zstr_outdata.
********excel输出 1*********
DATA:lv_title    TYPE string,
     lv_filter   TYPE string, "file filter
     lv_filename TYPE string,
     lv_path     TYPE string,
     lv_fullpath TYPE string.

*   显示文件保存对话框
lv_filter = 'Excel Files (*.xls)|*.xls|All Files (*.*)|*.*'.
** 保存文件名
lv_title  = '测试excel输出'.
*** 保存路径
CALL METHOD cl_gui_frontend_services=>file_save_dialog
  EXPORTING
    window_title         = lv_title
    default_extension    = '*.xls'
    default_file_name    = lv_title
    file_filter          = lv_filter
  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 OR lv_fullpath IS INITIAL.
  RETURN.
ELSE.
*      REFRESH lt_outdata.
*      CLEAR wa_outdata.
* 设定表格抬头字段
  wa_outdata-col1  = '测试1'.
  wa_outdata-col2  = '测试2'.
  wa_outdata-col3  = '测试3'.
  wa_outdata-col4  = '测试4'.
  APPEND wa_outdata TO lt_outdata.

*     添加行内容
*      CLEAR: wa_outdata,
*             jsytab1.
*      LOOP AT jsytab1.
  wa_outdata-col1  = 'jsytab1-prueflos'.
  wa_outdata-col2  = 'jsytab1-matnr'.
  wa_outdata-col3  = 'jsytab1-mblnr'.
  wa_outdata-col4  = 'jsytab1-zeile'.

  APPEND wa_outdata TO lt_outdata.
*        CLEAR jsytab1.
*      ENDLOOP.
***** excel 4
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename = lv_fullpath
      filetype = 'DAT'
    TABLES
      data_tab = lt_outdata.
  IF sy-subrc <> 0.

  ENDIF.

*** 下载数据
*      CALL METHOD cl_gui_frontend_services=>gui_download
*        EXPORTING
*          filename                = lv_fullpath
*          filetype                = 'DAT'
*        CHANGING
*          data_tab                = lt_outdata
*        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 text-010 TYPE 'S'.
*      ELSE.
*        MESSAGE text-011 TYPE 'S' DISPLAY LIKE 'E'.
*      ENDIF.
ENDIF.
************************输出excel3有局限性************************
*    perform  frm_export_excel_exp_10.
************************输出excel2*输出比较慢，可以***********************
**创建EXCEL进程
*    CREATE OBJECT excel 'EXCEL.APPLICATION'.           "创建excel进程
*    SET PROPERTY OF excel 'Visible' = 1.               "设置输出时为可视
*    CALL METHOD OF excel 'WORKBOOKS' = workbook.       "创建工作表
**创建sheet并添加到工作表
*    SET PROPERTY OF excel 'SHEETSINNEWWORKBOOK' = 1.
*    CALL METHOD OF workbook 'ADD' = workbook.
**设置工作薄名称
*    GET PROPERTY OF excel 'ACTIVESHEET' = sheet.
*    SET PROPERTY OF sheet 'NAME' = '结算单项目'.
*
** 设定表格抬头字段
*    PERFORM fill_cell USING 1 1 0 text-021.
*    PERFORM fill_cell USING 1 2 0 text-022.
*    PERFORM fill_cell USING 1 3 0 text-023.
*    PERFORM fill_cell USING 1 4 0 text-024.
*    PERFORM fill_cell USING 1 5 0 text-025.
*    PERFORM fill_cell USING 1 6 0 text-026.
*    PERFORM fill_cell USING 1 7 0 text-027.
*    PERFORM fill_cell USING 1 8 0 text-028.
*    PERFORM fill_cell USING 1 9 0 text-029.
*    PERFORM fill_cell USING 1 10 0 text-030.
*    PERFORM fill_cell USING 1 11 0 text-031.
*    PERFORM fill_cell USING 1 12 0 text-032.
*    PERFORM fill_cell USING 1 13 0 text-033.
*    PERFORM fill_cell USING 1 14 0 text-034.
*    PERFORM fill_cell USING 1 15 0 text-035.
*    PERFORM fill_cell USING 1 16 0 text-036.
*    PERFORM fill_cell USING 1 17 0 text-037.
*    PERFORM fill_cell USING 1 18 0 text-038.
*    PERFORM fill_cell USING 1 19 0 text-039.
*    PERFORM fill_cell USING 1 20 0 text-040.
*    PERFORM fill_cell USING 1 21 0 text-041.
*    PERFORM fill_cell USING 1 22 0 text-042.
*    PERFORM fill_cell USING 1 23 0 text-043.
*    PERFORM fill_cell USING 1 24 0 text-044.
*
*    LOOP AT jsytab1.
*      nnn = nnn + 1.
*      PERFORM fill_cell USING nnn 1 0 jsytab1-prueflos.
*      PERFORM fill_cell USING nnn 2 0 jsytab1-matnr.
*      PERFORM fill_cell USING nnn 3 0 jsytab1-mblnr.
*      PERFORM fill_cell USING nnn 4 0 jsytab1-zeile.
*      PERFORM fill_cell USING nnn 5 0 jsytab1-ebeln.
*      PERFORM fill_cell USING nnn 6 0 jsytab1-ebelp.
*      PERFORM fill_cell USING nnn 7 0 jsytab1-dmbtr.
*      PERFORM fill_cell USING nnn 8 0 jsytab1-netdmbtr.
*      PERFORM fill_cell USING nnn 9 0 jsytab1-fazhan.
*      PERFORM fill_cell USING nnn 10 0 jsytab1-dzdat.
*      PERFORM fill_cell USING nnn 11 0 jsytab1-checi.
*      PERFORM fill_cell USING nnn 12 0 jsytab1-mjsl.
*      PERFORM fill_cell USING nnn 13 0 jsytab1-yzf.
*      PERFORM fill_cell USING nnn 14 0 jsytab1-jsjg.
*      PERFORM fill_cell USING nnn 15 0 jsytab1-mjlsl.
*      PERFORM fill_cell USING nnn 16 0 jsytab1-erfme.
*      PERFORM fill_cell USING nnn 17 0 jsytab1-kzsl.
*      PERFORM fill_cell USING nnn 18 0 jsytab1-qtkl.
*      PERFORM fill_cell USING nnn 19 0 jsytab1-tuhao.
*      PERFORM fill_cell USING nnn 20 0 jsytab1-qtkk.
*      PERFORM fill_cell USING nnn 21 0 jsytab1-htjg.
*      PERFORM fill_cell USING nnn 22 0 jsytab1-qtkf.
*      PERFORM fill_cell USING nnn 23 0 jsytab1-bcjshl.
*      PERFORM fill_cell USING nnn 24 0 jsytab1-rema.
*
*    ENDLOOP.

*****************输出excel*****excel局限03版********
FORM frm_export_excel_exp_10.
  DATA: lt_fieldcat  TYPE kkblo_t_fieldcat.

  PERFORM frm_field_for_xls_exp_10 TABLES lt_fieldcat.

  CALL FUNCTION 'ALV_XXL_CALL'
    EXPORTING
      i_tabname           = 'GT_LAYOUT_EXCEL'
      it_fieldcat         = lt_fieldcat
    TABLES
      it_outtab           = lt_outdata
    EXCEPTIONS
      fatal_error         = 1
      no_display_possible = 2.

ENDFORM.                    " FRM_EXPORT_EXCEL_EXP_10
*&---------------------------------------------------------------------*
*&      Form  FRM_FIELD_FOR_XLS_EXP_10
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM frm_field_for_xls_exp_10 TABLES pt_fieldcat TYPE kkblo_t_fieldcat.
* 定义宏
  DEFINE lw_fieldcat.
    pt_fieldcat-tabname   = 'GT_LAYOUT_EXCEL'.
    pt_fieldcat-fieldname = &1.
    pt_fieldcat-seltext   = &2.
    append pt_fieldcat.
    clear  pt_fieldcat.
  END-OF-DEFINITION.   "lw_fieldcat

* 设定表格字段
  lw_fieldcat  'COL1'    '11111'.
  lw_fieldcat  'COL2'    '22222' .
  lw_fieldcat  'COL3'    '33333' .
  lw_fieldcat  'COL4'    '44444' .

ENDFORM.                    "frm_field_for_xls_exp_10