Skip to Content
Author's profile photo Santhosh Gowda

Export To Excel customization in UI5

Dear All,

     SAP Explored Provided to download Table data in csv view without format.But, most of the time we want to download as in format. here is the code, you can download the data in excel with format.


     For every table why should we write download excel method in controller. We will write below controller method as reusable by calling the method in controller to pass just tableId and the table binded reference. 



call the method in any controller method like below,


    var tableId = this.getView().byId(“idTable”);

        var oModel = table.getModel(“odata”);//odata is the model, which is binding to the table

     exportToExcel(tableId, oModel);


Add here you can export ObjectIdentifier, ObjectNumber, Link,… Controllers also.

In Xml View,

<Table id=”idTable”  items=”{odata>/modelData}” >

            <headerToolbar>

                <Toolbar>

                    <ToolbarSpacer></ToolbarSpacer>

                    <Button icon=”sap-icon://download” tooltip=”DownLoad XL” press=”exportToExcel” />

                </Toolbar>

            </headerToolbar>

      

            <columns>

                <Column minScreenWidth=”Tablet” demandPopin=”true”>

                    <Label text=”Label1″ />

                </Column>

                …..

    </columns>

            <items>

                <ColumnListItem id=”itemsID” vAlign=”Middle”>

                    <cells>

                        <Text text=”{odata>Attribute1}” />

                        <ObjectIdentifier  title=”{odata>Attribute2}” text=”{odata>Attribute13}”/>

                        <ObjectNumber number=”{path:’odata>Attribute14′, formatter: ‘your_Formatter’}” unit=”{odata>Attribute5}” /> 

                    </cells>

                </ColumnListItem>

            </items>

        </Table>

  

In controller, 

jQuery.sap.require(“sap.ui.core.util.Export”);

jQuery.sap.require(“sap.ui.core.util.ExportTypeCSV”);

//Add these 2 require.

exportToExcel: function(tableId, oModel)

{

    var cols = table.getColumns();

        var items = table.getItems();

         var cellId = null;

         var cellObj = null;

         var cellVal = null;

         var headerColId = null;

         var headerColObj = null;

         var headerColVal = null;

         var column = null;

         var json = {}; var colArray = []; var itemsArray= [];

         //push header column names to array

         for(var j=0; j<cols.length;j++){

                column = “”;

                column = cols[j];

                headerColId = column.getAggregation(“header”).getId();

                headerColObj = sap.ui.getCore().byId(headerColId);

                headerColVal = headerColObj.getText();

                if(headerColObj.getVisible()){

                    json={name: headerColVal};

                    colArray.push(json);

                }

            }

            itemsArray.push(colArray);

          //push table cell values to array

          for (i = 0; i < items.length; i++) {

              colArray = [];

              cellId = “”;   cellObj = “”;  cellVal = “”;

              headerColId = null; headerColObj = null; headerColVal = null;

              var item = items[i];

                for(var j=0; j<cols.length;j++){

                    cellId = item.getAggregation(“cells”)[j].getId();

                    cellObj = sap.ui.getCore().byId(cellId);

                    if(cellObj.getVisible()){

                        if(cellObj instanceof sap.m.Text ||cellObj instanceof sap.m.Label ||cellObj instanceof sap.m.Link) cellVal = cellObj.getText();

                        if(cellObj instanceof sap.m.ObjectNumber){

                            var k = cellObj.getUnit();

                            cellVal = cellObj.getNumber()+” “+k;

                        }

                        if(cellObj instanceof sap.m.ObjectIdentifier){

                            var objectIdentifierVal = “”;

                            if(cellObj.getTitle() != undefined && cellObj.getTitle() != “” && cellObj.getTitle() != null )

                                objectIdentifierVal = cellObj.getTitle();

                            if(cellObj.getText() != undefined && cellObj.getText() != “” && cellObj.getText() != null )

                                objectIdentifierVal = objectIdentifierVal+” “+cellObj.getText();

                      

                            cellVal = objectIdentifierVal;

                        }

                        if(cellObj instanceof sap.ui.core.Icon){

                            if(cellObj.getTooltip() != undefined && cellObj.getTooltip() != “” && cellObj.getTooltip() != null )

                            cellVal = cellObj.getTooltip();

                        }

                        if(j==0){

                            json={ name:  “\r”+cellVal};

                        }

                        else

                        {

                            json={ name:  cellVal};

                        }

                        colArray.push(json);

                    }

                }

                itemsArray.push(colArray);

          

          

            }

         //export json array to csv file

          var oExport = new sap.ui.core.util.Export({

                // Type that will be used to generate the content. Own ExportType’s can be created to support other formats

                exportType: new sap.ui.core.util.ExportTypeCSV({

                    separatorChar: “,”

                }),

                // Pass in the model created above

                models: oModel,

                // binding information for the rows aggregation

                rows: {

                    path: “/”

                },

                // column definitions with column name and binding info for the content

                columns: [itemsArray]

            });

          oExport.saveFile().always(function() {

                this.destroy();

            });

}

Assigned tags

      23 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dennis Seah
      Dennis Seah

      Hi

      I have seen many posting on this topic. I am wondering why do not get the value from the model instead we go through cell by cell to get the values.

      Thanks

      -D

      Author's profile photo Santhosh Gowda
      Santhosh Gowda
      Blog Post Author

      Dennis,

      I agree with you. I blogged it 3 months back.thanks for the suggestion. Definitely i will update it.

      Thanks,

      Santhosh Gowda.

      Author's profile photo Srinivasan S
      Srinivasan S

      Hi Dennis,

      I have a similar requirement to download the table. Can you please tell me how to download it from oModel.

      It would help a lot.

      Thanks,

      Srinivasan

      Author's profile photo Sashko Janev
      Sashko Janev

      The title of this blog says "Export to Excel"... All I could see here is how to export the model to .CSV.

      Do you know any way to make it in excel (.XLS) format?

      I tried like this:

      exportType: new  sap.ui.core.util.ExportType({

        fileExtension: "xls"

        }),


      But it only changes the type and not getting the model..

      This is my full code:

      onTableExport : sap.m.Table.prototype.exportData || function(oEvent) {

        jQuery.sap.require("sap.ui.core.util.Export"); 

        jQuery.sap.require("sap.ui.core.util.ExportTypeCSV"); 

        var oExport = new sap.ui.core.util.Export({

        // Type that will be used to generate the content. Own ExportType's

        // can be created to support other formats

        exportType: new  sap.ui.core.util.ExportType({

        fileExtension: "xls"

        }),

        // Pass in the model created above

        models : this.getView().getModel(),

        // binding information for the rows aggregation

        rows : {

        path : "/results"

        },

        // column definitions with column name and binding info for the

        // content

        columns : [ {

        name : "QWRNUM",

        template : {

        content : "{QWRNUM}"

        }

        }, {

        name : "QMNUM",

        template : {

        content : "{QMNUM}"

        }

        }, {

        name : "Contact Person",

        template : {

        content : "{ContactPerson}"

        }

        }, {

        name : "Article Number",

        template : {

        content : "{Matnr}"

        }

        }, {

        name : "Article Descr.",

        template : {

        content : "{Maktx}"

        }

        }, {

        name : "Equnr",

        template : {

        content : "{Equnr}"

        }

        }, {

        name : "Erdat",

        template : {

        content : "{Erdat}"

        }

        }, {

        name : "Date",

        template : {

        content : "{Qmdat}"

        }

        } ]

        });

        // download exported file

        oExport.saveFile().always(function() {

        this.destroy();

        });

        }

      It's showing me a blank XLS. Any ideas why?

      Thanks.

      Author's profile photo Former Member
      Former Member

      Hi Sashko,

      You can use below code to export file in xls format instead of csv file.

       

       exportType: new  sap.ui.core.util.ExportTypeCSV({

       separatorChar: "\t" ,

       mimeType: "application/vnd.ms-excel" ,

       charset: "utf-8" ,

       fileExtension: "xls"

        }),

       

      Thanks & Regards,

      Dashrath Singh

      Author's profile photo Ranjani Sekar
      Ranjani Sekar

      Hi Dashrath,

      i tried your code and its working perfect.Thanks for sharing.

       

      Thank you.

      Ranjani Sekar

       

      Author's profile photo Sagar Patil
      Sagar Patil

      Hi Santosh,

      Thanks for valuable information about exporting the table data into excel format. This code is working in Desktop/Laptop. But in iPad, on tap of Export to excel Button its not working.Even I have disabled the "Block Popup option" in iPad.Is There anything needed to add to the code to make it work in iPad..??

      Also I was looking into the export to excel example from Explored examples(below is the link)

      SAPUI5 Explored

      it works fine. In ipad it opens a table in new Tab in Browser. But If the table ontains the Links, it is not exporting the column data which contains Link.

      Please Help.

      Thanks and Regards,

      Sagar M Patil

      Author's profile photo Sagar Patil
      Sagar Patil

      Hi Santosh,

      I think the issue is with the Table Data. I f try to export the Table data which is in Arabic, Its not working and If I try to export the table data which is in English, Its Working. So how to handle, if the data is in Different Languages or Special characters like '######'

      Thanks and Regards,

      Sagar

      Author's profile photo Former Member
      Former Member

      Hi Santhosh,

      I am trying to implement this exporting functionality But getting a blank file out.

      my scenario is like this:

      Based on a search, table data is shown.

      And export this data out

      I already have omodel.read fucntion where I am generating the table in runtime based on the table structure .

      Also the model for this table is created as JSONModel.

      I am not able to read this model in the exporttoCSV fucntion.

      Can you please guide?

      Below is my code:

      Search : function(evt) {

        // check mandatory fields

        var BussA = this.getView().byId(

        "BussA").getValue();

        // Business Area is mandatory field

        if (BussA == "") {

        MessageToast

        .show("Please Enter Business Area");

        return;

        }

        var ProcA = this.getView().byId(

        "ProcA").getValue();

        // Process Area is mandatory field

        if (ProcA == "") {

        MessageToast

        .show("Please Enter Process Area");

        return;

        }

        // get user input value

        var aFilter = [];

        var period = this.getView().byId(

        "datePicker2").getValue();

        // period is mandatory field

        if (period == "") {

        MessageToast

        .show("Please Enter the Period and Fiscal Year");

        return;

        }

        var split = period.split(".");

        var splitPer = split[0];

        var fPer = splitPer

        .substring(splitPer.length - 2);

        var fYear = split[1];

        var that = this;

        var oModel = new ODataModel(

        '../Services/fileupload.xsodata/');

        var f2 = new sap.ui.model.Filter(

        {

        path : "F_PER",

        operator : sap.ui.model.FilterOperator.EQ,

        value1 : fPer,

        value2 : ""

        });

        aFilter.push(f2);

        var f3 = new sap.ui.model.Filter(

        {

        path : "F_YEAR",

        operator : sap.ui.model.FilterOperator.EQ,

        value1 : fYear,

        value2 : ""

        });

        aFilter.push(f3);

        var oTable = this.getView().byId(

        "Table");

        oTable

        .setSelectionMode('None');

        oTable.setVisible(true);

        oTable.removeAllColumns();

        var name = this.getView().byId(

        "ProcA").getValue();

        var oData = null;

        var myArray = new Array();

        if (name == null) {

        return;

        }

        name = "/" + name;

        oModel.read(

        name,

        {

        async : false,

        filters : aFilter,

        success : function(

        data) {

        oData = data.results;

        var columnName = oData[0];

        for ( var item in columnName) {

        if (item != '__metadata') {

        myArray

        .push(item);

        }

        }

        for (var i = 0; i < myArray.length; i++) {

        // define

        // the

        // Table

        // columns

        var oControl = new sap.m.Text(

        {

        text : "{"

        + myArray[i]

        + "}"

        }); // short

        // binding

        // notation

        oTable

        .addColumn(new sap.ui.table.Column(

        {

        label : new sap.m.Label(

        {

        text : myArray[i]

        }),

        template : oControl,

        // width:

        // "11rem"

        }));

        }

        var oModel = new JSONModel(

        data);

        oTable

        .setModel(oModel);

        //sapui.getCore().setModel(oModel);

        oTable

        .bindRows("/results");

        if (oData[0] == null) {

        MessageToast

        .show("No Data Found");

        oTable

        .setVisible(false);

        }

        // show

        // footer

        that

        .getView()

        .byId(

        "Page")

        .setShowFooter(

        true);

        },

        error : function() {

        MessageToast

        .show("OData Error");

        oTable

        .setVisible(false);

        }

        });

        },

        onDataExport : sap.m.Table.prototype.exportData || function(oEvent) {

        

        var oTable = this.getView().byId(

        "Table");

        //oTable.exportData.saveFile();

        var oExport = new sap.ui.core.util.Export({

        

        // Type that will be used to generate the content. Own ExportType's can be created to support other formats

        exportType : new sap.ui.core.util.ExportTypeCSV({

        separatorChar : ";"

        }),

        

        // Pass in the model created above

        models : oTable.getModel(),

        //models : this.getView().getModel(),

        // binding information for the rows aggregation

        rows : {

        path : "/results"

        }

        

        // column definitions with column name and binding info for the content

        });

        oExport.saveFile().always(function() {

                       this.destroy();

                   });

        /* catch(oError)

        {

        MessageToast.show("Error when downloading data. Browser might not be supported!\n\n" + oError);

        oExport.destroy();

        };

        */

        // download exported file

        },

      Author's profile photo Former Member
      Former Member

       

      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 tisha dharod
      tisha dharod

      I tried the above code but I am getting all the output in same line for excel 2016 . Can anybody help me resolve this issue

      Author's profile photo Sagar Patil
      Sagar Patil

      Hi Tisha,

      I too had a same issue. I think the issue is with the sapui5 library version. Point it to 1.38.5 , it works.

       

      Thanks and Regards,

      Sagar M Patil

      Author's profile photo Ankush Datey
      Ankush Datey

      Hi Sagar,

      We have Upgraded our SAPUI5 library version from 1.38 to 1.48, after that i am getting this issue.

      Any solution will great help

      Author's profile photo A Green
      A Green

      Hi Ankush,

       

      I found the issue is due to some quotes being inserted on the last column/ first columns of the csv eg.

      Transaction ID,Description,Status,Priority,Transaction Type,"

      12345",THE SAMPLE USER DANNIS,Open,5,Interaction Record

       

      If you delete those it will work in excel. I am investigating how to make it not output this way.

       

      Did you find a solution to this problem? I will update when/if I find a solution.

      Author's profile photo Former Member
      Former Member

       

      good afternoon to my brings the excel with the data of the header but not the data 
      that must have the header.
      
      you know why.
      
      Thanks.
      
      
      Author's profile photo Ankush Datey
      Ankush Datey

      Hi Tisha,

       

      I am also getting the same output while downloading.

      Please Let us if you will get any solution on this 🙂

       

      Author's profile photo Former Member
      Former Member

      Hi Ankush,

      We faced the same issue and we managed to solve it by removing parenthesis from statement: columns: [itemsArray]

      The solution that worked:     columns: itemsArray

      Author's profile photo Nagaraju Kasarla
      Nagaraju Kasarla

      Do we know how get file in xlsx format?

      Author's profile photo Hemilkumar Prajapati
      Hemilkumar Prajapati

      hi Santhosh Gowda

      Do you know how to export to excel with multi header in grid table.

      Thanks,

      Author's profile photo Khamar Shanveel
      Khamar Shanveel

      Hi,

      I have a similar requirement. Did you find the solution for this?

      Author's profile photo devender Naik
      devender Naik

      Dear,

      We have followed similar instructions, we are getting a warning as

      need help.

       

      Author's profile photo Yesica La Torre - YLDY
      Yesica La Torre - YLDY

      Hi comunity! I have to download an excel from an Smart table with a custom column that it's a multiinput. I used the export to excel functionality from Smart Table, and works all except for that field... Is there a way to include the navigation in an easy way? Plus, I have to create a new row for each value that I have in the Multiinput.

       

      thank you all!

      Author's profile photo Svea Becker
      Svea Becker

      Hi Yesica La Torre - YLDY I recommend asking your question in our question and answer section https://answers.sap.com/index.html so that you can reach a broader range of experts. Please add as much details as you can. The more details you provide, the more likely it is that members will be able to assist you.

      This tutorial might help you to find out more about asking a question in SAP Community: https://community.sap.com/resources/questions-and-answers

      Regards, Svea

      SAP Community Moderator