ABAP-DOI导出EXCEL

1.输入事务代码OAOR

 2.选择表模板

 3.选择文件路径

 4.如下图就可以了

5.要在程序中创建屏幕，如图

 最后附上源代码，可以直接复制运行

  1 REPORT y4_b4_test6.
  2 
  3 TABLES : ekko.
  4 
  5 *-->控件及界面定义
  6 DATA: gv_container  TYPE scrfname VALUE 'ALV_CON',  "ALV控键定义
  7       g_cust_con    TYPE REF TO cl_gui_custom_container,
  8       g_container   TYPE REF TO cl_gui_container,
  9       g_control     TYPE REF TO i_oi_container_control,
 10       g_document    TYPE REF TO i_oi_document_proxy,
 11       g_spreadsheet TYPE REF TO i_oi_spreadsheet.
 12 
 13 *-->类型池定义
 14 DATA: g_bds_documents      TYPE REF TO cl_bds_document_set,
 15       gv_classname         TYPE sbdst_classname,
 16       gv_classtype         TYPE sbdst_classtype,
 17       gv_objectkey         TYPE sbdst_object_key,
 18       gv_doc_components    TYPE sbdst_components,
 19       gv_doc_signature     TYPE sbdst_signature,
 20       gt_bds_uris          TYPE sbdst_uri,
 21       gs_bds_url           LIKE LINE OF gt_bds_uris,
 22       gv_template_url(256) TYPE c.    "模板url存储
 23 
 24 *-->定义写入excel时需要的ranges和contents
 25 DATA: gt_ranges   TYPE soi_range_list,
 26       gs_range    TYPE soi_range_item,
 27       gt_contents TYPE soi_generic_table,
 28       gs_content  TYPE soi_generic_item.
 29 
 30 *-->内表定义
 31 DATA : BEGIN OF gw_ekko,
 32          ebeln TYPE ekko-ebeln,
 33          bsart TYPE ekko-bsart,
 34          aedat TYPE ekko-aedat,
 35          lifnr TYPE ekko-lifnr,
 36        END OF gw_ekko.
 37 DATA : gt_ekko LIKE TABLE OF gw_ekko.
 38 
 39 *-->辅助字段定义
 40 DATA : ok_code TYPE sy-ucomm.
 41 
 42 *-->选择屏幕定义
 43 SELECTION-SCREEN BEGIN OF BLOCK blc_01 WITH FRAME TITLE TEXT-001.
 44   SELECT-OPTIONS : s_ebeln FOR ekko-ebeln.
 45 SELECTION-SCREEN END OF BLOCK blc_01.
 46 
 47 INITIALIZATION.
 48   "为参数赋值,此处对应事务码OAOR上传的文档信息
 49   gv_classname = 'HRFPM_EXCEL_STANDARD'.
 50   gv_classtype = 'OT'.
 51   gv_objectkey = 'ZDOITEST'.
 52 
 53 START-OF-SELECTION.
 54 
 55   PERFORM frm_get_data.
 56 
 57   PERFORM frm_deal_data.
 58 
 59   CALL SCREEN 100.
 60 
 61 END-OF-SELECTION.
 62 
 63 FORM frm_get_data .
 64   SELECT ebeln
 65          bsart
 66          aedat
 67          lifnr
 68     FROM ekko
 69     INTO TABLE gt_ekko
 70     WHERE ebeln IN s_ebeln.
 71 ENDFORM.
 72 
 73 FORM frm_deal_data .
 74 
 75 ENDFORM.
 76 
 77 MODULE status_0100 OUTPUT.
 78   SET PF-STATUS '100'.
 79   SET TITLEBAR '100'.
 80   PERFORM frm_excel_output.
 81 ENDMODULE.
 82 
 83 MODULE user_command_0100 INPUT.
 84 
 85   CASE ok_code.
 86     WHEN '&F03' OR '&F12' OR '&F15'.
 87       "退出前进行对象的释放
 88       PERFORM release_objects.
 89       LEAVE TO SCREEN 0.
 90   ENDCASE.
 91 
 92 ENDMODULE.
 93 
 94 FORM frm_excel_output .
 95 
 96   DATA: gv_available TYPE i.
 97   DATA: gv_row       TYPE i.  "计算内表有多少行
 98   DATA: gv_col       TYPE i.  "计算内表有多少列
 99   DATA: row_index    TYPE i.  "数据写入时的行
100 
101   DEFINE add_content.
102     gs_content-row    = &1.
103     gs_content-column = &2.
104     gs_content-value  = &3.
105     APPEND gs_content TO gt_contents.
106     CLEAR gs_content.
107   END-OF-DEFINITION.
108 ******************************开始*****************************************
109 *屏幕大小固定
110 **-->创建容器 create container control
111 *  CALL METHOD c_oi_container_control_creator=>get_container_control
112 *    IMPORTING
113 *      control = g_control.
114 *
115 *  IF g_cust_con IS INITIAL.
116 *    CREATE OBJECT g_cust_con
117 *      EXPORTING
118 *        container_name = gv_container.
119 *  ENDIF.
120 *
121 *  CALL METHOD g_control->init_control
122 *    EXPORTING
123 *      inplace_enabled          = 'X '
124 *      inplace_scroll_documents = 'X'
125 *      register_on_close_event  = 'X'
126 *      register_on_custom_event = 'X'
127 *      r3_application_name      = 'JYH test doi'
128 *      parent                   = g_cust_con.
129 *******************************结束*******************************************
130 
131 *******************************开始*******************************************
132 *屏幕大小随窗口大小动态变化
133   CALL METHOD c_oi_container_control_creator=>get_container_control
134     IMPORTING
135       control = g_control.
136 
137   DATA: container TYPE REF TO cl_gui_docking_container.
138   CREATE OBJECT container
139     EXPORTING
140       repid                       = sy-repid
141       dynnr                       = sy-dynnr
142       extension                   = 2050
143       side                        = cl_gui_docking_container=>property_floating
144     EXCEPTIONS
145       cntl_error                  = 1
146       cntl_system_error           = 2
147       create_error                = 3
148       lifetime_error              = 4
149       lifetime_dynpro_dynpro_link = 5.
150   IF sy-subrc NE 0.
151 
152   ENDIF.
153 
154 *-->初始化
155   CALL METHOD g_control->init_control
156     EXPORTING
157       inplace_enabled          = 'X '
158       inplace_scroll_documents = 'X'
159       register_on_close_event  = 'X'
160       register_on_custom_event = 'X'
161       r3_application_name      = 'JYH test doi'
162       parent                   = container.
163 ********************************结束************************************
164 
165 *-->url获取
166   CREATE OBJECT g_bds_documents.
167   CALL METHOD cl_bds_document_set=>get_info
168     EXPORTING
169       classname  = gv_classname  "initialization位置下定义
170       classtype  = gv_classtype  "initialization位置下定义
171       object_key = gv_objectkey  "initialization位置下定义
172     CHANGING
173       components = gv_doc_components
174       signature  = gv_doc_signature.
175 
176   CALL METHOD cl_bds_document_set=>get_with_url
177     EXPORTING
178       classname  = gv_classname
179       classtype  = gv_classtype
180       object_key = gv_objectkey
181     CHANGING
182       uris       = gt_bds_uris
183       signature  = gv_doc_signature.
184 
185   FREE g_bds_documents.
186 
187   "读取
188   READ TABLE gt_bds_uris INTO gs_bds_url INDEX 1.
189   gv_template_url = gs_bds_url-uri.
190 
191 *-->打开上传的excel文档
192   "
193   CALL METHOD g_control->get_document_proxy
194     EXPORTING
195       document_type      = 'Excel.Sheet'
196       no_flush           = 'X'
197       register_container = 'X'
198     IMPORTING
199       document_proxy     = g_document.
200 
201   CALL METHOD g_document->open_document
202     EXPORTING
203       open_inplace = 'X'
204       document_url = gv_template_url.
205 
206   CALL METHOD g_document->has_spreadsheet_interface
207     EXPORTING
208       no_flush     = 'X'
209     IMPORTING
210       is_available = gv_available.
211 
212   CALL METHOD g_document->get_spreadsheet_interface
213     EXPORTING
214       no_flush        = 'X'
215     IMPORTING
216       sheet_interface = g_spreadsheet.
217 
218   "选择展示的sheet页
219   CALL METHOD g_spreadsheet->select_sheet
220     EXPORTING
221       name     = 'Sheet1'
222       no_flush = 'X'.
223 
224 *-->往excel中写入数据
225   "检查内表是否有数据
226   CHECK gt_ekko IS NOT INITIAL.
227 
228   "获取内表的行数和列数
229   PERFORM frm_get_line_col USING gv_row gv_col.
230 
231   CALL METHOD g_spreadsheet->insert_range_dim
232     EXPORTING
233       name     = 'cell'
234       no_flush = 'X'
235       top      = 3        "从第几行开始
236       left     = 1        "从第几列考试
237       rows     = gv_row   "写几行
238       columns  = gv_col.  "写几列
239 
240   "根据行列填充ranges
241   CLEAR :gs_range,gt_ranges.
242   gs_range-name = 'cell'.
243   gs_range-rows = gv_row.
244   gs_range-columns = gv_col.
245   gs_range-code = 4.
246   APPEND gs_range TO gt_ranges.
247 
248   "批量的把数据写入
249   CLEAR row_index .
250   row_index = '1'.
251 
252   "把值精确到每一行每一列的单元格
253   LOOP AT gt_ekko INTO gw_ekko.
254     CLEAR gs_content.
255     add_content row_index 1 gw_ekko-ebeln.
256     add_content row_index 2 gw_ekko-bsart.
257     add_content row_index 3 gw_ekko-aedat.
258     add_content row_index 4 gw_ekko-lifnr.
259     row_index = row_index + 1.
260   ENDLOOP.
261 
262   CALL METHOD g_spreadsheet->set_ranges_data
263     EXPORTING
264       ranges   = gt_ranges
265       contents = gt_contents
266       no_flush = 'X'.
267 
268   "把数据精确写入到某一行某一列
269 
270   PERFORM frm_write_single USING '1' '7' sy-uname.
271   PERFORM frm_write_single USING '2' '7' sy-datum.
272 
273 ENDFORM.
274 
275 FORM frm_get_line_col  USING p_row p_col.
276   CLEAR : p_row,p_col.
277   FIELD-SYMBOLS <fs>.
278   "获取行数
279   p_row = lines( gt_ekko ).
280   "获取列数
281   DO.
282     ASSIGN COMPONENT sy-index OF STRUCTURE gw_ekko TO <fs>.
283     IF sy-subrc IS INITIAL.
284       p_col = p_col + 1.
285     ELSE.
286       EXIT.
287     ENDIF.
288   ENDDO.
289 ENDFORM.
290 
291 FORM release_objects .
292 
293   IF NOT g_document IS INITIAL.
294     CALL METHOD g_document->close_document.
295     FREE g_document.
296   ENDIF.
297 
298   IF NOT g_control IS INITIAL.
299     CALL METHOD g_control->destroy_control.
300     FREE g_control.
301   ENDIF.
302 
303   IF g_container IS NOT INITIAL.
304     CALL METHOD g_container->free.
305   ENDIF.
306 
307 
308 ENDFORM.
309 
310 FORM frm_write_single  USING  p_row p_col p_value.
311   DATA : lt_ranges   TYPE soi_range_list,
312          ls_ranges   TYPE soi_range_item,
313          lt_contents TYPE soi_generic_table,
314          ls_contents TYPE soi_generic_item.
315 
316   CLEAR :lt_ranges,ls_ranges,lt_contents,ls_contents.
317   "ranges中只存储一行一列
318   ls_ranges-name = 'cell' .
319   ls_ranges-columns = 1.
320   ls_ranges-rows = 1.
321   ls_ranges-code = 4.
322   APPEND ls_ranges TO lt_ranges.
323   "写入数据也只存一个值
324   ls_contents-column = 1.
325   ls_contents-row = 1.
326   ls_contents-value = p_value.
327   APPEND ls_contents TO lt_contents.
328 
329   "每次只写一行一列
330   CALL METHOD g_spreadsheet->insert_range_dim
331     EXPORTING
332       name     = 'cell'
333       no_flush = 'X'
334       top      = p_row
335       left     = p_col
336       rows     = 1
337       columns  = 1.
338 
339   CALL METHOD g_spreadsheet->set_ranges_data
340     EXPORTING
341       ranges   = lt_ranges
342       contents = lt_contents
343       no_flush = 'X'.
344 ENDFORM.