Skip to end of metadata
Go to start of metadata

Author: Narendran Muthukumaran
Submitted: 12/21/2007
Related Links: None

Description
This program can be used to upload the Excel data. The program uses the OO methods to do that. This program has been tested to upload double byte characters like Japanese characters. And works if the Excel is having a filter.

Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'
REPORT z_excel_upload.

* Define Screen Container
DATA: obj_container TYPE REF TO cl_gui_custom_container.
DATA: o_error       TYPE REF TO i_oi_error,
      o_control     TYPE REF TO i_oi_container_control,
      o_document    TYPE REF TO i_oi_document_proxy,
      o_spreadsheet TYPE REF TO i_oi_spreadsheet.

* Data declarations.
DATA: t_files       TYPE filetable,
      s_files       TYPE file_table,
      v_doc_name    TYPE char256,
      v_changed     TYPE int4,
      v_rcode       TYPE int4,
      t_ranges      TYPE soi_range_list,
      s_ranges      TYPE soi_range_item,
      t_data        TYPE soi_generic_table,
      s_data        TYPE soi_generic_item,
      v_action      TYPE int4.

* Initialization event.
INITIALIZATION. 

CLASS c_oi_errors DEFINITION LOAD.

* Create Instance control for container
  CALL METHOD c_oi_container_control_creator=>get_container_control
       IMPORTING control = o_control
                 error   = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.

* Create generic container linked to container in screen 100
  CREATE OBJECT obj_container
    EXPORTING
     container_name               = 'CONTAINER'
    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 e208(00) WITH 'Error creating container'.
  ENDIF.

* Establish connection to GUI Control
  CALL METHOD o_control->init_control
      EXPORTING r3_application_name = 'Excel Document Container'
                 inplace_enabled     = 'X'
                 parent              = obj_container
       IMPORTING error               = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.

* Create Document Proxy
  CALL METHOD o_control->get_document_proxy
       EXPORTING document_type   = soi_doctype_excel_sheet
       IMPORTING document_proxy  = o_document
                 error           = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.

* Start-of-selection.
START-OF-SELECTION.

* Call dialog to navigate to file
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
     EXPORTING
       default_extension       = '.xls'
       file_filter             = '*.xls'
       initial_directory       = 'C:\'
    CHANGING
       file_table              = t_files
       rc                      = v_rcode
       user_action             = v_action
    EXCEPTIONS
       file_open_dialog_failed = 1
       cntl_error              = 2
       error_no_gui            = 3
       OTHERS                  = 4.
  IF sy-subrc <> 0.
    MESSAGE e208(00) WITH 'FILE_OPEN_DIALOG'.
  ENDIF.

* Only continue if User hasn't cancelled
  CHECK: v_action = 0.

* Determine filename to open Excel document
  READ TABLE t_files INDEX 1 INTO s_files.
  IF sy-subrc = 0.
    CONCATENATE 'FILE://' s_files-filename INTO v_doc_name.
  ELSE.
    MESSAGE e208(00).
  ENDIF.  "sy-subrc = 0

* Open Spreadsheet in SAPWORKDIR
  CALL METHOD o_document->open_document
       EXPORTING open_inplace     = 'X'
                 document_title   = 'Excel'
                 document_url     = v_doc_name
                 no_flush         = ''
       IMPORTING error            = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.

* Open Spreadsheet interface
  CALL METHOD o_document->get_spreadsheet_interface
       EXPORTING no_flush        = ''
       IMPORTING sheet_interface = o_spreadsheet
                 error           = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.

* Set selection for 1000 rows
  CALL METHOD o_spreadsheet->set_selection
               EXPORTING top   = 1
                         left  = 1
                         rows  = '1000'
                         columns = '36'.

* Define Range in spreadsheet
  CALL METHOD o_spreadsheet->insert_range
         EXPORTING name      = 'Test'
                   rows      = '1000'
                   columns   = '36'
                   no_flush  = ''
         IMPORTING error     = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF. 

  s_ranges-name    = 'Test'.
  s_ranges-rows    = '1000'.
  s_ranges-columns = '36'.
  APPEND s_ranges TO t_ranges.

* Get data
  CALL METHOD o_spreadsheet->get_ranges_data
         EXPORTING all       = ''
                   no_flush  = ''
         IMPORTING contents  = t_data
                   error     = o_error
         CHANGING  ranges    = t_ranges. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.* Close document
 
* Close the document
  CALL METHOD o_document->close_document
         EXPORTING do_save     = ''
                   no_flush    = ''
         IMPORTING has_changed = v_changed
                   error       = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.

* Clear Document Resources
  CALL METHOD o_document->release_document
         EXPORTING no_flush = ''
         IMPORTING error    = o_error. 

  IF o_error->has_failed = 'X'.
    CALL METHOD o_error->raise_message
         EXPORTING type = 'E'.
  ENDIF.

* Clear table of file names
  FREE: t_files,
        o_control.

* Display the data
  LOOP AT t_data INTO s_data.
    AT NEW row.
      SKIP.
    ENDAT.
    WRITE:(10) s_data-value.
  ENDLOOP.

5 Comments

  1. Anonymous

    What if your Excel file has more than 1000 rows?

  2. Unknown User (k5jmq30)

    With works with filter do you mean that it reads only the filtered data or that it ignores the filter when reading the data?

  3. Former Member


    very good work!!!

  4. Former Member

    Thank you for posting your solution of reading excel files into sap system. Very good work!

  5. Former Member

    Great! Just what I need to import even hidden columns/rows! Thanx a lot.