Skip to end of metadata
Go to start of metadata

Link to Content's target Space :

http://wiki.sdn.sap.com/wiki/display/ABAP/BDS

Applies to:

SAP ECC 6.0/ ABAP / BDS

Summary

         The article demonstrates data handling in excel in-place display using BDS with the help of a program. The demo program maintains the entries in a database table through an excel in-place display. 

Author:  

Satyajit Mohapatra       
Company:        HCL Technologies Ltd
Created on:    10 May 2010
Author(s) Bio
Satyajit Mohapatra is working as a Consultant for HCL Technologies Ltd. He has more than three years of experience in various areas of ABAP.   

Table of Contents

1.            OVERVIEW

2.            Defining a BDS Class

3.            Uploading MS Excel Template

4.            Code to Handle Data in Excel In-place Display

OVERVIEW 

        MS Excel is the conventional way of storing and maintaining data. Sometimes, user prefers to display report output in specific MS Excel templates; which contain logos, user specific table formats, engineering drawings, diagrams and macros. The data modified manually in these excel templates may be again transferred to SAP for processing.  

        Excel integration is required due to various reasons like avoiding user training on newly developed custom programs and screens, use existing data templates, data integration with legacy system.

       BDS (Business Document Services) is a convenient option for excel integration as user specific MS Excel templates can be stored in it. These templates can be called in an ABAP program at runtime to display data. Also, the data modified by the user in MS Excel can be read into ABAP program for processing.

       The functionality will be demonstrated through a demo program. The program will display the content of a custom table in excel in-place display. The user can change the non key fields displayed and the modified contents will be updated to the table after validation.

1.    Defining a BDS Class

       A custom BDS class can be defined through transaction SBDSV1 as described below. An existing BDS class can be used, unless the user wants a separate class for a specific application.

In SBDSV1, Go to 'NEW ENTRIES'.  Enter the 'Class name', 'Class type' as 'Other objects(OT)', 'Log Level' as required and rest of the parameters should filled as shown below.  



 

 

2.    Uploading MS Excel Template

        Design a template as per user requirement in MS Excel. You can embed all static objects/data to be displayed such as logos, drawings, headers etc in the template, except the area, where the data will be filled at runtime.

A sample template has been created as shown below.

  
                

            Now, the MS Excel template needs to be uploaded to BDS using transaction OAOR under a class. Enter any existing Class Name, Class Type as 'OT' and Object Key in the selection screen of OAOR. Object key is like a sub folder, which is used to distinguish different sets of documents stored under a class. Any value can be entered to define an object key in OAOR. But to access a document, the same object key must be keyed in, in which it was stored initially.    

 

Now, go to 'Create' tab and double click on table template. It will show a pop up to upload the MS Excel template.

Enter the 'Description' for the table template after uploading.



The uploaded table template can be attached to a transport request as well.

 

3.    Code to Handle Data in Excel In-place Display

                    The program will maintain a custom table YSM_AGENTS, which has the following fields. 

             Initially, the program will display the table contents of YSM_AGENTS in the excel template uploaded in BDS. The user should be able to modify only the non key fields of the table filled with colour green. So, we need to protect the whole worksheet except a range or window, which will contain editable fields NAME & EMAIL. The user will not be able to modify anything  else except these fields.

  
           

 Also, the email entered will be validated. If an invalid email id is entered, error message will be displayed with the cell to be corrected filled with colour red. 

             
Create a screen '0100' and a custom control 'EXCEL' in it to display the excel document in-place. Also, activate the BACK, EXIT, CANCEL, SAVE options in GUI status.

*&---------------------------------------------------------------------*
*& Report  YSM_TEST5
*&---------------------------------------------------------------------*
*& Demo program for displaying table data in a specific excel template
*& using BDS. Also, reads the contents modified by user again into ABAP
*& program after validations and updates the table.
*&---------------------------------------------------------------------*
  REPORT  ysm_test5.

************************************************************************
* Data Declaration
************************************************************************
* Custom Table With 3 fields
*->AGENTID  (KEY)
*->NAME
*->EMAIL
TABLES: ysm_agents.

TYPESBEGIN OF t_agents,
             agentid  TYPE ysm_agents-agentid,
             name     TYPE ysm_agents-name,
             email    TYPE ysm_agents-email,
            END OF t_agents.

DATA: int_agents     TYPE TABLE OF t_agents,
           wf_entries     TYPE i.

TYPE-POOLS: soi,
                      sbdst.

DATA: r_document  TYPE REF TO cl_bds_document_set,
           r_excel         TYPE REF TO i_oi_spreadsheet,
           r_container  TYPE REF TO cl_gui_custom_container,
           r_control      TYPE REF TO i_oi_container_control,
           r_proxy        TYPE REF TO i_oi_document_proxy,
           r_error         TYPE REF TO i_oi_error,
           wf_retcode  TYPE soi_ret_string.

************************************************************************
* Selection Screen
************************************************************************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.
* User will enter the agent ids to be modified
SELECT-OPTIONS: s_agent FOR ysm_agents-agentid OBLIGATORY.

* Details of table template in BDS to be entered
PARAMETERS: p_clsnam  TYPE sbdst_classname  DEFAULT 'YSM_TESTBDS'    OBLIGATORY,
            p_clstyp  TYPE sbdst_classtype  DEFAULT 'OT'             OBLIGATORY,
            p_objkey  TYPE sbdst_object_key DEFAULT 'TEST'           OBLIGATORY,
            p_desc    TYPE char255          DEFAULT 'TABLE TEMPLATE' OBLIGATORY.
SELECTION-SCREEN END OF BLOCK b1.

************************************************************************
* START OF SELECTION
************************************************************************
START-OF-SELECTION.
* Call Excel Inplace Display
  CALL SCREEN 100"Create a screen 100 with custom container 'EXCEL'

************************************************************************
* SCREEN LOGIC
************************************************************************
*&---------------------------------------------------------------------*
*&      Module  STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
MODULE status_0100 OUTPUT.

  SET PF-STATUS 'STAT100'.   "Enable SAVE, BACK, EXIT, CANCEL
  SET TITLEBAR  'TITLE100'.  "Set title

* Get table data
  PERFORM f_get_table_data.

* Open the excel template in BDS in-place
  PERFORM f_open_document USING p_clsnam
                                p_clstyp
                                p_objkey
                                p_desc.

* Display table data in the excel template
  PERFORM f_dis_table_data.

* Protect the whole sheet except the editable fields
  PERFORM f_protect_sheet.

ENDMODULE.                 " STATUS_0100  OUTPUT

*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
MODULE user_command_0100 INPUT.

  CASE sy-ucomm.
    WHEN 'BACK' OR 'EXIT' OR 'CANCEL'.
* Close document
      PERFORM f_close_document.
      LEAVE TO SCREEN 0.
    WHEN 'SAVE'.
* Save the modified entries into database
      PERFORM f_save_document.
  ENDCASE.

ENDMODULE.                 " USER_COMMAND_0100  INPUT

************************************************************************
* SUBROUTINES
************************************************************************
*&---------------------------------------------------------------------*
*&      Form  f_get_table_data
*&---------------------------------------------------------------------*
*       Get fresh data from YSM_AGENTS
*----------------------------------------------------------------------*
FORM f_get_table_data .

* Get all the agents from table
  SELECT   agentid
           name
           email
      FROM ysm_agents
       INTO TABLE int_agents
        WHERE agentid IN s_agent.

  IF sy-subrc NE 0.
    MESSAGE 'No Agent Details Found' TYPE 'E'.
  ENDIF.

* Get the no of rows to be displayed
  DESCRIBE TABLE int_agents LINES wf_entries.

ENDFORM.                    " f_get_table_data

*&---------------------------------------------------------------------*
*&      Form  f_open_document
*&---------------------------------------------------------------------*
*       Open the table template from BDS
*----------------------------------------------------------------------*
*  -->  l_clsnam         Class Name in OAOR
*  -->  l_clstyp         Class Type in OAOR
*  -->  l_objkey         Object key in OAOR
*  -->  l_desc           Description of the excel template in OAOR
*----------------------------------------------------------------------*
FORM f_open_document USING l_clsnam TYPE sbdst_classname
                           l_clstyp TYPE sbdst_classtype
                           l_objkey TYPE sbdst_object_key
                           l_desc   TYPE char255.

  DATA: locint_signature   TYPE sbdst_signature,
        locint_uris        TYPE sbdst_uri,
        locwa_signature    LIKE LINE OF locint_signature,
        locwa_uris         LIKE LINE OF locint_uris.

  IF NOT r_document IS INITIAL.
    RETURN.
  ENDIF.

* Create container control
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = r_control
      retcode = wf_retcode.

  IF wf_retcode NE c_oi_errors=>ret_ok.
    CALL METHOD c_oi_errors=>raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

* Initialize Custom Control
  CREATE OBJECT r_container
    EXPORTING
      container_name = 'EXCEL'"Custom Control Name

  CALL METHOD r_control->init_control
    EXPORTING
      r3_application_name      = 'EXCEL INPLACE BDS'
      inplace_enabled          = abap_true
      inplace_scroll_documents = abap_true
      parent                   = r_container
    IMPORTING
      retcode                  = wf_retcode.

  IF wf_retcode NE c_oi_errors=>ret_ok.
    CALL METHOD c_oi_errors=>raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

* Create object for cl_bds_document_set
  CREATE OBJECT r_document.

* Get Document with URL
  locwa_signature-prop_name  = 'DESCRIPTION'.
* Description of the table template in OAOR
  locwa_signature-prop_value = l_desc.
  APPEND locwa_signature TO locint_signature.

  CALL METHOD r_document->get_with_url
    EXPORTING
      classname       = l_clsnam
      classtype       = l_clstyp
      object_key      = l_objkey
    CHANGING
      uris            = locint_uris
      signature       = locint_signature
    EXCEPTIONS
      nothing_found   = 1
      error_kpro      = 2
      internal_error  = 3
      parameter_error = 4
      not_authorized  = 5
      not_allowed     = 6.

  IF sy-subrc NE 0.
    MESSAGE 'Error Retrieving Document' TYPE 'E'.
  ENDIF.

  READ TABLE locint_uris INTO locwa_uris INDEX 1.

  CALL METHOD r_control->get_document_proxy
    EXPORTING
      document_type  = 'Excel.Sheet'
    IMPORTING
      document_proxy = r_proxy
      retcode        = wf_retcode.

  IF wf_retcode NE c_oi_errors=>ret_ok.
    CALL METHOD c_oi_errors=>show_message
      EXPORTING
        type = 'E'.
  ENDIF.

* Open Document
  CALL METHOD r_proxy->open_document
    EXPORTING
      document_url     = locwa_uris-uri
      open_inplace     = abap_true
      protect_document = abap_true "Protect Document initially
    IMPORTING
      retcode          = wf_retcode.

  IF wf_retcode NE c_oi_errors=>ret_ok.
    CALL METHOD c_oi_errors=>show_message
      EXPORTING
        type = 'E'.
  ENDIF.

* Get Excel Interface
  CALL METHOD r_proxy->get_spreadsheet_interface
    IMPORTING
      sheet_interface = r_excel
      retcode         = wf_retcode.

  IF wf_retcode NE c_oi_errors=>ret_ok.
    CALL METHOD c_oi_errors=>show_message
      EXPORTING
        type = 'E'.
  ENDIF.

ENDFORM.                    " f_open_document

*&---------------------------------------------------------------------*
*&      Form  f_dis_table_data
*&---------------------------------------------------------------------*
*       Display data in table template
*----------------------------------------------------------------------*
FORM f_dis_table_data .

  DATA: locint_fields TYPE TABLE OF rfc_fields.

* Create a range to insert data
  PERFORM f_create_range USING 9             "Begin on 9th row
                               3             "Begin on 3rd col
                               wf_entries    "No of rows reqd
                               3             "No of cols reqd
                               'AGENTS'.     "Range name
*-> Set Frame to the range
*# Calculation of TYP parameter
* The parameter has 8 bits
*0 Sets the left margin
*1 Sets the top margin
*2 Sets the bottom margin
*3 Sets the right margin
*4 Horizontal line
*5 Sets the left margin
*6 Thickness
*7 Thickness
* My figure will be 7 6 5 4 3 2 1 0
*                   1 0 1 1 1 1 1 1
* Binary 1011 1111 stands for 191 in decimal
* Check SAP help for more info.....
_* http://help.sap.com/saphelp_NW04s/helpdata/en/_
  " 21/b531bfe1ba11d2bdbe080009b4534c/frameset.htm

  CALL METHOD r_excel->set_frame
    EXPORTING
      rangename = 'AGENTS'
      typ       = 191
      color     = 21
    IMPORTING
      error     = r_error
      retcode   = wf_retcode.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

* Get field attributes of the table to be displayed
  CALL FUNCTION 'DP_GET_FIELDS_FROM_TABLE'
    TABLES
      data             = int_agents
      fields           = locint_fields
    EXCEPTIONS
      dp_invalid_table = 1
      OTHERS           = 2.

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

* Insert the table entries into Excel
  CALL METHOD r_excel->insert_one_table
    EXPORTING
      fields_table = locint_fields[]  "Defn of fields
      data_table   = int_agents[]     "Data
      rangename    = 'AGENTS'         "Range Name
    IMPORTING
      error        = r_error
      retcode      = wf_retcode.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

ENDFORM.                    " f_dis_table_data

*&---------------------------------------------------------------------*
*&      Form  f_protect_sheet
*&---------------------------------------------------------------------*
*       Protect the whole sheet except the fields to edited
*----------------------------------------------------------------------*
FORM f_protect_sheet .

  DATA: loc_protect      TYPE c,
        loc_sheetname    TYPE char31.

* Check whether the sheet is protected
*  in case it's unprotected manually
  CALL METHOD r_excel->get_active_sheet
    IMPORTING
      sheetname = loc_sheetname
      error     = r_error
      retcode   = wf_retcode.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

  CALL METHOD r_excel->get_protection
    EXPORTING
      sheetname = loc_sheetname   "Active sheet name
    IMPORTING
      error     = r_error
      retcode   = wf_retcode
      protect   = loc_protect.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ELSE.
* If not protected, protect the sheet
    IF loc_protect NE abap_true.
      CALL METHOD r_excel->protect
        EXPORTING
          protect = abap_true
        IMPORTING
          error   = r_error
          retcode = wf_retcode.

      IF r_error->has_failed = abap_true.
        CALL METHOD r_error->raise_message
          EXPORTING
            type = 'E'.
      ENDIF.
    ENDIF.
  ENDIF.

* The user should not be allowed to change the primary fields.
* The sheet is protected against change and a particular range will
* be unprotected for editing

* Create a range to enable editing for non key fields
  PERFORM f_create_range USING 9           "Begin on 9th row
                               4           "Begin on 4th col
                               wf_entries  "No of rows reqd
                               2           "Only 2 columns are editable
                               'EDIT'.     "Range name

* Unprotect the range for editing
  CALL METHOD r_excel->protect_range
    EXPORTING
      name    = 'EDIT'
      protect = space
    IMPORTING
      error   = r_error
      retcode = wf_retcode.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

*->Set colour to editable range
*# Check SAP help link for colour codes
http://help.sap.com/saphelp_NW04s/helpdata/en
  "/21/b531bfe1ba11d2bdbe080009b4534c/frameset.htm
  CALL METHOD r_excel->set_color
    EXPORTING
      rangename = 'EDIT'
      front     = 1
      back      = 4
    IMPORTING
      error     = r_error
      retcode   = wf_retcode.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

ENDFORM.                    " f_protect_sheet

*&---------------------------------------------------------------------*
*&      Form  f_close_document
*&---------------------------------------------------------------------*
*       Close the document when user leaves the program
*----------------------------------------------------------------------*
FORM f_close_document .

* Close document
  IF NOT r_proxy IS INITIAL.
    CALL METHOD r_proxy->close_document
      IMPORTING
        error   = r_error
        retcode = wf_retcode.

    IF r_error->has_failed = abap_true.
      CALL METHOD r_error->raise_message
        EXPORTING
          type = 'E'.
    ENDIF.
  ENDIF.

ENDFORM.                    " f_close_document

*&---------------------------------------------------------------------*
*&      Form  f_save_document
*&---------------------------------------------------------------------*
*       Save the modified entries into database table
*----------------------------------------------------------------------*
FORM f_save_document .

  DATA: locint_ranges      TYPE soi_range_list,
        locwa_ranges       TYPE soi_range_item,
        locint_moddata     TYPE soi_generic_table,
        locwa_moddata      TYPE soi_generic_item,
        locint_agents_mod  TYPE TABLE OF ysm_agents,
        locwa_agents_mod   TYPE ysm_agents,
        loc_error_row      TYPE i.

* Initialize the colour of the editable range
  CALL METHOD r_excel->set_color
    EXPORTING
      rangename = 'EDIT'
      front     = 1
      back      = 4
    IMPORTING
      error     = r_error
      retcode   = wf_retcode.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

* Define the range from which data needs to be read
  locwa_ranges-name    = 'AGENTS'.
  locwa_ranges-rows    = wf_entries.
  locwa_ranges-columns = 3.
  APPEND locwa_ranges TO locint_ranges.

* Get modified data
  CALL METHOD r_excel->get_ranges_data
    IMPORTING
      contents = locint_moddata
      error    = r_error
    CHANGING
      ranges   = locint_ranges.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

  LOOP AT locint_moddata INTO locwa_moddata.
    CASE locwa_moddata-column.
      WHEN 1.
        locwa_agents_mod-agentid = locwa_moddata-value.
      WHEN 2.
        locwa_agents_mod-name    = locwa_moddata-value.
      WHEN 3.
        locwa_agents_mod-email   = locwa_moddata-value.

*-> Validate the email id entered

* Get the current row no taking account the rows
*  in the sheet above the range
        loc_error_row = locwa_moddata-row + 8.
        PERFORM f_validate_email USING locwa_agents_mod-email
                                       loc_error_row.
    ENDCASE.

    AT END OF row.
      locwa_agents_mod-mandt = sy-mandt.
      APPEND locwa_agents_mod TO locint_agents_mod.
      CLEAR  locwa_agents_mod.
    ENDAT.

  ENDLOOP.

* Update Table
  MODIFY ysm_agents FROM TABLE locint_agents_mod.
  COMMIT WORK.

  IF sy-subrc EQ 0.
    MESSAGE 'DATA UPDATED' TYPE 'S'.
  ELSE.
    MESSAGE 'DATA NOT UPDATED' TYPE 'E'.
  ENDIF.

ENDFORM.                    " f_save_document

*&---------------------------------------------------------------------*
*&      Form  f_validate_email
*&---------------------------------------------------------------------*
*       Validate the email id entered
*----------------------------------------------------------------------*
*      -->l_email  Email Id
*----------------------------------------------------------------------*
FORM f_validate_email  USING    l_email   TYPE c
                                l_err_row TYPE i.

  TYPE-POOLS:sx.
  DATA: locwa_address TYPE sx_address.

* Check Email Id
  locwa_address-type = 'INT'.
  locwa_address-address = l_email.

  CALL FUNCTION 'SX_INTERNET_ADDRESS_TO_NORMAL'
    EXPORTING
      address_unstruct    = locwa_address
    EXCEPTIONS
      error_address_type  = 1
      error_address       = 2
      error_group_address = 3
      OTHERS              = 4.

  IF sy-subrc <> 0.

* Create a range to highlight the error cell
    PERFORM f_create_range USING l_err_row
                                 5   "Column no for email id
                                 1
                                 1
                                 'ERROR'.

* Display the error cell in red
    CALL METHOD r_excel->set_color
      EXPORTING
        rangename = 'ERROR'
        front     = 1
        back      = 3
      IMPORTING
        error     = r_error
        retcode   = wf_retcode.

    IF r_error->has_failed = abap_true.
      CALL METHOD r_error->raise_message
        EXPORTING
          type = 'E'.
    ENDIF.

    MESSAGE 'Invalid Email Address' TYPE 'E'.
  ENDIF.

ENDFORM.                    " f_validate_email

*&---------------------------------------------------------------------*
*&      Form  f_create_range
*&---------------------------------------------------------------------*
*       Create a range dynamically in excel sheet
*----------------------------------------------------------------------*
*      -->l_top       Begin on row
*      -->l_left      Begin on column
*      -->l_row       No of rows
*      -->l_column    No of columns
*      -->l_range     Range Name
*----------------------------------------------------------------------*
FORM f_create_range  USING  l_top    TYPE i
                            l_left   TYPE i
                            l_row    TYPE i
                            l_column TYPE i
                            l_range  TYPE char255.

* Select area for entries to be displayed
  CALL METHOD r_excel->set_selection
    EXPORTING
      top     = l_top
      left    = l_left
      rows    = l_row
      columns = l_column.

* Define Range
  CALL METHOD r_excel->insert_range
    EXPORTING
      name    = l_range
      rows    = l_row
      columns = l_column
    IMPORTING
      error   = r_error.

  IF r_error->has_failed = abap_true.
    CALL METHOD r_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

ENDFORM.                    " f_create_range

   Selection Screen:


 Initial Screen Displaying the Table Entries:

 

 Doesn't allow the user to modify the primary field AGENTID or rest of the cells, except the fields NAME & EMAILID:


 

   Shows error message and highlights the cell to be corrected in red, if an invalid email id is entered:

 

Data Changed and Successfully Saved:

 

  1. Guest

    Hi Satyajit,

    I am trying to store an Excel template through BDS and then trying to read that template to generate an Excel sheet on presentation server.

    for testing pupose i used your program. Except the module pool part, i have written the same program as you have mentioned in the Wiki. 

    Everything is going correct, i am getting Sy-subrc  =  0 for every method or FM call. But the Excelsheet is not generated. Program just executes successfully without generating any Excelsheet. 

    When i debugged the porgram. it gave me following url:

    SAPR3://SAPR3CMS/get/700/BDS_5FDB2_5FR/CF4620D538358A4B939975C020BE4EFC/TABLE_20TEMPLATE.xlsx

    Could you please tell me , that whyt could be the reason that, i am not able to generate the Excelsheet even though everything seems to be executing properly.

    Regards

    PG