Technical Articles
Read Data From Excel File (.XLSX) In SAP UI5 App And Display Contents In Table
Introduction:
Hello everyone. Hope you all are fine in this pandemic. Today, i am writing this blogpost to highlight the steps that can be used to read the data from an Excel file. It is easy to read the data from a csv file but reading data from XLSX file is somewhat different and require external libraries.
We would be using the SheetJS library to read the file and display the content in the sap.m.Table.
The Github repo of the SheetJS is here.
Steps:
Firstly, we will create a simple project with a single view. I have created the Project with name ReadFromExcel and namespace com.test.
Now i am adding a file uploader and sap.m.Table in my view. Following is the code:
<mvc:View controllerName="com.test.ReadFromExcel.controller.MainView" xmlns:mvc="sap.ui.core.mvc" displayBlock="true" xmlns="sap.m"
xmlns:u="sap.ui.unified">
<Shell id="shell">
<App id="app">
<pages>
<Page id="page" title="Read From Excel">
<customHeader>
<Bar>
<contentMiddle>
<Label text="Read Data From Excel"/>
</contentMiddle>
<contentRight>
<u:FileUploader id="FileUploaderId" sameFilenameAllowed="true" iconOnly="false" buttonOnly="true" fileType="XLSX,xlsx"
icon="sap-icon://upload" iconFirst="true" style="Emphasized" change="onUpload"/>
</contentRight>
</Bar>
</customHeader>
<content>
<Table items="{localModel>/items}">
<columns>
<Column>
<Label text="Name"/>
</Column>
<Column>
<Label text="Age"/>
</Column>
<Column>
<Label text="Job"/>
</Column>
<Column>
<Label text="Address"/>
</Column>
</columns>
<items>
<ColumnListItem>
<cells>
<Text text="{localModel>Name}"/>
<Text text="{localModel>Age}"/>
<Text text="{localModel>Job}"/>
<Text text="{localModel>Address}"/>
</cells>
</ColumnListItem>
</items>
</Table>
</content>
</Page>
</pages>
</App>
</Shell>
</mvc:View>
The Output of the page looks like this:
As per the code, i have registered the change event of FileUploader as onUpload which will trigger when the file is selected.
Also we need a JSON model to store the data of the excel and display in the sap.m.Table. I am creating the JSON model as localModel.
Below is the code for controller file:
onInit: function () {
this.localModel = new sap.ui.model.json.JSONModel();
this.getView().setModel(this.localModel, "localModel");
},
onUpload: function (e) {
this._import(e.getParameter("files") && e.getParameter("files")[0]);
},
_import: function (file) {
var that = this;
var excelData = {};
if (file && window.FileReader) {
var reader = new FileReader();
reader.onload = function (e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type: 'binary'
});
workbook.SheetNames.forEach(function (sheetName) {
// Here is your object for every sheet in workbook
excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
});
// Setting the data to the local model
that.localModel.setData({
items: excelData
});
that.localModel.refresh(true);
};
reader.onerror = function (ex) {
console.log(ex);
};
reader.readAsBinaryString(file);
}
}
But to make this run, we need to add the JS files of the SheetJS which i am picking from the CDN of the same and have linked in index.html file:
<!DOCTYPE html>
<html>
<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/xlsx.js"></script>
...
</head>
<body class="sapUiBody">
...
</body>
</html>
We need to use two files here. JSZIP.js and XLSX.js as clearly visible above.
Now we will run the application and use the file uploader to show the contents in sap.m.Table
Select the file:
After selection data is shown in the sap.m.Table:
The contents of the file are:
People who are facing issue on running the app from component.js or running on Launchpad where standalone Apps donot run, add the below code in Component.js file onInit method:
var jQueryScript = document.createElement('script');
jQueryScript.setAttribute('src', 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/jszip.js');
document.head.appendChild(jQueryScript);
var jQueryScript = document.createElement('script');
jQueryScript.setAttribute('src', 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/xlsx.js');
document.head.appendChild(jQueryScript);
Conclusion:
There are times where we need to read the data from XLSX file in the SAPUI5 application. You can follow the above steps for the same.
Suggestions and Questions are most welcome.
Regards
Anmol Chadha
Hi Anmol Chadha,
Could you please share the complete code of index.html?
Thanks,
Ram
Hi,
Below is the code:
Thanks Anmol Chadha. It worked !
It was very helpful blog.Thank you so much Anmol Chadha.
Hi Anmol Chadha - With the same XLSX library, can we read csv file as well. Any sample code that you can provide. Thank you.
Hi,
You can use the javascript reader for csv. No need of any external library.
I will share the snippet with you.
Hi Anmol,
Could you please share the complete code of Controller.js?
I face the below errors in js file.
Thanks and best regards,
iman
Hi,
Sorry for the late reply,
Just write
/*global XLSX*/
At the top of the controller file.
Hi Anmol,
Thanks for this blog.
Can you please tell me where i have to write Global XLSX ?
Hi
Please write it at the top of controller file.
Hi Iman,
I face same errros.....
Please help if your problems are solved!
Hi Anmol,
Thanks for blog.
I tried uploading the excel file into SAPUI5 table. Excel file is having 26 columns and it has 2 rows of data. When i tried getting JSON data from Excel, in the ExcelData array, if any of the row cell is empty that header data also not coming in the JSON data. In the Excel sheet Account Holder field is empty due to that i am not getting Account Holder parameter itself for excelData[1]. Could you please let me know how to get the parameter if it does not have any values also.
Regards,
Manimaran R.
Hi
For this, if you have a fixed set of columns, you can use a JSON object with all the fields and map and check with data received from excel.
hi, please tell me how to do dynamic columns table by uploading a excel file.,?
Hi Anmol,
Thanks for the blog!
I have written /*global xlsx*/ at the top of the controller and this is working fine in my local, but while in the cloud or site level this is not working, after minifying the commented out lines are taken out hence throwing error, could you please help me?
Hi Anmol Chadha , with the new SAP BAS, is it this straightforward? I have made simple app, and want to display excel contents in my dashboard as a table. Thanx for your reply.
Yes it should go same.
CDN excel libraries in INDEX.HTML file are refured to load on the launchpad.
Refused to load the script 'https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js' because it violates the following Content Security Policy directive: "script-src 'self' *.hana.ondemand.com 'unsafe-eval'". Note that 'script-src-elem' was not explicitly set, so 'script-src' is used as a fallback.
can anyone help??
People who are facing issue on running the app from component.js or running on Launchpad where standalone Apps donot run, add the below code in Component.js file onInit method:
You can use the destination to CDN and use it in the app for loading the file using component.js file.
People who are facing issue on running the app from component.js or running on Launchpad where standalone Apps donot run, add the below code in Component.js file onInit method:
Hello Anmol,
I have added this code to my component.js file, I am getting this error, Is there any alternative for this?
Hi Anmol,
I also getting the same error, can you please share your component.js code
Hi Anmol, I have added the 2 external library files to my source code and referring them from my controller. It works fine but it takes more time to process, any idea how to reduce the processing time?
Hi Diwakar,
I dont think there would be a work around as we are fetching the files from CDN.
Regards
Anmol
Very useful! thank a lot!
Dear Anmol,
First of all very useful and thank you very much! I have managed to replicate it in SAPUI5 in WEBIDE.
But my challenge is using this external Library in MDK (Mobile Development Kit) app codes, especially in Offline mode where we need to be able to Upload Excel file and process the data coming from it in frontend. So my more specific questions if you could give some ideas are:
Many thanks,
Yergali
Hi Yergali,
Sorry for delayed response.
I am not sure about MDK. Didnt checked it for that.
Regards
Anmol
Hi Anmol,
Good Day!!
Thanks for this helpful blog!
Is it possible to achieve same scenario without using external libraries? If it is possible, can you please help me out?
Best Regards,
Paresh
Hi paresh.
Sorry for the late reply. I didnt found any way to achive it without external libs.
Regards
Anmol
Hello Anmol,
Thank you so much for this blog. It is really helpful.
However, I have a requirement to read the uploaded excel file from the frontend and update the data to a custom transparent table in the backend.
Could you please let me know if this is possible?
Thank you!
Best Regards,
Tanya
Hi Tanya,
You can use oData Create Deep Entity call to achieve that as far as i know. Push the data to backend using oData.
Regards
Anmol
hello, i wrote /*global XLSX*/ bu still get "Uncaught ReferenceError: XLSX is not defined" error
hi ,
i aslo face same error
hello
elif n
I have the same error
You must define it within line 4, it would look like this: funtion (Controller, XLSX){
Can I upload a excel with second row as a header? , If yes please tell me How can I be able to achieve this.