More from the Series related to the UI5 Excel Upload Control
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 startHow 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:
- Spreadsheet Importer Landing Page
- UI5 Spreadsheet Upload GitHub Repository
- UI5 Excel Upload Documentation
- UI5 Excel Upload npmjs Package
- SAP Managed Tags:
5 Comments
