Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member621657
Discoverer
Hello All,

In this blog I am going to explain you, How to download a tree table data to excel.

There are lot of methods available to export data if you are using sap.**.Table or sap.*.table.Table, but In case of tree table you will have your model data in nested structure.

Here using JSON data and downloading the tree data as it is.

 

Note - * : ui , ** : m

Step-1

First create a json data file in model folder. Then add that data to a model in manifest file model section.

Step-2

Go to view file and create a tree table by the help of that model data then view will be .


 

Step-3

In Controller write function for export the table data. In that first take table header info. Then take model data and create the row dynamically.


Step-4

Add the row info in CSV. and write code for file name.


Then use below code to create the excel data.


 

STEP-5

After download the excel file structure will be.


 

Please find all the code below.

 

JSON FILE

add this code to a json file and save that file in model folder in your project.
{
"catalog": {
"clothing": {
"categories": [
{"name": "Women", "categories": [
{"name":"Clothing", "categories": [
{"name": "Dresses", "categories": [
{"name": "Casual Red Dress", "amount": 16.99, "currency": "EUR", "size": "S"},
{"name": "Short Black Dress", "amount": 47.99, "currency": "EUR", "size": "M"},
{"name": "Long Blue Dinner Dress", "amount": 103.99, "currency": "USD", "size": "L"}
]},
{"name": "Tops", "categories": [
{"name": "Printed Shirt", "amount": 24.99, "currency": "USD", "size": "M"},
{"name": "Tank Top", "amount": 14.99, "currency": "USD", "size": "S"}
]},
{"name": "Pants", "categories": [
{"name": "Red Pant", "amount": 32.99, "currency": "USD", "size": "M"},
{"name": "Skinny Jeans", "amount": 44.99, "currency": "USD", "size": "S"},
{"name": "Black Jeans", "amount": 99.99, "currency": "USD", "size": "XS"},
{"name": "Relaxed Fit Jeans", "amount": 56.99, "currency": "USD", "size": "L"}
]},
{"name": "Skirts", "categories": [
{"name": "Striped Skirt", "amount": 24.99, "currency": "USD", "size": "M"},
{"name": "Black Skirt", "amount": 44.99, "currency": "USD", "size": "S"}
]}
]},
{"name":"Jewelry", "categories": [
{"name": "Necklace", "amount": 16.99, "currency": "USD"},
{"name": "Bracelet", "amount": 47.99, "currency": "USD"},
{"name": "Gold Ring", "amount": 399.99, "currency": "USD"}
]},
{"name":"Handbags", "categories": [
{"name": "Little Black Bag", "amount": 16.99, "currency": "USD", "size": "S"},
{"name": "Grey Shopper", "amount": 47.99, "currency": "USD", "size": "M"}
]},
{"name":"Shoes", "categories": [
{"name": "Pumps", "amount": 89.99, "currency": "USD"},
{"name": "Sport Shoes", "amount": 47.99, "currency": "USD"},
{"name": "Boots", "amount": 103.99, "currency": "USD"}
]}
]},
{"name": "Men", "categories": [
{"name":"Clothing", "categories": [
{"name": "Shirts", "categories": [
{"name": "Black T-shirt", "amount": 9.99, "currency": "USD", "size": "XL"},
{"name": "Polo T-shirt", "amount": 47.99, "currency": "USD", "size": "M"},
{"name": "White Shirt", "amount": 103.99, "currency": "USD", "size": "L"}
]},
{"name": "Pants", "categories": [
{"name": "Blue Jeans", "amount": 78.99, "currency": "USD", "size": "M"},
{"name": "Stretch Pant", "amount": 54.99, "currency": "USD", "size": "S"}
]},
{"name": "Shorts", "categories": [
{"name": "Trouser Short", "amount": 62.99, "currency": "USD", "size": "M"},
{"name": "Slim Short", "amount": 44.99, "currency": "USD", "size": "S"}
]}
]},
{"name":"Accessories", "categories": [
{"name": "Tie", "amount": 36.99, "currency": "USD"},
{"name": "Wallet", "amount": 47.99, "currency": "USD"},
{"name": "Sunglasses", "amount": 199.99, "currency": "USD"}
]},
{"name":"Shoes", "categories": [
{"name": "Fashion Sneaker", "amount": 89.99, "currency": "USD"},
{"name": "Sport Shoe", "amount": 47.99, "currency": "USD"},
{"name": "Boots", "amount": 103.99, "currency": "USD"}
]}
]},
{"name": "Girls", "categories": [
{"name":"Clothing", "categories": [
{"name": "Shirts", "categories": [
{"name": "Red T-shirt", "amount": 16.99, "currency": "USD", "size": "S"},
{"name": "Tunic Top", "amount": 47.99, "currency": "USD", "size": "M"},
{"name": "Fuzzy Sweater", "amount": 103.99, "currency": "USD", "size": "L"}
]},
{"name": "Pants", "categories": [
{"name": "Blue Jeans", "amount": 24.99, "currency": "USD", "size": "M"},
{"name": "Red Pant", "amount": 54.99, "currency": "USD", "size": "S"}
]},
{"name": "Shorts", "categories": [
{"name": "Jeans Short", "amount": 32.99, "currency": "USD", "size": "M"},
{"name": "Sport Short", "amount": 14.99, "currency": "USD", "size": "S"}
]}
]},
{"name":"Accessories", "categories": [
{"name": "Necklace", "amount": 26.99, "currency": "USD"},
{"name": "Gloves", "amount": 7.99, "currency": "USD"},
{"name": "Beanie", "amount": 12.99, "currency": "USD"}
]},
{"name":"Shoes", "categories": [
{"name": "Sport Shoes", "amount": 39.99, "currency": "USD"},
{"name": "Boots", "amount": 87.99, "currency": "USD"},
{"name": "Sandals", "amount": 63.99, "currency": "USD"}
]}
]},
{"name": "Boys", "categories": [
{"name":"Clothing", "categories": [
{"name": "Shirts", "categories": [
{"name": "Black T-shirt with Print", "amount": 16.99, "currency": "USD", "size": "S"},
{"name": "Blue Shirt", "amount": 47.99, "currency": "USD", "size": "M"},
{"name": "Yellow Sweater", "amount": 63.99, "currency": "USD", "size": "L"}
]},
{"name": "Pants", "categories": [
{"name": "Blue Jeans", "amount": 44.99, "currency": "USD", "size": "M"},
{"name": "Brown Pant", "amount": 89.99, "currency": "USD", "size": "S"}
]},
{"name": "Shorts", "categories": [
{"name": "Sport Short", "amount": 32.99, "currency": "USD", "size": "M"},
{"name": "Jeans Short", "amount": 99.99, "currency": "USD", "size": "XS"},
{"name": "Black Short", "amount": 56.99, "currency": "USD", "size": "L"}
]}
]},
{"name":"Accessories", "categories": [
{"name": "Sunglasses", "amount": 36.99, "currency": "USD"},
{"name": "Beanie", "amount": 17.99, "currency": "USD"},
{"name": "Scarf", "amount": 15.99, "currency": "USD"}
]},
{"name":"Shoes", "categories": [
{"name": "Sneaker", "amount": 89.99, "currency": "USD"},
{"name": "Sport Shoe", "amount": 47.99, "currency": "USD"},
{"name": "Boots", "amount": 103.99, "currency": "USD"}
]}
]}
]}

},
"sizes": [
{"key": "XS", "value": "Extra Small"},
{"key": "S", "value": "Small"},
{"key": "M", "value": "Medium"},
{"key": "L", "value": "Large"}
]
}

MANIFEST

go to manifest . json in model section add below code.


 

VIEW

add below code in your view.
<TreeTable id="TreeTableBasic" rows="{path:'modelProducts>/catalog/clothing', parameters: { arrayNames: ['categories'] } }"
selectionMode="None" minAutoRowCount="1" width="100%">
<extension>
<m:OverflowToolbar style="Clear">
<m:Title id="title" text="Clothing"/>
<m:ToolbarSpacer/>
<m:Button icon="sap-icon://excel-attachment" press="onDownload"/>
</m:OverflowToolbar>
</extension>
<columns>
<Column width="13rem">
<m:Label text="Categories"/>
<template>
<m:Text text="{modelProducts>name}" wrapping="false"/>
</template>
</Column>
<Column width="9rem">
<m:Label text="Price"/>
<template>
<u:Currency value="{modelProducts>amount}" currency="{modelProducts>currency}"/>
</template>
</Column>
<Column width="11rem">
<m:Label text="Size"/>
<template>
<m:Select items="{path: 'modelProducts>/sizes', templateShareable: true}" selectedKey="{modelProducts>size}"
visible="{= !!${modelProducts>size}}" forceSelection="false">
<core:Item key="{modelProducts>key}" text="{modelProducts>value}"/>
</m:Select>
</template>
</Column>
</columns>
</TreeTable>

 

CONTROLER
onInit: function () {
var oTableModel = this.getView().getModel("modelProducts");
this.getView().byId("TreeTableBasic").setModel(oTableModel);
},
onDownload: function (oEvent) {
var jsonData = this.getView().getModel("modelProducts").getData().catalog.clothing.categories;
this.JSONToCSVConvertor(jsonData);
},
JSONToCSVConvertor: function (JSONData) {
var arrData = JSONData;
var CSV = '';
var row = ""; // To add Table column header in excel
var row1 = "";
var table = this.getView().byId("TreeTableBasic");
table.getColumns().forEach(function (column) {
row1 += '"' + column.getLabel().getText() + '",';
});
CSV += row1 + '\r\n'; //Row that will create Header Columns
var column = {
"name": "name",
"amount": "amount",
"currency": "currency",
"size": "size",
};
var replaceUndefined = function (oExcel) {
var newExcel = oExcel.replace(/undefined/g, " ");
return newExcel;
};
var createRow = function (aTree) {
if (jQuery.isArray(aTree)) {
for (var iIndex = 0; iIndex < aTree.length; iIndex++) {
row = '"' + " " + aTree[iIndex][column.name] +
'","' + aTree[iIndex][column.amount] +
'" ' + aTree[iIndex][column.currency] +
',"' + aTree[iIndex][column.size] +
'",';
CSV += row + "\r\n";
CSV = replaceUndefined(CSV);
if (aTree[iIndex].categories) {
createRow(aTree[iIndex].categories);
}
}
}
};
//create row
createRow(arrData);
if (CSV === '') {
sap.m.MessageToast.show("Invalid data");
return;
}
// Generate a file name
var fileName = "MyReport_";
var blob = new Blob([CSV], {
type: "text/csv;charset=utf-8;"
});

if (sap.ui.Device.browser.name === "ie") { // IE 10+
navigator.msSaveBlob(blob, "csvname.csv");
} else {
var uri = 'data:application/csv;charset=utf-8,' + escape(CSV);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = fileName + ".csv";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
}



 

Regards,

Abinasha Das
3 Comments
Labels in this area