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

      23 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 Sachin Das
      Sachin Das

      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 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 Enric Castella
      Enric Castella

      Very useful! thank a lot!