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

            });

}

To report this post you need to login first.

9 Comments

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

  1. 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

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

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

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

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

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

    (0) 
  5. Deep PIYUSH DESAI

    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

      },

    (0) 
  6. sharan patil

     

    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) 

Leave a Reply