Export sap.m.Table in CSV and XLS format

Hello Readers,

It is very common requirement from clients to display data from back end system in tabular format having the functionality to export it in excel file. Writing this blog hoping, fellow consultants might find it useful.

The requirement can be achieved creating a ODATA service in SAP Gateway and using sap.m.Table and sap.ui.core.util.Export classes in front end (SAP WEB IDE or Eclipse).

Below example shows purchase order header details. Follow the below steps:-

Create Table using sap.m class, bind with ODATA service and Export data in CSV and XLS format.

Declare Table in the View: 

<Page title="XML" showHeader="false" floatingFooter="true">

<footer>

<Bar design="sap.m.BarDesign.Footer">

<contentRight>

<Button icon="sap-icon://excel-attachment" press="exportxls" />

</contentRight>

</Bar>

</footer>

<content>

<Table id="idtable" growing="true" growingThreshold="50">

<columns>

<Column width="8em">

<Text text="Purchasing Doc." />

</Column>

<Column>

<Text text="Purchasing Doc. Category" width="6em"/>

</Column>

<Column>

<Text text="Purchasing Doc. Type" width="6em"/>

</Column>

<Column>

<Text text="Status" />

</Column>

<Column>

<Text text="Created on" />

</Column>

<Column>

<Text text="Created by" />

</Column>

</columns>

</Table>

</content>

</Page> 

Create ODATA Model and Items Aggregation for Table in Controller

onInit : function() {

//* Create ODATA model

var url = "/sap/opu/odata/SAP/<service>/";

oModel = new sap.ui.model.odata.ODataModel(url, true);

//* Create Items Aggregation for Table

this.getView().byId("idtable").bindAggregation("items", "/<EntitySet>",

new sap.m.ColumnListItem({

cells : [

new sap.m.Text({

text: "{Ebeln}"

}),

new sap.m.Text({

text : "{Bstyp}"

}),

new sap.m.Text({

text : "{Bsart}"

}),

new sap.m.Text({

text : "{Statu}"

}),

new sap.m.Text({

text : "{Aedat}"

}),

new sap.m.Text({

text : "{Ernam}"

})

]

}));

//* Set ODATA Model to Table

this.getView().setModel(oModel);

this.getView().byId("idtable").setModel(oModel);

Export in CSV format

exportxls: sap.m.Table.prototype.exportData || function(oEvent) {

var oExport = new sap.ui.core.util.Export({

exportType : new sap.ui.core.util.ExportTypeCSV({

separatorChar : ";"

}),

models : this.getView().getModel(),

rows : {

path : "/<EntitySet>"

},

columns : [

{

name : "Purchasing Doc.",

template : {

content : "{Ebeln}"

}

}, {

name : "Purchasing Doc. Category",

template : {

content : "{Bstyp}"

}

}, {

name : "Purchasing Doc. Type",

template : {

content : "{Bsart}"

}

}, {

name : "Status",

template: {

content: "{Statu}"

}

}, {

name: "Created on",

template: {

content: "{Aedat}"

}

}, {

name: "Created by",

template: {

content: "{Ernam}"

}

}

]

});

//* download exported file

oExport.saveFile().always(function() {

this.destroy();

}); 

Export in XLS format

 exportxls: sap.m.Table.prototype.exportData || function(oEvent) {

       var oExport = new sap.ui.core.util.Export({

       exportType : new sap.ui.core.util.ExportTypeCSV({

       separatorChar: "\t",

       mimeType: "application/vnd.ms-excel",

       charset: "utf-8",

       fileExtension: "xls"

      }),

     models : this.getView().getModel(),

     rows : {

       path : "/<EntitySet>"

    },

   columns : [

   {

    name : "Purchasing Doc.",

    template : {

   content : "{Ebeln}"

  }

  }, {

   name : "Purchasing Doc. Category",

   template : {

   content : "{Bstyp}"

   }

  }, {

 name : "Purchasing Doc. Type",

 template : {

 content : "{Bsart}"

 }

 }, {

name : "Status",

template: {

content: "{Statu}"

}

}, {

name: "Created on",

template: {

content: "{Aedat}"

}

}, {

name: "Created by",

template: {

content: "{Ernam}"

}

}

]

});

//* download exported file

oExport.saveFile().always(function() {

this.destroy();

});

In this example the functionality is achieved using class sap.ui.core.util.Export. It can also be achieved using class sap.ui.export.Spreadsheet or by using jQuery.  For more details about the API’s check https://sapui5.hana.ondemand.com/#/api/sap.ui.core.util.Export.

Thanks,

Aurobindo Sarola