代码语言：javascript复制
*&---------------------------------------------------------------------*
*& Report ZTRAINING26 事件的使用与说明 *
*& *
*&---------------------------------------------------------------------*
*& Created by Xavery Hsueh on 2009-05-09. *
*& *
*&---------------------------------------------------------------------*
REPORT ztraining26 NO STANDARD PAGE HEADING.
************************************************************************
** 声明数据库表 Declaration of database **
************************************************************************
TABLES:mara. "物料主数据
************************************************************************
** 定义结构类型 Define the structure's type **
************************************************************************
* 结果内表结构
TYPES:BEGIN OF typ_result,
matnr TYPE matnr, "物料编号
werks TYPE werks_d, "工厂
lgort TYPE lgort_d, "库存地点
charg TYPE charg_d, "批次
box TYPE c,
END OF typ_result.
************************************************************************
** 定义变量与内表 Define the variants and Internal tables **
************************************************************************
DATA:gt_result TYPE TABLE OF typ_result WITH HEADER LINE. "结果内表
*@---------------------------------------------------------------------*
*调用相关对象使用的变量
TYPE-POOLS:vrm, sbdst, soi.
* 定位EXCEL的单元格，
TYPES:BEGIN OF typ_excel,
row TYPE kcd_ex_row_n,
col TYPE kcd_ex_col_n,
value TYPE char50,
END OF typ_excel.
DATA:gt_tabcontent TYPE TABLE OF typ_excel.
DATA:wa_tabcontent TYPE typ_excel.
TYPES: typ_url LIKE bapiuri-uri.
TYPES: BEGIN OF typ_docu_descr,
document_name(40),
document_id(64),
END OF typ_docu_descr.
TYPES:BEGIN OF typ_area_excel,
areano(2) TYPE n,
tabcontent LIKE gt_tabcontent,
END OF typ_area_excel.
* SAP Desktop Office Integration Interfaces
DATA: cl_container TYPE REF TO cl_gui_container,
cl_splitter TYPE REF TO cl_gui_splitter_container,
cl_control TYPE REF TO i_oi_container_control,
cl_docu_proxy TYPE REF TO i_oi_document_proxy,
cl_spreadsheet TYPE REF TO i_oi_spreadsheet,
cl_error TYPE REF TO i_oi_error,
cl_errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE.
* spreadsheet interface structures for Excel data input
DATA:wa_cellitem TYPE soi_generic_item,
wa_rangeitem TYPE soi_range_item,
gt_ranges TYPE soi_range_list,
gt_excel_input TYPE soi_generic_table,
wa_excel_input TYPE soi_generic_item,
g_initialized type c,
g_retcode TYPE soi_ret_string,
gt_excel_format TYPE soi_format_table,
wa_format LIKE LINE OF gt_excel_format.
DATA:gt_itab TYPE TABLE OF alsmex_tabline WITH HEADER LINE,
gt_imt_tab TYPE TABLE OF typ_area_excel,
wa_imt_tab LIKE LINE OF gt_imt_tab,
g_macro TYPE text100,
g_sheet(10) TYPE c,
g_cell_fit TYPE c.
DATA:cl_bds_instance TYPE REF TO cl_bds_document_set,
gt_doc_signature TYPE sbdst_signature,
wa_doc_signature LIKE LINE OF gt_doc_signature,
gt_doc_components TYPE sbdst_components,
gt_doc_uris TYPE sbdst_uri,
wa_doc_uris LIKE LINE OF gt_doc_uris.
DATA:g_app TYPE vrm_id,
gt_applist TYPE vrm_values,
g_excel TYPE text80 VALUE 'Excel.Sheet', "EXCEL的表单
g_docu_type TYPE text80,
g_url(256) TYPE c,
g_has_activex TYPE c,
g_col TYPE i, "字段所在的列数
g_row TYPE i. "字段所在的行数
*以下三个值为Tcode:OAOR里面新建模板文件的参数
DATA: g_classname TYPE sbdst_classname VALUE 'HRFPM_EXCEL_STANDARD',
g_classtype TYPE sbdst_classtype VALUE 'OT',
g_object_key TYPE sbdst_object_key VALUE 'ZMB51_FZ'.
************************************************************************
** 宏定义 Define the macro **
************************************************************************
DEFINE m_value.
clear &1.
&1-key = &2.
&1-text = &3.
append &1.
END-OF-DEFINITION.
* 将数据对应到EXCEL的单元格,并添加到内表中
DEFINE m_excel_cell.
clear wa_tabcontent.
g_col = g_col 1.
wa_tabcontent-row = g_row.
wa_tabcontent-col = g_col.
wa_tabcontent-value = &1.
shift wa_tabcontent-value left deleting leading space.
append wa_tabcontent to gt_tabcontent.
END-OF-DEFINITION.
************************************************************************
** 选择屏幕 Customize the selection-screen **
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK xavery WITH FRAME TITLE text-001.
SELECT-OPTIONS:s_matnr FOR mara-matnr MEMORY ID mat. "物料编号
SELECTION-SCREEN END OF BLOCK xavery.
************************************************************************
** 执行程序事件 Executing the program's events **
************************************************************************
INITIALIZATION.
START-OF-SELECTION.
PERFORM sub_query_mara.
END-OF-SELECTION.
PERFORM sub_process_excel.
CALL SCREEN 0100.
*&---------------------------------------------------------------------*
*& Form sub_query_mara
*&---------------------------------------------------------------------*
* 从物料主数据表MARA中取数
*----------------------------------------------------------------------*
FORM sub_query_mara .
* 取物料主数据
SELECT matnr
FROM mara
INTO CORRESPONDING FIELDS OF TABLE gt_result
WHERE matnr IN s_matnr.
ENDFORM. " sub_query_mara
************************************************************************
** Dialog Modules PBO and PAI
************************************************************************
MODULE status_0100 OUTPUT.
DATA: wa_tab TYPE typ_area_excel.
DATA: its_tab TYPE TABLE OF typ_excel.
SET PF-STATUS 'XAVERY_HSUEH'.
SET TITLEBAR 'TIT_100' WITH '活动界面示例'.
PERFORM sub_create_basic_object.
LOOP AT gt_imt_tab INTO wa_tab.
REFRESH gt_itab.
gt_itab[] = wa_tab-tabcontent[].
PERFORM sub_set_cell.
ENDLOOP.
IF g_cell_fit = 'X'.
CALL METHOD cl_spreadsheet->fit_widest
EXPORTING
name = space
no_flush = 'X'.
ENDIF.
* 修改WORK SHEET 的名字
CALL METHOD cl_spreadsheet->set_sheet_name
EXPORTING
newname = '物料主数据清单'
oldname = 'Sheet1'
IMPORTING
error = cl_errors.
* 宏执行
IF NOT g_macro IS INITIAL.
* CALL METHOD DOCUMENT->EXECUTE_MACRO
* EXPORTING
* g_macro = g_macro
** SCRIPT_NAME = 'Open_text'
** NO_FLUSH = ' '
* IMPORTING
* cl_error = cl_errorS.
** CALL METHOD C_OI_ERRORS=>SHOW_MESSAGE
** EXPORTING
** TYPE = 'E'.
* APPEND cl_errorS.
ENDIF.
ENDMODULE. "status_0100 OUTPUT
*&---------------------------------------------------------------------*
*& Dialog Modules PAI
*&---------------------------------------------------------------------*
MODULE user_command_0100 INPUT.
LEAVE TO SCREEN 0.
ENDMODULE. "USER_COMMAND_0100 INPUT
*&---------------------------------------------------------------------*
*& Form SUB_CREATE_BASIC_OBJECT
*&---------------------------------------------------------------------*
* 创建相关的对象
*----------------------------------------------------------------------*
FORM sub_create_basic_object .
CHECK g_initialized IS INITIAL.
CALL FUNCTION 'GUI_HAS_ACTIVEX'
IMPORTING
return = g_has_activex.
IF g_has_activex IS INITIAL.
MESSAGE e007(demoofficeintegratio).
ENDIF.
* first get the SAP DOI i_oi_container_control interface
CALL METHOD
c_oi_container_control_creator=>get_container_control
IMPORTING
control = cl_control
error = cl_error.
* check no errors occured
CALL METHOD cl_error->raise_message
EXPORTING
type = 'E'.
* 创建CONTIANER
CREATE OBJECT cl_splitter
EXPORTING
parent = cl_gui_container=>screen0
rows = 1
columns = 1.
CALL METHOD cl_splitter->set_border
EXPORTING
border = cl_gui_cfw=>false.
cl_container = cl_splitter->get_container( row = 1 column = 1 ).
* initialize the SAP DOI Container, tell it to run in the container
* specified above and tell it to run Excel in-place
CALL METHOD cl_control->init_control
EXPORTING
r3_application_name = 'RBasis' "#EC NOTEXT
inplace_enabled = 'X'
inplace_scroll_documents = 'X'
parent = cl_container
register_on_close_event = 'X'
register_on_custom_event = 'X'
no_flush = 'X'
IMPORTING
error = cl_errors.
* save error object in collection
APPEND cl_errors.
CALL METHOD cl_gui_cfw=>dispatch.
wa_doc_signature-prop_name = 'DESCRIPTION'.
g_app = 'excel'. "
IF g_app = 'excel'.
g_docu_type = g_excel.
wa_doc_signature-prop_value = 'PP_REPORT'.
ELSE.
ENDIF.
APPEND wa_doc_signature TO gt_doc_signature.
CREATE OBJECT cl_bds_instance.
CALL METHOD cl_bds_instance->get_info
EXPORTING
classname = g_classname
classtype = g_classtype
object_key = g_object_key
CHANGING
components = gt_doc_components
signature = gt_doc_signature
EXCEPTIONS
nothing_found = 1
error_kpro = 2
internal_error = 3
parameter_error = 4
not_authorized = 5
not_allowed = 6.
CALL METHOD cl_bds_instance->get_with_url
EXPORTING
classname = g_classname
classtype = g_classtype
object_key = g_object_key
CHANGING
uris = gt_doc_uris
signature = gt_doc_signature.
FREE cl_bds_instance.
* ask the SAP DOI container for a i_oi_document_proxy for Excel
CALL METHOD cl_control->get_document_proxy
EXPORTING
document_type = 'Excel.Sheet'
no_flush = 'X'
* REGISTER_CONTAINER = 'X'
IMPORTING
document_proxy = cl_docu_proxy
error = cl_errors.
APPEND cl_errors.
* open a document saved in business document service.
* READ TABLE doc_uris INTO wa_doc_uris INDEX 1.
LOOP AT gt_doc_uris INTO wa_doc_uris. " WHERE uri CP '*ZSDR17.xls*'.
g_url = wa_doc_uris-uri.
ENDLOOP.
CALL METHOD cl_docu_proxy->open_document
EXPORTING
open_inplace = 'X'
* NO_FLUSH = 'X'
document_url = g_url
IMPORTING
error = cl_errors.
* STARTUP_MACRO = g_macro.
DATA: has TYPE i.
CALL METHOD cl_docu_proxy->has_spreadsheet_interface
EXPORTING
no_flush = 'X'
IMPORTING
is_available = has
error = cl_errors.
APPEND cl_errors.
CALL METHOD cl_docu_proxy->get_spreadsheet_interface
EXPORTING
no_flush = 'X'
IMPORTING
sheet_interface = cl_spreadsheet
error = cl_errors.
APPEND cl_errors.
* Activate sheet 1
g_sheet = '物料'.
DATA: g_def_active_sheetname(50) VALUE 'REPORT'.
CALL METHOD cl_spreadsheet->select_sheet
EXPORTING
name = g_sheet
IMPORTING
error = cl_errors.
APPEND cl_errors.
* LOOP AT cl_errorS.
* CALL METHOD cl_errorS->RAISE_MESSAGE
* EXPORTING
* TYPE = 'E'.
* ENDLOOP.
FREE cl_errors.
g_initialized = 'X'.
ENDFORM. " FRM_CREATE_BASIC_OBJECT
*&---------------------------------------------------------------------*
*& Form SUB_SET_CELL
*&---------------------------------------------------------------------*
* 根据数据设置 EXCEL 表的属性
*----------------------------------------------------------------------*
FORM sub_set_cell .
DATA: i_top TYPE i, i_left TYPE i,
i_rows TYPE i, i_cols TYPE i,
t_row TYPE i, t_col TYPE i,
s_row TYPE i, s_col TYPE i.
SORT gt_itab BY row col.
READ TABLE gt_itab INDEX 1.
IF sy-subrc = 0.
MOVE gt_itab-row TO i_top.
MOVE gt_itab-col TO i_left.
ENDIF.
SORT gt_itab BY row DESCENDING col DESCENDING.
READ TABLE gt_itab INDEX 1.
IF sy-subrc = 0.
i_rows = gt_itab-row - i_top 1.
i_cols = gt_itab-col - i_left 1.
ENDIF.
SORT gt_itab BY row col.
CALL METHOD cl_spreadsheet->insert_range_dim
EXPORTING
name = 'cell'
no_flush = 'X'
top = i_top
left = i_left
rows = i_rows
columns = i_cols
IMPORTING
error = cl_errors.
APPEND cl_errors.
REFRESH gt_ranges.
wa_rangeitem-name = 'cell'.
wa_rangeitem-columns = i_cols.
wa_rangeitem-rows = i_rows.
wa_rangeitem-code = 4.
APPEND wa_rangeitem TO gt_ranges.
PERFORM sub_fill_blank USING i_top i_left i_rows i_cols .
REFRESH gt_excel_input.
REFRESH gt_excel_format.
LOOP AT gt_itab.
wa_excel_input-column = gt_itab-col - i_left 1.
wa_excel_input-row = gt_itab-row - i_top 1.
MOVE gt_itab-value TO wa_excel_input-value.
* CONDENSE wa_excel_input-value NO-GAPS.
APPEND wa_excel_input TO gt_excel_input.
ENDLOOP.
* set data
CALL METHOD cl_spreadsheet->set_ranges_data
EXPORTING
ranges = gt_ranges
contents = gt_excel_input
* UPDATING = '1'
no_flush = 'X'
IMPORTING
error = cl_errors.
APPEND cl_errors.
* 设置 FRAME.
IF wa_tab-areano NE 1.
CALL METHOD cl_spreadsheet->set_frame
EXPORTING
rangename = 'cell'
typ = '127'
color = '1'
no_flush = 'X'
IMPORTING
error = cl_errors.
ENDIF.
ENDFORM. " SUB_SET_CELL
*&---------------------------------------------------------------------*
*& Form SUB_FILL_BLANK
*&---------------------------------------------------------------------*
* 将数据填充到 EXCEL 表中
*----------------------------------------------------------------------*
FORM sub_fill_blank USING i_top TYPE i
i_left TYPE i
i_rows TYPE i
i_cols TYPE i .
DATA: inx_col(4) TYPE n,
inx_row(4) TYPE n.
DATA: BEGIN OF it_row OCCURS 0,
row(4) TYPE n,
END OF it_row.
MOVE i_top TO inx_row.
DO i_rows TIMES.
MOVE i_left TO inx_col.
DO i_cols TIMES.
READ TABLE gt_itab WITH KEY row = inx_row
col = inx_col.
IF sy-subrc <> 0.
MOVE inx_row TO gt_itab-row.
MOVE inx_col TO gt_itab-col.
MOVE '' TO gt_itab-value.
APPEND gt_itab.
ENDIF.
ADD 1 TO inx_col.
ENDDO.
ADD 1 TO inx_row.
ENDDO.
SORT gt_itab BY row col.
ENDFORM. " SUB_FILL_BLANK
*&---------------------------------------------------------------------*
*& Form sub_process_excel
*&---------------------------------------------------------------------*
* 处理结果内表
*----------------------------------------------------------------------*
FORM sub_process_excel .
g_row = 3.
CLEAR g_col.
* 将数据添充到EXCEL内表中
REFRESH gt_tabcontent.
LOOP AT gt_result.
g_col = 1.
m_excel_cell gt_result-matnr.
m_excel_cell gt_result-werks.
m_excel_cell gt_result-lgort.
m_excel_cell gt_result-charg.
g_row = g_row 1.
ENDLOOP.
wa_imt_tab-areano = 2.
wa_imt_tab-tabcontent = gt_tabcontent.
APPEND wa_imt_tab TO gt_imt_tab.
g_col = 1.
m_excel_cell '执行日期'.
m_excel_cell sy-datum.
m_excel_cell '登录用户'.
m_excel_cell sy-uname.
wa_imt_tab-areano = 1.
wa_imt_tab-tabcontent = gt_tabcontent.
APPEND wa_imt_tab TO gt_imt_tab.
ENDFORM. " sub_process_excel
