Technical Articles

Anmol Chadha

May 27, 2020 3 minute read

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

37 Comments

You must be Logged on to comment or reply to a post.

ABAP Team

July 8, 2020 at 6:30 am

Hi Anmol Chadha,

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

Thanks,

Ram

Anmol Chadha

Blog Post Author

July 8, 2020 at 7:07 am

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>

Ramakrishnan Subramaniam

July 20, 2020 at 4:52 pm

Thanks Anmol Chadha. It worked !

shally pathak

August 27, 2020 at 9:31 am

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

Manjunath GUDISI

September 3, 2020 at 6:06 am

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

Anmol Chadha

Blog Post Author

October 23, 2020 at 10:23 am

Hi,

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

I will share the snippet with you.

Iman Ahmadpour

September 30, 2020 at 9:58 am

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

Anmol Chadha

Blog Post Author

October 23, 2020 at 10:21 am

Hi,

Sorry for the late reply,

Just write

/*global XLSX*/

At the top of the controller file.

Deb D S

March 19, 2021 at 7:13 am

Hi Anmol,

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

Anmol Chadha

April 5, 2021 at 5:55 pm

Please write it at the top of controller file.

Zenith Shah

October 22, 2020 at 8:27 am

Hi Iman,

I face same errros.....

Please help if your problems are solved!

Manimaran Rajendran

December 23, 2020 at 7:28 am

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.

Anmol Chadha

April 5, 2021 at 5:56 pm

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.

Vignesh M

April 16, 2021 at 11:59 am

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

Manjunath NJ

May 12, 2021 at 12:31 pm

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?

Snn ui5

June 11, 2021 at 6:19 am

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.

Anmol Chadha

Blog Post Author

August 20, 2021 at 2:41 pm

Yes it should go same.

japa sadhana

July 29, 2021 at 6:30 am

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

Anmol Chadha

Blog Post Author

August 20, 2021 at 2:41 pm

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);

Anmol Chadha

Blog Post Author

August 20, 2021 at 2:47 pm

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

Anmol Chadha

Blog Post Author

August 20, 2021 at 2:40 pm

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);

Shivani Matkar

July 15, 2022 at 1:29 pm

Hello Anmol,

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

Shubham Bansod

July 20, 2022 at 9:34 am

Hi Anmol,

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

Diwakar Pemmaraju

October 11, 2021 at 9:28 am

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?

Anmol Chadha

Blog Post Author

February 15, 2022 at 10:05 am

Hi Diwakar,

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

Regards

Anmol

Enric Castella

October 23, 2021 at 9:23 pm

Very useful! thank a lot!

Yergali Yertuganov

October 26, 2021 at 2:34 pm

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:

Do you have any example of using this or similar external Library in the MDK app (or Native Android)?
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

Anmol Chadha

Blog Post Author

February 15, 2022 at 10:04 am

Hi Yergali,

Sorry for delayed response.

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

Regards

Anmol

Paresh Dhanwani

November 29, 2021 at 1:37 pm

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

Anmol Chadha

Blog Post Author

February 15, 2022 at 10:02 am

Hi paresh.

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

Regards

Anmol

tanya nanaiah

February 15, 2022 at 8:43 am

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

Anmol Chadha

Blog Post Author

February 15, 2022 at 10:03 am

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

elif n

February 28, 2022 at 12:34 am

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

Rohit Patil

February 10, 2023 at 5:35 am

hi ,

i aslo face same error

Rohit Patil

February 10, 2023 at 5:37 am

hello

elif n

I have the same error

Luis Fernando Henao

April 11, 2023 at 4:51 pm

You must define it within line 4, it would look like this: funtion (Controller, XLSX){

Sambit Pattanaik

June 9, 2022 at 1:11 pm

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

Technical Articles

Read Data From Excel File (.XLSX) In SAP UI5 App And Display Contents In Table

Introduction:

Steps:

Conclusion:

Assigned Tags