Skip to Content
Technical Articles
Author's profile photo Namasivayam Mani

Excel Upload using RAP: Part -1


Links to other blogs post in this series –

  1. Excel Upload using RAP: Part – 1
  2. Excel Upload using RAP: Part – 2
  3. Excel Upload using RAP: Part – 3

Introduction

One of the most common scenario in the SAP On Premise System is to develop a program in ABAP for uploading data to a custom database table using a Excel or CSV file. I was wondering how to  achieve the same in SAP BTP ABAP Environment.

This will be a series of 3 blog post to develop a solution on uploading data to custom database table using SAP RAP Model using the Fiori Interface.

I will be following the below approach –

  1. Creating an OData Service using SAP RAP Model.
  2. Create a Fiori Element Application for the SAP RAP Model OData Service. ( Excel Upload using RAP: Part -2 | SAP Blogs )
  3. Extending the created Fiori Application with excel upload functionality.

Prerequisites

  • Basic Concept on how to generate an OData Service using RAP
  • For using Late Numbering in Managed RAP BO, SAP BTP ABAP Environment 2111 is required.

References

  1. SAP BTP ABAP Environment – Release 2111 | SAP Blogs
  2. Flight Scenario RAP Service Book
  3. ABAP Platform in SAP S/4HANA 2021 – Overview and Product Highlights

Business Scenario

The requirement to develop an Fiori application for a company whose primary business is to provide accommodation to a person (Paying Guest) in monthly Basis.

This application must have the following features –

  • Generating the Building ID using Number Range using Late Numbering RAP Managed BO.
  • Validation on the filed No of Rooms(n_rooms) of Building Entity – the value of it must lie between 1 and 10.

Basic ER Diagram for this Application is show below, which has three entities Building, Rooms and Beds –

ER%20Diagram%20PGMS

ER Diagram

However in this blog post we will consider only Building Entity and will create an OData Service using Managed RAP BO Runtime


Lets start building the application

Step -1: Creating Database table – ZMN_BUILDINGS

@EndUserText.label : 'PG Building'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zmn_buildings {
  key client      : abap.clnt not null;
  key building_id : abap.char(20) not null;
  building_name   : abap.char(50);
  n_rooms         : abap.int1;
  address_line    : abap.char(100);
  city            : abap.string(0);
  state           : abap.string(0);
  country         : abap.string(0);
  created_by      : syuname;
  created_at      : timestampl;
  last_changed_by : syuname;
  last_changed_at : timestampl;
}

Step – 2: Creating an Interface CDS View

The annotation @EndUserText.label is used for setting the field label in Fiori Application.
I will explain the use of field – ExcelRowNumber, in the upcoming blog post

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Buildings Interface View'
define root view entity zmn_i_buildings
  as select from zmn_buildings
{

      @EndUserText.label: 'Building ID'
  key building_id     as BuildingId,
      @EndUserText.label: 'Building Name'
      building_name   as BuildingName,
      @EndUserText.label: 'No of Rooms'
      n_rooms         as NRooms,
      
      @EndUserText.label: 'Address Line'
      address_line        as AddressLine,
      @EndUserText.label: 'City'
      city            as City,
      @EndUserText.label: 'State'
      state           as State,
      @EndUserText.label: 'Country'
      country         as Country,

      @Semantics.user.createdBy: true
      @EndUserText.label: 'Created By'
      created_by      as CreatedBy,
      @Semantics.systemDateTime.createdAt: true
      @EndUserText.label: 'Created At'
      created_at      as CreatedAt,
      @Semantics.user.lastChangedBy: true
      @EndUserText.label: 'Last Changed By'
      last_changed_by as LastChangedBy,
      @Semantics.systemDateTime.lastChangedAt: true
      @EndUserText.label: 'Last Changed At'
      last_changed_at as LastChangedAt,
      
      0 as ExcelRowNumber
}

Step – 3: Creating a Consumption View

In this step, using the CDS annotation creating the basic list view report and an object page for the building entity

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Buildings Consumption View'

@UI.headerInfo:{
  typeName: 'Building',
  typeNamePlural: 'Buildings',
  typeImageUrl: 'sap-icon://building',
  title: {
    type: #STANDARD,
    value: 'BuildingName'
  },
  description.value: 'BuildingId'
}

define root view entity zmn_c_buildings
  provider contract transactional_query
  as projection on zmn_i_buildings
{
      @UI.facet: [{ id: 'Building',
        purpose: #STANDARD,
        type: #IDENTIFICATION_REFERENCE,
        label: 'Building',
        position: 10
      }, {
        id: 'addr',position:20,
        type: #FIELDGROUP_REFERENCE,
        targetQualifier: 'Address',
        label:'Address'
      }, {
        id:'log',position:30,
        type: #FIELDGROUP_REFERENCE,
        targetQualifier: 'ChangeLog',
        label:'Change Log'
      }]

      @UI: { lineItem: [{ position: 10 }],
      identification: [{ position: 10 }],
      selectionField: [{ position: 10 }] }
  key BuildingId,
      @UI: { lineItem: [{ position: 20 }],
      identification: [{ position: 20 }],
      selectionField: [{ position: 20 }] }
      BuildingName,
      @UI: { lineItem: [{ position: 30 }],
      identification: [{ position: 30 }] }
      NRooms,

      @UI.fieldGroup: [{ qualifier: 'Address', position: 10 }]
      AddressLine,
      @UI.fieldGroup: [{ qualifier: 'Address', position: 20 }]
      City,
      @UI.fieldGroup: [{ qualifier: 'Address', position: 30 }]
      State,
      @UI.fieldGroup: [{ qualifier: 'Address', position: 40 }]
      Country,

      @UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 10 }]
      CreatedBy,
      @UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 20 }]
      CreatedAt,
      @UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 30 }]
      LastChangedBy,
      @UI.fieldGroup: [{ qualifier: 'ChangeLog', position: 40 }]
      LastChangedAt,
      
      @UI.hidden: true
      ExcelRowNumber
}

Step – 4: Creating the Behavior Definition for the interface View zmn_i_buildings

This consists of

  • Implementing the Late Number Concept for generating the Building ID, and
  • The validation for the field n_rooms which was mentioned earlier.
managed implementation in class zbp_mn_i_buildings unique;
strict;

define behavior for zmn_i_buildings alias Building
persistent table zmn_buildings
lock master
authorization master ( instance )
//etag master <field_name>
{
  create;
  update;
  delete;

  field ( readonly ) BuildingId, CreatedBy, CreatedAt, LastChangedBy, LastChangedAt;

  validation validateNRooms on save { create; field NRooms; }

  mapping for zmn_buildings
  {
    BuildingId = building_id;
    BuildingName = building_name;
    NRooms = n_rooms;
    AddressLine = address_line;
    City = city;
    State = state;
    Country = country;
    CreatedBy = created_by;
    CreatedAt = created_at;
    LastChangedBy = last_changed_by;
    LastChangedAt = last_changed_at;
  }

}

Step – 5: Creating the Behavior Definition projection on the Consumption View

projection;
strict;

define behavior for zmn_c_buildings alias Building
{
  use create;
  use update;
  use delete;
}

Step – 6: Creating the Behavior Implementation for Late Numbering and Validation Method –

Global Class to implement the behavior specified in the behavior definition

CLASS zbp_mn_i_buildings DEFINITION PUBLIC ABSTRACT FINAL 
    FOR BEHAVIOR OF zmn_i_buildings.
ENDCLASS.

CLASS zbp_mn_i_buildings IMPLEMENTATION.
ENDCLASS.

Local handler classes are created in Local Types section of Global Class to implement the business logics.

CLASS lsc_zmn_i_buildings DEFINITION INHERITING FROM cl_abap_behavior_saver.

  PROTECTED SECTION.

    METHODS adjust_numbers REDEFINITION.

ENDCLASS.

CLASS lsc_zmn_i_buildings IMPLEMENTATION.

*  this method using the late numbering concept to assign the building id for the entity
*  using number range object
  METHOD adjust_numbers.

    DATA lv_bldg_num TYPE n LENGTH 5.

    LOOP AT mapped-building ASSIGNING FIELD-SYMBOL(<map_building>) WHERE %key-BuildingId IS INITIAL .
      TRY.
*          using number range to generate the building id
          cl_numberrange_runtime=>number_get(
            EXPORTING
              nr_range_nr       = 'N1'
              object            = 'ZNR_BLD_NO'
              quantity          = 1
            IMPORTING
              number            = DATA(number)
              returncode        = DATA(ret_code)
              returned_quantity = DATA(ret_qty)
          ).
          lv_bldg_num = number.
          <map_building>-%key-BuildingId = |B{ lv_bldg_num }|.
        CATCH cx_nr_object_not_found cx_number_ranges INTO DATA(lox_exp).
          APPEND VALUE #(
              %key = <map_building>-%key
              %msg = lox_exp
          ) TO reported-building.
      ENDTRY.
    ENDLOOP.

  ENDMETHOD.

ENDCLASS.

CLASS lhc_Building DEFINITION INHERITING FROM cl_abap_behavior_handler.
  PRIVATE SECTION.

    METHODS get_instance_authorizations FOR INSTANCE AUTHORIZATION
      IMPORTING keys REQUEST requested_authorizations FOR Building RESULT result.

    METHODS validateNRooms FOR VALIDATE ON SAVE
      IMPORTING keys FOR Building~validateNRooms.

ENDCLASS.

CLASS lhc_Building IMPLEMENTATION.

  METHOD get_instance_authorizations.
  ENDMETHOD.

*  method to validate the no of rooms field
  METHOD validateNRooms.

*    reading the building entites
    READ ENTITIES OF zmn_i_buildings IN LOCAL MODE
        ENTITY Building
        ALL FIELDS
        WITH CORRESPONDING #( keys )
        RESULT DATA(buildings)
        FAILED DATA(building_failed).

    IF building_failed IS NOT INITIAL.
*     if the above read fails then return the error message
      failed = CORRESPONDING #( DEEP building_failed ).
      RETURN.
    ENDIF.

    LOOP AT buildings ASSIGNING FIELD-SYMBOL(<building>).

      IF NOT <building>-NRooms BETWEEN 1 AND 10.

*        if bulk upload, then the excel row no field will not be initial,
*        creating a message prefix for the output message
        DATA(lv_msg) = |No of Rooms must be in Range 1 to 10|.
        lv_msg = COND #( WHEN <building>-ExcelRowNumber IS INITIAL
            THEN lv_msg
            ELSE |Row { <building>-ExcelRowNumber }: { lv_msg }|
          ).

        APPEND VALUE #(
            %tky = <building>-%tky
        ) TO failed-building.

        APPEND VALUE #(
            %tky = <building>-%tky
            %state_area = 'Validate_Rooms'
            %msg = new_message_with_text(
                     severity = if_abap_behv_message=>severity-error
                     text     = lv_msg
                   )
            %element-NRooms = if_abap_behv=>mk-on
        ) TO reported-building.
      ENDIF.

      CLEAR lv_msg.

    ENDLOOP.

  ENDMETHOD.

ENDCLASS.

Step – 7: Creating Service Definition

@EndUserText.label: 'Building Service'
define service Zmn_building {
  expose zmn_c_buildings as Buildings;
}

Step – 8: Creating Service Bindings. In this scenario we will be create a OData V2 Service. I’m skipping this step as I am assuming you know how to do that.


Application Preview

  • List Report Page
  • Building Object Page
  • Validation
    The below screen shows the testing for the validation message.uy

 

Conclusion

And there you have learned how to create a basic RAP OData service with Late Numbering & Validations Concepts. Along with this, you also had a chance to know about some of the CDS Annotation to design & build the Fiori Element Application (List Report & Object Page).

Thanks for reading this post, I would like to read your thoughts in the comments !!!

In the next blog post, we will be creating a Fiori Elements App for this RAP OData Service using Business application Studio.

 

 

 

 

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Venkatesh P
      Venkatesh P

      Hi Mani,

      Thanks for the wonderful blog. I have few questions:

      1. In Behavior Definition you are using the Class zbp_mn_i_buildings. Whereas in step 6, you are using different classes. Can you please clarify?
      2. Also, where are you using the method adjust_numbers ?

      Awaiting for your next blog 🙂

      Author's profile photo Namasivayam Mani
      Namasivayam Mani
      Blog Post Author

      Hi Venkatesh,

      Thanks for your comment. 🙂

      For the 1st question, the class zbp_mn_i_buildings is a special global class for implementing the behavior definition. The classes which is in step 6 are called as local handler class, are declared and implemented in the local types of the global class section. In RAP, the local handler classes are used to implement custom business logic. and here the custom logic is the entity validation.
      Reference: Handler Classes - SAP Help Portal

      For the 2nd question, the method adjust_numbers gets automatically called by the RAP framework after the succesful validation check and before the final save call of the object to generate the primary key for that entity.
      Reference: Save Sequence Runtime - SAP Help Portal

      For more clarity, I have also updated the step 6.

      Author's profile photo Karthikeyan B
      Karthikeyan B

      Hi @Namasivayam Mani

       

      Could you pls post the Excel upload blog ASAP?
      I have a requirement for that in my project and have been searching for such an example. Good that you have initiated. could you pls post the other parts ASAP?

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      You can use ABAP2XLSX for all the Excel needs in ABAP or use one of what must be hundreds of existing blog posts on this in ABAP.

      The data uploaded from Excel would need to go somewhere, obviously. If it just goes into a Z table, then I guess you could use RAP managed scenario (there is a massive RAP document here). Otherwise unmanaged scenario with a BAPI or something would be the way to go.

      All the separate parts of the process already have information and tools available. It's very nice of the author to describe an end-to-end scenario but I doubt there should be a work stoppage situation because next part has not been published yet. (It is published now though.)