Download data in excel in SAPUI5 Application

22
22788

Hello everyone, in this tutorial we will show you how to download data in excel in SAPUI5 application.

Prerequisites

Step-by-Step Procedure

Export data into excel in SAPUI5 application.

1. Open Eclipse IDE and create a SAPUI5 Application project by clicking on File → New → Project. Choose Application Project under SAPUI5 Application Development.

New SAPUI5 Application Project2. Click on Next and provide the Project Name, choose sap.m library and hit Next to create a initial view.

Choose sap.m Library3. Provide the view name and choose type of the view as Java Script.Here you can choose any of the option as per your preference and hit Next to create project.

Create a New View4. A new SAPUI5 project is created in Project Explorer.

SAPUI5 Application5. Select index.html file in the project and double click to appear in the workbench window.Copy and paste the below code in the index.html file (or) adjust the code as per the below code.

Index.html

<!DOCTYPE HTML>
<html>
	<head>
		<meta http-equiv="X-UA-Compatible" content="IE=edge">
		<meta http-equiv='Content-Type' content='text/html;charset=UTF-8'/>

<!-- only load the mobile lib "sap.m" and the "sap_bluecrystal" theme -->
		<script src="resources/sap-ui-core.js"
				id="sap-ui-bootstrap"
				data-sap-ui-libs="sap.m" 
				data-sap-ui-theme="sap_bluecrystal">
		</script>
		
		<script>
		sap.ui.localResources("demotable");
// 		1. Create an App
		var app = new sap.m.App({initialPage:"iddemoTable1"});

//		2. Create a Page
		var page = sap.ui.view({id:"iddemoTable1", 
                                        viewName:"demotable.demoTable", 
                                        type:sap.ui.core.mvc.ViewType.JS});

//              3. Add page to the App
                app.addPage(page);
	
		app.placeAt("content");
		</script>
	</head>
	<body class="sapUiBody" role="application">
		<div id="content"></div>
	</body>
</html>

6. Now double click on the demoTable.view.js to create and add Table inside the View. All the UI element controls should be placed in the CreateContent( ) method of the view. Add the below code to create a Table and columns of the table in the view.

demoTable.view.js

     // 1. Create Table
	  var oTable = new sap.m.Table("idOrderList", {   
      inset : true, 
      headerText : "List of Sales Orders",
      headerDesign : sap.m.ListHeaderDesign.Standard, 
      mode : sap.m.ListMode.None,   
      includeItemInSelection : false,   
    });
    
    // 2. Add columns to the Table
    var col1 = new sap.m.Column("col1",{header: new sap.m.Label({text:"Order ID"})});
    oTable.addColumn(col1); 
    
    var col2 = new sap.m.Column("col2",{header: new sap.m.Label({text:"Buyer ID"})});
    oTable.addColumn(col2); 
    
    var col3 = new sap.m.Column("col3",{header: new sap.m.Label({text:"Buyer Name"})});
    oTable.addColumn(col3);

    var col4 = new sap.m.Column("col4",{header: new sap.m.Label({text:"Net Amount"})});
    oTable.addColumn(col4);
    
    var col5 = new sap.m.Column("col5",{header: new sap.m.Label({text:"Currency"})});
    oTable.addColumn(col5);       

     
    // 3. Bind Aggregation 
    // "/SalesOrderSet" is the entity set Name
    var colItems = new sap.m.ColumnListItem("colItems",{type:"Active"});
    oTable.bindAggregation("items","/SalesOrderSet",colItems); 
    
    // 4. Define type of each every column
    var txtNAME = new sap.m.Text("txtNAME",{text:"{SoId}"});
    colItems.addCell(txtNAME); 
        
    var txtNAME2 = new sap.m.Text("txtNAME2",{text:"{BuyerId}"});
    colItems.addCell(txtNAME2); 
       
    var txtNAME3 = new sap.m.Text("txtNAME3",{text:"{BuyerName}"});
    colItems.addCell(txtNAME3);

    var txtNAME4 = new sap.m.Text("txtNAME4",{text:"{NetAmount}"});
    colItems.addCell(txtNAME4); 
       
    var txtNAME5 = new sap.m.Text("txtNAME5",{text:"{CurrencyCode}"});
    colItems.addCell(txtNAME5);

    
    // 5. Create a button "Download" with Event handler method 
    var btExcel = new sap.m.Button({      
      text: "Download",
      press : function(oEvent){ oController.Download(oEvent); } 
    });
    
    // 5. Add Table to Page
    return new sap.m.Page({
      title: "Excel Download",
      content: [ oTable ],
      footer: new sap.m.Bar({ contentRight: [btExcel],
        design: "Footer"
      })
    });
  }

7. Now double click on demoTable.controller.js to call the OData service url to get the data from the backed and also the code to download the table data in excel.

demoTable.controller.js

	onInit: function() {
	  
	  var sServiceUrl = "/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/";
	  	  
	  // set data model
	  var oModel = new sap.ui.model.odata.ODataModel(sServiceUrl, {
	    json: true,
	    defaultBindingMode: "OneWay",
	    useBatch: true,
	    defaultCountMode: "Inline",
	    loadMetadataAsync: true
	  });	  
    sap.ui.getCore().setModel(oModel);
	},

//      Code to download the data in excel format
	Download: function(oEvent){
	  var sUrl = "/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/SalesOrderSet?$format=xlsx";
          var encodeUrl = encodeURI(sUrl);
	  sap.m.URLHelper.redirect(encodeUrl,true);
	},

8. We are ready with application. Save the application and deploy it in SAPUI5 ABAP Repository.To know the steps on how to deploy SAPUI5 application to ABAP Repository click here.

9. After deployment of the application its time to test the URL.The URL of the SAPUI5 application will look like below.

http://<server_name>:8000/sap/bc/ui5_ui5/sap/<application_name>/index.html?sap-client=300

For Example:
http://saplearners.com:8000/sap/bc/ui5_ui5/sap/ztest_download/index.html?sap-client=300

10. If every thing is working fine, you should see the below output.

SAPUI5 Application Output11. Now click on the Download button to download the table data into excel.

Excel Download Completed

12. Open the excel file downloaded.

Data in Excel Sheet

You have successfully created a SAPUI5 Application to download data in excel.

Please stay tuned to us for more SAPUI5 Tutorials. Please feel free to comment and let us know your feedback. Subscribe for more updates

Thank you. 🙂

22 COMMENTS

  1. Hi, I see that the excel file which is downloaded has same name as the Entityset. Please tell me how to change this name to something else.

  2. Hi, Very nice code. But. How i can download a files to FioriClient ? I have a tablet Samsung. when i executed “sap.m.HelperURL(“url_image”)”
    the program try open a file but the tablet showed a message: “not found element.”

    • Hello Felipe,

      We have not tried this scenario. We will get back to you soon.

      Thanks,
      Prakash

  3. Hi, I am getting the below error when i tried to do the same activity.
    Error message : “The Data Services Request could not be understood due to malformed syntax”.
    I deployed my app to HCP and through HCP launchpad i am accessing the app. HCP is communicating to backend properly.
    Is this the reason for not working.? Please let me know your views.

    • Hi sunsid,

      I am also getting the same error. If you have resolved it can you please provide me the solution also.

      Thanks,
      Srinivasan

  4. Great job guyz. Nice post..I just wanted to know if there is way to format the excel file. i have a requirement where data is huge and columns have different length so i need to format my excrel file beforehand. I found few libraries like excelbuilder but they are in node.js so not helping my cause. A liitle help would be appreciated.
    Thanks
    Prabuddha

  5. Excellent explanation!

    How can we download the data in PDF of DOCX format? Please help me in this regard.

    • Hi MLHN,

      Thanks for your comments. As of now we dont have code for your request. We will update once the code is ready with us.

      Thanks,
      Akash

  6. Hello while downloading data into excel i am getting the following error as

    Unsupported media type requested.

    The MIME type ‘xlsx’ is invalid or unspecified.
    Microsoft.Data.OData.ODataContentTypeException

    please help me out.

  7. Hi,
    Thanks for post.. it is working fine.

    Is there any way to download only selected columns in excel?
    We have a requirement where we need to download only few columns from service.

    Please let me know if anybody can help.

    Regards,
    Saurabh

    • Hi Saurabh,
      You can combine the above query with $select query. So, for eg., if you want to select only ‘Id’ and ‘Name’ fields from ’employeeSet’ the entire query would be like this:
      var sUrl = “/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/employeeSet?$select=Id,Name&$format=xlsx”

      Thanks,
      Bhoomika

  8. Hi
    Suppose my table has only selected fields/columns, and then i applied filters and now i want to download it into excel format.
    Now how can i download?

    Thanks

  9. Hello,
    I am facing issue to download data in excel file from services. Your service section is
    var sUrl = “/sap/opu/odata/sap/ZDEMO_GW_SRV_SRV/SalesOrderSet?$format=xlsx”;

    If we use HANA odata service then always it ends with “.xsodata” other odata cases it is “.svc” but your service does not contain any extension!

    When I am trying to invoke it by service url ending with “server-details/service_name.xsodata/SalesOrderSet?$format=xlsx” always getting error message format not supported. Only XML and JSON is supported.

    Can you please explain what is the issue?

    Thanks
    Suman

    • Hi Suman,

      All the URI options are specific to SAP Gateway System. Not sure about the HANA XSODATA. We will keep you posted when we have the info. Please send an email to akash.sapui5@gmail.com

      Thanks,
      Prakash

Comments are closed.