INCLUDE officeintegrationinclude.

TYPE-POOLS: SBDST.
TYPE-POOLS: SYDES.

DATA: doc_classname TYPE sbdst_classname VALUE 'SOFFICEINTEGRATION',
      doc_classtype TYPE sbdst_classtype VALUE 'OT',
      doc_object_key TYPE sbdst_object_key. " VALUE 'lutin'.

DATA: bds_instance TYPE REF TO cl_bds_document_set.
DATA: doc_signature TYPE sbdst_signature,
      wa_doc_signature LIKE LINE OF doc_signature,
      doc_components TYPE sbdst_components,
      doc_uris TYPE sbdst_uri,
      wa_doc_uris LIKE LINE OF doc_uris,
      g_macros_run type text32,
      g_CREATE_SCREEN type char1,
      g_ON_SCREEN type char1.

*DATA: already_done,
*      colCount type i value 10.
DATA: has TYPE i.

*DATA  cell_value(64) TYPE C.
DATA: control TYPE REF TO i_oi_ole_container_control.
DATA: int_container TYPE REF TO cl_gui_custom_container.
DATA: ext_container TYPE REF TO cl_gui_custom_container.
DATA: table_coll TYPE REF TO i_oi_table_collection.

DATA: retcode TYPE t_oi_ret_string,
      error   TYPE REF TO i_oi_error,
      document_type TYPE soi_document_type,
      document TYPE REF TO i_oi_document_proxy.
*     document_format TYPE soi_document_type.
DATA  handle TYPE REF TO i_oi_spreadsheet.

DATA item_url(256).
*   таблица для вывода при помощи Z_EXCEL
Data value_table type zexcel_cells.
Data wa_value_table like line of value_table.
Data wa_in_value_table like line of value_table.
Data str like wa_value_table-value.
Data len type i.

FUNCTION Z_EXCEL.

* переносим строки входной таблицы во внутреннюю

  g_macros_run = macros_run.
  g_CREATE_SCREEN = CREATE_SCREEN.
  g_ON_SCREEN = ON_SCREEN.

  REFRESH value_table.
  loop at in_value_table into wa_in_value_table.
*   адрес, примечание и цвет фона ячейки переносим как есть
    wa_value_table-range = wa_in_value_table-range.
    wa_value_table-commentariy = wa_in_value_table-commentariy.
    wa_value_table-color = wa_in_value_table-color.

*   значение ячейки сначала проверим
    str = wa_in_value_table-value.
    IF str <> ''.
      len = strlen( str ) - 1.
*    если значение типа '    12.45-', тогда надопреобразовать к
*    нормальному виду   '-12.45'
      if str+len(1) eq '-'.
        str+len = ' '.
        condense str.
        concatenate '-' str into str.
      endif.
    ENDIF.
    wa_value_table-value = str.
    append wa_value_table to value_table.
  endloop.



* first get the SAP DOI i_oi_container_control interface
  CALL METHOD c_oi_ole_control_creator=>get_ole_container_control
                    IMPORTING control = control
                              error = error.

* create a control container as defined in dynpro 100

  IF ON_SCREEN IS INITIAL.

    IF container is initial.
      CREATE OBJECT int_container
            EXPORTING container_name = '__CONTAINER'.
    else.
      int_container = container.
    endif.

  ELSE.

      CREATE OBJECT int_container
            EXPORTING container_name = 'CONTAINER'.

  ENDIF.

* initialize the SAP DOI Container, tell it to run in the container
* specified above and tell it to run Excel in-place
    CALL METHOD control->init_control
                      EXPORTING r3_application_name =
                                            'R/3 Basis'     "#EC NOTEXT
                                inplace_enabled = ON_SCREEN
                                inplace_scroll_documents = 'X'
                                parent = int_container
                                register_on_close_event = 'X'
                                register_on_custom_event = 'X'
                                no_flush = 'X'
                      IMPORTING error = error.

  CALL METHOD control->get_table_collection
                     EXPORTING no_flush = 'X'
                     IMPORTING table_collection = table_coll
                               retcode = retcode.

* Передаем таблицы:
  IF NOT value_table[] IS INITIAL.
    CALL METHOD table_coll->add_table
                         EXPORTING table_name = 'value_table'
                           table_type = table_coll->table_type_output
                            no_flush  = 'X'
                         IMPORTING retcode = retcode
                         CHANGING  data_table = value_table.
  ENDIF.

CLEAR item_url.
  wa_doc_signature-prop_name = 'DESCRIPTION'.
  document_type = 'Excel.Sheet'.
  wa_doc_signature-prop_value = DOC_ID. " имя файла (его ID)
  doc_object_key = DOC_KEY.

  APPEND wa_doc_signature TO doc_signature.
  CREATE OBJECT bds_instance.

  CALL METHOD bds_instance->get_info
                  EXPORTING classname = doc_classname
                            classtype = doc_classtype
                            object_key = doc_object_key
                  CHANGING components = doc_components
                           signature = doc_signature.

  CALL METHOD bds_instance->get_with_url
                       EXPORTING classname = doc_classname
                                 classtype = doc_classtype
                                 object_key = doc_object_key
                       CHANGING uris = doc_uris
                                signature = doc_signature.

  FREE bds_instance.

  READ TABLE doc_uris INTO wa_doc_uris INDEX 1.

  item_url = wa_doc_uris-uri.
  CALL METHOD control->get_document_proxy
             EXPORTING document_type = document_type
                       REGISTER_CONTAINER = 'X'
             IMPORTING document_proxy = document
                       error = error.

  if create_screen is initial.

    perform open_document using g_macros_run.

  else.

    call screen 100.

  endif.

endfunction.

form open_document using macros_run TYPE  TEXT32.

  CALL METHOD document->open_document
  EXPORTING
    open_inplace   = g_ON_SCREEN
    protect_document = ' '
    document_url = item_url
  IMPORTING
    retcode = retcode
    error = error.

  CALL METHOD document->has_spreadsheet_interface
      IMPORTING
              is_available = has
              retcode = retcode
              error = error.

  IF NOT has IS INITIAL.
    CALL METHOD document->get_spreadsheet_interface
        IMPORTING
                sheet_interface = handle.

    CALL METHOD document->execute_macro
         EXPORTING
           macro_string = MACROS_RUN
         IMPORTING
           retcode = retcode.

  ENDIF.

endform.