我们之所以用Excel输出，主要是想用Excel在显示中的优势，所以最常见的做法，往往调用Excel模板文件来实现。这一篇，我们就来操作Excel模板文件，另外，excel文档放在dialog screen中显示。

创建Dialog Screen

新建一个screen, scren number为100，在screen中添加一个custom control, name属性为CONTAINER1，界面如下：

Paste_Image.png

新建一个GUI Status，编号为100，在gui status中定义function code为EXIT，functional type为E的功能码（function key）。

Paste_Image.png

切换到screen的flow logic，增加一个PAI事件，然后编写代码，用于处理屏幕的退出：

module exit_program input.
  save_ok = ok_code.
  clear ok_code.

  if save_ok = 'EXIT'.
    leave program.
  endif.

endmodule.     

上传Excel文档

用事务码OAOR将Excel文档上传。输入OAOR，进入下面的界面，输入后面程序需要使用的几个重要标识：class name, class type和object key。class name选择SAP提供的HRFPM_EXCEL_STANDARD就可以了。如果没有，请用事物码SBDSV1进行定于。object key建议使用excel文档的文件名，以便查找。

OAOR

点击“执行”（F8)按钮，进入下面的界面

Paste_Image.png

从左下部分的doc type中，选择table template，右键菜单，导入文档。导入模板文档doitest.xls。

Paste_Image.png

OK，现在文档已经导入了。我们可以在OAOR界面中，显示文档、文档的详细信息(detail info.）等。

获取模板文档的信息

操作excel模板文档，使用cl_bds_document_set类，这个类的get_with_url方法获取文档的url。首先定义一些global变量：

* business document system
data: gr_bds_documents type ref to cl_bds_document_set,
      g_classname type sbdst_classname,
      g_classtype type sbdst_classtype,
      g_objectkey type sbdst_object_key,
      g_doc_components type sbdst_components,
      g_doc_signature type sbdst_signature.

* template url
data: gt_bds_uris type sbdst_uri,
      gs_bds_url like line of gt_bds_uris,
      g_template_url(256) type c.

获取excel template文档的url:

form get_template_url.

  create object gr_bds_documents.

  call method cl_bds_document_set=>get_info
    exporting
      classname  = g_classname
      classtype  = g_classtype
      object_key = g_objectkey
    changing
      components = g_doc_components
      signature  = g_doc_signature.

  call method cl_bds_document_set=>get_with_url
    exporting
      classname  = g_classname
      classtype  = g_classtype
      object_key = g_objectkey
    changing
      uris       = gt_bds_uris
      signature  = g_doc_signature.

  free gr_bds_documents.

  read table gt_bds_uris into gs_bds_url index 1.
  g_template_url = gs_bds_url-uri.
endform.                    "get_template_url

cl_bds_document_set的静态方法get_with_url获取excel template的url。数据存放在内表中，读取后放在global变量g_template_url里面。

打开Excel文档

根据获取的excel template的url，打开excel文档：

form open_excel_doc.
  call method gr_control->get_document_proxy
    exporting
      document_type      = 'Excel.Sheet'
      no_flush           = 'X'
      register_container = 'X'
    importing
      document_proxy     = gr_document.

  call method gr_document->open_document
    exporting
      open_inplace = 'X'
      document_url = g_template_url.

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

  call method gr_document->get_spreadsheet_interface
    exporting
      no_flush        = 'X'
    importing
      sheet_interface = gr_spreadsheet.

  call method gr_spreadsheet->select_sheet
    exporting
      name     = 'Sheet1'
      no_flush = 'X'.
endform.       

将数据写入Excel

我们从spfli表中获取所有航班的信息：

* output internale table
data: begin of gs_spfli,
        carrid like spfli-carrid,
        connid like spfli-connid,
        cityfrom like spfli-cityfrom,
        cityto like spfli-cityto,
      end of gs_spfli.
data: gt_spfli like standard table of gs_spfli.

form get_data.
  select * from spfli
    into corresponding fields of table gt_spfli.
endform.         

数据写入Excel，可以使用批量的方式或者逐个单元格写入的方式。批量写入的方式效率高，逐个单元格写入的方式比较灵活，在程序中都能用到。将数据写入excel需要使用i_oi_spreadsheet接口实例的两个方法：

• insert_range_dim方法，定义一个范围(range)，设定range的名称、位置和大小。比如下面的代码，定义一个名称为cell, 共line_count行、4列的range，从第2行第1列开始。

  call method gr_spreadsheet->insert_range_dim
    exporting
      name     = 'cell'
      no_flush = 'X'
      top      = 2
      left     = 1
      rows     = line_count
      columns  = 4.

• set_range_data方法，写入数据到range，写入的时候，ranges参数设定range的名称和大小, contents参数设定写入的内容。OK，假设我们将要输出的数据在内表gt_spfli中是这样的：

Paste_Image.png

我们想要在excel中按同样的方式输出。我们定义ranges参数的内表如下：

Paste_Image.png

上图表示我们将要输出的数据，名称为cell，4列5行。

定义contents参数的内表如下，确定每一个单元个的内容：

Paste_Image.png

set_range_data方法：

  call method gr_spreadsheet->set_ranges_data
    exporting
      ranges   = gt_ranges
      contents = gt_contents
      no_flush = 'X'.

对象销毁

在PAI的exit-command事件中处理对spreadsheet, control和container等对象的销毁。网上有博客认为DOI没有必要创建screen，而我觉得screen的好处就是可以很好地处理对象销毁。

form release_objects.
  if not gr_document is initial.
    call method gr_document->close_document.
    free gr_document.
  endif.

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

  if gr_container is not initial.
    call method gr_container->free.
  endif.
endform.  

exit-command事件的代码变成这个样子

module exit_program input.
  save_ok = ok_code.
  clear ok_code.

  if save_ok = 'EXIT'.
    perform release_objects.
    leave program.
  endif.
endmodule.   

完整代码

*&---------------------------------------------------------------------*
*& Report  ZDOI_DOC_TEMPLATE
*&
*&---------------------------------------------------------------------*
*&
*& Written by Stone Wang
*& Version 1.0 on Dec 16, 2016
*&---------------------------------------------------------------------*

report  zdoi_doc_template.

data: gr_custom_container type ref to cl_gui_custom_container.
data: gr_container type ref to cl_gui_container,
      gr_control type ref to i_oi_container_control,
      gr_document type ref to i_oi_document_proxy,
      gr_spreadsheet type ref to i_oi_spreadsheet.

* business document system
data: gr_bds_documents type ref to cl_bds_document_set,
      g_classname type sbdst_classname,
      g_classtype type sbdst_classtype,
      g_objectkey type sbdst_object_key,
      g_doc_components type sbdst_components,
      g_doc_signature type sbdst_signature.

* template url
data: gt_bds_uris type sbdst_uri,
      gs_bds_url like line of gt_bds_uris,
      g_template_url(256) type c.

data: ok_code type sy-ucomm,
      save_ok like ok_code.

* output internale table
data: begin of gs_spfli,
        carrid like spfli-carrid,
        connid like spfli-connid,
        cityfrom like spfli-cityfrom,
        cityto like spfli-cityto,
      end of gs_spfli.
data: gt_spfli like standard table of gs_spfli.

* Required for writing data to Excel
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.

initialization.
  g_classname = 'HRFPM_EXCEL_STANDARD'.
  g_classtype = 'OT'.
  g_objectkey = 'DOITEST'.

start-of-selection.
  perform get_data.
  call screen 100.

  define write_content_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.

*&---------------------------------------------------------------------*
*&      Form  get_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form get_data.
  select * from spfli
    into corresponding fields of table gt_spfli up to 5 rows.
endform.                    "get_data

*&---------------------------------------------------------------------*
*&      Form  get_container
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form get_container.
  create object gr_custom_container
    exporting
      container_name = 'CONTAINER1'.
endform.                    "get_container

*&---------------------------------------------------------------------*
*&      Form  create_container_control
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form create_container_control.
* create container control
  call method c_oi_container_control_creator=>get_container_control
    importing
      control = gr_control.

* initialize control
  call method gr_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 by Stone Wang'
      parent                   = gr_custom_container.
endform.                    "create_container_control


*&---------------------------------------------------------------------*
*&      Form  get_template_url
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form get_template_url.

  create object gr_bds_documents.

  call method cl_bds_document_set=>get_info
    exporting
      classname  = g_classname
      classtype  = g_classtype
      object_key = g_objectkey
    changing
      components = g_doc_components
      signature  = g_doc_signature.

  call method cl_bds_document_set=>get_with_url
    exporting
      classname  = g_classname
      classtype  = g_classtype
      object_key = g_objectkey
    changing
      uris       = gt_bds_uris
      signature  = g_doc_signature.

  free gr_bds_documents.

  read table gt_bds_uris into gs_bds_url index 1.
  g_template_url = gs_bds_url-uri.
endform.                    "get_template_url

*&---------------------------------------------------------------------*
*&      Form  open_excel_doc
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form open_excel_doc.
  call method gr_control->get_document_proxy
    exporting
      document_type      = 'Excel.Sheet'
      no_flush           = 'X'
      register_container = 'X'
    importing
      document_proxy     = gr_document.

  call method gr_document->open_document
    exporting
      open_inplace = 'X'
      document_url = g_template_url.

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

  call method gr_document->get_spreadsheet_interface
    exporting
      no_flush        = 'X'
    importing
      sheet_interface = gr_spreadsheet.

  call method gr_spreadsheet->select_sheet
    exporting
      name     = 'Sheet1'
      no_flush = 'X'.
endform.                    "open_excel_doc

*&---------------------------------------------------------------------*
*&      Form  fill_ranges
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form fill_ranges.
  data: line_count type i value 0,
        col_count type i value 0.

* 获取内表的行列数
  perform read_itab_structure using 'GT_SPFLI' line_count col_count.

* fill gt_ranges[]
  clear gs_range.
  clear gt_ranges[].
  gs_range-name = 'cell'.
  gs_range-rows = line_count.
  gs_range-columns = col_count.
  gs_range-code = 4.
  append gs_range to gt_ranges.
endform.                    "fill_ranges

*&---------------------------------------------------------------------*
*&      Form  fill_contents
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form fill_contents.
  data: row_index type i.
  row_index = 1.

  loop at gt_spfli into gs_spfli.
    clear gs_content.

    write_content_cell row_index 1 gs_spfli-carrid.
    write_content_cell row_index 2 gs_spfli-connid.
    write_content_cell row_index 3 gs_spfli-cityfrom.
    write_content_cell row_index 4 gs_spfli-cityto.

    row_index = row_index + 1.
  endloop.
endform.                    "fill_contents

*&---------------------------------------------------------------------*
*&      Form  read_itab_structure
*&---------------------------------------------------------------------*
*       get internal number of rows and number of columns of itab
*----------------------------------------------------------------------*
form read_itab_structure using p_tabname p_rowcount p_colcount.

  data: l_rowcount type i,
        l_colcount type i.

  field-symbols: <fs1>.
  data: ls_spfli like line of gt_spfli.

* Line count
  describe table gt_spfli lines l_rowcount.

* Row count
  do.
    assign component sy-index of structure ls_spfli to <fs1>.
    if sy-subrc is initial.
      l_colcount = l_colcount + 1.
    else.
      exit.
    endif.
  enddo.

  p_rowcount = l_rowcount.
  p_colcount = l_colcount.

endform.                    "read_itab_structure

*&---------------------------------------------------------------------*
*&      Form  write_data_to_excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form write_data_to_excel.
  data: line_count type i value 0,
        col_count type i value 0.

  check not gt_spfli is initial.

* 获取内表的行列数
  perform read_itab_structure using 'GT_SPFLI' line_count col_count.

  call method gr_spreadsheet->insert_range_dim
    exporting
      name     = 'cell'
      no_flush = 'X'
      top      = 2
      left     = 1
      rows     = line_count
      columns  = col_count.

* populate tow internal tables required for 'set_range_data'
  perform fill_ranges.
  perform fill_contents.

  call method gr_spreadsheet->set_ranges_data
    exporting
      ranges   = gt_ranges
      contents = gt_contents
      no_flush = 'X'.

endform.                    "write_data_to_excel

*&---------------------------------------------------------------------*
*&      Form  release_objects
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form release_objects.
  if not gr_document is initial.
    call method gr_document->close_document.
    free gr_document.
  endif.

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

  if gr_container is not initial.
    call method gr_container->free.
  endif.
endform.                    "release_objects


*&---------------------------------------------------------------------*
*&      Form  main
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
form main.
  perform get_container.
  perform create_container_control.
  perform get_template_url..
  perform open_excel_doc.
  perform write_data_to_excel.
endform.                    "main

*&---------------------------------------------------------------------*
*&      Module  exit_program  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
module exit_program input.
  save_ok = ok_code.
  clear ok_code.

  if save_ok = 'EXIT'.
    perform release_objects.
    leave program.
  endif.
endmodule.                 " exit_program  INPUT
*&---------------------------------------------------------------------*
*&      Module  status_0100  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
module status_0100 output.
  set pf-status '100'.
  perform main.
endmodule.                 " status_0100  OUTPUT

程序的界面如下。最主要的缺点是container的大小是固定的，后面我们看看怎么解决这个问题。

Paste_Image.png