Загрузка данных из Excel в таблицу базы данных с помощью ООП

  1. *&---------------------------------------------------------------------*
  2. *& Report ZBW_P_AG_LOADIXLS
  3. *&
  4. *&---------------------------------------------------------------------*
  5. *&Created by: Artyom Grabarov
  6. *&Version: 1.0
  7. *&---------------------------------------------------------------------*
  8. *&Comments:
  9. *&Программа для загрузки таблицы соответствия реквизитов перевозки и статей доходов
  10. *&
  11. *&---------------------------------------------------------------------*
  12.  
  13. report zbw_p_ag_loadixls.
  14. include zbw_p_ag_loadixls_log.
  15. include zbw_p_ag_loadixls_data.
  16. * Initialization event.
  17.  
  18. class c_oi_errors definition load.
  19.  
  20. * Create Instance control for container
  21. call method c_oi_container_control_creator=>get_container_control
  22. importing
  23. control = o_control
  24. error = o_error.
  25.  
  26. if o_error->has_failed = 'X'.
  27. call method o_error->raise_message
  28. exporting
  29. type = 'E'.
  30.  
  31. * Create generic container linked to container in screen 100
  32. create object obj_container
  33. exporting
  34. container_name = 'CONTAINER'
  35. exceptions
  36. cntl_error = 1
  37. cntl_system_error = 2
  38. create_error = 3
  39. lifetime_error = 4
  40. lifetime_dynpro_dynpro_link = 5
  41. others = 6.
  42.  
  43. if sy-subrc <> 0.
  44. message e208(00) with 'Error creating container'.
  45.  
  46. * Establish connection to GUI Control
  47. call method o_control->init_control
  48. exporting
  49. r3_application_name = 'Excel Document Container'
  50. inplace_enabled = 'X'
  51. parent = obj_container
  52. importing
  53. error = o_error.
  54.  
  55. if o_error->has_failed = 'X'.
  56. call method o_error->raise_message
  57. exporting
  58. type = 'E'.
  59.  
  60. * Create Document Proxy
  61. call method o_control->get_document_proxy
  62. exporting
  63. document_type = soi_doctype_excel_sheet
  64. importing
  65. document_proxy = o_document
  66. error = o_error.
  67.  
  68. if o_error->has_failed = 'X'.
  69. call method o_error->raise_message
  70. exporting
  71. type = 'E'.
  72.  
  73. * Start-of-selection.
  74.  
  75. * Call dialog to navigate to file
  76. call method cl_gui_frontend_services=>file_open_dialog
  77. exporting
  78. default_extension = '.xls'
  79. file_filter = '*.xls'
  80. initial_directory = 'C:\'
  81. changing
  82. file_table = t_files
  83. rc = v_rcode
  84. user_action = v_action
  85. exceptions
  86. file_open_dialog_failed = 1
  87. cntl_error = 2
  88. error_no_gui = 3
  89. others = 4.
  90. if sy-subrc <> 0.
  91. message e208(00) with 'FILE_OPEN_DIALOG'.
  92.  
  93. * Only continue if User hasn't cancelled
  94. check: v_action = 0.
  95.  
  96. * Determine filename to open Excel document
  97. read table t_files index 1 into s_files.
  98. if sy-subrc = 0.
  99. concatenate 'FILE://' s_files-filename into v_doc_name.
  100. message e208(00).
  101. endif. "sy-subrc = 0
  102.  
  103. * Open Spreadsheet in SAPWORKDIR
  104. call method o_document->open_document
  105. exporting
  106. open_inplace = 'X'
  107. document_title = 'Excel'
  108. document_url = v_doc_name
  109. no_flush = ''
  110. importing
  111. error = o_error.
  112.  
  113. if o_error->has_failed = 'X'.
  114. call method o_error->raise_message
  115. exporting
  116. type = 'E'.
  117.  
  118. * Open Spreadsheet interface
  119. call method o_document->get_spreadsheet_interface
  120. exporting
  121. no_flush = ''
  122. importing
  123. sheet_interface = o_spreadsheet
  124. error = o_error.
  125.  
  126. if o_error->has_failed = 'X'.
  127. call method o_error->raise_message
  128. exporting
  129. type = 'E'.
  130.  
  131. * Set selection for 1000 rows
  132. call method o_spreadsheet->set_selection
  133. exporting
  134. top = 1
  135. left = 1
  136. rows = '7900'
  137. columns = '25'.
  138.  
  139. * Define Range in spreadsheet
  140. call method o_spreadsheet->insert_range
  141. exporting
  142. name = 'Test'
  143. rows = '7900'
  144. columns = '25'
  145. no_flush = ''
  146. importing
  147. error = o_error.
  148.  
  149. if o_error->has_failed = 'X'.
  150. call method o_error->raise_message
  151. exporting
  152. type = 'E'.
  153.  
  154. s_ranges-name = 'Test'.
  155. s_ranges-rows = '7900'.
  156. s_ranges-columns = '25'.
  157. append s_ranges to t_ranges.
  158.  
  159. * Get data
  160. call method o_spreadsheet->get_ranges_data
  161. exporting
  162. all = ''
  163. no_flush = ''
  164. importing
  165. contents = t_data
  166. error = o_error
  167. changing
  168. ranges = t_ranges.
  169.  
  170. if o_error->has_failed = 'X'.
  171. call method o_error->raise_message
  172. exporting
  173. type = 'E'.
  174. * Close document
  175.  
  176. * Close the document
  177. call method o_document->close_document
  178. exporting
  179. do_save = ''
  180. no_flush = ''
  181. importing
  182. has_changed = v_changed
  183. error = o_error.
  184.  
  185. if o_error->has_failed = 'X'.
  186. call method o_error->raise_message
  187. exporting
  188. type = 'E'.
  189.  
  190. * Clear Document Resources
  191. call method o_document->release_document
  192. exporting
  193. no_flush = ''
  194. importing
  195. error = o_error.
  196.  
  197. if o_error->has_failed = 'X'.
  198. call method o_error->raise_message
  199. exporting
  200. type = 'E'.
  201.  
  202. * Clear table of file names
  203. free: t_files,
  204. o_control.
  205.  
  206. * Process the data before write in to bd table
  207. loop at t_data into s_data.
  208. IF s_data-row >= 5.
  209.  
  210. at new row.
  211. if i_data-Z_REVENUES_GROUP is NOT INITIAL.
  212. APPEND i_data.
  213. CLEAR i_data.
  214.  
  215. case s_data-column.
  216. when '1'.
  217. "i_data-Z_INC_ARTICLE_ID = s_data-VALUE.
  218. when '2'.
  219. i_data-Z_REVENUES_GROUP = s_data-VALUE.
  220. when '3'.
  221. "i_data-Z_INCOME_SUB1 = s_data-VALUE.
  222. when '4'.
  223. "i_data-Z_INCOME_SUB2 = s_data-VALUE.
  224. when '5'.
  225. " i_data-Z_INCOME_ITEM = s_data-VALUE.
  226. SELECT SINGLE /BIC/TEXT_AN1 /BIC/TEXT_AN2 /BIC/TEXT_AN3 /BIC/TEXT_AN4 FROM /BI0/MPROFIT_CTR BYPASSING BUFFER INTO
  227. (temp_txt_string1, temp_txt_string2, temp_txt_string3, temp_txt_string4)
  228. WHERE PROFIT_CTR = i_data-Z_REVENUES_GROUP and DATETO >= sy-datum AND OBJVERS eq 'A' AND CO_AREA EQ '1'.
  229. CONCATENATE temp_txt_string1 temp_txt_string2 temp_txt_string3 temp_txt_string4 into i_data-Z_INCOME_ITEM.
  230. when '6'.
  231. if s_data-VALUE is NOT INITIAL.
  232. i_data-Z_MZHS = 'X'.
  233. when '7'.
  234. if s_data-VALUE is NOT INITIAL.
  235. i_data-Z_PV = 'X'.
  236. when '8'.
  237. if s_data-VALUE is NOT INITIAL.
  238. i_data-Z_PK = 'X'.
  239. when '9'.
  240. if s_data-VALUE is NOT INITIAL.
  241. i_data-Z_LT = 'X'.
  242. when '10'.
  243. if s_data-VALUE is NOT INITIAL.
  244. i_data-Z_GKR = 'X'.
  245. when '11'.
  246. i_data-Z_MESSAGE_CODE = s_data-VALUE.
  247. when '12'.
  248. i_data-Z_PAYMENT_CODE = s_data-VALUE.
  249. when '13'.
  250. i_data-Z_PAYER_CODE = s_data-VALUE.
  251. when '14'.
  252. i_data-Z_SIGN_CORRECT = s_data-VALUE.
  253. when '15'.
  254. i_data-Z_PLACE_AND_FORM = s_data-VALUE.
  255. when '16'.
  256. i_data-Z_TYPE_OF_MSG = s_data-VALUE.
  257. when '17'.
  258. i_data-Z_PROPERTY_SIGN = s_data-VALUE.
  259. when '18'.
  260. i_data-Z_SIGN_OF_IT = s_data-VALUE.
  261. when '19'.
  262. i_data-Z_SIGN_OF_V_OR_C = s_data-VALUE.
  263. when '20'.
  264. i_data-Z_KIND_OF_WAGON = s_data-VALUE.
  265. when '21'.
  266. i_data-Z_SHIPPING_CODE = s_data-VALUE.
  267. when '22'.
  268. i_data-Z_CARCO_CODE = s_data-VALUE.
  269. when '23'.
  270. i_data-Z_EMPTY_COL = s_data-VALUE.
  271. when '24'.
  272. i_data-Z_TARIFF_COMP_ID = s_data-VALUE.
  273. when '25'.
  274. i_data-Z_TARIFF_COMP_SU = s_data-VALUE.
  275. when others.
  276. " write:(10) s_data-value.
  277. * записываем данные в БД
  278. modify ZBW_TABL_CORRTAB FROM TABLE i_data .
  279. if sy-subrc eq 0.
  280. WRITE 'Данные успешно загружены в таблицу ZBW_TABL_CORRTAB'.
  281. WRITE 'Ошибка при записи данных в таблицу. Обратитесь к ABAP разработчику для решения данной проблемы'.
  1. *&---------------------------------------------------------------------*
  2. *& Include ZBW_P_AG_LOADIXLS_DATA
  3. *&---------------------------------------------------------------------*
  4. * Define Screen Container
  5. data: obj_container type ref to cl_gui_custom_container.
  6. data: o_error type ref to i_oi_error,
  7. o_control type ref to i_oi_container_control,
  8. o_document type ref to i_oi_document_proxy,
  9. o_spreadsheet type ref to i_oi_spreadsheet.
  10.  
  11. * Data declarations.
  12. data: t_files type filetable,
  13. s_files type file_table,
  14. v_doc_name type char256,
  15. v_changed type int4,
  16. v_rcode type int4,
  17. t_ranges type soi_range_list,
  18. s_ranges type soi_range_item,
  19. t_data type soi_generic_table,
  20. s_data type soi_generic_item,
  21. i_data TYPE TABLE OF ZBW_TABL_CORRTAB WITH HEADER LINE, " ZBW_TABL_CORRTAB this is table BD for write data
  22. v_action type int4.
  23.  
  24. data: temp_txt_string1 TYPE string,
  25. temp_txt_string2 TYPE string,
  26. temp_txt_string3 TYPE string,
  27. temp_txt_string4 TYPE string.

Include zbw_p_ag_loadixls_log содержит только лог изменений и не нужен для функционирования программы.

Комментарии