EXCEL——DOI操作EXCEL方法封装（包含动态创建操作SHEET页）

一、SE24 构建类



二、基于源码

class ZCLFI00170 definition
  public
  final
  create public .

public section.

  data V_CONTAINER type ref to CL_GUI_CONTAINER .
  data V_CONTROL type ref to I_OI_CONTAINER_CONTROL .
  data V_DOCUMENT type ref to I_OI_DOCUMENT_PROXY .
  data V_SPREADSHEET type ref to I_OI_SPREADSHEET .
  data V_ERROR type ref to I_OI_ERROR .
  data V_BDS_DOCUMENTS type ref to CL_BDS_DOCUMENT_SET .
  data V_DOC_COMPONENTS type SBDST_COMPONENTS .
  data V_DOC_SIGNATURE type SBDST_SIGNATURE .
  data T_BDS_URIS type SBDST_URI .
  data CL_ERROR type ref to I_OI_ERROR .
  data:
    v_bds_url like line of t_bds_uris .
  data:
    v_template_url(256) type c .

  methods OPEN_EXCEL
    importing
      !I_CLASSNAME type SBDST_CLASSNAME
      !I_CLASSTYPE type SBDST_CLASSTYPE
      !I_OBJECTKEY type SBDST_OBJECT_KEY .
  methods FILL_DATA_TO_EXCEL
    importing
      !I_TOP type I
      !I_LEFT type I
      !I_ROWS type I
      !I_COLUMNS type I
      !I_RANGES type SOI_RANGE_LIST
      !I_CONTENTS type SOI_GENERIC_TABLE .
  methods DOWNLOAD_EXCEL
    importing
      !I_PATH type C
      !I_FILENAME type C .
  methods CLOSE_EXCEL .
  methods ADD_SHEET
    importing
      !SHEET_NAME type CHAR20 .
  methods SET_SHEET_NAME
    importing
      !NEW_NAME type CHAR20
      !OLD_NAME type CHAR20 .
  methods DELETE_SHEET
    importing
      !SHEET_NAME type CHAR20 .
  methods SELECT_SHEET
    importing
      !SHEET_NAME type CHAR20 default 'Sheet1' .
protected section.
private section.

  methods INIT_CONTAINER .
  methods GET_TEMPLATE_URL
    importing
      !I_CLASSNAME type SBDST_CLASSNAME
      !I_CLASSTYPE type SBDST_CLASSTYPE
      !I_OBJECTKEY type SBDST_OBJECT_KEY .
ENDCLASS.



CLASS ZCLFI00170 IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->ADD_SHEET
* +-------------------------------------------------------------------------------------------------+
* | [--->] SHEET_NAME                     TYPE        CHAR20
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method ADD_SHEET.

    call method v_spreadsheet->ADD_SHEET
     exporting
       NAME       = SHEET_NAME
       NO_FLUSH   = 'X'.

  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->CLOSE_EXCEL
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method CLOSE_EXCEL.
    if not v_document is initial.
      call method v_document->close_document.
      free v_document.
    endif.

    if not v_control is initial.
      call method v_control->destroy_control.
      free v_control.
    endif.

    if v_container is not initial.
      call method v_container->free.
    endif.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->DELETE_SHEET
* +-------------------------------------------------------------------------------------------------+
* | [--->] SHEET_NAME                     TYPE        CHAR20
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD DELETE_SHEET.

    CALL METHOD V_SPREADSHEET->DELETE_SHEET
      EXPORTING
        NAME     = SHEET_NAME
        NO_FLUSH = 'X'.

  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->DOWNLOAD_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_PATH                         TYPE        C
* | [--->] I_FILENAME                     TYPE        C
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method DOWNLOAD_EXCEL.
    data: l_filename type c length 270.
    concatenate i_path i_filename into l_filename.
    call method v_document->save_as
      exporting
       file_name = l_filename
      importing
       error     = v_error.

    if v_error->has_failed eq 'X'.
     call method v_error->raise_message
      exporting
        type = 'I'.
    endif.

    call method close_excel.

  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->FILL_DATA_TO_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_TOP                          TYPE        I
* | [--->] I_LEFT                         TYPE        I
* | [--->] I_ROWS                         TYPE        I
* | [--->] I_COLUMNS                      TYPE        I
* | [--->] I_RANGES                       TYPE        SOI_RANGE_LIST
* | [--->] I_CONTENTS                     TYPE        SOI_GENERIC_TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD FILL_DATA_TO_EXCEL .

    CALL METHOD V_SPREADSHEET->INSERT_RANGE_DIM
      EXPORTING
        NAME     = 'cell'
        NO_FLUSH = 'X'
        TOP      = I_TOP  "6
        LEFT     = I_LEFT  "3
        ROWS     = I_ROWS "3
        COLUMNS  = I_COLUMNS. "1

    CALL METHOD V_SPREADSHEET->SET_RANGES_DATA
      EXPORTING
        RANGES   = I_RANGES
        CONTENTS = I_CONTENTS
        NO_FLUSH = 'X'.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCLFI00170->GET_TEMPLATE_URL
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_CLASSNAME                    TYPE        SBDST_CLASSNAME
* | [--->] I_CLASSTYPE                    TYPE        SBDST_CLASSTYPE
* | [--->] I_OBJECTKEY                    TYPE        SBDST_OBJECT_KEY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method GET_TEMPLATE_URL.
    create object v_bds_documents.

    call method cl_bds_document_set=>get_info
       exporting
         classname  = i_classname
         classtype  = i_classtype
         object_key = i_objectkey
       changing
         components = v_doc_components
         signature  = v_doc_signature.

    call method cl_bds_document_set=>get_with_url
       exporting
         classname  = i_classname
         classtype  = i_classtype
         object_key = i_objectkey
       changing
         uris       = t_bds_uris
         signature  = v_doc_signature.

     free v_bds_documents.
     read table t_bds_uris into v_bds_url index 1.
     v_template_url = v_bds_url-uri.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Private Method ZCLFI00170->INIT_CONTAINER
* +-------------------------------------------------------------------------------------------------+
* +--------------------------------------------------------------------------------------</SIGNATURE>
   method INIT_CONTAINER.
    v_container = cl_gui_container=>screen0.

    "create container control
    call method c_oi_container_control_creator=>get_container_control
    importing
      control = v_control.

    "initialize control
    call method v_control->init_control
      exporting
        inplace_enabled          = 'X '
        inplace_scroll_documents = 'X'
        register_on_close_event  = 'X'
        register_on_custom_event = 'X'
        r3_application_name      = 'DOI'
        parent                   = v_container.

  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->OPEN_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_CLASSNAME                    TYPE        SBDST_CLASSNAME
* | [--->] I_CLASSTYPE                    TYPE        SBDST_CLASSTYPE
* | [--->] I_OBJECTKEY                    TYPE        SBDST_OBJECT_KEY
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method OPEN_EXCEL.

    call method init_container.
    call method get_template_url exporting i_classname = i_classname i_classtype = i_classtype i_objectkey = i_objectkey.
    call method v_control->get_document_proxy
      exporting
        document_type      = 'Excel.Sheet'
        no_flush           = 'X'
        register_container = 'X'
      importing
        document_proxy     = v_document.

    call method v_document->open_document
      exporting
        open_inplace = 'X'
        document_url = v_template_url.

    data: l_available type i.
    call method v_document->has_spreadsheet_interface
      exporting
        no_flush     = 'X'
      importing
        is_available = l_available.

    call method v_document->get_spreadsheet_interface
      exporting
        no_flush        = 'X'
      importing
        sheet_interface = v_spreadsheet.

*    call method v_spreadsheet->select_sheet
*      exporting
*        name     = 'Sheet1'
*        no_flush = 'X'
*      importing
*        error = cl_error.

  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->SELECT_SHEET
* +-------------------------------------------------------------------------------------------------+
* | [--->] SHEET_NAME                     TYPE        CHAR20 (default ='Sheet1')
* +--------------------------------------------------------------------------------------</SIGNATURE>
  method SELECT_SHEET.
    CALL METHOD V_SPREADSHEET->SELECT_SHEET
      EXPORTING
        NAME     = SHEET_NAME
        NO_FLUSH = 'X'
      IMPORTING
        ERROR    = CL_ERROR.
  endmethod.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCLFI00170->SET_SHEET_NAME
* +-------------------------------------------------------------------------------------------------+
* | [--->] NEW_NAME                       TYPE        CHAR20
* | [--->] OLD_NAME                       TYPE        CHAR20
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD SET_SHEET_NAME.
    CALL METHOD V_SPREADSHEET->SET_SHEET_NAME
      EXPORTING
        NEWNAME  = NEW_NAME
        OLDNAME  = OLD_NAME
        NO_FLUSH = 'X'.
  ENDMETHOD.
ENDCLASS.

三、使用实例

*&---------------------------------------------------------------------*
*& Program ID : ZCOR0010
*& Description: MC 日式损益表
*&---------------------------------------------------------------------*
*& Created by : Mitchell Wang                Date: 2019/07/09 Ver: 01.0
*&---------------------------------------------------------------------*
*& Modified by: author                       Date: YYYY/MM/DD Ver: 02.0
*&   … Description of modification …
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZCOR0010.


" 类型
TYPES:BEGIN OF TY_LRZX,
        COALL    TYPE GRPDYNP-NAME_COALL,    " 利润中心组
        DESCRIPT TYPE CHAR40,                " 组描述
        PRCTR    TYPE BSEG-PRCTR,            " 利润中心
        ZBS      TYPE CHAR1,                 " 标识
      END OF TY_LRZX.
TYPES:BEGIN OF TY_SHEET,
        NUMBER     TYPE CHAR2,
        SHEET_NAME TYPE CHAR20,
        TEXT       TYPE CHAR50,
        ZBS        TYPE CHAR1,                 " 组标识 / 集标识
      END OF TY_SHEET.
TYPES:BEGIN OF TP_PMON,
        SPMON TYPE S031-SPMON,
      END OF TP_PMON.
TYPES:T_PRCTR TYPE STANDARD TABLE OF ZFIS035.

"  变量
DATA:L_PMON TYPE S031-SPMON ##NEEDED.
DATA:TY_COALL TYPE GRPDYNP-NAME_COALL,
     TY_RCNTR TYPE BSEG-PRCTR.
"equired for writing data to Excel
DATA: V_RANGES      TYPE SOI_RANGE_LIST ##NEEDED,
      V_RANGE       TYPE SOI_RANGE_ITEM ##NEEDED,
      V_CONTENTS    TYPE SOI_GENERIC_TABLE ##NEEDED,
      V_CONTENT     TYPE SOI_GENERIC_ITEM ##NEEDED,
      V_CONTENTS_SJ TYPE SOI_GENERIC_TABLE ##NEEDED,
      V_CONTENT_SJ  TYPE SOI_GENERIC_ITEM ##NEEDED.

" 内表 结构
DATA:T_LRZX TYPE STANDARD TABLE OF TY_LRZX ##NEEDED,
     W_LRZX TYPE TY_LRZX ##NEEDED.
DATA:T_SHEET TYPE STANDARD TABLE OF TY_SHEET ##NEEDED,
     W_SHEET TYPE TY_SHEET ##NEEDED.

DATA:T_ZFIT016 TYPE STANDARD TABLE OF ZFIT016 ##NEEDED,
     W_ZFIT016 TYPE ZFIT016 ##NEEDED.
DATA:VR_RACCT TYPE RANGE OF FAGLFLEXT-RACCT ##NEEDED,
     VW_RACCT LIKE LINE OF VR_RACCT ##NEEDED.
DATA:VR_PRCTR TYPE RANGE OF BSEG-PRCTR ##NEEDED,
     VW_PRCTR LIKE LINE OF VR_PRCTR ##NEEDED.
DATA:T_PMON_JP TYPE TABLE OF TP_PMON ##NEEDED,
     W_PMON_JP TYPE TP_PMON ##NEEDED.
DATA:T_PMON_NUM TYPE TABLE OF TP_PMON ##NEEDED,
     W_PMON_NUM TYPE TP_PMON ##NEEDED.
DATA:T_PMON_CN TYPE TABLE OF TP_PMON ##NEEDED,
     W_PMON_CN TYPE TP_PMON ##NEEDED.

" 宏定义
DEFINE SET_PRCTR.
  clear VW_PRCTR.
  VW_PRCTR-sign   = 'I'.
  VW_PRCTR-