Exporting to Excel from sap.m.Table via csv
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!!!!!!!!!
Nice Job. Consider changing the title to CSV instead of Excel. It is misleading.
Thanks,
Bhaskar
I have changed it.
Hi Ranjit,
Thanks for the document.
I am getting data in CSV format. Is there a way to get in xlsx format from CSV??
And the resulting CSV file row width is not automated as text size.
Can you have any suggetions to make it as automated.
Hi Surendra,
I don't know whether it is possible or not. I will try surely 🙂
Do you have any suggestions for row width in excel?
Hi @surendra Pamidi
Is there any build in properties or methods to set color or width to downloaded csv file columns.
Or is there in way we can achieve this?
Hi Ranjit,
For me it is not working in IE9.
Is there any other additional solution for IE9?
Hi,
Could you please elaborate on "it is not working in IE9"?
This might be due to incomplete Data URI support on that browser (http://caniuse.com/#feat=datauri). Unfortunately
Check this for hints: How to write a file / give it to the user
Best regards,
Guillaume
Hi Ranjit,
Thank you for this blog. its really helpful.
Can you have any suggestion to make the cells automated. any autoOptimeze cell property, or any applying css? any clue will be appreciated.
Thank you
Nice doc.
I would suggest that you do not do too many string concatenation for performance reason. Optimizing JavaScript code - Make the Web Faster &mdash; Google Developers.. You can map your array to an another array and then do join like this
var csv = arrData.map(function(d) { return '"' + d.name.replace(/"/g, '""') + '", "' + d.short.replace(/"/g, '""') + '"'; }).join('\r\n') + '\r\n';and you have to escape double quote (if any) in your data.
-D
Nice job.
Its working for me 🙂
Hi Ranjit,
I got the excel sheet. Is there any way to merge the cells in excel sheet?
Hi Ranjit,
How to insert new column between two already existing column and how to remove empty row between column heading and first value in column one where my first column value starts at row four so how to remove second and third row and make my fourth row value in second row.
Thanks in advance.
Remove /n from the function. Each /n denotes a line
Hi Ranjit,
Thanks for your suggestion and it is working fine.i have one more doubt have to add more no of column like Report, Country,Region ,City .how can i add this like passing parameters .
Thanks in advance
JSONToCSVConvertor: function(JSONData, ColumnName, ShowLabel) { // If JSONData is not an object then JSON.parse will parse the JSON // string in an Object var arrData = typeof JSONData.Entities != 'object' ? JSON.parse(JSONData.Entities) : JSONData.Entities; var CSV = ''; // Set Report title in first row or line CSV += ReportTitle + '\r'; if (ShowLabel) { var row = ""; row = row.slice(0, -1); CSV += row + '\r\n'; } //loop is to extract each row var row = ""; for (var i = 0; i < ColumnName.length; i++) { row=row+'"'+ColumnName[i]+'",'; } row.slice(0, row.length - 1); CSV += row + '\r\n'; row = '"","","","","","","",'; row.slice(0, row.length - 1); CSV += row + '\r\n'; for (var i = 0; i < arrData.length; i++) { row = ""; row += '"' + arrData[i].EntityName + '","' + arrData[i].Country + '","' + arrData[i].Cluster_District + '","' + arrData[i].Active_Inactive + '","' + arrData[i].AAASatus + '","' + arrData[i].Certifications + '","' + arrData[i].PrimaryContact + '",'; 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 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); },
replace the new function wirh the old one and pass the column names a as an array as shown
Thanks
Hi Rao,
Nice blog.
when I use it as xls , both data displaying in one column with ,.
how can I get one data in one column.
Hi Ranjit,
Nice work,But
Could you Please let me know if there is a way to format the generated file in anyway in SAPUI5. I have found few libraries in nodes.js. As require function cannot be used when application is running on netweaver system so i am not able to use the nodejs libraries for excel formatting.
A little help would be appreciated
Thanks and Regards,
Prabuddha
hi if I am using that jsondata converter function its given error unexpected token illegal
Good Job, it work nice for me but is there a way to give every single column from my ui5 table a single column in csv?
When i´m exporting my data, the whole row is in one cell.
Could you please help me in this personally.
Good job! But for who needs, doesn’t work with IE.
Please check this great solution https://sapui5.hana.ondemand.com/sdk/#/sample/sap.ui.export.sample.json/preview
Hi,
this solution is not working in IE, any workaround for IE
Use below code in case you it to be working in IE, Chrome and Edge
var blob = new Blob([CSV], {
type: "text/xls;charset=utf-8,"
});
if (window.navigator.msSaveBlob) { // IE
window.navigator.msSaveOrOpenBlob(blob, fileName);
} else { //Chrome
// Initialize file format you want csv or xls
var uri = "data:text/xls;charset=utf-8," + escape(CSV);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = fileName;
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
CSV is nothing but the data
I am able to get xls file without any issues, However I need the file to be in XLSX format.
Anyone know how to get XLSX format in Chrome and IE browsers
I am able to get xls file without any issues, However I need the file to be in XLSX format.
Anyone know how to get XLSX format in Chrome and IE browsers
Hi,
I am getting the data in correct format in csv file but in xls file all data is coming in one cell.
Can anyone tell me how to correct it?