SAP ABAP EXCEL报表输出
*&---------------------------------------------------------------------*
*& Report YTEST_LJM_01
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT YTEST_LJM_01.
*&---------------------------------------------------------------------*
*& DEFINE INCLUDE
*&---------------------------------------------------------------------*
INCLUDE ytest_ljm_01_cmdoishare."DOI share
INCLUDE ytest_ljm_01_top."Top
INCLUDE ytest_ljm_01_f01."Form
INCLUDE ytest_ljm_01_srn."屏幕元素
*&---------------------------------------------------------------------*
* AT SELECTION-SCREEN
*&---------------------------------------------------------------------*
START-OF-SELECTION.
* 检查执行的操作是报表输出/维护数据
PERFORM FRM_CHECK_REQUIRED.
*&---------------------------------------------------------------------*
*& 包含 YTEST_LJM_01_CMDOISHARE
*&---------------------------------------------------------------------*
*----------------------------------------------------------------------*
* DEFINE TYPE POOL
*----------------------------------------------------------------------*
* DOI使用的Type pool
TYPE-POOLS:sbdst,
soi.
*----------------------------------------------------------------------*
* CLASS DECLARATION
*----------------------------------------------------------------------*
CLASS lc_excel_document DEFINITION DEFERRED.
CLASS cl_gui_cfw DEFINITION LOAD.
* DOI使用的变量
DATA: go_document TYPE REF TO lc_excel_document.
DATA: gv_doc_url TYPE bapiuri-uri.
DATA: gv_document_type TYPE soi_document_type VALUE soi_doctype_excel_chart.
DATA: go_control TYPE REF TO i_oi_container_control.
DATA: go_error TYPE REF TO i_oi_error.
DATA: go_container TYPE REF TO cl_gui_custom_container.
DATA: go_link_server TYPE REF TO i_oi_link_server.
DATA: go_bds_instance TYPE REF TO cl_bds_document_set.
DATA: gt_ranges TYPE soi_range_list.
DATA: gt_error_table TYPE TABLE OF REF TO i_oi_error.
DATA: gt_contents TYPE soi_generic_table.
DATA: gt_celltab TYPE soi_cell_table.
DATA: gwa_range LIKE LINE OF gt_ranges.
DATA: gwa_content LIKE LINE OF gt_contents.
DATA: gwa_celltab LIKE LINE OF gt_celltab.
DATA: gv_available TYPE i.
DATA: gv_classname TYPE sbdst_classname VALUE 'SOFFICEINTEGRATION',
gv_classtype TYPE sbdst_classtype VALUE 'OT',
gt_doc_uris TYPE sbdst_uri,
gt_doc_signature TYPE sbdst_signature,
gwa_doc_signature LIKE LINE OF gt_doc_signature,
gwa_doc_uris LIKE LINE OF gt_doc_uris.
DATA: go_save_error TYPE REF TO i_oi_error.
DATA: gv_retcode TYPE soi_ret_string .
DATA: gt_data_table TYPE sbdst_content,
gv_data_size TYPE i.
*----------------------------------------------------------------------*
* DEFINE LOCAL CLASS
*----------------------------------------------------------------------*
CLASS lc_excel_document DEFINITION.
PUBLIC SECTION.
DATA: proxy TYPE REF TO i_oi_document_proxy,
document_type TYPE soi_document_type,
data_table TYPE sbdst_content,
data_size TYPE i,
doc_url TYPE bapiuri-uri.
DATA: sheet_interface TYPE REF TO i_oi_spreadsheet.
* 构造器
METHODS: constructor
IMPORTING control TYPE REF TO i_oi_container_control
document_type TYPE soi_document_type.
* 关闭DOI文档时事件，询问是否保存
METHODS: on_close_document
FOR EVENT on_close_document OF i_oi_document_proxy
IMPORTING document_proxy has_changed.
* 打开DOI
METHODS: open_document_url
IMPORTING open_inplace TYPE c DEFAULT ' '
open_readonly TYPE c DEFAULT ' '
doc_url TYPE bapiuri-uri DEFAULT ' '
EXPORTING error TYPE REF TO i_oi_error.
* 关闭DOI文档
METHODS: close_document
IMPORTING do_save TYPE c DEFAULT ' '
VALUE(no_flush) TYPE c DEFAULT ' '
EXPORTING error TYPE REF TO i_oi_error.
PRIVATE SECTION.
DATA: control TYPE REF TO i_oi_container_control.
ENDCLASS. "c_excel_document DEFINITION
*----------------------------------------------------------------------*
* CLASS lc_excel_document IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS lc_excel_document IMPLEMENTATION.
METHOD: constructor.
me->control = control.
me->document_type = document_type.
ENDMETHOD. "constructor
METHOD open_document_url.
IF NOT proxy IS INITIAL.
CALL METHOD me->close_document.
ENDIF.
CALL METHOD control->get_document_proxy
EXPORTING
document_type = document_type
register_container = abap_true
IMPORTING
document_proxy = proxy
error = error.
IF error->error_code NE c_oi_errors=>ret_ok.
EXIT.
ENDIF.
me->doc_url = doc_url.
IF sy-tcode = 'ZFI027' OR sy-tcode = 'ZFI028' or sy-tcode = 'ZFI030' OR sy-tcode = 'ZFI039' OR sy-tcode = 'ZFI091' OR sy-tcode = 'ZFI099'.
CALL METHOD proxy->open_document
EXPORTING
document_url = doc_url
open_inplace = open_inplace
open_readonly = open_readonly
* PROTECT_DOCUMENT = ABAP_TRUE
IMPORTING
error = error.
ELSE.
CALL METHOD proxy->open_document
EXPORTING
document_url = doc_url
open_inplace = open_inplace
open_readonly = open_readonly
protect_document = abap_true
IMPORTING
error = error.
ENDIF.
* IF error->error_code EQ 'OPEN_DOCUMENT_FAILED'.
* LEAVE TO SCREEN 0.
* ENDIF.
IF error->error_code NE c_oi_errors=>ret_ok.
EXIT.
ENDIF.
CALL METHOD proxy->has_spreadsheet_interface
IMPORTING
is_available = gv_available
error = error.
IF gv_available EQ 1.
CALL METHOD proxy->get_spreadsheet_interface
IMPORTING
sheet_interface = sheet_interface
error = error.
SET HANDLER me->on_close_document FOR proxy.
ENDIF.
ENDMETHOD. "open_document_url
METHOD close_document.
DATA: is_closed TYPE i, has_changed TYPE i.
DATA: save_error TYPE REF TO i_oi_error.
IF NOT proxy IS INITIAL.
CALL METHOD proxy->is_destroyed
IMPORTING
ret_value = is_closed.
IF is_closed IS INITIAL.
CALL METHOD proxy->close_document
EXPORTING
do_save = do_save
IMPORTING
has_changed = has_changed
error = error.
IF error->error_code NE c_oi_errors=>ret_ok.
EXIT.
ENDIF.
ENDIF.
IF NOT has_changed IS INITIAL.
CALL METHOD proxy->save_document_to_table
EXPORTING
no_flush = abap_true
IMPORTING
error = save_error
CHANGING
document_table = data_table
document_size = data_size.
ENDIF.
CALL METHOD proxy->release_document
IMPORTING
error = error.
IF NOT save_error IS INITIAL.
IF save_error->error_code NE c_oi_errors=>ret_ok.
error = save_error.
ENDIF.
ENDIF.
SET HANDLER me->on_close_document FOR proxy ACTIVATION ' '.
ELSE.
CALL METHOD c_oi_errors=>create_error_for_retcode
EXPORTING
retcode = c_oi_errors=>ret_document_not_open
no_flush = ' '
IMPORTING
error = error.
ENDIF.
ENDMETHOD. "close_document
METHOD on_close_document.
DATA: answer, do_save.
IF has_changed EQ 1.
CALL FUNCTION 'POPUP_TO_CONFIRM'
EXPORTING
titlebar = 'Office Integration'
text_question = 'Save Document?'
display_cancel_button = ' '
IMPORTING
answer = answer.
IF answer EQ '1'.
do_save = 'X'.
ELSE.
do_save = ' '.
ENDIF.
CALL METHOD me->close_document
EXPORTING
do_save = do_save
IMPORTING
error = go_error.
CALL METHOD go_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
ENDMETHOD. "on_close_document
ENDCLASS. "lc_excel_document IMPLEMENTATION
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_SAVE
*&---------------------------------------------------------------------*
* text：保存excel到本地
*----------------------------------------------------------------------*
FORM frm_excel_save .
DATA:lv_retcode TYPE soi_ret_string.
IF NOT go_document IS INITIAL AND NOT go_document->proxy IS INITIAL.
* CALL METHOD go_document->proxy->execute_macro
* EXPORTING
* macro_string = 'Module1.unLock_hide'
* param_count = '0'
* IMPORTING
* error = go_error
* retcode = gv_retcode.
CALL METHOD go_document->proxy->save_as
EXPORTING
prompt_user = abap_true
IMPORTING
error = go_error
retcode = lv_retcode.
* CALL METHOD go_error->raise_message
* EXPORTING
* type = 'E'.
IF lv_retcode = 'OK'.
MESSAGE s000(mi_ts).
ENDIF.
ELSE.
MESSAGE s006(plmn_bo) DISPLAY LIKE 'E'.
ENDIF.
CALL METHOD cl_gui_cfw=>flush.
CALL METHOD cl_gui_cfw=>dispatch.
ENDFORM. " FRM_EXCEL_SAVE
*&---------------------------------------------------------------------*
*& Form FRM_EXIT
*&---------------------------------------------------------------------*
* text：退出DOI
*----------------------------------------------------------------------*
FORM frm_exit .
CALL METHOD cl_gui_cfw=>flush.
CALL METHOD cl_gui_cfw=>dispatch.
* 解锁excel工具栏和其他功能等
* CALL METHOD go_document->proxy->execute_macro
* EXPORTING
* macro_string = 'Module1.unLock_hide'
* param_count = '0'
* IMPORTING
* error = go_error
* retcode = gv_retcode.
CALL METHOD go_link_server->stop_link_server
IMPORTING
error = go_error
retcode = gv_retcode.
CALL METHOD go_document->proxy->close_document
EXPORTING
do_save = ' '
no_flush = ' '
IMPORTING
error = go_error
retcode = gv_retcode.
CALL METHOD go_control->release_all_documents
EXPORTING
no_flush = ' '
IMPORTING
error = go_error
retcode = gv_retcode.
go_control->destroy_control( ).
* perform frm_init_excel_var.
FREE: go_control,go_link_server,go_document,
go_error,go_link_server,go_bds_instance.
REFRESH: gt_contents, gt_doc_uris,gt_doc_signature.
* CALL METHOD go_container->free.
LEAVE TO SCREEN 0.
ENDFORM. " FRM_EXIT
*&---------------------------------------------------------------------*
*& Form FRM_EXCEL_PRINT
*&---------------------------------------------------------------------*
* text：调用excel打印功能直接打印
*----------------------------------------------------------------------*
FORM frm_excel_print .
DATA:lv_retcode TYPE soi_ret_string.
IF NOT go_document IS INITIAL AND
NOT go_document->proxy IS INITIAL.
CALL METHOD go_document->proxy->print_document
EXPORTING
prompt_user = abap_true
IMPORTING
error = go_error
retcode = lv_retcode.
CALL METHOD go_error->raise_message
EXPORTING
type = 'E'.
IF lv_retcode = 'OK'.
MESSAGE s039(sppf_media).
ENDIF.
ELSE.
MESSAGE s238(lf) DISPLAY LIKE 'E'.
ENDIF.
CALL METHOD cl_gui_cfw=>flush.
CALL METHOD cl_gui_cfw=>dispatch.
ENDFORM. " FRM_EXCEL_PRINT
*&---------------------------------------------------------------------*
*& Form frm_getcell
*&---------------------------------------------------------------------*
* 得到单元格
*----------------------------------------------------------------------*
* -->U_I 行
* -->U_J 列
* -->U_VALUE 单元格值
*----------------------------------------------------------------------*
FORM frm_getcell TABLES t_contents TYPE soi_generic_table
USING u_i u_j u_value u_i_flag .
DATA: l_value(255).
l_value = u_value.
IF u_i_flag EQ abap_true.
CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
CHANGING
value = l_value.
ENDIF.
gwa_content-row = u_i.
gwa_content-column = u_j.
gwa_content-value = l_value.
APPEND gwa_content TO t_contents.
CLEAR gwa_content.
ENDFORM. "frm_getcell
*&---------------------------------------------------------------------*
*& 包含 YTEST_LJM_01_TOP
*&---------------------------------------------------------------------*
TYPES:BEGIN OF TY_SFLIGHT,
CARRID TYPE SFLIGHT-CARRID,
CONNID TYPE SFLIGHT-CONNID,
CARRNAME TYPE SCARR-CARRNAME,
CITYFROM TYPE SPFLI-CITYFROM,
CITYTO TYPE SPFLI-CITYTO,
PRICE TYPE SFLIGHT-PRICE,
END OF TY_SFLIGHT.
DATA:GT_SFLIGHT TYPE STANDARD TABLE OF TY_SFLIGHT.
DATA:GS_SFLIGHT TYPE TY_SFLIGHT.
DATA:GV_OBJECT_KEY TYPE SBDST_OBJECT_KEY VALUE 'YTESTLJM01TMPL',"导入EXCEL模板
OK_CODE TYPE SY-UCOMM."保存数据按钮
SELECTION-SCREEN:BEGIN OF BLOCK BLK1 WITH FRAME.
PARAMETER:
P_RADIO1 RADIOBUTTON GROUP G1 USER-COMMAND SEL DEFAULT 'X',"报表展示
P_RADIO2 RADIOBUTTON GROUP G1."科目维护
SELECTION-SCREEN:END OF BLOCK BLK1.
*&---------------------------------------------------------------------*
*& 包含 YTEST_LJM_01_F01
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form FRM_CHECK_REQUIRED
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_check_required .
IF p_radio1 = 'X'.
PERFORM frm_retrieve_data."数据处理
CALL SCREEN '0101'.
ELSEIF p_radio2 = 'X'.
CALL TRANSACTION 'YTESTLJM01B' AND SKIP FIRST SCREEN.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_RETRIEVE_DATA
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_retrieve_data .
SELECT t~carrid,t~connid,r~carrname,i~cityfrom,i~cityto,t~price
FROM sflight AS t
INNER JOIN scarr AS r
ON r~carrid = t~carrid
INNER JOIN spfli AS i
ON i~carrid = t~carrid
AND i~connid = t~connid
INTO TABLE @gt_sflight.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_FILL_HEAD
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_fill_head .
DATA:lv_str TYPE string.
* 选中范围
CLEAR:gt_ranges[],gt_contents[].
CALL METHOD go_document->sheet_interface->set_selection
EXPORTING
top = 2
left = 1
rows = 1
columns = 1.
* 创建range
CALL METHOD go_document->sheet_interface->insert_range
EXPORTING
name = 'DATE'
rows = 1
columns = 1
no_flush = ''
IMPORTING
error = go_error.
gwa_range-name = 'DATE'.
gwa_range-rows = 1.
gwa_range-columns = 1.
APPEND gwa_range TO gt_ranges.
CLEAR gwa_range.
CLEAR lv_str.
CONCATENATE sy-datum+0(4) '/' sy-datum+4(2) '/' sy-datum+6(2) INTO lv_str.
PERFORM frm_getcell TABLES gt_contents USING 1 1 lv_str space.
* 设置range的数据
CALL METHOD go_document->sheet_interface->set_ranges_data
EXPORTING
ranges = gt_ranges
contents = gt_contents
IMPORTING
error = go_error
retcode = gv_retcode.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_FILL_ITEM
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_fill_item .
DATA:lv_row TYPE i.
* 选中范围
DATA(lv_rows) = lines( gt_sflight ).
CLEAR:gt_ranges[],gt_contents[].
CALL METHOD go_document->sheet_interface->set_selection
EXPORTING
top = 4
left = 1
rows = lv_rows
columns = 6.
* 创建range
CALL METHOD go_document->sheet_interface->insert_range
EXPORTING
name = 'PAY'
rows = lv_rows
columns = 6
no_flush = ''
IMPORTING
error = go_error.
gwa_range-name = 'PAY'.
gwa_range-rows = lv_rows.
gwa_range-columns = 6.
APPEND gwa_range TO gt_ranges.
* 将数据放入单元格内
CLEAR lv_row.
CLEAR gs_sflight.
LOOP AT gt_sflight INTO gs_sflight.
lv_row = lv_row + 1.
PERFORM frm_getcell TABLES gt_contents USING lv_row 1 gs_sflight-carrid 'X'.
PERFORM frm_getcell TABLES gt_contents USING lv_row 2 gs_sflight-connid 'X'.
PERFORM frm_getcell TABLES gt_contents USING lv_row 3 gs_sflight-carrname 'X'.
PERFORM frm_getcell TABLES gt_contents USING lv_row 4 gs_sflight-cityfrom 'X'.
PERFORM frm_getcell TABLES gt_contents USING lv_row 5 gs_sflight-cityto 'X'.
PERFORM frm_getcell TABLES gt_contents USING lv_row 6 gs_sflight-price 'X'.
CLEAR gs_sflight.
ENDLOOP.
* 设置range的数据
CALL METHOD go_document->sheet_interface->set_ranges_data
EXPORTING
ranges = gt_ranges
contents = gt_contents
IMPORTING
error = go_error
retcode = gv_retcode.
ENDFORM.
*&---------------------------------------------------------------------*
*& 包含 YTEST_LJM_01_SRN
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Module STATUS_0101 OUTPUT
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
MODULE status_0101 OUTPUT.
DATA:lv_has_activex.
SET PF-STATUS 'PF_200'. "创建一个GUI状态
SET TITLEBAR 'TI_200'.
IF go_control IS INITIAL.
CLEAR gt_error_table.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = TEXT-t02.
* Test Whether ActiveX Controls Are Supported
CALL FUNCTION 'GUI_HAS_ACTIVEX'
IMPORTING
return = lv_has_activex.
IF lv_has_activex IS INITIAL.
MESSAGE e001(zcm).
ENDIF.
* 实例化container control
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = go_control
error = go_error.
APPEND go_error TO gt_error_table.
* 实例化container
CREATE OBJECT go_container
EXPORTING
container_name = 'CUSTOM'. "屏幕上custom控件名称
* 初始化container control，关联controller与container
CALL METHOD go_control->init_control
EXPORTING
r3_application_name = sy-repid "#EC NOTEXT
inplace_enabled = abap_true
inplace_scroll_documents = abap_true
parent = go_container
register_on_close_event = abap_true
register_on_custom_event = abap_true
* inplace_show_toolbars = abap_false
no_flush = abap_true
IMPORTING
error = go_error.
APPEND go_error TO gt_error_table.
* 建立container和link server的链接
CALL METHOD go_control->get_link_server
EXPORTING
no_flush = abap_true
IMPORTING
link_server = go_link_server
error = go_error.
APPEND go_error TO gt_error_table.
* 启动link server
CALL METHOD go_link_server->start_link_server
EXPORTING
no_flush = abap_true
IMPORTING
error = go_error.
APPEND go_error TO gt_error_table.
* 创建excel DOI对象
CREATE OBJECT go_document
EXPORTING
control = go_control
document_type = gv_document_type.
* 打开BDS excel模板
IF go_bds_instance IS INITIAL.
CREATE OBJECT go_bds_instance.
ENDIF.
gwa_doc_signature-prop_name = 'DESCRIPTION'."固定值
gwa_doc_signature-prop_value = TEXT-t03."模板名
APPEND gwa_doc_signature TO gt_doc_signature.
CLEAR gwa_doc_signature.
CLEAR: gt_doc_uris[],gwa_doc_uris.
CALL METHOD go_bds_instance->get_with_url
EXPORTING
classname = gv_classname
classtype = gv_classtype
object_key = gv_object_key
CHANGING
uris = gt_doc_uris
signature = gt_doc_signature
EXCEPTIONS
nothing_found = 1
error_kpro = 2
internal_error = 3
parameter_error = 4
not_authorized = 5
not_allowed = 6.
IF sy-subrc <> 0.
MESSAGE w001(zfi) WITH gv_object_key DISPLAY LIKE 'E'.
LEAVE TO SCREEN 0.
ENDIF.
CLEAR gwa_doc_uris.
READ TABLE gt_doc_uris INTO gwa_doc_uris INDEX 1.
gv_doc_url = gwa_doc_uris-uri.
CALL METHOD go_document->close_document.
CALL METHOD go_document->open_document_url
EXPORTING
open_inplace = abap_true
doc_url = gv_doc_url
IMPORTING
error = go_error.
IF go_error->error_code EQ 'OPEN_DOCUMENT_FAILED'.
CALL METHOD cl_gui_cfw=>flush.
CALL METHOD cl_gui_cfw=>dispatch.
CALL METHOD go_link_server->stop_link_server
IMPORTING
error = go_error
retcode = gv_retcode.
FREE: go_control,go_link_server,go_document,
go_error,go_link_server,go_bds_instance.
REFRESH: gt_contents.
CALL METHOD go_container->free.
MESSAGE w001(zcm) DISPLAY LIKE 'E'.
LEAVE TO SCREEN 0.
ENDIF.
* 填入抬头
PERFORM frm_fill_head.
* 填写excel行项目表
PERFORM frm_fill_item.
CALL METHOD go_document->sheet_interface->set_selection
EXPORTING
top = 1
left = 1
rows = '1'
columns = '1'.
* Document shall also be available in ITAB for respective operations:
CALL METHOD go_document->proxy->save_document_to_table
IMPORTING
error = go_save_error
CHANGING
document_table = gt_data_table
document_size = gv_data_size.
IF NOT go_save_error IS INITIAL.
IF go_save_error->error_code NE c_oi_errors=>ret_ok.
go_error = go_save_error.
EXIT.
ENDIF.
ENDIF.
CALL METHOD go_document->proxy->update_document_links
IMPORTING
error = go_error.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDIF.
MESSAGE '数据获取成功' TYPE 'S'.
ENDMODULE.
*&---------------------------------------------------------------------*
*& Module USER_COMMAND_0101 INPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
MODULE user_command_0101 INPUT.
CASE ok_code.
WHEN:'EXIT' OR '&F03' OR '&F12' OR '&F15'.
* 退出屏幕并清空所有的对象
PERFORM frm_exit.
WHEN 'SAVE'.
* 将Excel内容保存到本地
PERFORM frm_excel_save.
WHEN 'PRINT'.
* 调用excel打印功能直接打印
PERFORM frm_excel_print.
ENDCASE.
ENDMODULE.
效果如下图所示：
