Technical Articles
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
Good stuff
Thanks Aurobindo, for the good information.
Can you please let me know, this above mentioned stuff holds good for which SAP version. and also can you please let me know is this applicable for SAP BYD also.
Thanks for reading the blog.
I tried the above code in SAP UI5 version 1.38.37 but it is applicable for higher versions as well. for time being not sure about SAP By Design, will check and get back to you.
Dear Aurobindo Sarola ,
I have followed the same, and we are able to get expected result. But it throws a warning message as
Can some one help on this.
regards,
Devender