Skip to Content
Technical Articles
Author's profile photo Anmol Chadha

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

Assigned Tags

      37 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo ABAP Team
      ABAP Team

      Hi Anmol Chadha,

      Could you please share the complete code of index.html?

      Thanks,

      Ram

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      Hi,

       

      Below is the code:

       

      <!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>
      		<meta charset="utf-8">
      	    <meta name="viewport" content="width=device-width, initial-scale=1.0">
      		<title>ReadFromExcel</title>
      		<script id="sap-ui-bootstrap"
      			src="resources/sap-ui-core.js"
      			data-sap-ui-theme="sap_fiori_3"
      			data-sap-ui-resourceroots='{"com.test.ReadFromExcel": "./"}'
      			data-sap-ui-compatVersion="edge"
      			data-sap-ui-oninit="module:sap/ui/core/ComponentSupport"
      			data-sap-ui-async="true"
      			data-sap-ui-frameOptions="trusted">
      		</script>
      	</head>
      	<body class="sapUiBody">
      		<div data-sap-ui-component data-name="com.test.ReadFromExcel" data-id="container" data-settings='{"id" : "ReadFromExcel"}'></div>
      	</body>
      </html>
      Author's profile photo Ramakrishnan Subramaniam
      Ramakrishnan Subramaniam

      Thanks Anmol Chadha.  It worked !

      Author's profile photo shally pathak
      shally pathak

      It was very helpful blog.Thank you so much Anmol Chadha.

      Author's profile photo Manjunath GUDISI
      Manjunath GUDISI

      Hi Anmol Chadha - With the same XLSX library, can we read csv file as well. Any sample code that you can provide. Thank you.

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      Hi,

       

      You can use the javascript reader for csv. No need of any external library.

       

      I will share the snippet with you.

       

      Author's profile photo Iman Ahmadpour
      Iman Ahmadpour

      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

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      Hi,

       

      Sorry for the late reply,

       

      Just write

      /*global XLSX*/

      At the top of the controller file.

       

       

      Author's profile photo Deb D S
      Deb D S

      Hi Anmol,

      Thanks for this blog.
      Can you please tell me where i have to write Global XLSX ?

      Author's profile photo Anmol Chadha
      Anmol Chadha

      Hi

       

      Please write it at the top of controller file.

      Author's profile photo Zenith Shah
      Zenith Shah

      Hi Iman,

      I face same errros.....

      Please help if your problems are solved!

      Author's profile photo Manimaran Rajendran
      Manimaran Rajendran

      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.

      Author's profile photo Anmol Chadha
      Anmol Chadha

      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.

      Author's profile photo Vignesh M
      Vignesh M

      hi, please tell me how to do dynamic columns table by uploading a excel file.,?

      Author's profile photo Manjunath NJ
      Manjunath NJ

      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?

       

      Author's profile photo Snn ui5
      Snn ui5

      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.

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      Yes it should go same.

      Author's profile photo japa sadhana
      japa sadhana

      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??

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      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);
      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      You can use the destination to CDN and use it in the app for loading the file using component.js file.

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      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);
      Author's profile photo Shivani Matkar
      Shivani Matkar

      Hello Anmol,

      I have added this code to my component.js file, I am getting this error, Is there any alternative for this?

      Author's profile photo Shubham Bansod
      Shubham Bansod

      Hi Anmol,

      I also getting the same error, can you please share your component.js code

      Author's profile photo Diwakar Pemmaraju
      Diwakar Pemmaraju

      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?

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      Hi Diwakar,

       

      I dont think there would be a work around as we are fetching the files from CDN.

       

      Regards

      Anmol

      Author's profile photo Enric Castella
      Enric Castella

      Very useful! thank a lot!

      Author's profile photo Yergali Yertuganov
      Yergali Yertuganov

      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:

      1. Do you have any example of using this or similar external Library in the MDK app (or Native Android)?
      2. Since it will be Offline, therefore at times it won´t be able to reach external Libraries and is there way to incorporate this library in the bundle?

      Many thanks,

      Yergali

       

       

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      Hi Yergali,

       

      Sorry for delayed response.

      I am not sure about MDK. Didnt checked it for that.

       

      Regards

      Anmol

      Author's profile photo Paresh Dhanwani
      Paresh Dhanwani

      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

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      Hi paresh.

       

      Sorry for the late reply. I didnt found any way to achive it without external libs.

       

      Regards

      Anmol

      Author's profile photo tanya nanaiah
      tanya nanaiah

      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

      Author's profile photo Anmol Chadha
      Anmol Chadha
      Blog Post Author

      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

      Author's profile photo elif n
      elif n

      hello, i wrote /*global XLSX*/ bu still get "Uncaught ReferenceError: XLSX is not defined" error

      Author's profile photo Rohit Patil
      Rohit Patil

      hi ,

      i aslo face same error

      Author's profile photo Rohit Patil
      Rohit Patil

      hello

      elif n

      I have the same error

      Author's profile photo Luis Fernando Henao
      Luis Fernando Henao
      You must define it within line 4, it would look like this: funtion (Controller, XLSX){
      Author's profile photo Sambit Pattanaik
      Sambit Pattanaik

      Can I upload a excel with second row as a header? , If yes please tell me How can I be able to achieve this.