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://marianfoo.github.io/ui5-cc-excelUpload/.
git clone https://github.com/marianfoo/ui5-cc-excelUpload-sample-standalone
npm install
npm startHow do I add the function to my app?
1. Add the npm package
To add the npm package ui5-cc-excelupload-button to your app, do as the sample app shows:
"dependencies": {
"ui5-cc-excelupload-button": "0.2.0"
},
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.excelUploadButton.v0_2_0). You can always find the latest version of “ui5-cc-excelupload” here.
"componentUsages": {
"excelUpload": {
"name": "cc.excelUpload.v0_13_0"
}
},
"resourceRoots": {
"cc.excelUpload.v0_13_0": "./thirdparty/customControl/excelUpload/v0_13_0",
"cc.excelUploadButton.v0_2_0": "./thirdparty/customControl/excelUploadButton/v0_2_0"
},
3. Add namespace to the view
Add the namespace to the view as shown in the sample app.
<mvc:View controllerName="exceluploadtotable.controller.Main"
xmlns:mvc="sap.ui.core.mvc" displayBlock="true"
xmlns="sap.m"
xmlns:excel="cc.excelUploadButton.v0_2_0">4. Add Button to the view
Add the button to the view as shown in the sample app. 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.
<excel:ExcelUpload id="excelUploadButton" standalone="true" text="Excel Upload Button" columns="customer,product_ID,quantity" uploadButtonPress="uploadButtonPress"/>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.
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:
More Information
Further Links:
