Building custom Excel view in SAPUI5 : Technical Part 3

Hi All,

This is part 3 of the blog series and focus of this blog will be on making tables editable, option to add new worksheets and applying CSS to change look and feel of it.

Check out Part 1 and Part 2- before jumping to this blog.

After completing Part 1 and 2, we were able to achieve file upload, data parsing and generate tabs, tables dynamically, Tabs at bottom, basic toolbar and Borders.

Ok, this looks good enough for start.

But, It should be editable at least !!

Excel is Editable, right?

It allows to Create new tabs.

Too much space around cells, remove that.

lets try to add these features.

1. Make tables Editable

Earlier, while generating tables when file gets uploaded, we set sap.m.Label to display headers and sap.m.Text to display data.

Instead of Labels and Text, Use sap.m.Input to make it editable. That’s all is needed !!

Replace existing code with below snippet. ( for reference I have commented out old code and below it, placed new code )

Function to be edited is “generateTablewithTabsxlsx” which was created in Part 1 of this blog series.

generateTablewithTabsxlsx: function () {
… old code …
// 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 Table({
fixedLayout: false,
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 header = that.getView().getModel(“header”);
header.setProperty(“/” + val + “/”, oColumnNames);
var oTemplate = new Column({
// ——— old code commented —————
// header: new Label({
// text: “{header>Text}”,
// customData: [
// new CustomData({
// key: “colName”,
// value: “{header>Text}”,
// writeToDom: true
// })
// ],
// })
// ——— below is the New code snippet —————
header: new Input({
value: “{header>Text}”,
customData: [
new 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;
// ——— Old code commented out —————
// oItemTemplate.addCell(new Text({
// text: “{data>” + oHeaderName + “}”
// }));
// ——— below is the New code snippet —————
oItemTemplate.addCell(new Input({
value: “{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 IconTabFilter({
text: val,
content: new ScrollContainer({
height: “350px”,
vertical: true,
content: oTable
})
})
);

});

… old code …
}

It should look like this..

Add custom CSS to hide boxes and underline effect of Input control.

.sapMInputBaseContentWrapper:not(.sapMInputBaseReadonlyWrapper):not(.sapMInputBaseContentWrapperState)
{
box-shadow: none;
}

.sapMInputBaseContentWrapper{
background: none;
}

After that, it should look like ..

Table looks good but what happend to the Combobox ?

Digression over overriding UI5 standard class

If you look at Combo Box at top closely, it is transparent now. CSS has affected other UI5 controls as well.

Whenever custom CSS style or effect is needed, make sure to add a custom class or an ID to the control and restrict CSS style to that control only.

Since all dynamically generated content is inside IconTabBar and it has a custom class, apply it to Custom CSS styling to restrict it to UI5 contols contained by IconTabBar.

Custom CSS restricted to Icon Tab Bar. To do so, CSS Combinators are used.

Check out more details about CSS Combinators here.

Specific Controls which are descendents of a control with class “excel”. ( just add space after .excel )

.excel .sapMListTblCell {
border-left: 1px solid #ccc;
}

.excel .sapMInputBaseContentWrapper:not(.sapMInputBaseReadonlyWrapper):not(.sapMInputBaseContentWrapperState)
{
box-shadow: none;
}

.excel .sapMInputBaseContentWrapper{
background: none;
}

after that, it should look like ..

Now, no custom style is applied on Combobox ( cause it is not in scope of .excel class )

2. While scrolling, Headers should stick to top

generateTablewithTabsxlsx: function () {
… old code as it is …
oTable = new sap.m.Table({
fixedLayout: false,
backgroundDesign: sap.m.BackgroundDesign.Solid,
sticky : [sap.m.Sticky.HeaderToolbar,
sap.m.Sticky.InfoToolbar,
sap.m.Sticky.ColumnHeaders],
showNoData: true
});
… old code as it is …
}

This will make sure that while scrolling content, header sticks to the top.

It should look like this..

Header ( First Row ) is sticky and is not moving while scrolling.

Optional : If you don’t want extra padding that is applied to all cells, add below styles in CSS file.

.excel .sapMColumnHeader,

.excel .sapMListTblCell,

.excel .sapMListTblHighlightCol+.sapMListTblHeaderCell,

.excel .sapMListTblHighlightCell+.sapMListTblCell,

.sapUiSizeCompact .excel .sapMListTblHighlightCol+.sapMListTblCell.sapMListTblHeaderCell

{
padding: 0;
}

and it would look like..

3. Option to create Tabs

update old code to add dummy entry at the end of all worksheets with “+” sign.

generateTablewithTabsxlsx: function () {

.. old code as it is..

// 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) {
.. old code as it is ..
});

// ———- new code below ———-
// dummy entry at end of actual worksheets
oITB.addItem(
new IconTabFilter({
text: “+”,
key: “+”
})
);

Add new function onClickNew as below. This will create and insert new empty worksheet tabs when “+” is clicked.

onClickNew: function(oEvent){
debugger;

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;
// do not expand
// add new filter bar before current position
oITB.insertItem(
new IconTabFilter({
text: “New Tab”,
key: that.counter,
content: new ScrollContainer({
height: “350px”,
vertical: true,
content: new Text({
text : “hi”
})
})
})
, sLen – 1);
oITB.setSelectedKey(that.counter);

}
}

Make sure to add “select” event handler “onClickNew” for the IconTabBar defined in the View.

Whenever any of the IconTabFilters inside IconTabBar is clicked, select event is fired.

.. old code as it is ..
<IconTabBar id=”id_itb” class=”excel” select=”onClickNew”>
.. old code as it is ..

It should look like..

Add below code to add an empty table when new worksheet is added.

onClickNew: function (oEvent) {

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 = that.counter;
aWorkSheet[sNewWorksheet] = [
{
column1: “”,
column2: “”,
column3: “”,
column4: “”,
column5: “”,
},
{
column1: “”,
column2: “”,
column3: “”,
column4: “”,
column5: “”,
},
{
column1: “”,
column2: “”,
column3: “”,
column4: “”,
column5: “”,
},
{
column1: “”,
column2: “”,
column3: “”,
column4: “”,
column5: “”,
},
];

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

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

// add new filter bar before current position
oITB.insertItem(
new IconTabFilter({
text: “New Tab”,
key: that.counter,
content: new ScrollContainer({
height: “350px”,
vertical: true,
content: oTable,
}),
}),
sLen – 1
);

// set selected key to newly created tab
oITB.setSelectedKey(that.counter);
}
},

It should look like this..

Thank you for going though the entire blog.

Will publish next blog soon on exporting all tabs as worksheets of single EXCEL file.

Stay tuned for upcoming blogs on more features enhancement to this app.

Hi All, This is part 3 of the blog series and focus of this blog will be on making tables editable, option to add new worksheets and applying CSS to change look and feel of it. Check out Part 1 and Part 2- before jumping to this blog.After completing Part 1 and 2, we were able to achieve file upload, data parsing and generate tabs, tables dynamically, Tabs at bottom, basic toolbar and Borders.Ok, this looks good enough for start.But, It should be editable at least !! Excel is Editable, right? It allows to Create new tabs. Too much space around cells, remove that. lets try to add these features.1. Make tables EditableEarlier, while generating tables when file gets uploaded, we set sap.m.Label to display headers and sap.m.Text to display data.Instead of Labels and Text, Use sap.m.Input to make it editable. That’s all is needed !! Replace existing code with below snippet. ( for reference I have commented out old code and below it, placed new code ) Function to be edited is “generateTablewithTabsxlsx” which was created in Part 1 of this blog series. generateTablewithTabsxlsx: function () {
… old code …
// 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 Table({
fixedLayout: false,
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 header = that.getView().getModel(“header”);
header.setProperty(“/” + val + “/”, oColumnNames);
var oTemplate = new Column({
// ——— old code commented —————
// header: new Label({
// text: “{header>Text}”,
// customData: [
// new CustomData({
// key: “colName”,
// value: “{header>Text}”,
// writeToDom: true
// })
// ],
// })
// ——— below is the New code snippet —————
header: new Input({
value: “{header>Text}”,
customData: [
new CustomData({
key: “colName”,
value: “{header>Text}”,
writeToDom: true
})
],
})
});

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

});

… old code …
} It should look like this..Add custom CSS to hide boxes and underline effect of Input control. .sapMInputBaseContentWrapper:not(.sapMInputBaseReadonlyWrapper):not(.sapMInputBaseContentWrapperState)
{
box-shadow: none;
}

.sapMInputBaseContentWrapper{
background: none;
} After that, it should look like ..Table looks good but what happend to the Combobox ?Digression over overriding UI5 standard class If you look at Combo Box at top closely, it is transparent now. CSS has affected other UI5 controls as well.Whenever custom CSS style or effect is needed, make sure to add a custom class or an ID to the control and restrict CSS style to that control only.Since all dynamically generated content is inside IconTabBar and it has a custom class, apply it to Custom CSS styling to restrict it to UI5 contols contained by IconTabBar. <IconTabBar id=”id_itb” class=”excel”> Custom CSS restricted to Icon Tab Bar. To do so, CSS Combinators are used.Check out more details about CSS Combinators here. Specific Controls which are descendents of a control with class “excel”. ( just add space after .excel ) .excel .sapMListTblCell {
border-left: 1px solid #ccc;
}

.excel .sapMInputBaseContentWrapper:not(.sapMInputBaseReadonlyWrapper):not(.sapMInputBaseContentWrapperState)
{
box-shadow: none;
}

.excel .sapMInputBaseContentWrapper{
background: none;
} after that, it should look like ..Now, no custom style is applied on Combobox ( cause it is not in scope of .excel class ) 2. While scrolling, Headers should stick to top generateTablewithTabsxlsx: function () {
… old code as it is …
oTable = new sap.m.Table({
fixedLayout: false,
backgroundDesign: sap.m.BackgroundDesign.Solid,
sticky : [sap.m.Sticky.HeaderToolbar,
sap.m.Sticky.InfoToolbar,
sap.m.Sticky.ColumnHeaders],
showNoData: true
});
… old code as it is …
} This will make sure that while scrolling content, header sticks to the top.It should look like this..Header ( First Row ) is sticky and is not moving while scrolling. Optional : If you don’t want extra padding that is applied to all cells, add below styles in CSS file. .excel .sapMColumnHeader,

.excel .sapMListTblCell,

.excel .sapMListTblHighlightCol+.sapMListTblHeaderCell,

.excel .sapMListTblHighlightCell+.sapMListTblCell,

.sapUiSizeCompact .excel .sapMListTblHighlightCol+.sapMListTblCell.sapMListTblHeaderCell

{
padding: 0;
} and it would look like.. 3. Option to create Tabsupdate old code to add dummy entry at the end of all worksheets with “+” sign. generateTablewithTabsxlsx: function () {

.. old code as it is..

// 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) {
.. old code as it is ..
});

// ———- new code below ———-
// dummy entry at end of actual worksheets
oITB.addItem(
new IconTabFilter({
text: “+”,
key: “+”
})
);

}, Add new function onClickNew as below. This will create and insert new empty worksheet tabs when “+” is clicked. onClickNew: function(oEvent){
debugger;

var sKey = oEvent.getParameters().key;
var oITB = that.getView().byId(“id_itb”);
var sLen = oITB.getItems().length;

}
} Make sure to add “select” event handler “onClickNew” for the IconTabBar defined in the View.Whenever any of the IconTabFilters inside IconTabBar is clicked, select event is fired. .. old code as it is ..
<IconTabBar id=”id_itb” class=”excel” select=”onClickNew”>
.. old code as it is .. It should look like.. Add below code to add an empty table when new worksheet is added. onClickNew: function (oEvent) {

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;

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

// set selected key to newly created tab
oITB.setSelectedKey(that.counter);
}
}, It should look like this..Thank you for going though the entire blog. Will publish next blog soon on exporting all tabs as worksheets of single EXCEL file. Stay tuned for upcoming blogs on more features enhancement to this app. Read More Technology Blogs by Members articles

#SAP

#SAPTechnologyblog