Skip to Content

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!!!!!!!!!

To report this post you need to login first.

23 Comments

You must be Logged on to comment or reply to a post.

      1. Surendra Pamidi

        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.

        (0) 
            1. sharan patil

              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?

               

              (0) 
          1. 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

            (0) 
  1. 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

    (0) 
  2. 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.

    (0) 
      1. 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

        (0) 
        1. Ranjit Rao 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); },

          (0) 
  3. 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

    (0) 
  4. 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.

    (0) 

Leave a Reply