Read multiple sheets of an Excel file into SAP through ABAP

엑셀 여러 쉬트의 데이터를 컨트롤 할수 있는 소스..

 

출처: https://wiki.scn.sap.com/wiki/display/Snippets/Read+multiple+sheets+of+an+Excel+file+into+SAP+through+ABAP

 

Read multiple sheets of an Excel file into SAP through ABAP

Skip to end of metadata

• Created by Sourav Bhaduri, last modified by Vikas Singh on Dec 01, 2014

Go to start of metadata

I couldn't find a good solution in ABAP to read data from multiple worksheets of an excel file. I developed this code using SAP classes and intefaces:  CL_GUI_CUSTOM_CONTAINER,I_OI_CONTAINER_CONTROL,I_OI_DOCUMENT_PROXY AND I_OI_SPREADSHEET.

 

?

*&---------------------------------------------------------------------* *& Report  ZTEST_SOURAV_EXCEL *& *&---------------------------------------------------------------------* *& Sourav Bhaduri 02-Dec-2008 *&---------------------------------------------------------------------*   REPORT  ztest_sourav_excel NO STANDARD PAGE HEADING.   DATA: oref_container   TYPE REF TO cl_gui_custom_container, iref_control     TYPE REF TO i_oi_container_control, iref_document    TYPE REF TO i_oi_document_proxy, iref_spreadsheet TYPE REF TO i_oi_spreadsheet, iref_error       TYPE REF TO i_oi_error.   DATA: v_document_url TYPE c LENGTH 256, i_sheets TYPE soi_sheets_table, wa_sheets TYPE soi_sheets, i_data        TYPE soi_generic_table, wa_data       TYPE soi_generic_item, i_ranges      TYPE soi_range_list.   PARAMETERS: p_file  TYPE  localfile OBLIGATORY, p_rows TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536) p_cols TYPE i DEFAULT 10 OBLIGATORY.    "Columns (Maximum 256)   INITIALIZATION.     CALL METHOD c_oi_container_control_creator=>get_container_control      IMPORTING        control = iref_control        error   = iref_error *      retcode =       .   IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'E'.   ENDIF.       CREATE OBJECT oref_container     EXPORTING *      parent                      =        container_name              = 'CONT' *      style                       = *      lifetime                    = lifetime_default *      repid                       = *      dynnr                       = *      no_autodef_progid_dynnr     =     EXCEPTIONS        cntl_error                  = 1        cntl_system_error           = 2        create_error                = 3        lifetime_error              = 4        lifetime_dynpro_dynpro_link = 5        OTHERS                      = 6.   IF sy-subrc <> 0.     MESSAGE e001(00) WITH 'Error while creating container'.   ENDIF.     CALL METHOD iref_control->init_control     EXPORTING *      dynpro_nr                = SY-DYNNR *      gui_container            = ' '        inplace_enabled          = 'X' *      inplace_mode             = 0 *      inplace_resize_documents = ' ' *      inplace_scroll_documents = ' ' *      inplace_show_toolbars    = 'X' *      no_flush                 = ' ' *      parent_id                = cl_gui_cfw=>dynpro_0        r3_application_name      = 'EXCEL CONTAINER' *      register_on_close_event  = ' ' *      register_on_custom_event = ' ' *      rep_id                   = SY-REPID *      shell_style              = 1384185856        parent                   = oref_container *      name                     = *      autoalign                = 'x'     IMPORTING        error                    = iref_error *      retcode                  =     EXCEPTIONS        javabeannotsupported     = 1        OTHERS                   = 2           .   IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'E'.   ENDIF.     CALL METHOD iref_control->get_document_proxy     EXPORTING *     document_format    = 'NATIVE'       document_type      = soi_doctype_excel_sheet *     no_flush           = ' ' *     register_container = ' '      IMPORTING        document_proxy     = iref_document        error              = iref_error *    retcode            =       .   IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'E'.   ENDIF.   AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.   * To provide F4 help for the file   PERFORM sub_file_f4.   START-OF-SELECTION.     CONCATENATE 'FILE://' p_file INTO v_document_url.     CALL METHOD iref_document->open_document     EXPORTING       document_title   = 'Excel'       document_url     = v_document_url *     no_flush         = ' '       open_inplace     = 'X' *     open_readonly    = ' ' *     protect_document = ' ' *     onsave_macro     = ' ' *     startup_macro    = '' *     user_info        =     IMPORTING       error            = iref_error *     retcode          =       .   IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'I'.     LEAVE LIST-PROCESSING.   ENDIF.     CALL METHOD iref_document->get_spreadsheet_interface      EXPORTING        no_flush        = ' '     IMPORTING       error           = iref_error       sheet_interface = iref_spreadsheet *     retcode         =       .     IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'I'.     LEAVE LIST-PROCESSING.   ENDIF.     CALL METHOD iref_spreadsheet->get_sheets       EXPORTING        no_flush = ' ' *      updating = -1      IMPORTING        sheets   = i_sheets        error    = iref_error *      retcode  =       .   IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'I'.     LEAVE LIST-PROCESSING.   ENDIF.   LOOP AT i_sheets INTO wa_sheets.     CALL METHOD iref_spreadsheet->select_sheet        EXPORTING           name     = wa_sheets-sheet_name *         no_flush = ' '       IMPORTING           error    = iref_error *         retcode  =             .     IF iref_error->has_failed = 'X'.       EXIT. *      call method iref_error->raise_message *        exporting *          type = 'E'.     ENDIF.     CALL METHOD iref_spreadsheet->set_selection       EXPORTING         top     = 1         left    = 1         rows    = p_rows         columns = p_cols.       CALL METHOD iref_spreadsheet->insert_range       EXPORTING         name     = 'Test'         rows     = p_rows         columns  = p_cols         no_flush = ''       IMPORTING         error    = iref_error.     IF iref_error->has_failed = 'X'.       EXIT. *      call method iref_error->raise_message *        exporting *          type = 'E'.     ENDIF.       REFRESH i_data.       CALL METHOD iref_spreadsheet->get_ranges_data        EXPORTING *        no_flush  = ' '          all       = 'X' *        updating  = -1 *        rangesdef =        IMPORTING          contents  = i_data          error     = iref_error *        retcode   =        CHANGING          ranges    = i_ranges   * Remove ranges not to be processed else the data keeps on adding up         call method iref_spreadsheet->delete_ranges           exporting             ranges = i_ranges                  .     DELETE i_data WHERE value IS INITIAL OR value = space.     ULINE.     WRITE:/1 wa_sheets-sheet_name COLOR 3.     ULINE.       LOOP AT i_data INTO wa_data.       WRITE:(50) wa_data-value.       AT END OF row.         NEW-LINE.       ENDAT.     ENDLOOP.   ENDLOOP.     CALL METHOD iref_document->close_document *  EXPORTING *    do_save     = ' ' *    no_flush    = ' '      IMPORTING        error       = iref_error *    has_changed = *    retcode     =       .   IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'I'.     LEAVE LIST-PROCESSING.   ENDIF.   CALL METHOD iref_document->release_document *  EXPORTING *    no_flush = ' '      IMPORTING        error    = iref_error *    retcode  =       .   IF iref_error->has_failed = 'X'.     CALL METHOD iref_error->raise_message       EXPORTING         type = 'I'.     LEAVE LIST-PROCESSING.   ENDIF.   *&---------------------------------------------------------------------* *&      Form  SUB_FILE_F4 *&---------------------------------------------------------------------* *       F4 help for file path *----------------------------------------------------------------------* FORM sub_file_f4 .   DATA:   l_desktop       TYPE string,   l_i_files       TYPE filetable,   l_wa_files      TYPE file_table,   l_rcode         TYPE int4.   * Finding desktop   CALL METHOD cl_gui_frontend_services=>get_desktop_directory     CHANGING       desktop_directory    = l_desktop     EXCEPTIONS       cntl_error           = 1       error_no_gui         = 2       not_supported_by_gui = 3       OTHERS               = 4.   IF sy-subrc <> 0.     MESSAGE e001(00) WITH         'Desktop not found'.   ENDIF.   * Update View   CALL METHOD cl_gui_cfw=>update_view     EXCEPTIONS       cntl_system_error = 1       cntl_error        = 2       OTHERS            = 3.     CALL METHOD cl_gui_frontend_services=>file_open_dialog      EXPORTING        window_title            = 'Select Excel file'        default_extension       = '.xls' *      default_filename        =        file_filter             = '.xls' *      with_encoding           =        initial_directory       = l_desktop *      multiselection          =     CHANGING       file_table              = l_i_files       rc                      = l_rcode *     user_action             = *     file_encoding           =     EXCEPTIONS       file_open_dialog_failed = 1       cntl_error              = 2       error_no_gui            = 3       not_supported_by_gui    = 4       OTHERS                  = 5           .   IF sy-subrc <> 0.     MESSAGE e001(00) WITH 'Error while opening file'.   ENDIF.     READ TABLE l_i_files INDEX 1 INTO l_wa_files.   IF sy-subrc = 0.     p_file = l_wa_files-filename.   ELSE.     MESSAGE e001(00) WITH 'Error while opening file'.   ENDIF.   ENDFORM.                    " SUB_FILE_F4