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.