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

NX     SAPBW.ru