REPORT LINE-SIZE 200.


PARAMETERS p_file  TYPE string                 DEFAULT 'd:\temp\test.xlsx'.
PARAMETERS p_alles RADIOBUTTON GROUP grp       DEFAULT 'X'.
PARAMETERS p_berch RADIOBUTTON GROUP grp.
PARAMETERS p_bname TYPE c LENGTH 20 LOWER CASE DEFAULT 'Bereich_A'.

START-OF-SELECTION.
  PERFORM doi_test.

*&---------------------------------------------------------------------*
*&      Form  doi_test
*&---------------------------------------------------------------------*
FORM doi_test.


  DATA error            TYPE REF TO i_oi_error.
  DATA lv_size          TYPE i.
  DATA lt_upload        TYPE umb_bds_content.
  DATA lr_control       TYPE REF TO i_oi_container_control." OIContainerCtrl
  DATA lr_document      TYPE REF TO i_oi_document_proxy.   " Office Dokument
  DATA lr_spreadsheet   TYPE REF TO i_oi_spreadsheet.      " Spreadsheet

  CALL METHOD cl_gui_frontend_services=>gui_upload
    EXPORTING
      filename     = p_file
      filetype     = 'BIN'
      read_by_line = ' '
    IMPORTING
      filelength   = lv_size
    CHANGING
      data_tab     = lt_upload
    EXCEPTIONS
      OTHERS       = 1.
  IF sy-subrc <> 0.
    MESSAGE e000(oo) WITH 'Fehler bei GUI_UPLOAD'.
  ENDIF.


  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = lr_control
      error   = error.

*** init control
  CALL METHOD lr_control->init_control
    EXPORTING
      inplace_enabled       = 'X'
      no_flush              = 'X'
      r3_application_name   = 'Test DOI'
      parent                = cl_gui_container=>screen9
      inplace_show_toolbars = abap_false
    IMPORTING
      error                 = error
    EXCEPTIONS
      OTHERS                = 2.
  IF error->has_failed = abap_true. error->raise_message( 'I' ). STOP. ENDIF.

*** Get Documentproxy
  CALL METHOD lr_control->get_document_proxy
    EXPORTING
      document_type  = soi_doctype_excel_sheet "'Excel.Sheet'                " EXCEL
      no_flush       = 'X'
    IMPORTING
      document_proxy = lr_document
      error          = error.
  IF error->has_failed = abap_true. error->raise_message( 'I' ). STOP. ENDIF.

  CALL METHOD lr_document->open_document_from_table
    EXPORTING
      document_size    = lv_size
      document_table   = lt_upload
      document_title   = 'Test DOI'
      open_inplace     = 'X'
      protect_document = ' '
    IMPORTING
      error            = error.
  IF error->has_failed = abap_true. error->raise_message( 'I' ). STOP. ENDIF.


  CALL METHOD lr_document->get_spreadsheet_interface
    IMPORTING
      sheet_interface = lr_spreadsheet
      error           = error.
  IF error->has_failed = abap_true. error->raise_message( 'I' ). STOP. ENDIF.


  DATA lt_values  TYPE soi_generic_table.
  FIELD-SYMBOLS <value> LIKE LINE OF lt_values.
  DATA lt_ranges  TYPE soi_range_list.
  DATA lv_retcode TYPE soi_ret_string.
  DATA ls_range   LIKE LINE OF lt_ranges.

  CASE abap_true.
    WHEN p_alles.
*== Neuen Bereich definieren, falls notwendig:
      lr_spreadsheet->insert_range_dim(
         EXPORTING name = 'Mein_Bereich'
                   top       = 1
                   left      = 1
                   rows      = 10
                   columns   = 10
                   no_flush  = 'X' ).
      ls_range-name = 'Mein_Bereich'.
      APPEND ls_range TO lt_ranges.
    WHEN p_berch.
*== Ansonsten die in Excel benannten Bereiche verwenden
*      ls_range-name = 'Bereich_B'.
*      APPEND ls_range TO lt_ranges.
      ls_range-name = p_bname.
      APPEND ls_range TO lt_ranges.
  ENDCASE.

*== Laden der Zellwerte
  lr_spreadsheet->get_ranges_data( EXPORTING all = abap_false
                                   IMPORTING contents = lt_values
                                             error    = error
                                             retcode  = lv_retcode
                                    CHANGING ranges = lt_ranges ).

*== Ausgabe
  DATA lv_row TYPE i.
  DATA lv_pos TYPE i.
  LOOP AT lt_values ASSIGNING <value>.
    IF lv_row <> <value>-row.
      lv_row = <value>-row.
      WRITE: / <value>-row.
    ENDIF.
    lv_pos = ( <value>-column ) * 12.
    WRITE: <value>-value(10) COLOR COL_GROUP.
  ENDLOOP.

ENDFORM.