Additional Blogs by Members
cancel
Showing results for 
Search instead for 
Did you mean: 
ennowulff
Active Contributor

With Desktop-Office-Integration - or in short terms DOI - you are able to display MS-Office documents in an SAP-application. If you like you can use the documents inplace. Using the DOI-SAP classes you can do most of the relevant things. But at least you will have to display the document.

The following trick shows how to use DOI without the need of displaying the document...

Named Ranges

I will present you a report that uploads an Excel file and reads the cell data. The data access will be done by named ranges.

In this german example I have named three ranges:

  • Bereich_A
  • Bereich_B
  • Bereich_C

If there are no named ranges in your Excel you will have to define them.

You will get the cell data in a table with the following fields:

  • ROW
  • COLUMN
  • VALUE

You can access multiple ranges with one call. You will get back how many columns and rows are used with each range but it's a pity there is no assignment of ranges to the returned data.

This is the ranges table you get back:

And here are the cell values:

Hint:

If you want to be sure which data comes with which range you should use one call for each range!

Attention:

The DOI-Interface only supports up to 9999 rows and columns! Due to CHAR4-types for row and column count you will need to work with different ranges if you want to process more lines/ columns.

Building a DOI Document

The DOI framework works with interfaces. In the beginning you start with a concrete builder:

c_oi_container_control_creator=>get_container_control

This one builds a container control for the document. The control must be bound to a container. You do that within the initialzation method:

lr_control->init_control( ... ).

Afterwards you get the universal office document using a universal document interface. All provided functions are unspecific and will work with excel and word documents:

lr_control->get_document_proxy( ... ).

To directly access excel functions you need to get the concrete document object:

lr_document->get_spreadsheet_interface( IMPORTING sheet_interface = lr_spreadsheet ).

With this object you have access to excel-specific functions like accessing cell data:

lr_spreadsheet->get_ranges_data( ... ).

Do Not Display!

As far as I know there is no option for a hidden use of DOI documents. Except the following trick...

The dynpro that will be used in first instance in the SAPGUI is CL_GUI_CONTAINER=>SCREEN0. If you bind SCREEN0 as parent to a control the control will be displayed fullscreen.

For different popup-levels there exist some more screens: SCREEN1, SCREEN2 and so on. All these screens are "online" and accessible but not visible. We will use this circumstance and bind the DOI-Container to CL_GUI_CONTAINER=>SCREEN9

CALL METHOD lr_control->init_control

  EXPORTING

    inplace_enabled = 'X'

    no_flush = 'X'

    r3_application_name = 'Test DOI'

    parent = cl_gui_container=>screen9

  IMPORTING

    error = error

  EXCEPTIONS

    OTHERS = 1.

So you can WRITE something on the screen although you use DOI...!

Find the complete code here:

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.

3 Comments