ABAP DOI技术

DOI也不算是特别新的技术了，相对于OLE来说,DOI的速度还是可以的，因为OLE对于有特定格式要求的EXCEL来说写入是按照单元格逐个填充的。DOI可以根据RANGES和CONTENT实现一次写入。

下文将介绍DOI开发的全部过程：

一、OAOR模板导入：

与SMW0差不多.

类名称：系统里随便找一个

分类类型：随意

点击新建按钮，然后下方出现一个文件夹，单击下面的文档类型，DEMO里选择是"EXCEL"，当然也可以选择"WORD"，但是实现的代码不一样。

二 、代码实现

*&---------------------------------------------------------------------*
*& Report  Y_DOI_TEST
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zfi0060.

*&---------------------------------------------------------------------*
*& Report Z_JYH_DOI
*&---------------------------------------------------------------------*
*& 本程序是DOI的demo测试程序，用excel展示内表的数据
*&---------------------------------------------------------------------*

TABLES : zzpiccl1a.

*-->控件及界面定义
DATA: gv_container  TYPE scrfname VALUE 'ALV_CON',  "ALV控键定义
      g_cust_con    TYPE REF TO cl_gui_custom_container,
      g_container   TYPE REF TO cl_gui_container,
      g_control     TYPE REF TO i_oi_container_control,
      g_document    TYPE REF TO i_oi_document_proxy,
      g_spreadsheet TYPE REF TO i_oi_spreadsheet.

DATA: gr_splitter TYPE REF TO cl_gui_splitter_container.

*-->类型池定义
DATA: g_bds_documents      TYPE REF TO cl_bds_document_set,
      gv_classname         TYPE sbdst_classname,
      gv_classtype         TYPE sbdst_classtype,
      gv_objectkey         TYPE sbdst_object_key,
      gv_doc_components    TYPE sbdst_components,
      gv_doc_signature     TYPE sbdst_signature,
      gt_bds_uris          TYPE sbdst_uri,
      gs_bds_url           LIKE LINE OF gt_bds_uris,
      gv_template_url(256) TYPE c.    "模板url存储

*-->定义写入excel时需要的ranges和contents
DATA: gt_ranges   TYPE soi_range_list,
      gs_range    TYPE soi_range_item,
      gt_contents TYPE soi_generic_table,
      gs_content  TYPE soi_generic_item.


*-->内表定义
DATA : BEGIN OF gw_data,
       xnegp      TYPE bseg-xnegp,     "反记账标识
       rzzxz      TYPE zzpiccl1a-rzzxz,"险种
       bezek      TYPE t25a2-bezek,    "险种描述
       xl2        TYPE t25a2-bezek,    "险类2
       ls         TYPE char2,          "长短期险
       xl         TYPE t25a2-bezek,   "系列
       xl1        TYPE t25a2-bezek,   "险类1
       dj_lj      TYPE zzpiccl1a-hsl, "趸交累计
       snsq_lj    TYPE zzpiccl1a-hsl, "首年首期累计
       snxq_lj    TYPE zzpiccl1a-hsl, "首年续期累计
       xnxq_lj    TYPE zzpiccl1a-hsl, "续年续期累计
       sum_lj     TYPE zzpiccl1a-hsl, "累计汇总
       dj_tq      TYPE zzpiccl1a-hsl, "趸交同期
       snsq_ljtq  TYPE zzpiccl1a-hsl, "首年续期
       snxq_ljtq  TYPE zzpiccl1a-hsl, "首年续期
       xnxq_ljtq  TYPE zzpiccl1a-hsl, "续年续期同期
       sum_ljtq   TYPE zzpiccl1a-hsl, "累计同期合计
       dj_dq      TYPE zzpiccl1a-hsl,
       snsq_dq    TYPE zzpiccl1a-hsl,
       snxq_dq    TYPE zzpiccl1a-hsl,
       xnxq_dq    TYPE zzpiccl1a-hsl,
       sum_dq     TYPE zzpiccl1a-hsl,
       dj_dqtq    TYPE zzpiccl1a-hsl,
       snsq_dqtq  TYPE zzpiccl1a-hsl,
       snxq_dqtq  TYPE zzpiccl1a-hsl,
       xnxq_dqtq  TYPE zzpiccl1a-hsl,
       sum_dqtq   TYPE zzpiccl1a-hsl,
       END OF gw_data.
DATA : gt_data LIKE TABLE OF gw_data.

DATA : BEGIN OF gw_comp,
       bukrs      TYPE t001-bukrs,
       butxt      TYPE t001-butxt,
       rzzxz      TYPE zzpiccl1a-rzzxz,"险种
       bezek      TYPE t25a2-bezek,    "险种描述
       dj_lj      TYPE zzpiccl1a-hsl, "趸交累计
       snsq_lj    TYPE zzpiccl1a-hsl, "首年首期累计
       snxq_lj    TYPE zzpiccl1a-hsl, "首年续期累计
       xnxq_lj    TYPE zzpiccl1a-hsl, "续年续期累计
       sum_lj     TYPE zzpiccl1a-hsl, "累计汇总
       dj_tq      TYPE zzpiccl1a-hsl, "趸交同期
       snsq_ljtq  TYPE zzpiccl1a-hsl, "首年续期
       snxq_ljtq  TYPE zzpiccl1a-hsl, "首年续期
       xnxq_ljtq  TYPE zzpiccl1a-hsl, "续年续期同期
       sum_ljtq   TYPE zzpiccl1a-hsl, "累计同期合计
       dj_dq      TYPE zzpiccl1a-hsl,
       snsq_dq    TYPE zzpiccl1a-hsl,
       snxq_dq    TYPE zzpiccl1a-hsl,
       xnxq_dq    TYPE zzpiccl1a-hsl,
       sum_dq     TYPE zzpiccl1a-hsl,
       dj_dqtq    TYPE zzpiccl1a-hsl,
       snsq_dqtq  TYPE zzpiccl1a-hsl,
       snxq_dqtq  TYPE zzpiccl1a-hsl,
       xnxq_dqtq  TYPE zzpiccl1a-hsl,
       sum_dqtq   TYPE zzpiccl1a-hsl,
       END OF gw_comp.
DATA : gt_comp LIKE TABLE OF gw_comp.

"辅助核算表
DATA:gt_l1t TYPE TABLE OF zzpiccl1t,
     gw_l1t TYPE zzpiccl1t,
     gt_l1a_tmp TYPE TABLE OF zzpiccl1a.

*-->辅助字段定义
DATA : ok_code TYPE sy-ucomm,
       g_year  TYPE gjahr.

*-->选择屏幕定义
SELECTION-SCREEN BEGIN OF BLOCK blc_01 WITH FRAME TITLE text-001.
PARAMETERS:p_gjahr TYPE zzpiccl1a-ryear OBLIGATORY DEFAULT sy-datum+0(4).
PARAMETERS:p_monat TYPE zzpiccl1a-poper OBLIGATORY DEFAULT sy-datum+4(2).
SELECTION-SCREEN END OF BLOCK blc_01.


INITIALIZATION.

  "为参数赋值,此处对应事务码OAOR上传的文档信息
  gv_classname = 'HRFPM_EXCEL_STANDARD'.
  gv_classtype = 'OT'.
  gv_objectkey = 'ZFI0060'.

START-OF-SELECTION.

  PERFORM frm_get_data.

  PERFORM frm_deal_data.

  CALL SCREEN 100.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form frm_get_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_get_data.


  g_year = p_gjahr - 1.

  SELECT *
    INTO TABLE gt_l1t
    FROM zzpiccl1t
    WHERE ( ryear = p_gjahr OR ryear = g_year )
*      AND poper = p_monat
      AND rldnr = 'Z1'
      AND rbukrs BETWEEN '1000' AND '9AZZ'
      AND racct IN ('4101010000','4101020000','4101030000','4101040000',
                    '7411010000','7411020000','7411030000','7411040000').

ENDFORM.                    "frm_get_data
*&---------------------------------------------------------------------*
*& Form frm_deal_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_deal_data.

  DATA:lt_t01 TYPE TABLE OF t001,
       ls_t01 TYPE t001,
       lt_a2 TYPE HASHED TABLE OF t25a2 WITH UNIQUE KEY ww002,
       ls_a2 TYPE t25a2,
       lt_a6 TYPE HASHED TABLE OF t25a6 WITH UNIQUE KEY ww006,
       ls_a6 TYPE t25a6,
       lt_a8 TYPE HASHED TABLE OF t25a8 WITH UNIQUE KEY ww008,
       ls_a8 TYPE t25a8,
       lt_a1 TYPE HASHED TABLE OF t25a1 WITH UNIQUE KEY ww001,
       ls_a1 TYPE t25a1,
       lt_a5 TYPE HASHED TABLE OF t25a5 WITH UNIQUE KEY ww005,
       ls_a5 TYPE t25a5,
       "引申规则
       lt_k9 TYPE TABLE OF k9redq2000004,
       ls_k9 TYPE k9redq2000004,
       l_flag TYPE c,
       l_fname TYPE char255,
       l_str  TYPE n LENGTH 2.

  FIELD-SYMBOLS:<fs> TYPE any.

  "险种描述
  SELECT * INTO TABLE lt_t01 FROM t001 WHERE spras = '1' AND ktopl = 'PICL'.
  SELECT * INTO TABLE lt_a2 FROM t25a2 WHERE spras = '1'.
  SELECT * INTO TABLE lt_a6 FROM t25a6 WHERE spras = '1'.
  SELECT * INTO TABLE lt_a8 FROM t25a8 WHERE spras = '1'.
  SELECT * INTO TABLE lt_a1 FROM t25a1 WHERE spras = '1'.
  SELECT * INTO TABLE lt_k9 FROM k9redq2000004.
  SELECT * INTO TABLE lt_a5 FROM t25a5 WHERE spras = '1'.

  LOOP AT gt_l1t INTO gw_l1t.

    CLEAR:gw_data,
          l_flag,
          l_str.

    "反记账标识
    CASE gw_l1t-racct+0(1).
      WHEN '4'.
        gw_data-xnegp  = ''.
      WHEN '7'.
        gw_data-xnegp  = 'X'.
    ENDCASE.

    "险种
    gw_data-rzzxz = gw_l1t-rzzxz.

    "险种描述
    READ TABLE lt_a2 INTO ls_a2 WITH KEY ww002 = gw_l1t-rzzxz.
    IF sy-subrc EQ 0.
      gw_data-bezek = ls_a2-bezek.
    ENDIF.

    "险类2
    READ TABLE lt_a6 INTO ls_a6 WITH KEY ww006 = gw_l1t-rzzxl2.
    IF sy-subrc EQ 0.
      gw_data-xl2 = ls_a6-bezek.
    ENDIF.

    "长短险
    LOOP AT lt_k9 INTO ls_k9 WHERE ( sour1_from LE gw_l1t-rzzxz
                                     AND sour1_to GE gw_l1t-rzzxz
                                   ).
      IF sy-subrc EQ 0.
        l_flag = 'X'.
        EXIT.
      ENDIF.
    ENDLOOP.

    IF l_flag = 'X'.
      READ TABLE lt_a8 INTO ls_a8 WITH KEY ww008 = ls_k9-target1.
      IF sy-subrc EQ 0.
        gw_data-ls = ls_a8-bezek.
      ENDIF.
    ENDIF.

    "系列
    READ TABLE lt_a1 INTO ls_a1 WITH KEY ww001 = gw_l1t-rzzxl.
    IF sy-subrc EQ 0.
      gw_data-xl = ls_a1-bezek.
    ENDIF.

    "险类1
    READ TABLE lt_a5 INTO ls_a5 WITH KEY ww005 = gw_l1t-rzzxl1.
    IF sy-subrc EQ 0.
      gw_data-xl1 = ls_a5-bezek.
    ENDIF.

    "处理当期累计的数据
    DO p_monat TIMES.
      l_str = sy-index.
      CONCATENATE 'GW_L1T-HSL' l_str INTO l_fname.
      ASSIGN (l_fname)  TO <fs>.
      IF <fs> IS ASSIGNED.
        "趸缴当年累计
        CASE gw_l1t-racct.
            "趸缴
          WHEN '4101010000' OR '7411010000'.
            IF gw_l1t-ryear = p_gjahr.
              ADD <fs> TO gw_data-dj_lj.
            ELSEIF gw_l1t-ryear = g_year.
              ADD <fs> TO gw_data-dj_tq.
            ENDIF.
            "首年首期累计
          WHEN '4101020000' OR '7411020000'.
            IF gw_l1t-ryear = p_gjahr.
              ADD <fs> TO gw_data-snsq_lj.
            ELSEIF gw_l1t-ryear = g_year.
              ADD <fs> TO gw_data-snsq_ljtq.
            ENDIF.
          WHEN '4101030000' OR '7411030000'.
            IF gw_l1t-ryear = p_gjahr.
              ADD <fs> TO gw_data-snxq_lj.
            ELSEIF gw_l1t-ryear = g_year.
              ADD <fs> TO gw_data-snxq_ljtq.
            ENDIF.
          WHEN '4101040000' OR '7411040000'.
            IF gw_l1t-ryear = p_gjahr.
              ADD <fs> TO gw_data-xnxq_lj.
            ELSEIF gw_l1t-ryear = g_year.
              ADD <fs> TO gw_data-xnxq_ljtq.
            ENDIF.
        ENDCASE.
      ENDIF.
    ENDDO.

    "处理档期数据
    CLEAR:l_str,l_fname.
    CONCATENATE 'GW_L1T-HSL' p_monat+1(2) INTO l_fname.
    ASSIGN (l_fname)  TO <fs>.
    IF <fs> IS ASSIGNED.
      "趸缴当年累计
      CASE gw_l1t-racct.
          "趸缴
        WHEN '4101010000' OR '7411010000'.
          IF gw_l1t-ryear = p_gjahr.
            ADD <fs> TO gw_data-dj_dq.
          ELSEIF gw_l1t-ryear = g_year.
            ADD <fs> TO gw_data-dj_dqtq.
          ENDIF.
          "首年首期累计
        WHEN '4101020000' OR '7411020000'.
          IF gw_l1t-ryear = p_gjahr.
            ADD <fs> TO gw_data-snsq_dq.
          ELSEIF gw_l1t-ryear = g_year.
            ADD <fs> TO gw_data-snsq_dqtq.
          ENDIF.
        WHEN '4101030000' OR '7411030000'.
          IF gw_l1t-ryear = p_gjahr.
            ADD <fs> TO gw_data-snxq_dq.
          ELSEIF gw_l1t-ryear = g_year.
            ADD <fs> TO gw_data-snxq_dqtq.
          ENDIF.
        WHEN '4101040000' OR '7411040000'.
          IF gw_l1t-ryear = p_gjahr.
            ADD <fs> TO gw_data-xnxq_dq.
          ELSEIF gw_l1t-ryear = g_year.
            ADD <fs> TO gw_data-xnxq_dqtq.
          ENDIF.
      ENDCASE.
    ENDIF.

    "处理各种合计
    gw_data-sum_lj   = gw_data-dj_lj + gw_data-snsq_lj + gw_data-snxq_lj + gw_data-xnxq_lj.
    gw_data-sum_ljtq = gw_data-dj_tq + gw_data-snsq_ljtq + gw_data-snxq_ljtq + gw_data-xnxq_ljtq.
    gw_data-sum_dq   = gw_data-dj_dq + gw_data-snsq_dq   + gw_data-snxq_dq   + gw_data-xnxq_dq.
    gw_data-sum_dqtq = gw_data-dj_dqtq + gw_data-snsq_dqtq + gw_data-snxq_dqtq + gw_data-xnxq_dqtq.

    COLLECT gw_data INTO gt_data.

    MOVE-CORRESPONDING gw_data TO gw_comp.
    gw_comp-bukrs = gw_l1t-rbukrs.
    gw_comp-bukrs+2(2) = '00'.

    READ TABLE lt_t01 INTO ls_t01 WITH KEY bukrs = gw_comp-bukrs.
    IF sy-subrc EQ 0.
      gw_comp-butxt = ls_t01-butxt.
    ENDIF.

    COLLECT gw_comp INTO gt_comp.
  ENDLOOP.


  SORT gt_data BY xnegp rzzxz xl2 ls xl xl1.
  SORT gt_comp BY bukrs rzzxz.

ENDFORM.                    "frm_deal_data
*&---------------------------------------------------------------------*
*& Module STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
  SET PF-STATUS '100'.
  SET TITLEBAR '100'.
  PERFORM frm_excel_output.
ENDMODULE.                    "status_0100 OUTPUT
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE user_command_0100 INPUT.

  CASE ok_code.
    WHEN '&F03' OR '&F12' OR '&F15'.
      "退出前进行对象的释放
      PERFORM release_objects.
      LEAVE TO SCREEN 0.
  ENDCASE.

ENDMODULE.                    "user_command_0100 INPUT
*&---------------------------------------------------------------------*
*& Form main
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM frm_excel_output .

  DATA: gv_available TYPE i.
  DATA: gv_row       TYPE i.  "计算内表有多少行
  DATA: gv_col       TYPE i.  "计算内表有多少列
  DATA: row_index    TYPE i.  "数据写入时的行
  DATA: gr_container type ref to cl_gui_container.

  DATA: lv_row     TYPE i,
        lv_col     TYPE i,
        lv_top     TYPE i,
        lv_left    TYPE i,
        lv_rows     TYPE i,
        lv_index   TYPE i,
        lv_version TYPE string.

  DATA:cl_descr TYPE REF TO cl_abap_structdescr.

  DEFINE add_content.
    gs_content-row    = &1.
    gs_content-column = &2.
    gs_content-value  = &3.
    append gs_content to gt_contents.
    clear gs_content.
  END-OF-DEFINITION.


    CREATE OBJECT gr_splitter
      EXPORTING
        parent  = cl_gui_container=>screen0
        rows    = 1
        columns = 1.

    CALL METHOD gr_splitter->set_border
      EXPORTING
        border = cl_gui_cfw=>false.

    gr_container = gr_splitter->get_container( row = 1 column = 1 ).



*-->创建容器 create container control
    CALL METHOD c_oi_container_control_creator=>get_container_control
      IMPORTING
        control = g_control.

*-->初始化
    CALL METHOD g_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                   = gr_container.

*-->url获取I
    CREATE OBJECT g_bds_documents.
    CALL METHOD cl_bds_document_set=>get_info
      EXPORTING
        classname  = gv_classname  "initialization位置下定义
        classtype  = gv_classtype  "initialization位置下定义
        object_key = gv_objectkey  "initialization位置下定义
      CHANGING
        components = gv_doc_components
        signature  = gv_doc_signature.

    CALL METHOD cl_bds_document_set=>get_with_url
      EXPORTING
        classname  = gv_classname
        classtype  = gv_classtype
        object_key = gv_objectkey
      CHANGING
        uris       = gt_bds_uris
        signature  = gv_doc_signature.

    FREE g_bds_documents.

    "读取
    READ TABLE gt_bds_uris INTO gs_bds_url INDEX 1.
    gv_template_url = gs_bds_url-uri.


*-->打开上传的excel文档
    "
    CALL METHOD g_control->get_document_proxy
      EXPORTING
        document_type      = 'Excel.Sheet'
        no_flush           = 'X'
        register_container = 'X'
      IMPORTING
        document_proxy     = g_document.

    CALL METHOD g_document->open_document
      EXPORTING
        open_inplace = 'X'
        document_url = gv_template_url.

    CALL METHOD g_document->has_spreadsheet_interface
      EXPORTING
        no_flush     = 'X'
      IMPORTING
        is_available = gv_available.

    CALL METHOD g_document->get_spreadsheet_interface
      EXPORTING
        no_flush        = 'X'
      IMPORTING
        sheet_interface = g_spreadsheet.

    "选择展示的sheet页
    CALL METHOD g_spreadsheet->select_sheet
      EXPORTING
        name     = '险种维度'
        no_flush = 'X'.

*-->往excel中写入数据
    "检查内表是否有数据
    CHECK gt_data IS NOT INITIAL.

    "获取内表的行数和列数
    PERFORM frm_get_line_col USING gv_row gv_col.

    CALL METHOD g_spreadsheet->insert_range_dim
      EXPORTING
        name     = 'cell'
        no_flush = 'X'
        top      = 3        "从第几行开始
        left     = 1        "从第几列考试
        rows     = gv_row   "写几行
        columns  = gv_col.  "写几列

    "根据行列填充ranges
    CLEAR :gs_range,gt_ranges.
    gs_range-name = 'cell'.
    gs_range-rows = gv_row.
    gs_range-columns = gv_col.
    gs_range-code = 4.
    APPEND gs_range TO gt_ranges.

    "批量的把数据写入
    CLEAR row_index .
    row_index = '1'.

    FIELD-SYMBOLS:<fs> TYPE any.

    "把值精确到每一行每一列的单元格
    LOOP AT gt_data INTO gw_data.
      CLEAR gs_content.
      DO gv_col TIMES.
        ASSIGN COMPONENT sy-index OF STRUCTURE gw_data TO <fs>.
        add_content row_index sy-index  <fs>.
      ENDDO.
      row_index = row_index + 1.
    ENDLOOP.


    CALL METHOD g_spreadsheet->set_ranges_data
      EXPORTING
        ranges   = gt_ranges
        contents = gt_contents
        no_flush = 'X'.


    "处理公司维度
    CLEAR:gv_row,gv_col,gt_ranges,gt_contents.

    CALL METHOD g_spreadsheet->select_sheet
      EXPORTING
        name     = '公司维度'
        no_flush = 'X'.


    CHECK gt_comp IS NOT INITIAL.


    "获取行数
    lv_row = lines( gt_comp ).


    "获取列数
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE gw_comp TO <fs>.
      IF sy-subrc IS INITIAL.
        lv_col = lv_col + 1.
      ELSE.
        EXIT.
      ENDIF.
    ENDDO.

    "批量的把数据写入
    CLEAR:row_index,
          lv_top.


    "把值精确到每一行每一列的单元格
    LOOP AT gt_comp INTO gw_comp.

      CLEAR:gs_content,
            gt_ranges[].

      row_index = row_index + 1.

      DO gv_col TIMES.
        ASSIGN COMPONENT sy-index OF STRUCTURE gw_comp TO <fs>.
        add_content row_index sy-index  <fs>.
      ENDDO.

      IF row_index EQ 9000.
        lv_top = sy-tabix + 1.
        PERFORM frm_fill_ranges_data USING lv_top '1' '9000' lv_col.
        CLEAR row_index.
      ENDIF.

      AT LAST.
        IF lv_row < 9000.
          lv_top = sy-tabix + 1.
          PERFORM frm_fill_ranges_data USING '2' '1' row_index lv_col.
        ELSE.
          lv_top = row_index + 1.
          PERFORM frm_fill_ranges_data USING lv_top '1' row_index lv_col.
        ENDIF.
      ENDAT.

    ENDLOOP.

    lv_index = lv_index + lv_rows.

    IF lv_row = 0.
      EXIT.
    ENDIF.

  ENDFORM.                    "frm_excel_output
*&---------------------------------------------------------------------*
*& Form frm_get_line_col
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> P_
*&      --> GV_LINE
*&      --> GV_COL
*&---------------------------------------------------------------------*
FORM frm_get_line_col  USING p_row p_col.
  CLEAR : p_row,p_col.
  FIELD-SYMBOLS <fs>.
  "获取行数
  p_row = lines( gt_data ).
  "获取列数
  DO.
    ASSIGN COMPONENT sy-index OF STRUCTURE gw_data TO <fs>.
    IF sy-subrc IS INITIAL.
      p_col = p_col + 1.
    ELSE.
      EXIT.
    ENDIF.
  ENDDO.
ENDFORM.                    "frm_get_line_col
*&---------------------------------------------------------------------*
*& Form release_objects
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM release_objects .

  IF NOT g_document IS INITIAL.
    CALL METHOD g_document->close_document.
    FREE g_document.
  ENDIF.

  IF NOT g_control IS INITIAL.
    CALL METHOD g_control->destroy_control.
    FREE g_control.
  ENDIF.

  IF g_container IS NOT INITIAL.
    CALL METHOD g_container->free.
  ENDIF.


ENDFORM.                    "release_objects
*&---------------------------------------------------------------------*
*& Form frm_write_single
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*&      --> P_
*&      --> P_
*&      --> SY_UNAME
*&---------------------------------------------------------------------*
FORM frm_write_single  USING  p_row p_col p_value.
  DATA : lt_ranges   TYPE soi_range_list,
         ls_ranges   TYPE soi_range_item,
         lt_contents TYPE soi_generic_table,
         ls_contents TYPE soi_generic_item.

  CLEAR :lt_ranges,ls_ranges,lt_contents,ls_contents.
  "ranges中只存储一行一列
  ls_ranges-name = 'cell' .
  ls_ranges-columns = 1.
  ls_ranges-rows = 1.
  ls_ranges-code = 4.
  APPEND ls_ranges TO lt_ranges.
  "写入数据也只存一个值
  ls_contents-column = 1.
  ls_contents-row = 1.
  ls_contents-value = p_value.
  APPEND ls_contents TO lt_contents.

  "每次只写一行一列
  CALL METHOD g_spreadsheet->insert_range_dim
    EXPORTING
      name     = 'cell'
      no_flush = 'X'
      top      = p_row
      left     = p_col
      rows     = 1
      columns  = 1.


  CALL METHOD g_spreadsheet->set_ranges_data
    EXPORTING
      ranges   = lt_ranges
      contents = lt_contents
      no_flush = 'X'.

ENDFORM.                    "frm_write_single
*&---------------------------------------------------------------------*
*&      Form  FRM_FILL_RANGES_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_LV_TOP  text
*      -->P_LV_LEFT  text
*      -->P_LV_ROW  text
*      -->P_LV_COL  text
*----------------------------------------------------------------------*
FORM frm_fill_ranges_data  USING    pv_top
                                    pv_left
                                    pv_row
                                    pv_col.

  CALL METHOD g_spreadsheet->insert_range_dim
    EXPORTING
      name     = 'cell'
      no_flush = 'X'
      top      = pv_top   "从第几行开始
      left     = pv_left  "从第几列开始
      rows     = pv_row   "写几行
      columns  = pv_col.  "写几列


  "根据行列填充ranges
  CLEAR :gs_range,gt_ranges.
  gs_range-name    = 'cell'.
  gs_range-rows    = pv_row.
  gs_range-columns = pv_col.
  gs_range-code = 4.
  APPEND gs_range TO gt_ranges.


  CALL METHOD g_spreadsheet->set_ranges_data
    EXPORTING
      ranges   = gt_ranges
      contents = gt_contents
      no_flush = 'X'.


ENDFORM.                    " FRM_FILL_RANGES_DATA

三、注意事项

由于soi_range_item的行只有4位，意味者只能一次性只能写入9999行。如果要写入的数据大于9999行，需要进行拆分分包写入。insert_range和set_ranges需要配合使用，正确确认好每次写入的

top（起始行）以及row(写入)行数是代码关键地方。上文给出的代码不是最终版，需要微调才能跑出来。

(注意用spliter创建容器的方法可以使EXCEL容器自适应屏幕，实现全屏效果。不需要单独创建屏幕容器。)