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
