Skip to Content
Author's profile photo Ranjit Rao

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:

/wp-content/uploads/2014/11/chrome_589961.jpg

Hmmmm, Not Bad!!!!!!!!!

Assigned Tags

      31 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Bhaskar B
      Bhaskar B

      Nice Job. Consider changing the title to CSV instead of Excel. It is misleading.

      Thanks,

      Bhaskar

      Author's profile photo Ranjit Rao
      Ranjit Rao
      Blog Post Author

      I have changed it.

      Author's profile photo S P
      S P

      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.

      Author's profile photo Ranjit Rao
      Ranjit Rao
      Blog Post Author

      Hi Surendra,

      I don't know whether it is possible or not. I will try surely 🙂

      Author's profile photo S P
      S P

      Do you have any suggestions for row width in excel?

      Author's profile photo Former Member
      Former Member

      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?

       

      Author's profile photo S P
      S P

      Hi Ranjit,

      For me it is not working in IE9.

      Is there any other additional solution for IE9?

      Author's profile photo Guillaume GARCIA
      Guillaume GARCIA

      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

      Author's profile photo Gyan Panigrahi
      Gyan Panigrahi

      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

      Author's profile photo Dennis Seah
      Dennis Seah

      Nice doc.

      I would suggest that you do not do too many string concatenation for performance reason. Optimizing JavaScript code - Make the Web Faster &amp;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

      Author's profile photo Former Member
      Former Member

      Nice job.

      Its working for me 🙂

      Author's profile photo Former Member
      Former Member

      Hi Ranjit,


      I got the excel sheet. Is there any way to merge the cells in excel sheet?



      Author's profile photo Gnanamanikandan Rajagopal
      Gnanamanikandan Rajagopal

      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.

      Author's profile photo Ranjit Rao
      Ranjit Rao
      Blog Post Author

      Remove /n from the function. Each /n denotes a line

      Author's profile photo Gnanamanikandan Rajagopal
      Gnanamanikandan Rajagopal

      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

      Author's profile photo Ranjit Rao
      Ranjit Rao
      Blog Post Author

      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); },

      Author's profile photo Ranjit Rao
      Ranjit Rao
      Blog Post Author

      replace the new function wirh the old one and pass the column names a as an array as shown

      Author's profile photo Former Member
      Former Member

      Thanks

      Author's profile photo Sai Battula
      Sai Battula

      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.

      Author's profile photo Prabuddha Raj
      Prabuddha Raj

      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

      Author's profile photo Former Member
      Former Member

      hi if I am using that jsondata converter function its given error unexpected token illegal

      Author's profile photo Tim Schlömp
      Tim Schlömp

      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.

      Author's profile photo Former Member
      Former Member

      Could  you please help me in this personally.

      Author's profile photo Former Member
      Former Member

      Good job! But for who needs, doesn’t work with IE.

      Author's profile photo DANIEL JOSE CARPIO CONTRERAS
      DANIEL JOSE CARPIO CONTRERAS

      Please check this great solution https://sapui5.hana.ondemand.com/sdk/#/sample/sap.ui.export.sample.json/preview

      Author's profile photo Nagaraju Kasarla
      Nagaraju Kasarla

      Hi,

      this solution is not working in IE, any workaround for IE

      Author's profile photo Nagaraju Kasarla
      Nagaraju Kasarla

      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);
      }

      Author's profile photo Nagaraju Kasarla
      Nagaraju Kasarla

      CSV is nothing but the data

      Author's profile photo Nagaraju Kasarla
      Nagaraju Kasarla

      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

      Author's profile photo Nagaraju Kasarla
      Nagaraju Kasarla

      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

      Author's profile photo Siddharth Gupta
      Siddharth Gupta

      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?