Hi guys, in first article of 2024 I want to share about How to create FIORI app download template and upload excel. First, we will go through business scenario
BUSINESS SCENARIO
We have custom table (ZTABLE) to save all data which sent by end user. Normally, we will create FIORI App and end user will be use this app to input manual data.
Because of Input data line by line, one by one so long, end user have data in excel file and they need to upload this excel file one time. Application will be read excel file get data and save into ZTABLE.
SOLUTION APPROACH
Create custom table (ZTABLE)
Implementation RAP model with manage code and Service Binding with type is ODATA V2 UI
Create custom logic on Business Application Studio (BAS) for upload and download excel with Excel library java script
Configuration connectivity between BAS & S4HC
Deploy FIORI App from BAS into S/4 HANA public cloud
TOOLs
ADT – ABAP Development Tools
Business Technology Plaform – BTP with trial account
Business Application Studio – BAS
HOW TO STEP BY STEP
ADT – ABAP Development Tool
Create custom table
@EndUserText.label : 'Custom Table For Quality Inspection'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zupload_a_insp {
key client : abap.clnt not null;
key materiala : matnr not null;
key materialb : matnr not null;
movementype : abap.char(3);
vendor : abap.char(10);
poref : abap.char(10);
plant : werks_d;
storageloc : abap.char(4);
trpoststatus : abap.char(1);
created_by : syuname;
created_at : timestampl;
last_changed_by : syuname;
last_changed_at : timestampl;
}Create Root View Entity
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Root Entity For Quatily Inspection'
define root view entity ZUPLOAD_I_INSP
as select from zupload_a_insp
{
@EndUserText.label: 'Material A'
key materiala as Materiala,
@EndUserText.label: 'Material B'
key materialb as Materialb,
@EndUserText.label: 'Movement Type'
movementype as Movementype,
@EndUserText.label: 'Supplier/Vendor'
vendor as Vendor,
@EndUserText.label: 'PO reference'
poref as Poref,
@EndUserText.label: 'Plant'
plant as Plant,
@EndUserText.label: 'Storage Location'
storageloc as Storageloc,
@EndUserText.label: 'Transfer Post Status'
trpoststatus as Trpoststatus,
@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
}
Create Projection View with Annotation for FIORI app
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Projection view for Quality Inspection'
@UI.headerInfo:{
typeName: 'Inspectation',
typeNamePlural: 'Inspectation',
typeImageUrl: 'sap-icon://inspectation',
title: {
type: #STANDARD,
value: 'Materiala'
},
description.value: 'Materiala'
}
define root view entity ZUPLOAD_C_INSP
provider contract transactional_query
as projection on ZUPLOAD_I_INSP
{
@UI.facet: [{ id: 'PurchaseOrders',
purpose: #STANDARD,
type: #IDENTIFICATION_REFERENCE,
label: 'Purchase Orders',
position: 10
},{
id:'log',position:20,
type: #FIELDGROUP_REFERENCE,
targetQualifier: 'ChangeLog',
label:'Change Log'
}]
@UI: { lineItem: [{ position: 10 }],
identification: [{ position: 10 }],
selectionField: [{ position: 10 }] }
key Materiala as Materiala,
@UI: { lineItem: [{ position: 20 }],
identification: [{ position: 20 }],
selectionField: [{ position: 20 }] }
key Materialb as Materialb,
@UI: { lineItem: [{ position: 30 }],
identification: [{ position: 30 }]}
Movementype as Movementype,
@UI: { lineItem: [{ position: 40 }],
identification: [{ position: 40 }]}
Vendor as Vendor,
@UI: { lineItem: [{ position: 50 }],
identification: [{ position: 50 }]}
Poref as Poref,
@UI: { lineItem: [{ position: 60 }],
identification: [{ position: 60 }]}
Plant as Plant,
@UI: { lineItem: [{ position: 70 }],
identification: [{ position: 70 }]}
Storageloc as Storageloc,
@UI: { lineItem: [{ position: 80 }],
identification: [{ position: 80 }]}
Trpoststatus as Trpoststatus,
@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
}
Create Behavior for Root View with Manage Code
managed implementation in class zbp_upload_i_insp unique;
strict (2) ;
define behavior for ZUPLOAD_I_INSP alias Inspection
persistent table zupload_a_insp
lock master
authorization master ( instance )
//etag master <field_name>
{
create;
update;
delete;
field (readonly) CreatedBy, CreatedAt, LastChangedBy, LastChangedAt;
validation validateVendor on save { create;field Vendor;}
// Mapping view field with table filed
mapping for zupload_a_insp
{
Materiala = materiala;
Materialb = materialb;
Movementype = movementype;
Plant =plant;
Vendor = vendor;
Poref = poref;
Storageloc = storageloc;
Trpoststatus = trpoststatus;
CreatedAt = created_at;
CreatedBy = created_by;
LastChangedBy = last_changed_by;
LastChangedAt = last_changed_at;
}
}Implement code validation in class manage code – Behavior handler class
CLASS lhc_inspection DEFINITION INHERITING FROM cl_abap_behavior_handler.
PRIVATE SECTION.
METHODS get_instance_authorizations FOR INSTANCE AUTHORIZATION
IMPORTING keys REQUEST requested_authorizations FOR inspection RESULT result.
METHODS validatevendor FOR VALIDATE ON SAVE
IMPORTING keys FOR inspection~validatevendor.
ENDCLASS.
CLASS lhc_inspection IMPLEMENTATION.
METHOD get_instance_authorizations.
ENDMETHOD.
METHOD validatevendor.
**********************************************************************
* reading entities from CDS view
Read ENTITIES OF zupload_i_insp in LOCAL MODE
ENTITY Inspection
ALL FIELDS
WITH CORRESPONDING #( keys )
RESULT DATA(inspectations)
FAILED DATA(inspectation_failed).
if inspectation_failed is not INITIAL.
* if the above read fails then return the error message
failed = CORRESPONDING #( Deep inspectation_failed ).
RETURN.
ENDIF.
LOOP AT inspectations ASSIGNING FIELD-SYMBOL(<inspectation>).
if <inspectation>-Vendor <> '9999999999'.
DATA(lv_msg) = |Vendor must be '9999999999'|.
lv_msg = COND #( WHEN <inspectation>-ExcelRowNumber is INITIAL
THEN lv_msg
ELSE |Row { <inspectation>-ExcelRowNumber } : { lv_msg }| ).
APPEND VALUE #( %tky = <inspectation>-%tky ) to failed-inspection.
APPEND VALUE #( %tky = <inspectation>-%tky
%state_area = 'Validate Vendor'
%msg = new_message_with_text(
severity = if_abap_behv_message=>severity-error
text = lv_msg )
%element-vendor = if_abap_behv=>mk-on ) TO reported-inspection.
ENDIF.
CLEAR lv_msg.
ENDLOOP.
**********************************************************************
ENDMETHOD.
ENDCLASS.Create behavior for projection view
projection;
strict ( 2 );
define behavior for ZUPLOAD_C_INSP alias Inspection
{
use create;
use update;
use delete;
}Create service definition expose projection view
@EndUserText.label: 'Service Definition For Inspectation'
define service Zupload_UI_INSP {
expose ZUPLOAD_C_INSP as Inspectations;
}Create service binding with ODATA V2 UI
Test & Preview App
BUSINESS TECHNOLOGY PLATFORM
Create destination for development 080 S4HC
| AuthnContextClassRef | urn:oasis:names:tc:SAML:2.0:ac:classes:PreviousSession |
| nameIdFormat | urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress |
BUSINESS APPLICATION STUDIO – BAS
Login dev space BAS
Test service OData V2 UI from BAS
Create project from template
Create custom action to page
Create folder fragment & create file xml ExcelUpload.fragment.xml
<core:FragmentDefinition xmlns="sap.m" xmlns:l="sap.ui.layout" xmlns:core="sap.ui.core" xmlns:u="sap.ui.unified" xmlns:upload="sap.m.upload">
<Dialog id="uploadDialogSet" title="Excel Upload">
<content>
<upload:UploadSet uploadEnabled="true" id="uploadSet" items="{path: '/', templateShareable: false}" fileTypes="xlsx, xls" maxFileNameLength="200" beforeUploadStarts="onBeforeUploadStart" uploadCompleted="onUploadSetComplete" afterItemRemoved="onItemRemoved"
terminationEnabled="true">
<upload:UploadSetItem id="_IDGenUploadSetItem1" visibleRemove="true" visibleEdit="false" fileName="{name}" url="/upload">
<upload:attributes>
<ObjectAttribute id="_IDGenObjectAttribute1" title="Uploaded by" text="{user}" active="false"/>
</upload:attributes>
</upload:UploadSetItem>
</upload:UploadSet>
</content>
<buttons>
<Button id="_IDGenButton1" text="Template" press="onTempDownload" icon="sap-icon://download-from-cloud" type="Emphasized"/>
<Button id="_IDGenButton2" text="Upload" press="onUploadSet" icon="sap-icon://upload-to-cloud" type="Emphasized"/>
<Button id="_IDGenButton3" press="onCloseDialog" text="Cancel" icon="sap-icon://cancel"/>
</buttons>
<endButton>
<Button id="_IDGenButton4" press=".onCloseDialog" text="Ok"/>
</endButton>
</Dialog>
</core:FragmentDefinition>Add ui5-tooling-modules npm
Configuration ui5 tooling module in package json
Configuration yaml
Dowload javascript library xlsx and import into folder Utils – same level with controller
Add code java script in controller
sap.ui.define(["sap/ui/core/Fragment", "sap/m/MessageToast","ns/inspectationv6/ext/utils/xlsx"],
function (Fragment,MessageToast, xlsx){
"use strict";
return {
// this variable will hold the data of excel file
excelSheetsData: [],
pDialog: null,
openExcelUploadDialog: function(oEvent) {
var oView = this.getView();
if (!this.pDialog) {
Fragment.load({
id: "excel_upload",
name: "ns.inspectationv6.ext.fragment.ExcelUpload",
type: "XML",
controller: this
}).then((oDialog) => {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog = oDialog;
this.pDialog.open();
})
.catch(error => alert(error.message));
} else {
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
oFileUploader.removeAllItems();
this.pDialog.open();
}
},
onUploadSet: function(oEvent) {
console.log("Upload Button Clicked!!!")
/* TODO:Call to OData */
// checking if excel file contains data or not
if (!this.excelSheetsData.length) {
MessageToast.show("Select file to Upload");
return;
}
var that = this;
var oSource = oEvent.getSource();
// creating a promise as the extension api accepts odata call in form of promise only
var fnAddMessage = function () {
return new Promise((fnResolve, fnReject) => {
that.callOdata(fnResolve, fnReject);
});
};
var mParameters = {
sActionLabel: oSource.getText() // or "Your custom text"
};
// calling the oData service using extension api
this.extensionAPI.securedExecution(fnAddMessage, mParameters);
this.pDialog.close();
},
onTempDownload: function (oEvent) {
console.log("Template Download Button Clicked!!!")
/* TODO: Excel file template download */
// get the odata model binded to this application
var oModel = this.getView().getModel();
console.log(oModel.getServiceMetadata().dataServices.schema[0].entityType);
// get the property list of the entity for which we need to download the template
var oBuilding = oModel.getServiceMetadata().dataServices.schema[0].entityType.find(x => x.name === 'InspectationsType');
// set the list of entity property, that has to be present in excel file template
var propertyList = ['Materiala', 'Materialb', 'Movementype', 'Vendor',
'Poref', 'Plant', 'Storageloc','Trpoststatus'];
var excelColumnList = [];
var colList = {};
// finding the property description corresponding to the property id
propertyList.forEach((value, index) => {
let property = oBuilding.property.find(x => x.name === value);
colList[property.extensions.find(x => x.name === 'label').value] = '';
});
excelColumnList.push(colList);
// initialising the excel work sheet
const ws = XLSX.utils.json_to_sheet(excelColumnList);
// creating the new excel work book
const wb = XLSX.utils.book_new();
// set the file value
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
// download the created excel file
XLSX.writeFile(wb, 'RAP - Inspectation.xlsx');
MessageToast.show("Template File Downloading...");
},
onCloseDialog: function (oEvent) {
this.pDialog.close();
},
onBeforeUploadStart: function (oEvent) {
console.log("File Before Upload Event Fired!!!")
/* TODO: check for file upload count */
},
onUploadSetComplete: function (oEvent) {
console.log("File Uploaded!!!")
/* TODO: Read excel file data*/
// getting the UploadSet Control reference
var oFileUploader = Fragment.byId("excel_upload", "uploadSet");
// since we will be uploading only 1 file so reading the first file object
var oFile = oFileUploader.getItems()[0].getFileObject();
var reader = new FileReader();
var that = this;
reader.onload = (e) => {
// getting the binary excel file content
let xlsx_content = e.currentTarget.result;
let workbook = XLSX.read(xlsx_content, { type: 'binary' });
// here reading only the excel file sheet- Sheet1
var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets["Sheet1"]);
workbook.SheetNames.forEach(function (sheetName) {
// appending the excel file data to the global variable
that.excelSheetsData.push(XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]));
});
console.log("Excel Data", excelData);
console.log("Excel Sheets Data", this.excelSheetsData);
};
reader.readAsBinaryString(oFile);
MessageToast.show("Upload Successful");
},
onItemRemoved:function (oEvent) {
console.log("File Remove/delete Event Fired!!!")
/* TODO: Clear the already read excel file data */
},
// helper method to call OData
callOdata: function (fnResolve, fnReject) {
console.log(">>>>>>>>>>>>>>>>>>>>>")
// intializing the message manager for displaying the odata response messages
var oModel = this.getView().getModel();
// creating odata payload object for Building entity
var payload = {};
this.excelSheetsData[0].forEach((value, index) => {
// setting the payload data
payload = {
"Materiala": value["Material A"].toString(),
"Materialb": value["Material B"].toString(),
"Movementype": value["Movement Type"].toString(),
"Vendor": value["Supplier/Vendor"].toString(),
"Poref": value["PO reference"].toString(),
"Plant": value["Plant"].toString(),
"Storageloc": value["Storage Location"].toString(),
"Trpoststatus": value["Transfer Post Status"].toString()
};
console.log("This is payload")
console.log(payload)
// setting excel file row number for identifying the exact row in case of error or success
payload.ExcelRowNumber = (index + 1);
// calling the odata service
oModel.create("/Inspectations", payload, {
success: (result) => {
console.log('>>>>>>>>>>>>>>>>>')
console.log(result);
var oMessageManager = sap.ui.getCore().getMessageManager();
var oMessage = new sap.ui.core.message.Message({
message: "Inspectation Created with ID: ",
persistent: true, // create message as transition message
type: sap.ui.core.MessageType.Success
});
oMessageManager.addMessages(oMessage);
fnResolve();
},
error: fnReject
});
});
}
};
});
Preview & Test App
Build app into SAP HANA Cloud
BUILD APP INTO S/4 HANA CLOUD – ADT TOOL
Check BSP Application in ADT
After build deploy BAS successful, one BSP application will be generate auto in ADT
Create IAM App in ADT
Create IAM Business Catalogs
We can create many IAM Business Catalogs with every FIORI App.
Or We just need only one IAM Business Catalog for many FIORI custom App.
As you like.
Configuration on S/4 HANA public cloud customizing tenant 100
Create business role
Assign business catalog into business role
Assign business user
Create launchpad spaces
Add content to launchpad page
Test & Preview App in S4HC
CONCLUSION
In this article I shared all steps How to upload data into custom table by excel file and How to use library javascript excel in BAS and How to deploy FIORI app with library javascript from BAS to S4HC. Upon on this simple example, you can extend accordingly with your business requirement. Thanks for your reading and any advise , kindly leave your comment on this.
Appriciate for every advise from you.
Thanks
Joseph.
SAP Zero to Hero 