Exporting to Excel from sap.m.Table via csv

[ranjit_rao]

Guys some good news, hopefully for many others as well. In this process of learning and playing with SAPUI5 mobile, I have silently got a way out to export the data to excel. To be frank, we can’t directly export the data to excel. It needs to be formed into a csv format and then to excel.

So, lets begin.

Go to eclipse---New SAPUI5 project<Mobile>--Give the view name as “View1” (js view).

Firstly, the Design

Paste the following code in the createContent() of  View1.view.js (Design):

createContent() of  View1.view.js

var oPage = new sap.m.Page({                     title: "Company Details"              }); var oButton = new sap.m.Button({                     text: "Export",                     press:[ oController.Export, oController ]              });              var oTable = new sap.m.Table({                     id: "Countries",                     mode: sap.m.ListMode.None,                     columns: [ new sap.m.Column({                        width: "1em",                          header: new sap.m.Label({                            text: "Name"                            })                     }),new sap.m.Column({                            width: "1em",                             header: new sap.m.Label({                             text: "short"                             })                       })                     ]               });              var template = new sap.m.ColumnListItem({                    id: "first_template",                       type: "Navigation",                       visible: true,                       selected: true,                       cells: [ new sap.m.Label({                            text: "{name}"                     }),new sap.m.Label({                            text: "{short}"                       })                    ]               });                oTable.bindItems("/countries", template, null, null);                oPage.addContent(oButton);                oPage.addContent(oTable);               return oPage;

Now, Lets create the model which will hold the data.

For that paste the following code in the onInit() method of the controller.js file:

onInit() method of the controller.js

var data1 = {                     "countries" : [ {                            "name" : "India",                            "short" : "In"                     }, {                            "name" : "England",                            "short" : "En"                     }, {                            "name" : "Australia",                            "short" : "Oz"                     }, {                            "name" : "New Zealand",                            "short" : "Nz"                     }, {                            "name" : "France",                            "short" : "Fr"                     }, {                            "name" : "SouthAfrica",                            "short" : "SA"                     }, {                            "name" : "Germany",                            "short" : "Gr"                     } ]       }; var oModel = new sap.ui.model.json.JSONModel(data1); sap.ui.getCore().setModel(oModel);

Now the functionality which would export the data to excel.

For that paste the following method in the controller.js file

JSONToCSVConvertor

JSONToCSVConvertor : function(JSONData, ReportTitle, ShowLabel) { // If JSONData is not an object then JSON.parse will parse the JSON // string in an Object    var arrData = typeof JSONData.countries!= 'object' ?JSON.parse(JSONData.countries) : JSONData.countries;    var CSV = '';    // Set Report title in first row or line CSV+= ReportTitle + '\r\n\n';              if (ShowLabel) {                     var row = "";                                  row= row.slice(0, -1);                   CSV+= row + '\r\n';              }              //loop is to extract each row              for (var i = 0; i <arrData.length; i++) {                     var row = "";                   row+= '"' + arrData[i].name + '","' + arrData[i].short +'",';                   row.slice(0,row.length - 1);                     CSV+= row + '\r\n';              }              if (CSV == '') {           alert("Invalid data");                     return;              }              // Generate a file name              var fileName = "MyReport_";            fileName+= ReportTitle.replace(/ /g, "_");              // Initialize file format you want csv or xls              var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);              // Now the little tricky part.              // you can use either>> window.open(uri);              // but this will not work in some browsers              // or you will not get the correct file extension              // this trick will generate a temp <a /> tag              var link =document.createElement("a");        link.href= uri;              // set the visibility hidden so it will not effect on your web layout        link.style= "visibility:hidden";        link.download= fileName + ".csv"; // this part will append the anchor tag and remove it after automatic   // click              document.body.appendChild(link);              link.click();              document.body.removeChild(link);        }

And the last part. In the design we added a button, now we need to invoke the above function on the click of the button.

For that past the following code in controller.js as well.

Export function in controller.js
Export : function() {   var data= sap.ui.getCore().getModel().getData();   this.JSONToCSVConvertor(data,"Report", true);        }

Now our application ready for testing.  Here, we go:

Hmmmm, Not Bad!!!!!!!!!