ABAP DOI详解(2)

我们之所以用Excel输出，主要是想用Excel在显示中的优势，所以最常见的做法，往往调用Excel模板文件来实现。这一篇，我们就来操作Excel模板文件，另外，excel文档放在dialog screen中显示。

创建Dialog Screen

新建一个screen, scren number为100，在screen中添加一个custom control, name属性为CONTAINER1，界面如下：

新建一个GUI Status，编号为100，在gui status中定义function code为EXIT，functional type为E的功能码（function key）。

切换到screen的flow logic，增加一个PAI事件，然后编写代码，用于处理屏幕的退出：

module exit_program input.  save_ok = ok_code.  clear ok_code.  if save_ok = 'EXIT'.    leave program.  endif.endmodule.     
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

上传Excel文档

用事务码OAOR将Excel文档上传。输入OAOR，进入下面的界面，输入后面程序需要使用的几个重要标识：class name, class type和object key。class name选择SAP提供的HRFPM_EXCEL_STANDARD就可以了。如果没有，请用事物码SBDSV1进行定于。object key建议使用excel文档的文件名，以便查找。

点击“执行”（F8)按钮，进入下面的界面

从左下部分的doc type中，选择table template，右键菜单，导入文档。导入模板文档doitest.xls。

OK，现在文档已经导入了。我们可以在OAOR界面中，显示文档、文档的详细信息(detail info.）等。

获取模板文档的信息

操作excel模板文档，使用cl_bds_document_set类，这个类的get_with_url方法获取文档的url。首先定义一些global变量：

* business document systemdata: 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 urldata: gt_bds_uris type sbdst_uri,      gs_bds_url like line of gt_bds_uris,      g_template_url(256) type c.
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12

获取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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

cl_bds_document_set的静态方法get_with_url获取excel template的url。数据存放在内表中，读取后放在global变量g_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.       
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

将数据写入Excel

我们从spfli表中获取所有航班的信息：

* output internale tabledata: 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.form get_data.  select * from spfli    into corresponding fields of table gt_spfli.endform.         
1
2
3
4
5
6
7
8
9
10
11
12
13
1
2
3
4
5
6
7
8
9
10
11
12
13

数据写入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.
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8

• set_range_data方法，写入数据到range，写入的时候，ranges参数设定range的名称和大小, contents参数设定写入的内容。OK，假设我们将要输出的数据在内表gt_spfli中是这样的：

我们想要在excel中按同样的方式输出。我们定义ranges参数的内表如下：

上图表示我们将要输出的数据，名称为cell，4列5行。

定义contents参数的内表如下，确定每一个单元个的内容：

set_range_data方法：

  call method gr_spreadsheet->set_ranges_data    exporting      ranges   = gt_ranges      contents = gt_contents      no_flush = 'X'.
1
2
3
4
5
1
2
3
4
5

对象销毁

在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.  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

exit-command事件的代码变成这个样子

module exit_program input.  save_ok = ok_code.  clear ok_code.  if save_ok = 'EXIT'.    perform release_objects.    leave program.  endif.endmodule.   
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9

完整代码

*&---------------------------------------------------------------------**& 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 systemdata: 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 urldata: 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 tabledata: 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 Exceldata: 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347

程序的界面如下。最主要的缺点是container的大小是固定的，后面我们看看怎么解决这个问题。