Skip to Content

Export data into excel file using sap.m.table in SAP UI5

Hello Friends,

How to download data into excel file from sap.m.table. I mean i need to download from UI5 side. whatever data is available in table that data i need to download

for example:

  • view

<mvc:View controllerName="sapui5_databinding_list_xml.tableView"
xmlns="sap.ui.table" xmlns:mvc="sap.ui.core.mvc" xmlns:u="sap.ui.unified"
xmlns:c="sap.ui.core" xmlns:m="sap.m" xmlns:la="sap.ui.table">
<m:Page showHeader="false" enableScrolling="false">
<m:content>
<la:Table id="lineItemsList2" rows="{ojsonModel>/tableDetails}" title="Display Data into the Table using JSON Model"
selectionMode="MultiToggle" visibleRowCount="4" enableGrouping="true" threshold="2">
<la:columns>
<la:Column width="3rem">
<m:Label text="Delv.Qty" />
<la:template>
<m:Input id="delQtyId" value="{ojsonModel>delvqty}" liveChange="onChangeDelQty"/>
</la:template>
</la:Column>
<la:Column width="3rem">
<m:Label text="Picked Qty" />
<la:template>
<m:Input id="pickedqtyId" value="{ojsonModel>pickedqty}" liveChange="onChangePickedQty"/>
</la:template>
</la:Column>
<la:Column width="3rem">
<m:Label text="Variance" />
<la:template>
<m:Text id="varianceId" text="{ojsonModel>Variance}" />
</la:template>
</la:Column>

<la:Column width="3rem">
<m:Label text="Total Picked Wt" />
<la:template>
<m:Text text="{ojsonModel>totalpickedwt}" />
</la:template>
</la:Column>

<la:Column width="3rem">
<m:Label text="Unit Wt." />
<la:template>
<m:Text text="{ojsonModel>Unitwt}" />
</la:template>
</la:Column>

<la:Column width="3rem">
<m:Label text="Total Picked Volume" />
<la:template>
<m:Text text="{ojsonModel>totalpickedvolume}" />
</la:template>
</la:Column>

<la:Column width="3rem">
<m:Label text="UnitVolume" />
<la:template>
<m:Text text="{ojsonModel>UnitVolume}" />
</la:template>
</la:Column>


</la:columns>

</la:Table>

<m:Button text="Back to Main View" type="Accept" press="navtoMain"/>
<m:Button text="Export Excel" type="Accept" press="onExportExcel"/>

</m:content>

</m:Page>
</mvc:View>

  • controller

onInit: function() {

var ojsonModel = new sap.ui.model.json.JSONModel("model/tableData.json");
sap.ui.getCore().setModel(ojsonModel,"ojsonModel");
console.log(ojsonModel);


// sap.ui.getCore().setModel(ojsonModel,"empInfoAlias"); // with alias name


},
navtoMain:function()
{
eventBus.publish("nvChannel1", "nvEvent1",{gotoPage1:"iddataBinding",effect:"flip"});
},
onChangeDelQty:function(oEvent)
{
debugger;
/*var deliverQtyValID = oEvent.getSource().sId;
var deliverQtyVal = this.getView().byId(deliverQtyValID).getValue();
var length = oEvent.getSource().oPropagatedProperties.oBindingContexts.ojsonModel.oModel.oData.tableDetails.length;

var pickedQtyVal = this.getView().byId("idTableItem1--pickedqtyId-col1-row0").getValue();
var finalVarience = deliverQtyVal-pickedQtyVal
this.getView().byId("idTableItem1--varianceId-col2-row0").setText(finalVarience);*/

var deliverQtyVal = oEvent.getSource().getValue();
var pickedQtyVal= oEvent.getSource().getParent().mAggregations.cells[1].getValue();
var finalVarience = deliverQtyVal-pickedQtyVal;
oEvent.getSource().getParent().mAggregations.cells[2].setText(finalVarience);


},
onChangePickedQty:function(oEvent)
{
debugger
var pickedQtyVal = oEvent.getSource().getValue();
var deliverQtyVal= oEvent.getSource().getParent().mAggregations.cells[0].getValue();
var finalVarience = deliverQtyVal-pickedQtyVal;
oEvent.getSource().getParent().mAggregations.cells[2].setText(finalVarience);

//Total Picked Wt
var totalUnitwt = oEvent.getSource().getParent().mAggregations.cells[4].getText();
var totalPickedwt = pickedQtyVal*totalUnitwt;
oEvent.getSource().getParent().mAggregations.cells[3].setText(totalPickedwt);

//Total Picked Volume
var totalUnitVol = oEvent.getSource().getParent().mAggregations.cells[6].getText();
var totalPickedVolu = pickedQtyVal*totalUnitVol;
oEvent.getSource().getParent().mAggregations.cells[5].setText(totalPickedVolu);
}

,
onExportExcel:function()
{

}

  • JSON File

{
"tableDetails":
[
{
"delvqty":"10",
"pickedqty": "5",
"Variance":"5",
"totalpickedwt":"55",
"Unitwt":"11",
"totalpickedvolume":"500",
"UnitVolume":"100"
}, {
"delvqty":"15",
"pickedqty": "3",
"Variance":"12",
"totalpickedwt":"12",
"Unitwt":"4",
"totalpickedvolume":"600",
"UnitVolume":"200"
}

]
}

How to download data into excel ?

Regards

Vijay

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Apr 26, 2017 at 07:48 AM

    Hi Vijay ,

    There might be two ways one using the javascript and one using sap library classes

    1. SAP lib classes : For this check out this example from explorer (download excel from the binding data) - https://sapui5.hana.ondemand.com/explored.html#/sample/sap.m.sample.TableExport/preview

    2. javascript : As sapui5 table is rendered as a HTML table , so you just need to get the innerHTML of that HTML table and follow the steps in the answer of the question in this link (download rendered htm table not the binding data - so if data does not render on the view than export excel will be empty) - http://stackoverflow.com/questions/17142427/javascript-to-export-html-table-to-excel

    Thanks

    Viplove

    Add comment
    10|10000 characters needed characters exceeded

  • May 03, 2017 at 02:12 AM

    Hello Friends,

    As per below code i have to download excel from ui screen. But its not in proper way. Can you please help me

    View

    <m:Button text="Export Excel" type="Accept" press="onExportExcel"/>

    Controller

    onExportExcel:function()
    {
    debugger;

    var data= sap.ui.getCore().getModel("ojsonModel").getData();

    this.JSONToExcelConvertor(data,"Report", true);

    },
    JSONToExcelConvertor:function(JSONData, ReportTitle, ShowLabel)
    {
    debugger;

    // If JSONData is not an object then JSON.parse will parse the JSON
    // string in an Object

    var arrData = typeof JSONData.tableDetails!= 'object' ?JSON.parse(JSONData.tableDetails) : JSONData.tableDetails;
    var CSV = "";
    //CSV+= ReportTitle + '\r\n\n';

    // Set Report title in first row or line
    if(ShowLabel){
    var row = "";
    row= row.slice(0, -1);
    // CSV+= row + '\r\n';
    }
    /*row+="'" + this.getView().byId("delvQtyId").getText() + "'"+this.getView().byId("delvQtyId").getText()+"'"+this.getView().byId("delvQtyId").getText() +"'"+this.getView().byId("delvQtyId").getText() +"'"+this.getView().byId("delvQtyId").getText() +"'"
    +this.getView().byId("delvQtyId").getText() +"'"+this.getView().byId("delvQtyId").getText() +"'";*/
    row += '"' + this.getView().byId("delvQtyId").getText() + '",';
    row += '"' + this.getView().byId("pickQtyId").getText() + '",';
    row += '"' + this.getView().byId("lvarianceId").getText() + '",';
    row += '"' + this.getView().byId("ltotalPickedWtId").getText() + '",';
    row += '"' + this.getView().byId("unitWtId").getText() + '",';
    row += '"' + this.getView().byId("totalPickedVoluId").getText() + '",';
    row += '"' + this.getView().byId("unitVoluId").getText() + '",';
    CSV+= row + '\r\n';
    //loop is to extract each row
    for (var i = 0; i <arrData.length; i++)
    {
    var row = "";
    for (var index in arrData[i]) {
    row += '"' + arrData[i][index] + '",';
    }

    /*row+="'" + arrData[i].delvqty + "'"+arrData[i].pickedqty +"'"+arrData[i].Variance +"'"+arrData[i].totalpickedwt +"'"+arrData[i].Unitwt +"'"
    +arrData[i].totalpickedvolume +"'"+arrData[i].UnitVolume +"'";*/
    row.slice(1,row.length);
    CSV+= row + '\r\n';

    }
    var endrow = "";
    endrow += '"",';
    endrow += '"",';
    endrow += '"",';
    endrow += '"Total:' + this.getView().byId("totalPickedWtId").getText() + '",';
    endrow += '"",';
    endrow += '"Total:' + this.getView().byId("totalPickedVolId").getText() + '",';
    endrow += '"",';
    CSV+= endrow + '\r\n';
    if (CSV == "") {
    alert("Invalid data");
    return;
    }
    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);
    var uri = 'data:application/vnd.ms-excel:base64,'+encodeURIComponent(CSV);
    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 + ".xls";

    // this part will append the anchor tag and remove it after automatic

    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);

    }

    Present Output using above code:

    I need Output as per below screen

    Regards

    Vijay

    Add comment
    10|10000 characters needed characters exceeded

  • May 03, 2017 at 07:43 AM

    Hi Vijay,

    Check this link.

    https://archive.sap.com/discussions/thread/3959366

    Maybe you need to do something similar for the seperators.

    Regards,

    Melwyn

    Add comment
    10|10000 characters needed characters exceeded