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 + 'rn'; //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 + "rn";
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);
}
}