Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Marian_Zeis
Active Contributor

More from the Series related to the UI5 Excel Upload Control














Simplifying Excel Upload in Fiori Elements: The Open Source and Easy-to-Use UI5 Custom Control
Create a UI5 custom library with versioning using a multi version namespace
How test multiple scenarios and UI5 Versions with wdi5 and GitHub Actions

If you follow the SAP community blogs, you may have seen some of my posts on how to upload Excel files in UI5 and send them directly to an SAP backend. I achieve this using a component that I developed and released as open source.

The basic principle is to read the Excel data on the frontend and then send it to the SAP backend using the UI5 standard APIs. Up until now, this has been accomplished using a table to read the metadata of the OData service and access the UI5 APIs from there.

However, with the latest release, it's now possible to upload an Excel file without a table binding. Once uploaded, the data from the Excel file can be accessed directly. You don't need to develop this yourself, and since a UI5 reuse component is used, integration is straightforward.

This functionality can also be used in OpenUI5!

Sample UI5 Application


If you want to try it out for yourself, you can use my sample application and test it right now. The sample is available here, and in the same repository, you can find a xlsx file that you can upload. There are screenshots of the sample application below. You can find all the documentation you need at https://docs.spreadsheet-importer.com/.
git clone https://github.com/marianfoo/ui5-cc-excelUpload-sample-standalone
npm install
npm start

How do I add the function to my app?


You can find always the latest guide in the documentation:
https://docs.spreadsheet-importer.com/pages/Button/

1. Add the npm package


To add the npm package ui5-cc-excelupload to your app:
"dependencies": {
"ui5-cc-spreadsheetimporter": "0.26.1"
},

2. Update manifest.json


Since a reuse component is used, you must add this component to your app's manifest.json. You must also add resourceRoots so that it always pulls from your app and not from the SAPUI5 resources.

Make sure you enter the latest version in the variables (like in cc.spreadsheetimporter.v0_26_1).
"componentUsages": {
"spreadsheetImporter": {
"name": "cc.spreadsheetimporter.v0_26_1"
}
},
"resourceRoots": {
"cc.spreadsheetimporter.v0_26_1": "./thirdparty/customControl/spreadsheetImporter/v0_26_1"
},

Also add it to the dependencies:
"dependencies": {
"minUI5Version": "1.108.19",
"libs": {
"sap.m": {},
"sap.ui.core": {},
"sap.f": {},
"sap.ui.table": {}
},
"components": {
"cc.spreadsheetimporter.v0_26_1": {}
}
},

3. Add core namespace to the view


Add "xmlns:core="sap.ui.core" to the XML View.
<mvc:View controllerName="ui.v2.ordersv2freestylenondraft.controller.UploadToTable"
xmlns="sap.m"
xmlns:semantic="sap.f.semantic"
xmlns:mvc="sap.ui.core.mvc"
xmlns:core="sap.ui.core">
...
</mvc:View>

4. Add Button to the view


Add now the4 ComponentContainer to your XML View. It's essential to set the parameter "standalone" to "true." This option turns off the default behavior of needing a table to send the data to the backend, which means that you can process the data yourself.
<core:ComponentContainer width="100%" 
usage="spreadsheetImporter" propagateModel="true" async="true"
settings="{
standalone:true,
columns: ['customer','product_ID','quantity'],
componentContainerData:{
uploadButtonPress:'uploadButtonPress',
buttonText:'Excel Upload'
}
}" />

If you include the "columns" parameter, a template will be available for the user. If this parameter is missing, the button to download the template will be hidden. You can find the documentation about this parameter here.

5. Handle the excel data


In my sample app i used a JSONModel to bind simply the data to a sap.m.Table
onInit: function () {
var oTableModel = new JSONModel();
this.getView().setModel(oTableModel, "tableData");
},

uploadButtonPress(oEvent) {
const model = this.getView().getModel("tableData");
model.setData(oEvent.getParameter("payload"));
}

The payload is the raw data which is provided by SheetJS.

The event "uploadButtonPress" is used here. The documentation for this event is found here.

How does that look?


These screenshots are from the sample app.
The setup is really simple, so it´s just a sap.m.Table in a view with the Excel Upload Button.

Live Demo: https://marianfoo.github.io/ui5-cc-excelUpload-sample-standalone/

After Starting the App


The Table is still empty



Afer opening the excel upload dialog




Selecting the Excel File




Inserting the Data


The Data is added to the JSONModel and the binding is updated automatically


 

If you have any questions or suggestions, just contact me. All contact are here:

https://github.com/marianfoo

More Information



You can find more informationen in the documentation or directly in the source code in the GitHub Repo.


Further Links:



5 Comments
Labels in this area