Community
Technology Blog Posts by Members
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

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

former_member540067
Active Participant
‎2020 May 27 9:58 AM
51,926

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
41 Comments