XLSX Workbench使用教程 - 基础

目录

1、单sheet页导出

1.1、Form properties

1.2、Sheet properties

 1.3、抬头字段映射

 1.4、行项目字段映射

 1.5、保存激活

 1.6代码调用

 2、多页签导出

XLSX Workbench是网上的开源项目，基于office open xml规范生成Excel文件。对于大批量的Excel导出需求，可以考虑使用，以节省开发时间。

事务码：ZXLWB_WORKBENCH

程序：ZXLWB

初始界面：

1、单sheet页导出

导出示例：

 注：截图中的图片和二维码请忽略，本贴没有涉及讲解。

 注：本例定义的结构形式仅参考

1.1、Form properties

 

 

1.2、Sheet properties

 1.3、抬头字段映射

 为需要输出的每个字段挨个选择输出范围

 1.4、行项目字段映射

 1.5、保存激活

 1.6代码调用

*&---------------------------------------------------------------------*
*& 常量
*&---------------------------------------------------------------------*
CONSTANTS: gc_objid TYPE w3objid  VALUE 'ZDEMO_EXCEL_001'.
*&---------------------------------------------------------------------*
*& 结构
*&---------------------------------------------------------------------*
DATA: gs_head TYPE zxlwb_s_demo_head.
*&---------------------------------------------------------------------*
*&  选择屏幕
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK bl01 WITH FRAME TITLE TEXT-t01.

  PARAMETERS: p_ebeln TYPE ekko-ebeln OBLIGATORY.

  PARAMETERS: p_rad1 RADIOBUTTON GROUP g1,
              p_rad2 RADIOBUTTON GROUP g1.

SELECTION-SCREEN END OF BLOCK bl01.

START-OF-SELECTION.

  PERFORM program_main.
*&---------------------------------------------------------------------*
*& Form program_main
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM program_main .

  PERFORM get_data.

  PERFORM download_excel.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form get_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM get_data .
  DATA: lv_barcdata(80) TYPE c,
        lv_lifnr        TYPE lfa1-lifnr.

  CLEAR: gs_head.

  SELECT SINGLE ebeln lifnr
    INTO CORRESPONDING FIELDS OF gs_head
    FROM ekko
    WHERE ebeln = p_ebeln.
  IF sy-subrc <> 0.
    EXIT.
  ENDIF.

  SELECT SINGLE name1 INTO gs_head-lifnrt
    FROM lfa1
    WHERE lifnr = gs_head-lifnr.

  SELECT ebelp matnr txz01 menge meins
    INTO CORRESPONDING FIELDS OF TABLE gs_head-item
    FROM ekpo
    WHERE ebeln = p_ebeln.

  gs_head-sheet_name = '采购订单'.

  SORT gs_head-item BY ebelp.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form download_excel
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM download_excel.
  DATA: lv_window_title TYPE string,
        lv_default_name TYPE string,
        lv_filename     TYPE string,
        lv_path         TYPE string,
        lv_fullpath     TYPE string.

  DATA: lv_documentx TYPE mime_data.

  IF p_rad1 = 'X'.
    CALL FUNCTION 'ZXLWB_CALLFORM'
      EXPORTING
        iv_formname         = gc_objid
        iv_context_ref      = gs_head
*       iv_viewer_title     = ''
*       iv_viewer_inplace   = ''
*       IV_VIEWER_CALLBACK_PROG = SY-CPROG
*       IV_VIEWER_CALLBACK_FORM =
        iv_viewer_suppress  = ''
        iv_protect          = 'X'         " X Excel禁止修改
*       iv_save_as          = ''          " 下载目录
*       IV_STARTUP_MACRO    =
*       IT_DOCPROPERTIES    =
      IMPORTING
        ev_document_rawdata = lv_documentx
      EXCEPTIONS
        process_terminated  = 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.
  ELSEIF p_rad2 = 'X'.

    lv_window_title = TEXT-t03. " 保存
    lv_default_name = TEXT-t02 && sy-datum && sy-uzeit.

    CALL METHOD cl_gui_frontend_services=>file_save_dialog
      EXPORTING
        window_title              = lv_window_title
        default_extension         = 'xlsx'
        default_file_name         = lv_default_name
        file_filter               = '.xlsx'
      CHANGING
        filename                  = lv_filename
        path                      = lv_path
        fullpath                  = lv_fullpath
      EXCEPTIONS
        cntl_error                = 1
        error_no_gui              = 2
        not_supported_by_gui      = 3
        invalid_default_file_name = 4
        OTHERS                    = 5.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.

    CHECK lv_fullpath IS NOT INITIAL.

    CALL FUNCTION 'ZXLWB_CALLFORM'
      EXPORTING
        iv_formname         = gc_objid
        iv_context_ref      = gs_head
*       iv_viewer_title     = ''
*       iv_viewer_inplace   = ''
*       IV_VIEWER_CALLBACK_PROG = SY-CPROG
*       IV_VIEWER_CALLBACK_FORM =
        iv_viewer_suppress  = 'X'         " X 不在SAP界面显示
        iv_protect          = 'X'         " X Excel禁止修改
        iv_save_as          = lv_fullpath " 下载目录
*       IV_STARTUP_MACRO    =
*       IT_DOCPROPERTIES    =
      IMPORTING
        ev_document_rawdata = lv_documentx
      EXCEPTIONS
        process_terminated  = 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.

  ENDIF.

ENDFORM.

*T01	  选择条件
*T02	  XLSX Workbench Demo one Sheet
*T03	  保存
*P_EBELN  采购订单
*P_RAD1	  显示
*P_RAD2	  下载

 2、多页签导出

注：本例定义的结构形式仅参考

注：此例中的黄灯原因是不同结构中的字段有重名

感谢原作者提供的XLSX Workbench开源工具

转载请注明出处 ！

如有疏漏之处，还请不吝赐教！