用户提出的报表,是用EXCLE显示的,有许多特殊格式,比如,加粗,大小字体等。
普通的ALV报表输出并不能满足用户的要求,那么只能用ALV与EXCLE的集成技术。
目前已知的技术有两种,一种是OLE技术,用SMW0上传模板,然后填写数据,多数用来做批导,
一种是DOI技术,可用Tcode OAOR去上传模板,然后对每一个单元格进行修改,
客户需求的格式如下:
我最终使用的就是第二种,分享一下步骤:
1.创建一个OO ALV
2.上传EXLE模板
用事务码OAOR将Excel文档上传。输入OAOR,进入下面的界面,输入后面程序需要使用的几个重要标识:class name, class type和object key。class name选择SAP提供的HRFPM_EXCEL_STANDARD就可以了。如果没有,请用事物码 SBDSV1 来定义。object key建议使用excel文档的文件名,以便查找。
F8之后进入点击Create table template
OK,现在文档已经导入了。我们可以在OAOR界面中,显示文档、文档的详细信息(detail info.)等。
3.获取模板的信息
操作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
打开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
数据写入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参数设定写入的内容
对象销毁
在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.
完整代码Demo:
*&---------------------------------------------------------------------*
*& 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