Export To Excel customization in UI5

[santhu_gowdaz]

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

            });

}