Skip to Content
0

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

Apr 25, 2017 at 01:38 PM

1.9k

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

VIPLOVE KHUSHALANI Apr 26, 2017 at 07:48 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Viplove,

Thanks for reply.

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

  • Using this example they can export data as .csv file format but i need to download .xlsx file.
  • It seems data is directly download from service. But I need to download from UI5 Table(sap.ui.table.Table)

Can you please share me any example with code.

Regards

Vijay

0
avatar image
Former Member May 03, 2017 at 02:12 AM
0

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


wrong-op.jpg (44.1 kB)
need-op.jpg (70.5 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member May 03, 2017 at 07:43 AM
0

Hi Vijay,

Check this link.

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

Maybe you need to do something similar for the seperators.

Regards,

Melwyn

Share
10 |10000 characters needed characters left characters exceeded