Excel export from HANA table

[Former Member]

Hi Experts,

Could you please tell me the steps how to Export a HANA Table into Excel/csv sheet using json parsing into UI5 dashboard?

Thanks & Regards,

Sandip Das Adhikari

Message was edited by: Sandip Das Adhikari

[Former Member · Accepted solution]

Hi,

U can follow these steps,

[Former Member · Accepted solution]

Hi Pradeep,

Yes, I have seen that...My requirement is little bid different. From the backend HANA table using json parsing, I am showing graph into UI5 dashborad. Now I need to export that backend table into excel/csv format. I am showing graph into UI5 dashboard parsing json url. How to parse it for generating excel file. Please help me out. If u interested then I can share the screenshorts and source code..

[Former Member · Accepted solution]

Hi,

To my knowledge there is no direct method to export HANA to excel using ui5. We need to do 2 step format.

Step 1. Bring data into ui5.

Step 2. Export them to excel.

If you dont want to show the table, you can keep them hidden or at run time you can trigger get the value and export as excel

[Former Member · Accepted solution]

Hi Pradeep,

Thanks for telling me the steps how to export Excel.

For bringing data into UI5, I need to bind it using json. Then I have to define a table for storing them into UI5 dashboard and there will be a button named 'EXPORT'. On clicking EXPORT option the excel file will be downloaded. I have been tried using the link Exporting to Excel from sap.m.Table via csv | SCN but unable to download the Excel file. If u give me a small example with its source code..it will be very helpful for me.

The example shows some static values. My calculation view table has 10 columns but I need to pickup only 3 columns whose values are dynamic. I am able to showing values in Graphical view into UI but now need to export them into excel format. By clicking EXPORT button the excel table file will be downloaded.

Thanks & Regards,

Sandip Das Adhikari

[Former Member · Accepted solution]

Hi,

$( "#export" ).click(function() { oTable.exportData().saveFile('Table'); });

Triggered using jquery

[Former Member · Accepted solution]

Hi,

It is working for the static data but I am unable to bind it to json url, which is coming from HANA Calculation view table..The Export Excel is unable to bind the given below url:

"http://111.221.107.7/workshop/sessiona/powerdaleN1/services/testsql1.xsodata/TIME3?$filter=SUBMETER%..."

The json url is fetching three columns from Calculation view table named: TIME3, ENERGY, ENERGY_DIF for the graph. I have to bind those same columns for downloading excel sheet.

But it is possible for the graphical view. I also attached the screenshot.

[Former Member · Accepted solution]

Hi,

If its possible for graphical view, it is possible to export to excel.

On each time when you bind grapahical data you also check data bind happen in ui5 table too.

[Former Member · Accepted solution]

Hi I wrote the given below logic for export excel via json. Please rectify me where error occurs..Because when the logic runs then the entire UI became invisible.

  //// Experimental Part for Export Excel ////

    var tableId = this.getView().byId("idTable");

          var oModel = table.getModel("http://111.221.107.7/workshop/sessiona/powerdaleN1/services/testsql1.xsodata/TIME3?$filter=SUBMETER%...");//odata is the model, which is binding to the table

      exportToExcel(tableId, oModel);

      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: "/d/results"

                     },

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

                     columns: [itemsArray]

                 });

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

                     this.destroy();

                 });

      }

--------------------------------------------------------------------------------------------------------------------------------

I have some queries regarding this code. If u agree then I can ask u..

[Former Member · Accepted solution]

Hi,

Actually i didn't use native sapui code for export to excel. For ui5 table u have created using oTable (assume).

Then you just write normal jquery function:

$( "#export" ).click(function() { oTable.exportData().saveFile('Table'); });

export is a button and its id is export (this is present in UI ).

[Former Member · Accepted solution]

Hi,

Weather the code worked. Else say suggestion. If it worked close the thread

[Former Member · Accepted solution]

Hi,

That code is not working... I am trying to parse the json url.. I cant understand why its not working...If u want to see the graph source code I can send u...In case of graph the url is working properly...

[Former Member · Accepted solution]

Hi,

see this code http://jsbin.com/soyuvaviju/edit?html,js,output

Here allow CORS and run this. You can able to export table