导语:DOI是SAP与Office集成的一种技术,是早期OLE的升级版本,把Excel嵌套在程序当中进行展示,需要提前上传模板,在Excel模板中,可以事先设计好公式,在SAP将数据写入Excel中之后会自动用公式进行计算,对于习惯于用Excel的用户来说,从界面上来说比较舒适,链接【ABAP DOI模板上传】。
本次分享的是一个简单的DOI实现,展示销售订单的一些信息,由于本人比较懒,就不对每一个组件进行详细介绍了,直接贴代码了,代码里有详细的注释,包括定义,取数,实现过程,以及如何批量写数据给Excel,如何逐个单元格写数据给Excel,代码放到程序中,创建一个screen 100之后直接就可以使用,screen 100的参数在文章的最后。
代码如下
*&---------------------------------------------------------------------*
*& Report Z_JYH_DOI
*&---------------------------------------------------------------------*
*& 本程序是DOI的demo测试程序,用excel展示内表的数据
*&---------------------------------------------------------------------*
REPORT z_jyh_doi.
TABLES : ekko.
*-->控件及界面定义
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: 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_ekko,
ebeln TYPE ekko-ebeln,
bsart TYPE ekko-bsart,
aedat TYPE ekko-aedat,
lifnr TYPE ekko-lifnr,
END OF gw_ekko.
DATA : gt_ekko LIKE TABLE OF gw_ekko.
*-->辅助字段定义
DATA : ok_code TYPE sy-ucomm.
*-->选择屏幕定义
SELECTION-SCREEN BEGIN OF BLOCK blc_01 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS : s_ebeln FOR ekko-ebeln.
SELECTION-SCREEN END OF BLOCK blc_01.
INITIALIZATION.
"为参数赋值,此处对应事务码OAOR上传的文档信息
gv_classname = 'HRFPM_EXCEL_STANDARD'.
gv_classtype = 'OT'.
gv_objectkey = 'DOITEST'.
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 .
SELECT ebeln
bsart
aedat
lifnr
FROM ekko
INTO TABLE gt_ekko
WHERE ebeln IN s_ebeln.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_deal_data
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_deal_data .
ENDFORM.
*&---------------------------------------------------------------------*
*& Module STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE status_0100 OUTPUT.
SET PF-STATUS '100'.
SET TITLEBAR '100'.
PERFORM frm_excel_output.
ENDMODULE.
*&---------------------------------------------------------------------*
*& 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.
*&---------------------------------------------------------------------*
*& 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. "数据写入时的行
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.
IF g_cust_con IS INITIAL.
CREATE OBJECT g_cust_con
EXPORTING
container_name = gv_container.
ENDIF.
*-->创建容器 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 = 'JYH test doi'
parent = g_cust_con.
*-->url获取
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 = 'Sheet1'
no_flush = 'X'.
*-->往excel中写入数据
"检查内表是否有数据
CHECK gt_ekko 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'.
"把值精确到每一行每一列的单元格
LOOP AT gt_ekko INTO gw_ekko.
CLEAR gs_content.
add_content row_index 1 gw_ekko-ebeln.
add_content row_index 2 gw_ekko-bsart.
add_content row_index 3 gw_ekko-aedat.
add_content row_index 4 gw_ekko-lifnr.
row_index = row_index + 1.
ENDLOOP.
CALL METHOD g_spreadsheet->set_ranges_data
EXPORTING
ranges = gt_ranges
contents = gt_contents
no_flush = 'X'.
"把数据精确写入到某一行某一列
PERFORM frm_write_single USING '1' '7' sy-uname.
PERFORM frm_write_single USING '2' '7' sy-datum.
ENDFORM.
*&---------------------------------------------------------------------*
*& 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_ekko ).
"获取列数
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE gw_ekko TO <fs>.
IF sy-subrc IS INITIAL.
p_col = p_col + 1.
ELSE.
EXIT.
ENDIF.
ENDDO.
ENDFORM.
*&---------------------------------------------------------------------*
*& 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.
*&---------------------------------------------------------------------*
*& 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.
效果展示
SCREEN 100 参数
作者:小飞猪猪猪猪猪猪猪–CSDN