Code Gallery
Skip to end of metadata
Go to start of metadata

Read data from multiple worksheets of an excel file, using SAP classes and interfaces:  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

The test data:

The selection screen:

The output:

19 Comments

  1. Unknown User (yzooe3m)

    This coding works really fine! Thanks a lot for your help...cool coding.

  2. Unknown User (c14phjl)

    Thank you very much .........venkat

  3. Unknown User (w9ro1ys)

    Hi, just happen to had requirement to load data from multiple worksheet from excel file.. and found this piece of codes. Great one that can be leverage easily.

    Thank you very much

    Wasis

  4. Unknown User (vl1g0o3)

    Hi, Really its excellent....thanks a lot.... Regards, Venkat...

  5. Former Member

    hi,

    great work. but there seems to be a problem when reading xlsx file . a pop up comes from MS excel which seems to be some security check.I am still try to find a way to get that pop up out :(

  6. Former Member

    Hi,

    Very good. but i still have some problem when reading xlsx file.

    Who is solved this problem ?

  7. Former Member

    Hi this issue with xlsx is due to your SAP GUI patch level . this  issue is not there for patch level 13 and above .

  8. Unknown User (ze313r6)

    Many Thanks, I just solve my problem.

  9. Unknown User (zqj76gm)

    Hi,  this solution work great. However, it didn't support characters that have more than 256 in a single column. Can anyone here propose an alternative solution ?

    Many thanks in advance...

  10. Dhirendra Pandit

    Thanks for this code,

    I have a problem when i tried to read the data from the sheet using below code. 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.

    The i_data will only give the data for max 9999 rows.
    In case of more records in excel how do i read those records and how should i populate that to the internal table again ?

  11. sankara rao bhatta

    Amazing stuff..Code works perfectly well

  12. Ou Peggy

    Just need it

    thank u very much!

  13. Vikas Singh

    Updated wiki to call 

    iref_spreadsheet->delete_ranges

     

    Otherwise the ranges kept on getting added and each subsequent call will bring old data as well which needed to be manually removed.

    1. Mustafa Kerim YILMAZ

      iref_spreadsheet->delete_ranges removed all ranges. Also user defined ranges deleted. So some formulas can be broken. Using RANGESDEF paramter for get_ranges_data instead of ALL is best option.

  14. Former Member

     

    Hi,
    This works perfectly fine for excel files without macros.
    But I am facing problem while uploading file with macros.
    The system freezes after the macro enable/diable popup.

    Can anyone help me on this?

     

    Regards

    Prabhat

  15. Chintamani Mhadgut

    Dear ,

    Thanks for this helpful article , but i am not able to find the code.

    Please provide the code for above as i have urgent requirment which resolve my current issue.

    Thanks & Regards,

    Chintamani Mhadgut

    1. Former Member

      The code is given above the snapshots. (smile)

       

  16. Former Member

    cnt find the source code !!

  17. Mustafa Kerim YILMAZ

    Code have bugs, get_ranges_data read all ranges in sheet. User can create own sheets. I prefer using RANGESDEF parameter for get_ranges_data instead of ALL.