WebDynpro 4 ABAP – Open Excel sheet from DMS

Posted on by

SAPIn this article I’ll show you how to retrieve an Excel sheet from SAP DMS (Document management System) where it is stored as “original” in a Document container.

Let’s presume that:

  • we already have our document stored in DMS and we know the document’s number, type, version and part (these are key fields we have to know to be able to retrieve the document)
  • We know how to create a webdynpro component/application
  • We have already created new WDY component/application with one Window containing one View where we placed the OfficeControl of type MS_EXCEL from the Integration layout controls.

1. Read document information (metadata)

DATA:
  ls_document_data  TYPE bapi_doc_draw2,
  ls_return         TYPE bapiret2,
  lt_document_files TYPE TABLE OF bapi_doc_files2_keys.

FIELD-SYMBOLS:
  <ls_document_file> LIKE LINE OF lt_document_files.

* Specify our document key
ls_document_data-documenttype    = 'ZTP'. 
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
  EXPORTING
    input         = '3000000000003'
  IMPORTING
    OUTPUT        = ls_document_data-documentnumber.
ls_document_data-documentversion = '00'.
ls_document_data-documentpart    = '000'.

* Read document info
CALL FUNCTION 'BAPI_DOCUMENT_GETLIST2'
      EXPORTING
        select_documentdata = ls_document_data
        getdocfiles         = 'X'
      IMPORTING
        return              = ls_return
      TABLES
        documentfiles       = lt_document_files.

    IF ls_return-type CA 'EA'
      OR lines( lt_document_files ) = 0.
*      RAISE EXCEPTION document_does_not_exist.
    ENDIF.

* To simplify our example we read just the first file from 
* the document container
    READ TABLE lt_document_files ASSIGNING <ls_document_file> INDEX 1.

2. Read document contents (binary data)

DATA:
  lt_cvapi_files   TYPE TABLE OF cvapi_doc_file,
  lt_bapi_files    TYPE t_bapi_doc_files2,
  lt_drao          TYPE dms_tbl_drao,
  ls_message       TYPE messages,
  lt_orblk         TYPE TABLE OF orblk,
  lv_excel_xstring TYPE xstring
  lv_data_size     TYPE i.

FIELD-SYMBOLS:
  <ls_drao>  TYPE drao,
  <ls_orblk> TYPE orblk.

* Convert BAPI file structure to CVAPI file structure
APPEND <ls_document_file> TO lt_bapi_files.
CALL FUNCTION 'MAP2I_BAPI_FILE2_TO_API_FILE'
  TABLES
    bapi_doc_file = lt_bapi_files
    api_doc_file  = lt_cvapi_files.

* Get binary data from system
* Content provider is set to 'TBL' 
* -> tabular data filled in lt_drao
CALL FUNCTION 'CVAPI_DOC_CHECKOUTVIEW'
  EXPORTING
    pf_dokar           = <ls_document_file>-documenttype
    pf_doknr           = <ls_document_file>-documentnumber
    pf_dokvr           = <ls_document_file>-documentversion
    pf_doktl           = <ls_document_file>-documentpart
    pf_content_provide = 'TBL'
  IMPORTING
    psx_message        = ls_message
  TABLES
    pt_files           = lt_cvapi_files
    ptx_content        = lt_drao
  EXCEPTIONS
    error_message      = 1
    OTHERS             = 2.

* Get the binary data and its length
LOOP AT lt_drao ASSIGNING <ls_drao>.
  APPEND INITIAL LINE TO lt_orblk ASSIGNING <ls_orblk>.
  <ls_orblk> = <ls_drao>-orblk.

  IF lv_data_size IS INITIAL.
    lv_data_size = <ls_drao>-orln.
  ENDIF.
ENDLOOP.

* Convert binary data from itab to xstring
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
  EXPORTING
    input_length       = lv_data_size
  IMPORTING
    BUFFER             = lv_excel_xstring
  tables
    binary_tab         = lt_orblk.

3. Initialize the WDY Office Control

As we initially presumed – we have a WDY component with one View which contains the OfficeControl of type MS_EXCEL element in the Layout.

Now we have to create a context Attribute where we will store the Excel binary data.

  1. In the View’s Context tab create new Attribute of type XSTRING and name it e.g. XLS_DATA
  2. In the View’s Layout select the OfficeControl and bind its dataSource to the context attribute XLS_DATA

If you reached this point, you already know how to read the Excel data, you know how to integrate OfficeControl to the WDY view and bind it to the view’s Context attribute. Now we just have to fill the Context attribute with data we’ve already prepared before – you can put the following piece of code e.g. in the View’s WDDOMODIFYVIEW method in case you want to initialize the Excel control in the WDY right from the start

wd_context->set_attribute(
  EXPORTING
    value = lv_excel_xstring
    name  = 'XLS_DATA'
).

Now if you try to run your WDY application in browser, you should see the Excel being displayed within the browser window and loaded with data of the XLS sheet from your DMS document.

4. Download XLS as attachment

Once we have our Excel data loaded into browser, we might also want to download the updated data to local file. This can be done e.g. by adding a button on the View’s layout and to the Action assigned to the new button you’ll put the following code

DATA:
  lv_content  TYPE xstring,
  lv_filename TYPE string,
  lv_ctype    TYPE string.

lv_filename = 'My_XLS_Sheet.xlsx'.
lv_ctype = 'xlsx'.

* Access data stored in View's context 
wd_context->get_attribute(
  EXPORTING
    name  = 'DATA'
  IMPORTING
    value = lv_content
).

cl_wd_runtime_services=>attach_file_to_response(
  EXPORTING
    i_filename      = lv_filename
    i_content       = lv_content
    i_mime_type     = lv_ctype
    i_in_new_window = abap_true
).

Leave a Reply