Export to Excel for Tree Structured Table

[Former Member]

Hello Community Members,

I am implementing Export to Excel functionality for one of the Fiori app which has tree structured data in tabular format (as shown below)

I tried implementing with ExportTypeCSV approach but it shows in linear format without tree structure. Below is the code.

exportToExcel: function(table, oModel)
{
        var cols = table.getColumns();      
        var items = table.getRows();

         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.getId();       
                headerColObj = sap.ui.getCore().byId(headerColId);      
                headerColVal = headerColObj.getLabel().getText();

                if(headerColObj.getVisible()){      
                    json={name: headerColVal};      
                    colArray.push(json);

                }       
            }       
            itemsArray.push(colArray);

          //push table cell values to array

          for (var 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.HBox){                                  
                            cellVal = cellObj.getItems()[0].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({

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

                    separatorChar: ","

                }),                     

                models: oModel,

                rows: {     
                    path: "/"       
                },

                // column definitions with column name and binding info for the content     
                columns: [itemsArray]       
            });

          oExport.saveFile().always(function() {        
                this.destroy();     
            });
}

Any pointers for achieving tree type formatting in excel will be very helpful.

Thanks,

Saurabh

[Sharathmg]

Did you try the preparation of excel using the JSON format as described in the link below:

https://sapui5.hana.ondemand.com/#/sample/sap.m.sample.TableExport/code/Table.controller.js