A how-to guide to add Excel file upload feature in SAP Fiori Elements List Report.
Use case: I have table ZEVENT to keep events and a Fiori Elements List report to view events from the table. With excel upload feature user can mass upload events using excel file.
Initial Setup
Table ZEVENT
Interface CDS View ZI_Event
@AbapCatalog.sqlViewName: 'ZIEVENT'
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Event'
define view ZI_Event
as select from zevent
{
key event_id as EventId,
descr as EventTitle,
ev_date as ScheduleDate,
ev_time as ScheduleTime,
ev_location as Location
}
Consumption CDS view ZC_Event
@AbapCatalog.sqlViewName: 'ZCEVENT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Event List'
@Search.searchable: true
@UI.headerInfo:{ typeName: 'Event', typeNamePlural: 'Events' }
define view ZC_Event
as select from ZI_Event
{
@UI.lineItem: [{ position: 10 }]
@EndUserText.label: 'Event Id'
key EventId,
@UI.lineItem: [{ position: 40 }]
@UI.selectionField: [{position: 40 }]
@Search.defaultSearchElement: true
@Search.fuzzinessThreshold: 0.75
@EndUserText.label: 'Agenda'
EventTitle,
@UI.lineItem: [{ position: 20 }]
@UI.selectionField: [{position: 20 }]
@Consumption.filter.selectionType: #INTERVAL
@EndUserText.label: 'Sch. Date'
ScheduleDate,
@UI.lineItem: [{ position: 30 }]
ScheduleTime,
@UI.lineItem: [{ position: 50 }]
@UI.selectionField: [{ position: 50 }]
@Search.defaultSearchElement: true
@Search.fuzzinessThreshold: 0.75
@EndUserText.label: 'Location'
Location
}
OData ZEVENT
Create SEGW project using Data Source Reference to CDS ZC_Event. Activate and publish it in /IWFND/MAINT_SERVICE
WebIDE Project
Create app in WebIDE using SAP Fiori List and Object Page template. You can of course use Business Application Studio or Fiori Tools in Visual Studio Code.
To add the upload Excel option we will need
- Backend support in SEGW project.
- Action button extension in SAP Fiori Elements List Report.
- View fragment with FileUploader UI control to help user browse file.
Backend support in SEGW
Add entity FileUpload (and corresponding entityset) to the project and mark it as Media. Create entity with ABAP structure of list entity and add Eventid as key.
Redefine method /IWBEP/IF_MGW_APPL_SRV_RUNTIME~CREATE_STREAM in DPC_EXT class. Call method PROCESS_EXCEL_UPLOAD to process the file for entity FileUpload. Just a bit of modularisation.
METHOD /iwbep/if_mgw_appl_srv_runtime~create_stream.
CASE iv_entity_name.
WHEN 'FileUpload'.
process_excel_upload( is_media_resource = is_media_resource ) .
WHEN OTHERS.
ENDCASE.
ENDMETHOD.
ABAP logic in method PROCESS_EXCEL_UPLOAD to process incoming excel file using class CL_FDT_XL_SPREADSHEET. Code is bare minimum and is not fit for production usage. You would need a lot more error handling and I recommend splitting logic further in smaller methods.
methods process_excel_upload
importing
!is_media_resource type ty_s_media_resource
!it_key_tab type /iwbep/t_mgw_name_value_pair optional .
method process_excel_upload.
try .
data(excel) = new cl_fdt_xl_spreadsheet( document_name = 'file' xdocument = is_media_resource-value ) .
excel->if_fdt_doc_spreadsheet~get_worksheet_names( importing worksheet_names = data(worksheets) ).
data(firstworksheet_data) = excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( worksheets[ 1 ] ).
field-symbols <excel_data> type table.
assign firstworksheet_data->* to <excel_data> .
"process excel file into internal table
data : numberofcolumns type i value 4,
date_string type string,
events_for_db type table of zevent,
event_db type zevent,
event_id type zevent-event_id.
field-symbols : <field> type any.
select max( event_id ) from zevent into @event_id .
loop at <excel_data> assigning field-symbol(<row>) from 2 .
clear event_db .
event_db-event_id = event_id = event_id + 1 .
"processing columns
do numberofcolumns times.
assign component sy-index of structure <row> to <field> .
if sy-subrc = 0 .
case sy-index .
when 1 . "date
date_string = <field> .
event_db-ev_date = date_convert( date_string ).
when 2 . "time
call function 'CONVERT_TIME_INPUT'
exporting
input = <field>
importing
output = event_db-ev_time.
when 3 . "agenda
event_db-descr = <field> .
when 4 . "location
event_db-ev_location = <field> .
endcase .
endif.
enddo .
append event_db to events_for_db .
endloop.
modify zevent from table events_for_db .
catch cx_root.
mo_context->get_message_container( )->add_message(
exporting
iv_msg_type = /iwbep/cl_cos_logger=>error
iv_msg_id = '5Q'
iv_msg_number = 395 ).
endtry.
endmethod.
For date conversion from string to SAP interface format we have method DATE_CONVERT. Logic also identifies the date format before converting.
methods date_convert
importing
!iv_date_string type string
returning
value(formatted_date) type datum .
method date_convert.
data: lv_convert_date(10) type c.
lv_convert_date = iv_date_string .
"date format YYYY/MM/DD
find regex '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' in lv_convert_date.
if sy-subrc = 0.
call function '/SAPDMC/LSM_DATE_CONVERT'
exporting
date_in = lv_convert_date
date_format_in = 'DYMD'
to_output_format = ' '
to_internal_format = 'X'
importing
date_out = lv_convert_date
exceptions
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
others = 4.
else.
" date format DD/MM/YYYY
find regex '^\d{1,2}[/|-]\d{1,2}[/|-]\d{4}$' in lv_convert_date.
if sy-subrc = 0.
call function '/SAPDMC/LSM_DATE_CONVERT'
exporting
date_in = lv_convert_date
date_format_in = 'DDMY'
to_output_format = ' '
to_internal_format = 'X'
importing
date_out = lv_convert_date
exceptions
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
others = 4.
endif.
endif.
if sy-subrc = 0.
formatted_date = lv_convert_date .
endif.
endmethod.
Activate the project and give it a test in Odata client.
Select POST HTTP Method, specify Entityset name /sap/opu/odata/sap/ZEVENT_SRV/FileUploadSet in URI. Click on Add File and browse for xlsx file which will be added to the request. Click Execute. This should call CREATE_STREAM method. If this does not work, try to debug the code and fix it before proceeding to the next step.
Action Button Extension
Add Action button extension to the project. In webide you can choose context menu New->Extension
Chose project type List Report Extension, Action, Select entityset and specify a label.
After completing the wizard you would see a change to the manifest file. Controller should appear in project under ext->controller path.
You can read SAP documentation on this topic here.
In the controller ListReportExt.controller.js add following code. Code in controller call fragment (which we will add in next step), pass file to Odata service and deal with the response back from server.
sap.ui.controller("listevent.ext.controller.ListReportExt", {
onClickActionZC_Event1: function (oEvent) {
if (!this.importDialog) {
//create instance of fragment
this.importDialog = sap.ui.xmlfragment("listevent.ext.fragment.fileUpload", this);
}
this.getView().addDependent(this.importDialog);
this.importDialog.open();
},
handleCancelPress: function (oEvent) {
this.importDialog.close();
this.importDialog.destroy();
this.importDialog = null;
},
handleUploadComplete: function (oEvent) {
var oResourceBundle = this.getView().getModel("i18n").getResourceBundle();
var sMsg = "";
var sMsgType = "Error";
var oResponse = oEvent.getParameters("response");
if (oResponse) {
if (oResponse.status === 201) {
//TODO use i18n
//sMsg = "Upload Success";
sMsg = this._parseResponse(oResponse.headers["sap-message"], 9);
sMsgType = "Information";
} else {
sMsg = this._parseResponse(oResponse.responseRaw, 23);
}
}
this.extensionAPI.refreshTable(
"listevent::sap.suite.ui.generic.template.ListReport.view.ListReport::ZC_Event--listReport");
this.importDialog.destroy();
this.importDialog = null;
sap.m.MessageToast.show("File Uploaded");
},
handleUploadPress: function (oEvent) {
//perform upload
var oModel = this.getView().getModel();
var oResourceBundle = this.getView().getModel("i18n").getResourceBundle();
var oFileUploader = sap.ui.getCore().byId("fupImport");
var sMsg = "";
//check file has been entered
var sFile = oFileUploader.getValue();
if (!sFile) {
sMsg = "Please select a file first";
sap.m.MessageToast.show(sMsg);
return;
}
else{
var that = this;
that._addTokenToUploader();
oFileUploader.upload();
that.importDialog.close();
}
},
_addTokenToUploader: function () {
//Add header parameters to file uploader.
var oDataModel = this.getView().getModel();
var sTokenForUpload = oDataModel.getSecurityToken();
var oFileUploader = sap.ui.getCore().byId("fupImport");
var oHeaderParameter = new sap.ui.unified.FileUploaderParameter({
name: "X-CSRF-Token",
value: sTokenForUpload
});
var sFile = oFileUploader.getValue();
var oHeaderSlug = new sap.ui.unified.FileUploaderParameter({
name: "SLUG",
value: sFile
});
//Header parameter need to be removed then added.
oFileUploader.removeAllHeaderParameters();
oFileUploader.addHeaderParameter(oHeaderParameter);
oFileUploader.addHeaderParameter(oHeaderSlug);
//set upload url
var sUrl = oDataModel.sServiceUrl + "/FileUploadSet";
oFileUploader.setUploadUrl(sUrl);
},
_parseResponse: function (sResponse, iOffset) {
var sTempStr, iIndexS, iIndexE;
//var oParseResults = {};
var sMessage;
iIndexS = sResponse.indexOf("<message");
iIndexE = sResponse.indexOf("</message>");
if (iIndexS !== -1 && iIndexE !== -1) {
sTempStr = sResponse.slice(iIndexS + iOffset, iIndexE);
sMessage = sTempStr;
}
return sMessage;
}
});
View fragment with FileUploader UI control
Add file fileUpload.fragment.xml under folder ext->fragment
<core:FragmentDefinition xmlns="sap.m" xmlns:form="sap.ui.layout.form" xmlns:core="sap.ui.core" xmlns:b="sap.suite.ui.commons"
xmlns:u="sap.ui.unified">
<Dialog class="sapUiPopupWithPadding" title="Import from Excel" resizable="false" draggable="false" showHeader="false"
horizontalScrolling="false" contentWidth="850px">
<content>
<form:SimpleForm title="Upload">
<form:content>
<u:FileUploader id="fupImport" name="myFileUpload" tooltip="Upload (.xlsx)" uploadComplete="handleUploadComplete" style="Emphasized"
fileType="xlsx" placeholder="Choose a file for Upload (.xlsx)" sendXHR="true" useMultipart="false"/>
</form:content>
</form:SimpleForm>
</content>
<buttons>
<Button text="Upload File" type="Emphasized" id="btnUpload" press="handleUploadPress"/>
<Button text="Cancel" type="Emphasized" id="btnCancel" press="handleCancelPress"/>
</buttons>
</Dialog>
</core:FragmentDefinition>
Save the project and test the app.
Table updated as expected.
Dude, Really good tutorial!
Thank you very much!
Incase instead of an excel file ,you have a CSV file. How will you handle ?
Hi Pawan,
Thank you for such nice blog!
Is it possible add functionality like Edit, Delete and Create in the current example? If you have idea on how to add these functionalities, please share.
Best regards,
Harshal
Hi Pawan,
Thank you for such nice blog!
Is it possible to have more functionalities like single/multiple row Edit, Delete and Create in this application using Fiori elements?
If possible then please share implementation details.
Best regards,
Harshal
Hi
Thank you for such a nice blog…
I have a doubt as you have used zc_event for refreshing data table …
In my case ,
I use one zcds1 to bind the data to list report…
User will download the same data in Excel file…edit/add that Excel and upload that file.
After uploading the file, we will get a new response data(and storing it in a new entity set) zcds2 .
Now I am able to read that entity using model read().But when I use it to refresh the table it is not refreshing the table…
Can you please tell me if we can use 2 different CDs views or we need to use one only as you have used?
Many Thanks 😊
Best Regards,
Swapnaja
Excellent blog. Very well written.
And extremely useful because it uses all the latest tech in both UI5 and oData.
UI5 – uses – List report & Extension + Action
oData – uses – CDS + gateway exposure which is very similar to standard SAP design.
You have my sincere thanks.
Thank you.
Arvind.