1.上传Excel模板

• 事务代码：OAOR
• 输入参数，然后执行
  • 类名：HRFPM_EXCEL_STANDARD
  • 分类类型：OT
  • 对象代码：DOIDEMO（自定义）
    
• 双击表模板，上传一个新建的空白Excel文档
  
  

2.创建一个空白屏幕100，并设置PBO PAI逻辑和OK_CODE变量

3.报表程序源代码

TYPES: BEGIN OF TY_SPFLI,
         CARRID   LIKE SPFLI-CARRID,
         CONNID   LIKE SPFLI-CONNID,
         CITYFROM LIKE SPFLI-CITYFROM,
         CITYTO   LIKE SPFLI-CITYTO,
       END OF TY_SPFLI.

CONSTANTS: GC_CLASSNAME TYPE SBDST_CLASSNAME VALUE 'HRFPM_EXCEL_STANDARD',
           GC_CLASSTYPE TYPE SBDST_CLASSTYPE VALUE 'OT',
           GC_OBJECTKEY TYPE SBDST_OBJECT_KEY VALUE 'DOIDEMO'.

DATA: GO_CONTAINER TYPE REF TO CL_GUI_CONTAINER,
      GO_SPLITTER  TYPE REF TO CL_GUI_SPLITTER_CONTAINER.

DATA: GT_SPFLI TYPE STANDARD TABLE OF TY_SPFLI WITH HEADER LINE.


DATA: GT_CONTENTS TYPE SOI_GENERIC_TABLE,
      GS_CONTENT  TYPE SOI_GENERIC_ITEM.

DATA: GO_CUSTOM_CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER,    "container对象
      GO_CONTROL          TYPE REF TO I_OI_CONTAINER_CONTROL,     "container control对象
      GO_DOCUMENT         TYPE REF TO I_OI_DOCUMENT_PROXY,        "DOI EXCEL文档对象
      GO_SPREADSHEET      TYPE REF TO I_OI_SPREADSHEET.           "DOISHEET文档处理对象


DATA: GO_BDS_DOCUMENTS TYPE REF TO CL_BDS_DOCUMENT_SET,           "Excel文档对象
      GT_BDS_URIS      TYPE SBDST_URI,                            "url类型的文档对象，一行表示一个Excel模板
      GS_BDS_URL       LIKE LINE OF GT_BDS_URIS.

DATA: GV_EXCEL_URL TYPE C LENGTH 256.                                   "Excel模板，Url类型

DEFINE FILL_CELL.
  GS_CONTENT-ROW = &1.
  gs_content-column = &2.
  gs_content-value = &3.
  append gs_content to gt_contents.
  clear gs_content.
END-OF-DEFINITION.

*---------------------------------------------------------------* 处理
INITIALIZATION.

START-OF-SELECTION.
  PERFORM FRM_GET_DATA.
  CALL SCREEN 100.

*---------------------------------------------------------------* PBO PAI

MODULE PROC_PROGRAM_OUTPUT OUTPUT.
  SET PF-STATUS '100'.
  PERFORM FRM_INIT_CONTAINER.
  PERFORM FRM_CNTR_CONTROL.           "初始化CONTAINER CONTRAL对象
  PERFORM FRM_GET_TEMPLATE.           "获取Excel模板
  PERFORM FRM_OPEN_EXCEL.
  PERFORM FRM_FILL_EXCEL.
ENDMODULE.

MODULE PROC_PROGRAM_INPUT INPUT.
  DATA: OK_CODE TYPE SY-UCOMM,
        SAVE_OK LIKE OK_CODE.

  SAVE_OK = OK_CODE.

  IF SAVE_OK = 'EXIT'.
    PERFORM FRM_RELEASE_OBJ.
    LEAVE PROGRAM.
  ENDIF.
ENDMODULE.
*---------------------------------------------------------------* FORM

FORM FRM_INIT_CONTAINER .
  CREATE OBJECT GO_SPLITTER
    EXPORTING
      PARENT  = CL_GUI_CONTAINER=>SCREEN0
      ROWS    = 1
      COLUMNS = 1.

  CALL METHOD GO_SPLITTER->SET_BORDER
    EXPORTING
      BORDER = CL_GUI_CFW=>FALSE.

  GO_CONTAINER = GO_SPLITTER->GET_CONTAINER( ROW = 1 COLUMN = 1 ).
ENDFORM.

FORM FRM_GET_DATA .
  SELECT * FROM SPFLI
    INTO CORRESPONDING FIELDS OF TABLE GT_SPFLI[] UP TO 20 ROWS.
ENDFORM.

FORM FRM_CNTR_CONTROL.
*创建GO_CONTROL对象
  CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL
    IMPORTING
      CONTROL = GO_CONTROL.



*实例化GO_CONTROL对象
  CALL METHOD GO_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 DEMO'
      PARENT                   = GO_CONTAINER.
ENDFORM.

FORM FRM_GET_TEMPLATE.

  CREATE OBJECT GO_BDS_DOCUMENTS. "创建Excel文档对象

  CALL METHOD CL_BDS_DOCUMENT_SET=>GET_WITH_URL   "通过URL传输返回Excel文档
    EXPORTING
      CLASSNAME  = GC_CLASSNAME
      CLASSTYPE  = GC_CLASSTYPE
      OBJECT_KEY = GC_OBJECTKEY
    CHANGING
      URIS       = GT_BDS_URIS.

  READ TABLE GT_BDS_URIS INTO GS_BDS_URL INDEX 1.  "读取第一个EXCEL模板
  GV_EXCEL_URL = GS_BDS_URL-URI.                   "保存到全局变量

  FREE GO_BDS_DOCUMENTS.

ENDFORM.

FORM FRM_OPEN_EXCEL.

  CALL METHOD GO_CONTROL->GET_DOCUMENT_PROXY
    EXPORTING
      DOCUMENT_TYPE      = 'Excel.Sheet'
      NO_FLUSH           = 'X'
      REGISTER_CONTAINER = 'X'
    IMPORTING
      DOCUMENT_PROXY     = GO_DOCUMENT.

  CALL METHOD GO_DOCUMENT->OPEN_DOCUMENT
    EXPORTING
      OPEN_INPLACE = 'X'
      DOCUMENT_URL = GV_EXCEL_URL.

  CALL METHOD GO_DOCUMENT->GET_SPREADSHEET_INTERFACE
    EXPORTING
      NO_FLUSH        = 'X'
    IMPORTING
      SHEET_INTERFACE = GO_SPREADSHEET.
ENDFORM.

FORM FRM_FILL_EXCEL.
  DATA: LINE_COUNT TYPE I VALUE 0,
        COL_COUNT  TYPE I VALUE 0,
        LT_COLUMN  TYPE TABLE OF RSTRUCINFO.

  DATA: GT_RANGES TYPE SOI_RANGE_LIST,
        GS_RANGE  TYPE SOI_RANGE_ITEM.

  DATA: ROW_INDEX TYPE I VALUE '1'.

  CHECK GT_SPFLI[] IS NOT INITIAL.

  "打开sheet1
  CALL METHOD GO_SPREADSHEET->SELECT_SHEET
    EXPORTING
      NAME     = 'Sheet1'
      NO_FLUSH = 'X'.


* 获取内表的行数
  DESCRIBE TABLE GT_SPFLI[] LINES LINE_COUNT.

* 获取内表的列数
  CALL FUNCTION 'GET_COMPONENT_LIST'
    EXPORTING
      PROGRAM    = SY-REPID
      FIELDNAME  = 'GT_SPFLI'
    TABLES
      COMPONENTS = LT_COLUMN.
  DESCRIBE TABLE LT_COLUMN LINES COL_COUNT.

  CALL METHOD GO_SPREADSHEET->INSERT_RANGE_DIM
    EXPORTING
      NAME     = 'cell'
      NO_FLUSH = 'X'
      TOP      = 2
      LEFT     = 1
      ROWS     = LINE_COUNT
      COLUMNS  = COL_COUNT.

  "填充一个内表大小的range到sheet1
  GS_RANGE-NAME = 'cell'.
  GS_RANGE-ROWS = LINE_COUNT.
  GS_RANGE-COLUMNS = COL_COUNT.
  GS_RANGE-CODE = 4.
  APPEND GS_RANGE TO GT_RANGES.

  "填充数据
  LOOP AT GT_SPFLI.
    CLEAR GS_CONTENT.

    FILL_CELL:
    ROW_INDEX 1 GT_SPFLI-CARRID,
    ROW_INDEX 2 GT_SPFLI-CONNID,
    ROW_INDEX 3 GT_SPFLI-CITYFROM,
    ROW_INDEX 4 GT_SPFLI-CITYTO.

    ROW_INDEX = ROW_INDEX + 1.
  ENDLOOP.

"I_OI_SPREADSHEET中有很多方法可对Excel进行处理，此处不再赘述

  "填充数据到sheet1
  CALL METHOD GO_SPREADSHEET->SET_RANGES_DATA
    EXPORTING
      RANGES   = GT_RANGES
      CONTENTS = GT_CONTENTS
      NO_FLUSH = 'X'.
ENDFORM.

FORM FRM_RELEASE_OBJ.
  IF NOT GO_DOCUMENT IS INITIAL.
    CALL METHOD GO_DOCUMENT->CLOSE_DOCUMENT.
    FREE GO_DOCUMENT.
  ENDIF.

  IF NOT GO_CONTROL IS INITIAL.
    CALL METHOD GO_CONTROL->DESTROY_CONTROL.
    FREE GO_CONTROL.
  ENDIF.

ENDFORM.

4.执行效果

• 本文只实现简单的功能，复杂功能可结合接口I_OI_SPREADSHEET中的方法进行实现
  
• 效果