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:
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
Original Article:
https://blogs.sap.com/2020/05/27/read-data-from-excel-file-.xlsx-in-sap-ui5-app-and-display-contents-in-table/