How to import Excel xlsx,xls file to SAP UI5 Application.

There can be multiple scenarios, where user would be required to import excel, csv file to UI5 application and then perform actions on selected line Items. In this blog, let us see ways to upload an excel or CSV file to UI5 application.

Normaly to do so, we have 2 ways:

1) Use external library - Sheet.js

2) Use javascript abilities - only for CSV files

Use External Library

Let's keep the use case simple, we will have one view having a FileUploader and to display the data lets bind it to a table control.

<mvc:View controllerName="com.UploadExcel.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>

Now, update the code in onUpload function in the respective controller.

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

Our main part of the code is done, we need to add the external library reference in the component.js file.

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

Now, click on the browse button and select an excel file, and click on open. After selection the data is shown in the table.

Hope, this was easy to understand. Now in the next blog let us see how to import a CSV file to UI5 application.

We can also see how we can validate the excel file using the header names in the next blog.

• Tweet
• Share
• Share
• Share
• Share

About Admin MC3

This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.

Related Post