SAP ABAP-DOI技术输出EXCEL文档（１）

DOI技术是ABAP程序编程中常用的一种技术,这种技术在使用中有许多不常用的技巧,灵活使用这种技巧可以让你在编程过程中如虎添翼,简化你的程序逻辑与代码复杂度。本文告诉你如何全屏幕显示DOI输出的EXCEL文档。创建一个空屏幕上,只在屏幕上激活PAI、PBO事件下的FUNCTIONMOUDLE。干嘛非要在屏幕上放一个CONTAINER组件呢,固定后那么难看。完全可以动态创建嘛!动态创建CL_GUI_CONTAINER这个类。动态创建的好处在于,CONTAINER的大小不是固定的,完全随着屏幕的大小而创建。注意检查屏幕是否支持对象的使用。OAOR上传模版。其实如果输出的EXCEL比较简单,就可以不使用模版。本文先输出一个简单的EXCEL文件,不使用上传的EXCEL模版。*&---------------------------------------------------------------------**& Report ZTRAINING26 ABAP DOI技术输出EXCEL文档 **& **&---------------------------------------------------------------------**& Created by Xavery Hsueh(薛现军) on 2015-07-26. **& **&---------------------------------------------------------------------*REPORT ztraining26 NO STANDARD PAGE HEADING.************************************************************************** 声明数据库表 Declaration of database **************************************************************************TABLES:mara. "物料主数据*调用相关对象使用的变量TYPE-POOLS:vrm, sbdst, soi.************************************************************************** 定义结构类型 Define the structure's type *************************************************************************** 结果内表结构TYPES:BEGIN OF ty_result, matnr TYPE mara-matnr, "物料编号 ersda TYPE mara-ersda, ernam TYPE mara-ernam, mtart TYPE mara-mtart, matkl TYPE mara-matkl, meins TYPE mara-meins, prdha TYPE mara-prdha, maktx TYPE makt-maktx, END OF ty_result.TYPES: BEGIN OF ty_docu_descr, document_name(40), document_id(64), END OF ty_docu_descr.* EXCEL的数据块TYPES:BEGIN OF ty_excel, areano(2) TYPE n, border TYPE c, top TYPE i, left TYPE i, rows TYPE i, cols TYPE i, content TYPE soi_generic_table, END OF ty_excel.************************************************************************** 定义变量与内表 Define the variants and Internal tables **************************************************************************DATA:gt_result TYPE TABLE OF ty_result WITH HEADER LINE. "结果内表*@---------------------------------------------------------------------** SAP Desktop Office Integration InterfacesDATA: cl_container TYPE REF TO cl_gui_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 inputDATA:gs_cellitem TYPE soi_generic_item, gs_rangeitem TYPE soi_range_item, gt_ranges TYPE soi_range_list, gv_initialized TYPE c, gv_retcode TYPE soi_ret_string, gt_excel_format TYPE soi_format_table, gs_format LIKE LINE OF gt_excel_format.DATA:gt_imt_tab TYPE TABLE OF ty_excel, gs_imt_tab TYPE ty_excel, gt_content TYPE soi_generic_table, gs_content TYPE soi_generic_item, gv_macro TYPE text100, gv_sheet TYPE char20, gv_cell_fit TYPE c VALUE 'X'.DATA:gv_app TYPE vrm_id, gt_applist TYPE vrm_values, gv_excel TYPE text80 VALUE 'Excel.Sheet', "EXCEL的表单 gv_docu_type TYPE text80, gv_url(256) TYPE c, gv_has_activex TYPE c, gv_has TYPE i, gv_line TYPE i, gv_col TYPE i, "字段所在的列数 gv_row TYPE i.**************************************************************************宏定义 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 gs_content.gv_col = gv_col + 1.gs_content-row = gv_row.gs_content-column = gv_col.gs_content-value = &1.shift gs_content-value left deleting leading space.append gs_content to gt_content.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 mara~matnr mara~ersda mara~ernam mara~mtart mara~matkl mara~meins mara~prdha makt~maktx FROM mara INNER JOIN makt ON mara~matnr = makt~matnr INTO CORRESPONDING FIELDS OF TABLE gt_result WHERE mara~matnr IN s_matnr AND makt~spras = sy-langu.ENDFORM.************************************************************************** Dialog Modules PBO and PAI************************************************************************MODULE status_0100 OUTPUT.SET PF-STATUS 'XAVERY_HSUEH'. SET TITLEBAR 'TIT_100' WITH '活动界面示例'. PERFORM sub_create_basic_object.PERFORM sub_set_cell_value.PERFORM sub_set_sheet.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 .CALL FUNCTION 'GUI_HAS_ACTIVEX'IMPORTING return = gv_has_activex.IF gv_has_activex IS INITIAL. MESSAGE e007(demoofficeintegratio).ENDIF. CHECK gv_initialized IS INITIAL.* 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初始化cl_container = cl_gui_container=>screen0.* initialize the SAP DOI Container, tell it to run in the container* specified above and tell it to run Excel in-placeCALL 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 collectionAPPEND cl_errors.CALL METHOD cl_gui_cfw=>dispatch.* 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.* 创建EXCEL文档CALL METHOD cl_docu_proxy->create_document EXPORTING open_inplace = 'X' no_flush = 'X' document_title = '明细表' IMPORTING error = cl_errors.* 检查标识: Whether Interface Is Supported For This Doc. TypeCALL METHOD cl_docu_proxy->has_spreadsheet_interface EXPORTING no_flush = 'X' IMPORTING is_available = gv_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. FREE cl_errors.gv_initialized = 'X'.ENDFORM.*&---------------------------------------------------------------------**& Form sub_process_excel*&---------------------------------------------------------------------** 处理结果内表*----------------------------------------------------------------------*FORM sub_process_excel .* 将表格抬头数据添加到内表 CLEAR gs_imt_tab.REFRESH gt_content.gv_col = 0.gv_row = 1.m_excel_cell '物料编码'.m_excel_cell '物料类型'.m_excel_cell '物料组'.m_excel_cell '物料描述'.gs_imt_tab-areano = 1.gs_imt_tab-border = 'X'.gs_imt_tab-top = 1. "输出起始单元格行gs_imt_tab-left = 1. "输出起始单元格列gs_imt_tab-rows = 1.gs_imt_tab-cols = 4.gs_imt_tab-content = gt_content.APPEND gs_imt_tab TO gt_imt_tab.* 将表格数据添加到内表REFRESH gt_content.gv_row = 0. LOOP AT gt_result. gv_col = 0. gv_row = gv_row + 1. m_excel_cell gt_result-matnr. m_excel_cell gt_result-mtart. m_excel_cell gt_result-matkl. m_excel_cell gt_result-meins. m_excel_cell gt_result-maktx.ENDLOOP.gv_line = gv_row.gs_imt_tab-areano = 2.gs_imt_tab-border = 'X'.gs_imt_tab-top = 2. "数据从第二行开始gs_imt_tab-left = 1. "数据从第一列开始gs_imt_tab-rows = gv_row.gs_imt_tab-cols = gv_col.gs_imt_tab-content = gt_content. APPEND gs_imt_tab TO gt_imt_tab. CLEAR gs_imt_tab.REFRESH gt_content.gv_col = 0.gv_row = 1.m_excel_cell '执行日期'.m_excel_cell sy-datum.m_excel_cell '登录用户'.m_excel_cell sy-uname.gs_imt_tab-areano = 3.gs_imt_tab-top = gv_line + 2. "输出起始单元格行 gs_imt_tab-left = 1. "输出起始单元格列 gs_imt_tab-rows = 1. "共N行 n = 1gs_imt_tab-cols = 4. "共N列 n = 4 gs_imt_tab-content = gt_content. APPEND gs_imt_tab TO gt_imt_tab.ENDFORM. " sub_process_excel*&---------------------------------------------------------------------**& Form SUB_SET_CELL_VALUE*&---------------------------------------------------------------------** 设置EXCEL文档的单元格的内容*----------------------------------------------------------------------*FORM sub_set_cell_value . LOOP AT gt_imt_tab INTO gs_imt_tab.* 给EXCEL添加数据块 CALL METHOD cl_spreadsheet->insert_range_dim EXPORTING name = 'cell' no_flush = 'X' top = gs_imt_tab-top left = gs_imt_tab-left rows = gs_imt_tab-rows columns = gs_imt_tab-cols IMPORTING error = cl_errors. APPEND cl_errors.* 设定输出数据的内容 REFRESH gt_ranges. gs_rangeitem-name = 'cell'. gs_rangeitem-columns = gs_imt_tab-cols. gs_rangeitem-rows = gs_imt_tab-rows. gs_rangeitem-code = 4. APPEND gs_rangeitem TO gt_ranges.* set data CALL METHOD cl_spreadsheet->set_ranges_data EXPORTING ranges = gt_ranges contents = gs_imt_tab-content no_flush = 'X' IMPORTING error = cl_errors. APPEND cl_errors.* 设置 FRAME. IF gs_imt_tab-border = 'X'. CALL METHOD cl_spreadsheet->set_frame EXPORTING rangename = 'cell' typ = '127' color = '1' no_flush = 'X' IMPORTING error = cl_errors. ENDIF. ENDLOOP.ENDFORM.*&---------------------------------------------------------------------**& Form SUB_SET_SHEET*&---------------------------------------------------------------------** 设置EXCEL文档的SHEET页的格式*----------------------------------------------------------------------*FORM sub_set_sheet .* 自动调整单元格宽度IF gv_cell_fit = 'X'. CALL METHOD cl_spreadsheet->fit_widest EXPORTING name = space no_flush = 'X'.ENDIF.* 修改WORK SHEET 的名字 gv_sheet = '物料主数据'. CALL METHOD cl_spreadsheet->set_sheet_name EXPORTING newname = gv_sheet oldname = 'Sheet1' IMPORTING error = cl_errors.ENDFORM. " SUB_SET_SHEET