Hi All,
This is part 4 of the blog series and focus of this blog will be on UI5-tooling to add third party libraries to the project, exporting multiple tables as multiple worksheets of single excel file.
Check out Part 1, Part 2 and Part 3 before jumping to this blog.
After completing Part 1, 2 and 3, we were able to achieve file upload, data parsing and generate tabs, tables dynamically, Tabs at bottom, basic toolbar, Borders of tables, Adding new worksheets and tables, and making tables editable.
Ok, this has a few and basic features of excel.
But, export functionality must be there !!
All tables should be exported as single excel file with multiple tabs.
lets try to add these features.
Before we try to add more features to this application, there is something important we need to take care and that is handling third party libraries in UI5 project.
Digression over UI5 tooling
We had added two third party libraries SheetJS and JSZip as Script tags in the component file. Best practice is to use UI5 tooling to handle it.
Check out this blog to for ui5-tooling for detailed understanding of UI5 tooling.
Install UI5-tooling if not already done.
npm install ui5-tooling-modules –save-dev
Add ui5-tooling-modules-middleware and ui5-tooling-modules-task to your ui5.yaml file.
builder:
customTasks:
– name: ui5-tooling-modules-task
afterTask: replaceVersion
configuration:
addToNamespace: true
and
server:
customMiddleware:
– name: ui5-tooling-modules-middleware
afterMiddleware: compression
– name: fiori-tools-proxy
afterMiddleware: compression
… other code as it is …
Install xlsx
npm i xlsx
this will install and add dependency to the package.json file
“dependencies”: {
… other dependency …
“xlsx”: “^0.18.5”
},
run below command to build and that will add xlsx library in the thirdparty folder.
npm run build
To use XLSX library in controller, all you have to do is add to your AMD Define syntax.
sap.ui.define(
[
“sap/ui/core/mvc/Controller”,
“sap/ui/model/json/JSONModel”,
… others …
“xlsx”, // ( or your-ns/thirdparty/xlsx )
],
/**
* {typeof sap.ui.core.mvc.Controller} Controller
… others …
*/
function (
Controller,
JSONModel,
… others …
XLSX
) {
“use strict”;
… old code as it is …
and remove code to add script tags in component file to load external libraries ( which we did in part 1 of this blog series )
Export all tables as worksheets of single excel file
1. Maintain order of worksheets
if you remember, we added a new entry in json model for each worksheet in OnClickNew( ) method.
var oModel = that.getView().getModel(“data”);
var aWorkSheet = oModel.oData;
// new work sheet
var sNewWorksheet = String(that.counter);
aWorkSheet[sNewWorksheet] = [
{
column1: “”,
…
oWorkSheet[sNewWorksheet] adds a new key value pair in the model, however it does not ensure that it will be inserted at the end.
Since adding new key value pair is not maintained in a correct order, we need to rely on extra variable that stores keys of all worksheets in an array.
So, Every time a new worksheet is added or changed, update the array.
To do so, add a new function to initialise the array that.worksheetNames.
// Initialization
maintainWorkSheetNames: function(){
var oModel = that.getView().getModel(“data”);
var aTabs = Object.keys(oModel.getProperty(“/”));
that.worksheetNames = Array();
that.worksheetNames.push(…aTabs);
},
2.Update onFileChange function to Initialise the array.
onFileChange: async function (oEvent) {
… old code as it is …
that.generateTablewithTabsxlsx();
that.maintainWorkSheetNames();
} catch (error) {
console.log(error);
}
}
3.Update onClickNew function to push new worksheet key to the array.
onClickNew: function (oEvent) {
debugger;
var oModel = that.getView().getModel(“data”);
var aWorkSheet = oModel.oData;
var sKey = oEvent.getParameters().key;
var oITB = that.getView().byId(“id_itb”);
var sLen = oITB.getItems().length;
if (sKey == “+”) {
if (!that.counter) {
that.counter = 0;
}
that.counter += 1;
var sNewWorksheet = String(that.counter);
that.worksheetNames.push(sNewWorksheet);
aWorkSheet[sNewWorksheet] = [
{
column1: “”,
column2: “”,
… old code as it is…
4. Add new method onExport( )
When Export button is clicked, onExport method will be called. So add this function to handle exporting.
Whatever changes are made in any of the tables, it gets reflected in the corresponding model, so read the model and create worksheets. To maintain the correct sequence of worksheets, that.worksheetNames is used.
book_new( ) creates a main Excel object.
JSON_toSheet( ) method of XLSX.utils creates new worksheets from JSON data.
book_append_sheet( ) adds newly created worksheet to the main Excel object.
and Finally, writeFile( ) downloads the new file from browser.
onExport: function () {
var sFileName = “newFile”;
var oModel = that.getView().getModel(“data”);
var oTable;
// loop over json Model containing all worksheets and tables
var oWB = XLSX.utils.book_new();
$.each(that.worksheetNames, function (i, sSheetName) {
var aModelData = oModel.getProperty(“/” + sSheetName + “/”);
// Convert JSON to worksheet
var oWS = XLSX.utils.json_to_sheet(aModelData);
// Add the worksheet to the workbook
XLSX.utils.book_append_sheet(oWB, oWS, sSheetName);
});
// Write the workbook to a file
XLSX.writeFile(oWB, sFileName + “.xlsx”);
},
5. Add a button on the screen for user to click.
<Button icon=”sap-icon://download” text=”Export” press=”onExport”></Button>
This is how it would look like..
exported as single excel file with multiple tabs.
Note: I have changed placement of “Upload File” and “Export” buttons to the File Menu.
Thanks for going through the entire blog.
Wait for upcoming blogs on more enhancements to this application.
Hi All,This is part 4 of the blog series and focus of this blog will be on UI5-tooling to add third party libraries to the project, exporting multiple tables as multiple worksheets of single excel file.Check out Part 1, Part 2 and Part 3 before jumping to this blog.After completing Part 1, 2 and 3, we were able to achieve file upload, data parsing and generate tabs, tables dynamically, Tabs at bottom, basic toolbar, Borders of tables, Adding new worksheets and tables, and making tables editable. Ok, this has a few and basic features of excel.But, export functionality must be there !! All tables should be exported as single excel file with multiple tabs. lets try to add these features. Before we try to add more features to this application, there is something important we need to take care and that is handling third party libraries in UI5 project.Digression over UI5 toolingWe had added two third party libraries SheetJS and JSZip as Script tags in the component file. Best practice is to use UI5 tooling to handle it. Check out this blog to for ui5-tooling for detailed understanding of UI5 tooling. Install UI5-tooling if not already done. npm install ui5-tooling-modules –save-dev Add ui5-tooling-modules-middleware and ui5-tooling-modules-task to your ui5.yaml file. builder:
customTasks:
– name: ui5-tooling-modules-task
afterTask: replaceVersion
configuration:
addToNamespace: true and server:
customMiddleware:
– name: ui5-tooling-modules-middleware
afterMiddleware: compression
– name: fiori-tools-proxy
afterMiddleware: compression
… other code as it is … Install xlsx npm i xlsxthis will install and add dependency to the package.json file”dependencies”: {
… other dependency …
“xlsx”: “^0.18.5”
}, run below command to build and that will add xlsx library in the thirdparty folder. npm run build To use XLSX library in controller, all you have to do is add to your AMD Define syntax. sap.ui.define(
[
“sap/ui/core/mvc/Controller”,
“sap/ui/model/json/JSONModel”,
… others …
“xlsx”, // ( or your-ns/thirdparty/xlsx )
],
/**
* {typeof sap.ui.core.mvc.Controller} Controller
… others …
*/
function (
Controller,
JSONModel,
… others …
XLSX
) {
“use strict”;
… old code as it is … and remove code to add script tags in component file to load external libraries ( which we did in part 1 of this blog series ) Export all tables as worksheets of single excel file1. Maintain order of worksheets if you remember, we added a new entry in json model for each worksheet in OnClickNew( ) method. var oModel = that.getView().getModel(“data”);
var aWorkSheet = oModel.oData;
// new work sheet
var sNewWorksheet = String(that.counter);
aWorkSheet[sNewWorksheet] = [
{
column1: “”,
… oWorkSheet[sNewWorksheet] adds a new key value pair in the model, however it does not ensure that it will be inserted at the end. Since adding new key value pair is not maintained in a correct order, we need to rely on extra variable that stores keys of all worksheets in an array.So, Every time a new worksheet is added or changed, update the array.To do so, add a new function to initialise the array that.worksheetNames. // Initialization
maintainWorkSheetNames: function(){
var oModel = that.getView().getModel(“data”);
var aTabs = Object.keys(oModel.getProperty(“/”));
that.worksheetNames = Array();
that.worksheetNames.push(…aTabs);
}, 2.Update onFileChange function to Initialise the array. onFileChange: async function (oEvent) {
… old code as it is …
that.generateTablewithTabsxlsx();
that.maintainWorkSheetNames();
} catch (error) {
console.log(error);
}
} 3.Update onClickNew function to push new worksheet key to the array. onClickNew: function (oEvent) {
debugger;
var oModel = that.getView().getModel(“data”);
var aWorkSheet = oModel.oData;
var sKey = oEvent.getParameters().key;
var oITB = that.getView().byId(“id_itb”);
var sLen = oITB.getItems().length;
if (sKey == “+”) {
if (!that.counter) {
that.counter = 0;
}
that.counter += 1;
var sNewWorksheet = String(that.counter);
that.worksheetNames.push(sNewWorksheet);
aWorkSheet[sNewWorksheet] = [
{
column1: “”,
column2: “”,
… old code as it is… 4. Add new method onExport( )When Export button is clicked, onExport method will be called. So add this function to handle exporting.Whatever changes are made in any of the tables, it gets reflected in the corresponding model, so read the model and create worksheets. To maintain the correct sequence of worksheets, that.worksheetNames is used. book_new( ) creates a main Excel object.JSON_toSheet( ) method of XLSX.utils creates new worksheets from JSON data.book_append_sheet( ) adds newly created worksheet to the main Excel object.and Finally, writeFile( ) downloads the new file from browser. onExport: function () {
var sFileName = “newFile”;
var oModel = that.getView().getModel(“data”);
var oTable;
// loop over json Model containing all worksheets and tables
var oWB = XLSX.utils.book_new();
$.each(that.worksheetNames, function (i, sSheetName) {
var aModelData = oModel.getProperty(“/” + sSheetName + “/”);
// Convert JSON to worksheet
var oWS = XLSX.utils.json_to_sheet(aModelData);
// Add the worksheet to the workbook
XLSX.utils.book_append_sheet(oWB, oWS, sSheetName);
});
// Write the workbook to a file
XLSX.writeFile(oWB, sFileName + “.xlsx”);
}, 5. Add a button on the screen for user to click. <Button icon=”sap-icon://download” text=”Export” press=”onExport”></Button> This is how it would look like..exported as single excel file with multiple tabs.Note: I have changed placement of “Upload File” and “Export” buttons to the File Menu. Thanks for going through the entire blog.Wait for upcoming blogs on more enhancements to this application. Read More Technology Blogs by Members articles
#SAP
#SAPTechnologyblog
