Implementing Excel-Based Bulk Data Upload With Tem...

Overview

This guide demonstrates how to implement Excel Template Download and Bulk Data Upload functionality in a Fiori Elements List Report application using RAP (RESTful ABAP Programming) in SAP BTP ABAP Environment.

Features covered:

Download an Excel template with predefined column headers
Upload Excel file with data to create multiple records
Client-side file handling with UI5
Server-side Excel parsing using XCO library

Prerequisites

SAP BTP ABAP Environment or SAP S/4HANA (RAP enabled)
SAP Business Application Studio (BAS)
Basic knowledge of RAP, CDS, and Fiori Elements
Existing RAP Business Object with List Report application

Step 1: Create Abstract Entity for File Parameters

Create an abstract entity to handle the file upload parameters passed from the UI to the backend.

@EndUserText.label: 'Abstract entity for Excel file parameters'
define abstract entity ZAE_EXCEL_FILE_PARAM
{
    mimeType    : abap.string(0);
    fileName    : abap.string(0);
    fileContent : abap.rawstring(0);
}

Note: This entity defines the structure for passing file metadata (MIME type, file name) and the actual file content (as base64 encoded raw string) from the frontend to the backend.

Step 2: Add Actions in Behavior Definition

Add static actions for download and upload functionality in your behavior definition file.

managed implementation in class zbp_i_travel unique;
strict ( 2 );
with draft;

define behavior for ZI_TRAVEL alias Travel
persistent table ztravel
draft table ztravel_d
etag master LastChangedAt
lock master total etag LastChangedAt
authorization master ( global )
{
  // ... existing configurations ...

  // Static actions for Excel operations
  static action downloadTemplate;
  static action uploadExcel parameter ZAE_EXCEL_FILE_PARAM;

  // ... other actions ...
}

Add method definitions in your behavior implementation class:

CLASS lhc_travel DEFINITION INHERITING FROM cl_abap_behavior_handler.
  PRIVATE SECTION.
    METHODS:
      downloadTemplate FOR MODIFY
        IMPORTING keys FOR ACTION Travel~downloadTemplate,
      
      uploadExcel FOR MODIFY
        IMPORTING keys FOR ACTION Travel~uploadExcel.
ENDCLASS.

Step 3: Configure UI Extension in Business Application Studio

3.1 Connect OData Service and Create List Report

Open your project in SAP Business Application Studio
Connect your OData V4 service
Generate a List Report application using the Fiori Elements template

3.2 Add Controller Extension

Open Page Map view in BAS
Navigate to your List Report page
Find “Controller Extensions” section for your page
Click “Add Controller Extension”
Provide a controller name (e.g., ExcelHandler)

3.3 Add Custom Actions via Page Editor

Go to Application Info page
Click on List Report to open Page Editor
In Page Editor, navigate to: Table → Toolbar → Actions
Click “Add Custom Action” and configure:

For Download Template Button:

Property Value

Action ID downloadTemplateAction

Button Text Download Template

Handler File Select your controller extension

Handler Method downloadTemplate

For Upload Excel Button:

Property Value

Action ID uploadExcelAction

Button Text Upload Excel

Handler File Select your controller extension

Handler Method uploadExcelDialog

Step 4: Implement Controller Extension (Frontend)

4.1 Controller Extension Code

Create/update the controller extension file:

webapp/ext/controller/ExcelHandler.controller.js

sap.ui.define([
    'sap/ui/core/mvc/ControllerExtension',
    'sap/ui/export/Spreadsheet',
    'sap/m/MessageBox',
    'sap/m/MessageToast',
    'sap/ui/core/Fragment'
], function (ControllerExtension, Spreadsheet, MessageBox, MessageToast, Fragment) {
    'use strict';

    return ControllerExtension.extend('yournamespace.ext.controller.ExcelHandler', {
        
        override: {
            onInit: function () {
                // Initialize service path - adjust according to your service
                this.oServicePath = "/YourEntitySet/com.sap.gateway.srvd.your_service.v0001.";
            },
            
            editFlow: {
                onAfterActionExecution: function (oEvent) {
                    // Handle action execution based on action name
                    if (oEvent.split(".")[6] === 'downloadTemplate') {
                        this.downloadTemplate();
                    }
                    if (oEvent.split(".")[6] === 'uploadExcel') {
                        this.uploadExcelDialog();
                    }
                }
            }
        },

        /**
         * Download Excel Template
         * Generates an Excel file with column headers for data entry
         */
        downloadTemplate: function () {
            // Define columns for the template
            var aColumns = [
                {
                    label: 'Travel ID',
                    property: 'TravelID',
                    type: 'String',
                    width: 15
                },
                {
                    label: 'Agency ID',
                    property: 'AgencyID',
                    type: 'String',
                    width: 15
                },
                {
                    label: 'Customer ID',
                    property: 'CustomerID',
                    type: 'String',
                    width: 15
                },
                {
                    label: 'Begin Date',
                    property: 'BeginDate',
                    type: 'Date',
                    width: 12
                },
                {
                    label: 'End Date',
                    property: 'EndDate',
                    type: 'Date',
                    width: 12
                },
                {
                    label: 'Booking Fee',
                    property: 'BookingFee',
                    type: 'Number',
                    scale: 2,
                    width: 12
                },
                {
                    label: 'Total Price',
                    property: 'TotalPrice',
                    type: 'Number',
                    scale: 2,
                    width: 12
                },
                {
                    label: 'Currency Code',
                    property: 'CurrencyCode',
                    type: 'String',
                    width: 12
                },
                {
                    label: 'Description',
                    property: 'Description',
                    type: 'String',
                    width: 40
                },
                {
                    label: 'Status',
                    property: 'Status',
                    type: 'String',
                    width: 12
                }
            ];

            // Configure spreadsheet settings
            var oSettings = {
                workbook: {
                    columns: aColumns
                },
                dataSource: [{}], // Empty data for template (headers only)
                fileName: 'Travel_Upload_Template.xlsx',
                showProgress: false
            };

            // Generate and download Excel file
            var oSheet = new Spreadsheet(oSettings);
            oSheet.build()
                .then(function () {
                    MessageToast.show("Template downloaded successfully!");
                })
                .catch(function (oError) {
                    MessageBox.error("Error generating template: " + oError.message);
                })
                .finally(function () {
                    oSheet.destroy();
                });
        },

        /**
         * Open Upload Dialog
         * Creates and displays a dialog for file selection
         */
        uploadExcelDialog: function () {
            var oView = this.getView();
            var that = this;

            if (!this.oDialog) {
                this.oDialog = Fragment.load({
                    id: oView.getId(),
                    controller: this,
                    name: "yournamespace.ext.fragment.FileUploadDialog"
                }).then(function (oDialog) {
                    oView.addDependent(oDialog);
                    return oDialog;
                });
            }

            this.oDialog.then(function (oDialog) {
                oDialog.open();
            });
        },

        /**
         * Handle File Selection
         * Reads and stores selected file content
         * @param {sap.ui.base.Event} oEvent - File change event
         */
        onFileChange: function (oEvent) {
            var oFile = oEvent.getParameters().files[0];
            
            if (!oFile) {
                return;
            }

            // Store file metadata
            this.fileType = oFile.type;
            this.fileName = oFile.name;

            // Read file content as Base64
            var oFileReader = new FileReader();
            oFileReader.onload = function (oLoadEvent) {
                // Extract Base64 content (remove data URL prefix)
                this.fileContent = oLoadEvent.target.result.split(",")[1];
            }.bind(this);

            oFileReader.readAsDataURL(oFile);
        },

        /**
         * Handle Upload Button Press
         * Sends file content to backend for processing
         */
        onUploadPress: function () {
            var that = this;
            this.oFileUploaderInput = this.getView().byId("idFileUpload");

            // Validate file selection
            if (!this.fileContent) {
                MessageBox.error("Please select a file to upload");
                return;
            }

            // Get OData model
            var oModel = this.base.getModel();

            // Create action binding context
            var oContext = oModel.bindContext(this.oServicePath + "uploadExcel(...)");

            // Set action parameters
            oContext.setParameter("mimeType", this.fileType);
            oContext.setParameter("fileName", this.fileName);
            oContext.setParameter("fileContent", this.fileContent);

            // Execute action
            oContext.execute()
                .then(function () {
                    MessageToast.show("Excel uploaded successfully!");
                    that._closeDialogAndReset();
                    // Refresh list to show new data
                    that.base.getExtensionAPI().refresh();
                })
                .catch(function (oError) {
                    MessageBox.error("Upload failed: " + oError.message);
                    that._closeDialogAndReset();
                });
        },

        /**
         * Handle Cancel Button Press
         * Closes the upload dialog
         */
        onCancelUpload: function () {
            this.getView().byId("idFileUploadDialog").close();
        },

        /**
         * Helper: Close dialog and reset file input
         * @private
         */
        _closeDialogAndReset: function () {
            this.oDialog.then(function (oDialog) {
                oDialog.close();
            });
            if (this.oFileUploaderInput) {
                this.oFileUploaderInput.clear();
            }
            this.fileContent="";
        }
    });
});

4.2 Create Upload Dialog Fragment

Create a fragment file for the upload dialog:

webapp/ext/fragment/FileUploadDialog.fragment.xml

Step 5: Implement Backend Logic (ABAP)

5.1 Define Table Type for Excel Data

Add the following type definitions in your behavior implementation class:

CLASS lhc_travel DEFINITION INHERITING FROM cl_abap_behavior_handler.
  PRIVATE SECTION.
  
    " Type definitions for Excel parsing
    TYPES: 
      lty_c20  TYPE c LENGTH 20,
      lty_c40  TYPE c LENGTH 40,
      lty_c100 TYPE c LENGTH 100,
      
      BEGIN OF ty_excel_upload,
        travel_id    TYPE lty_c20,
        agency_id    TYPE lty_c20,
        customer_id  TYPE lty_c20,
        begin_date   TYPE string,    " Will be converted to date
        end_date     TYPE string,    " Will be converted to date
        booking_fee  TYPE string,    " Will be converted to decimal
        total_price  TYPE string,    " Will be converted to decimal
        currency_code TYPE lty_c20,
        description  TYPE lty_c100,
        status       TYPE lty_c20,
      END OF ty_excel_upload,
      
      tt_excel_upload TYPE STANDARD TABLE OF ty_excel_upload WITH EMPTY KEY.

    " Constants for messaging
    CONSTANTS:
      c_msg_id     TYPE symsgid VALUE 'ZTRAVEL_MSG',
      c_msg_number TYPE symsgno VALUE '001'.

    METHODS:
      downloadTemplate FOR MODIFY
        IMPORTING keys FOR ACTION Travel~downloadTemplate,
      
      uploadExcel FOR MODIFY
        IMPORTING keys FOR ACTION Travel~uploadExcel.
        
ENDCLASS.

5.2 Implement Upload Method

CLASS lhc_travel IMPLEMENTATION.

  METHOD downloadTemplate.
    " Template download is handled on the frontend
    " This method can be used for logging or additional backend processing if needed
  ENDMETHOD.


  METHOD uploadExcel.
    DATA: lt_excel       TYPE tt_excel_upload,
          lt_data_create TYPE TABLE FOR CREATE zi_travel,
          lv_file_content TYPE xstring,
          lv_index       TYPE i.

    "---------------------------------------------------
    " 1. Get and Validate File Content
    "---------------------------------------------------
    lv_file_content = keys[ 1 ]-%param-fileContent.

    IF lv_file_content IS INITIAL.
      APPEND VALUE #(
        %msg = new_message_with_text(
                 severity = if_abap_behv_message=>severity-error
                 text="No file content provided. Please select a file." )
      ) TO reported-travel.
      RETURN.
    ENDIF.

    "---------------------------------------------------
    " 2. Parse Excel File Using XCO Library
    "---------------------------------------------------
    TRY.
        " Create document reader from file content
        DATA(lo_document) = xco_cp_xlsx=>document->for_file_content( lv_file_content )->read_access( ).
        
        " Get first worksheet
        DATA(lo_worksheet) = lo_document->get_workbook( )->worksheet->at_position( 1 ).

        " Define selection pattern (skip header row, start from row 2)
        DATA(lo_selection_pattern) = xco_cp_xlsx_selection=>pattern_builder->simple_from_to(
          )->from_row( xco_cp_xlsx=>coordinate->for_numeric_value( 2 )
          )->get_pattern( ).

        " Read data into internal table
        lo_worksheet->select( lo_selection_pattern
          )->row_stream(
          )->operation->write_to( REF #( lt_excel )
          )->execute( ).

      CATCH cx_root INTO DATA(lx_excel_error).
        APPEND VALUE #(
          %msg = new_message_with_text(
                   severity = if_abap_behv_message=>severity-error
                   text     = |Excel parsing error: { lx_excel_error->get_text( ) }| )
        ) TO reported-travel.
        RETURN.
    ENDTRY.

    "---------------------------------------------------
    " 3. Clean and Validate Data
    "---------------------------------------------------
    " Remove empty rows
    DELETE lt_excel WHERE 
      travel_id   IS INITIAL AND
      agency_id   IS INITIAL AND
      customer_id IS INITIAL.

    IF lt_excel IS INITIAL.
      APPEND VALUE #(
        %msg = new_message_with_text(
                 severity = if_abap_behv_message=>severity-warning
                 text="Excel file contains no valid data rows." )
      ) TO reported-travel.
      RETURN.
    ENDIF.

    "---------------------------------------------------
    " 4. Map Excel Data to RAP Create Structure
    "---------------------------------------------------
    lv_index = 0.

    LOOP AT lt_excel INTO DATA(ls_excel).
      lv_index += 1.

      " Convert string values to appropriate types
      DATA(lv_begin_date) = CONV d( ls_excel-begin_date ).
      DATA(lv_end_date)   = CONV d( ls_excel-end_date ).
      DATA(lv_booking_fee) = CONV decfloat34( ls_excel-booking_fee ).
      DATA(lv_total_price) = CONV decfloat34( ls_excel-total_price ).

      APPEND VALUE #(
        %cid = |CID_{ lv_index }|
        
        %data = VALUE #(
          AgencyID     = ls_excel-agency_id
          CustomerID   = ls_excel-customer_id
          BeginDate    = lv_begin_date
          EndDate      = lv_end_date
          BookingFee   = lv_booking_fee
          TotalPrice   = lv_total_price
          CurrencyCode = ls_excel-currency_code
          Description  = ls_excel-description
          Status       = ls_excel-status
        )
        
        %control = VALUE #(
          AgencyID     = if_abap_behv=>mk-on
          CustomerID   = if_abap_behv=>mk-on
          BeginDate    = if_abap_behv=>mk-on
          EndDate      = if_abap_behv=>mk-on
          BookingFee   = if_abap_behv=>mk-on
          TotalPrice   = if_abap_behv=>mk-on
          CurrencyCode = if_abap_behv=>mk-on
          Description  = if_abap_behv=>mk-on
          Status       = if_abap_behv=>mk-on
        )
      ) TO lt_data_create.
    ENDLOOP.

    "---------------------------------------------------
    " 5. Create Records Using EML
    "---------------------------------------------------
    MODIFY ENTITIES OF zi_travel IN LOCAL MODE
      ENTITY Travel
      CREATE FIELDS (
        AgencyID
        CustomerID
        BeginDate
        EndDate
        BookingFee
        TotalPrice
        CurrencyCode
        Description
        Status
      )
      WITH lt_data_create
      MAPPED DATA(lt_mapped)
      REPORTED DATA(lt_reported)
      FAILED DATA(lt_failed).

    "---------------------------------------------------
    " 6. Handle Results and Return Messages
    "---------------------------------------------------
    IF lt_failed-travel IS NOT INITIAL.
      " Report failures
      LOOP AT lt_failed-travel INTO DATA(ls_failed).
        APPEND VALUE #(
          %cid = ls_failed-%cid
          %msg = new_message_with_text(
                   severity = if_abap_behv_message=>severity-error
                   text     = |Failed to create record: { ls_failed-%cid }| )
        ) TO reported-travel.
      ENDLOOP.
    ELSE.
      " Success message
      APPEND VALUE #(
        %msg = new_message_with_text(
                 severity = if_abap_behv_message=>severity-success
                 text     = |Successfully created { lines( lt_mapped-travel ) } records.| )
      ) TO reported-travel.
    ENDIF.

  ENDMETHOD.

ENDCLASS.

Project Structure Summary

your-project/
├── src/
│   ├── zae_excel_file_param.ddls.asddls    # Abstract entity
│   ├── zi_travel.bdef.asbdef                # Behavior definition
│   ├── zbp_i_travel.clas.abap               # Behavior implementation
│   └── ...
│
└── webapp/
    ├── manifest.json
    └── ext/
        ├── controller/
        │   └── ExcelHandler.controller.js    # Controller extension
        └── fragment/
            └── FileUploadDialog.fragment.xml  # Upload dialog

Additional Resources

Conclusion

This implementation provides a complete solution for Excel-based bulk data operations in Fiori Elements applications. The approach leverages RAP’s static actions for clean separation of concerns and uses standard SAP libraries for reliable Excel handling.

Source link