Building custom Excel view in SAPUI5 : Technical Part 1

I am starting a small blog series on how to build an excel editor. This might help if you are learning freestyle UI5 development and working with Files.

When working with Excel and dynamic table creation, you cannot hardcode names and number of columns ( Headers ). Here in this sample application, both header and data are set dynamically.

I had came accross a really good blog for the same purpose and I have used the code as well.

here is the link for the blog. https://community.sap.com/t5/technology-blogs-by-members/creation-of-dynamic-table-with-csv-xlsx-files-using-file-upload-in-sap-ui5/ba-p/13478701 by poornamamatha

Consider my blog as extension of the above blog.

This is part 1 of the blog series and focus of this blog will be on a free style sample UI5 app with XLSX file upload with multiple worksheets.

I will cover advanced features in upcoming blogs.

Step 1 : A View that look similar to Excel

Initially, below code would be there in View1 ( or other name you give while generating app with fiori generator tools ). In below case, I set it to Home.view.xml

<mvc:View controllerName=”your-namespace.controller.Home”
xmlns:mvc=”sap.ui.core.mvc” displayBlock=”true”
xmlns=”sap.m”>
<Page id=”page” title=”{i18n>title}”>
<content />
</Page>
</mvc:View>

Replace above code with below code.

<mvc:View controllerName=”xl.sx.excelxlsx.controller.Home”
xmlns=”sap.m”
xmlns:mvc=”sap.ui.core.mvc”
xmlns:unified=”sap.ui.unified”
xmlns:uitab=”sap.ui.table”
xmlns:l=”sap.ui.layout”
xmlns:f=”sap.f”
xmlns:c=”sap.ui.core”
xmlns:table=”sap.ui.table”>
<f:DynamicPage id=”dynamicPageId”
stickySubheaderProvider=”iconTabBar”
class=”sapUiNoContentPadding”>
<!– DynamicPage Title –>
<!– <f:title>
<f:DynamicPageTitle>
<f:heading>
<Title text=”{i18n>MainPageTitle}” />
</f:heading>
<f:content>
<Toolbar>
</Toolbar>
</f:content>
<f:actions>
</f:actions>
<f:navigationActions>
</f:navigationActions>
</f:DynamicPageTitle>
</f:title> –>

<!– DynamicPage Header –>
<f:header>
<f:DynamicPageHeader pinnable=”true”>
<IconTabBar >
<items>
<IconTabFilter text=”File”>
<items>
<IconTabFilter text=”Save”/>
</items>
<content>
<Button text=”hi”></Button>
</content>
</IconTabFilter>
<IconTabFilter text=”Home”></IconTabFilter>
<IconTabFilter text=”Insert”></IconTabFilter>
<IconTabFilter text=”Page Layout”></IconTabFilter>
<IconTabFilter text=”Formulas”></IconTabFilter>
<IconTabFilter text=”Data”></IconTabFilter>
<IconTabFilter text=”Review”></IconTabFilter>
<IconTabFilter text=”View”></IconTabFilter>
<IconTabFilter text=”Help”></IconTabFilter>
</items>
</IconTabBar>
</f:DynamicPageHeader>
</f:header>

Add a Custom Css Class

/* Enter your custom styles here */
.sapMListTblCellNoData {
height: 400px;
}

When there is no data, it shows only one line with text set in “noData” property. for better view, height is set to 400 px even when there is no data.

After that, your screen would look like this..

Digression over File Uploader

Focus on below code snippet of FileUploader, it has gets rendered as button as buttonOnly is set to true.

When user clicks on the button, it opens up a popup for file selection.

Allowed file types are set to XLSX and CSV.

<unified:FileUploader
id=”FileUploaderid1″
name=”file”
fileType=”xlsx,csv”
buttonOnly=”true”
change=”onFileChange”
icon=”sap-icon://attachment-e-pub” />

Another option is for connecting to any backend and get JSON data to populate in the table. Not required right now.

Step 2 : Add external Libraries

To work with XLSX file, we need to add dependencies of external libraries that can parse the file.

Add sheetJS library in init method of Component.js file. ( Also, JSZip is required as it is a dependency for sheetJS )

var jQueryScript = document.createElement(‘script’);
jQueryScript.setAttribute(‘src’, ‘https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/jszip.js’);
document.head.appendChild(jQueryScript);

your component.js file should look like this..

Step 3 :

Initialize JSON Models

onInit: function () {
that = this;
that.getView().setModel(new JSONModel(), “header”);
var oModelData1 = new JSONModel();
oModelData1.setSizeLimit(1000);
that.getView().setModel(oModelData1, “data”);
},

Note : “that” is a variable declared globally in the controller and assigned to “this” in onInit method.

Default JSON model size is 100, I have increased it to 1000.

Handler for File Change event

Place below asynchronous function in your controller file.

Reading file is asynchronous activity and hence below function is declared as async.

onFileChange: async function (oEvent) {
var oFileUploader = that.getView().byId(“FileUploaderid1”);
var oFile = oFileUploader.getFocusDomRef().files[0];
that.oFile = oFile; // store for global usage

// -ignore
try {
if (oFile.type === “application/vnd.ms-excel”) {
await that.typeCsv();
}
else {
await that.typeXLSXwithTabs();
}
that.generateTablewithTabsxlsx();
} catch (error) {
console.log(error);
}
},

Read file based on type of the file – XLSX

typeXLSXwithTabs: async function () {

var file = that.file;
var excelData = {};

return new Promise((resolve, reject) => {
if (file && window.FileReader) {
var reader = new FileReader();
reader.onload = async function (evt) {
var data = evt.target.result;
var workbook = XLSX.read(data, {
type: ‘binary’
});

// loop over all worksheets of the uploaded XLSX File
// and store them in excelData Array
workbook.SheetNames.forEach(function (sheetName) {
excelData[sheetName] = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
});

that.getView().getModel(“data”).setData(excelData);
that.getView().getModel(“data”).refresh(true);
resolve(true);
};
reader.onerror = function (ex) {
reject(false);
};
reader.readAsBinaryString(file);
}
});

Generate Tables dynamically based on tabs ( worksheets )

generateTablewithTabsxlsx: function () {

var oITB = that.getView().byId(“id_itb”);
oITB.destroyItems();

var oModel = that.getView().getModel(“data”);
var aTabs = Object.keys(oModel.getProperty(“/”));
var oTable;

// loop over all worksheets stored in below array
// for each worksheet => add a table
// for each table => add columns and Cells
$.each(aTabs, function (i, val) {
oTable = new sap.m.Table({
fixedLayout: false,
backgroundDesign: sap.m.BackgroundDesign.Solid,
showNoData: true
});
var oModelData = oModel.getProperty(“/” + val + “/”);
var aColumns = Object.keys(oModelData[0]);
var oColumnNames = [];
$.each(aColumns, function (i, value) {
oColumnNames.push({
Text: aColumns[i]
});
});
var columnmodel = that.getView().getModel(“header”);
columnmodel.setProperty(“/” + val + “/”, oColumnNames);
var oTemplate = new Column({
header: new Label({
text: “{header>Text}”,
customData: [
new sap.ui.core.CustomData({
key: “colName”,
value: “{header>Text}”,
writeToDom: true
})
],
})
});

oTable.bindAggregation(“columns”, “header>/” + val + “/”, oTemplate);
var oItemTemplate = new ColumnListItem();

$.each(aColumns, function (j, value) {
var oHeaderName = value;
oItemTemplate.addCell(new Text({
text: “{data>” + oHeaderName + “}”
}));
});
oTable.bindAggregation(“items”, {
path: “data>/” + val + “/”,
template: oItemTemplate
});

// Add IconTabFilter for the current worksheet
// add the table, created in current loop, as content of IconTabFilter
oITB.addItem(
new sap.m.IconTabFilter({
text: val,
content: new sap.m.ScrollContainer({
height: “350px”,
vertical: true,
content: oTable
})
})
);

});

After uploading a file with single worksheet, it would look like ..

If there are more than one tabs ( worksheets ), it should look like ..

Worksheet 1 : Employee Data rendered as Icon Tab Filter with Content set as a Table

Worksheet 2 : Department Data rendered as Icon Tab Filter with Content set as a Table

Thanks you for going through the entire blog and I hope it may help in learning UI5 or developing any project.

Wait for upcoming blogs on more enhancements to this application.

I am starting a small blog series on how to build an excel editor. This might help if you are learning freestyle UI5 development and working with Files.When working with Excel and dynamic table creation, you cannot hardcode names and number of columns ( Headers ). Here in this sample application, both header and data are set dynamically.I had came accross a really good blog for the same purpose and I have used the code as well. here is the link for the blog. https://community.sap.com/t5/technology-blogs-by-members/creation-of-dynamic-table-with-csv-xlsx-files-using-file-upload-in-sap-ui5/ba-p/13478701 by poornamamatha Consider my blog as extension of the above blog.This is part 1 of the blog series and focus of this blog will be on a free style sample UI5 app with XLSX file upload with multiple worksheets.I will cover advanced features in upcoming blogs. Step 1 : A View that look similar to ExcelInitially, below code would be there in View1 ( or other name you give while generating app with fiori generator tools ). In below case, I set it to Home.view.xml <mvc:View controllerName=”your-namespace.controller.Home”
xmlns:mvc=”sap.ui.core.mvc” displayBlock=”true”
xmlns=”sap.m”>
<Page id=”page” title=”{i18n>title}”>
<content />
</Page>
</mvc:View> Replace above code with below code. <mvc:View controllerName=”xl.sx.excelxlsx.controller.Home”
xmlns=”sap.m”
xmlns:mvc=”sap.ui.core.mvc”
xmlns:unified=”sap.ui.unified”
xmlns:uitab=”sap.ui.table”
xmlns:l=”sap.ui.layout”
xmlns:f=”sap.f”
xmlns:c=”sap.ui.core”
xmlns:table=”sap.ui.table”>
<f:DynamicPage id=”dynamicPageId”
stickySubheaderProvider=”iconTabBar”
class=”sapUiNoContentPadding”>
<!– DynamicPage Title –>
<!– <f:title>
<f:DynamicPageTitle>
<f:heading>
<Title text=”{i18n>MainPageTitle}” />
</f:heading>
<f:content>
<Toolbar>
</Toolbar>
</f:content>
<f:actions>
</f:actions>
<f:navigationActions>
</f:navigationActions>
</f:DynamicPageTitle>
</f:title> –>

<!– DynamicPage Content –>
<f:content>
<VBox>
<OverflowToolbar id=”otbSubheader1″
backgroundDesign=”Solid”>
<ToolbarSpacer />
<unified:FileUploader
id=”FileUploaderid1″
name=”file”
fileType=”xlsx,csv”
buttonOnly=”true”
change=”onFileChange”
icon=”sap-icon://attachment-e-pub” />
<Text text=”/” class=”sapUiTinyMargin” />
<Button id=”DBConnect1″
backgroundDesign=”Solid”
press=”onDBConnect”
icon=”sap-icon://disconnected”
text=”Connect to DB” />
</OverflowToolbar>
<IconTabBar id=”id_itb”>
<items>
<IconTabFilter text=”worksheet1″>
<ScrollContainer height=”500px” vertical=”true”
horizontal=”true” id=”id_vbox1″
backgroundDesign=”Solid”>
<Table fixedLayout=”Strict”
backgroundDesign=”Solid”
showNoData=”true”
noDataText=”{i18n>noSourceDataText}”
id=”TableId1″
growingThreshold=”10000″
growingScrollToLoad=”true”
growing=”true”>
</Table>
</ScrollContainer>
</IconTabFilter>
</items>
</IconTabBar>
</VBox>
</f:content>
</f:DynamicPage>
</mvc:View>     Add a Custom Css Class     /* Enter your custom styles here */
.sapMListTblCellNoData {
height: 400px;
}    When there is no data, it shows only one line with text set in “noData” property. for better view, height is set to 400 px even when there is no data. After that, your screen would look like this.. Digression over File UploaderFocus on below code snippet of FileUploader, it has gets rendered as button as buttonOnly is set to true.When user clicks on the button, it opens up a popup for file selection. Allowed file types are set to XLSX and CSV.    <unified:FileUploader
id=”FileUploaderid1″
name=”file”
fileType=”xlsx,csv”
buttonOnly=”true”
change=”onFileChange”
icon=”sap-icon://attachment-e-pub” />    Another option is for connecting to any backend and get JSON data to populate in the table. Not required right now. Step 2 : Add external LibrariesTo work with XLSX file, we need to add dependencies of external libraries that can parse the file.Add sheetJS library in init method of Component.js file. ( Also, JSZip is required as it is a dependency for sheetJS )     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.full.min.js’);
document.head.appendChild(jQueryScript); your component.js file should look like this.. Step 3 : Initialize JSON Models onInit: function () {
that = this;
that.getView().setModel(new JSONModel(), “header”);
var oModelData1 = new JSONModel();
oModelData1.setSizeLimit(1000);
that.getView().setModel(oModelData1, “data”);
}, Note : “that” is a variable declared globally in the controller and assigned to “this” in onInit method. Default JSON model size is 100, I have increased it to 1000. Handler for File Change eventPlace below asynchronous function in your controller file.Reading file is asynchronous activity and hence below function is declared as async. onFileChange: async function (oEvent) {
var oFileUploader = that.getView().byId(“FileUploaderid1”);
var oFile = oFileUploader.getFocusDomRef().files[0];
that.oFile = oFile; // store for global usage

var file = that.file;
var excelData = {};

}, Generate Tables dynamically based on tabs ( worksheets ) generateTablewithTabsxlsx: function () {

var oITB = that.getView().byId(“id_itb”);
oITB.destroyItems();

var oModel = that.getView().getModel(“data”);
var aTabs = Object.keys(oModel.getProperty(“/”));
var oTable;

oTable.bindAggregation(“columns”, “header>/” + val + “/”, oTemplate);
var oItemTemplate = new ColumnListItem();

});

}, After uploading a file with single worksheet, it would look like .. If there are more than one tabs ( worksheets ), it should look like ..Worksheet 1 : Employee Data rendered as Icon Tab Filter with Content set as a Table Worksheet 2 : Department Data rendered as Icon Tab Filter with Content set as a Table Thanks you for going through the entire blog and I hope it may help in learning UI5 or developing any project.Wait for upcoming blogs on more enhancements to this application. ` Read More Technology Blogs by Members articles

#SAP

#SAPTechnologyblog