Handling Data in Excel In-place Display Using BDS

By Satyajit Mohapatra, HCL Technologies Ltd

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.

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 B DS 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 color 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.

Click here to continue...