ABAP DOI详解(2)

我们之所以用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

最后编辑于：2017.12.05 02:19:18

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,732评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,496评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,264评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,807评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,806评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,675评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,029评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,683评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,704评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,666评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,773评论 1赞 332
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,413评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,016评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,978评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,204评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,083评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,503评论 2赞 343

ABAP DOI详解(2)

创建Dialog Screen

上传Excel文档

获取模板文档的信息

打开Excel文档

将数据写入Excel

对象销毁

完整代码

推荐阅读更多精彩内容