Skip to Content

Introduction –

Most of the time we need functionality to download table content in the form of excel file. There is no standard way to achieve this on sapui5 table control.

This excellent blog Export sap.ui.table.Table as CSV by Angel Puertas talks about exporting the table content in the form of CSV file and it is generated on

client side.

In my current blog, I will share details of generating excel file in the backend and then downloading it in frontend UI5 application.


I am going to develop UI5 application showing details of standard SAP table SAIRPORT in ui5 table and will provide export button to download table content.


Procedure –

First, we need to develop FM, lets call it as Z_TEST_XLS_DISPLAY. The logic is as explained in below code.

FUNCTION Z_TEST_XLS_DISPLAY.

*”———————————————————————-

*”*”Local Interface:

*”  EXPORTING

*”     REFERENCE(E_URL) TYPE  STRING

*”———————————————————————-

* Data Declaration

  DATA :

  lv_app_type        TYPE string,

  lv_guid            TYPE guid_32,

  lo_cached_response TYPE REF TO if_http_response,

  ls_airport_string  TYPE string,

  ls_airport_xstring TYPE xstring,

  lt_airport         TYPE STANDARD TABLE OF SAIRPORT,

  ls_airport         LIKE LINE OF lt_airport,

  ls_file_name       type string.

*Get data from SAIRPORT table

  SELECT * FROM SAIRPORT INTO TABLE lt_airport UP TO 50 ROWS.

CONCATENATE ls_airport_string

                ‘Airport ID’   cl_abap_char_utilities=>horizontal_tab

                ‘Airport Name’ cl_abap_char_utilities=>horizontal_tab

                ‘Time Zone’    cl_abap_char_utilities=>horizontal_tab

                cl_abap_char_utilities=>newline

                INTO ls_airport_string.

  LOOP AT lt_airport INTO  ls_airport .

    CONCATENATE ls_airport_string

                ls_airport-id         cl_abap_char_utilities=>horizontal_tab

                ls_airport-name       cl_abap_char_utilities=>horizontal_tab

                ls_airport-TIME_ZONE  cl_abap_char_utilities=>horizontal_tab

                cl_abap_char_utilities=>newline

                INTO ls_airport_string.

  ENDLOOP.

  CALL FUNCTION ‘SCMS_STRING_TO_XSTRING’

    EXPORTING

      text  = ls_airport_string

    IMPORTING

      buffer = ls_airport_xstring.

CREATE OBJECT lo_cached_response

    TYPE

    cl_http_response

    EXPORTING

      add_c_msg = 1.

****set the data and the headers

  lo_cached_response->set_data( ls_airport_xstring ).

  lv_app_type = ‘APPLICATION/MSEXCEL; charset=utf-8’.

  lo_cached_response->set_header_field( name = if_http_header_fields=>content_type

                                     value = lv_app_type ).

****Set the Response Status

  lo_cached_response->set_status( code = 200 reason = ‘OK’ ).

****Set the Cache Timeout – 60 seconds – we only need this in the cache

****long enough to build the page

  lo_cached_response->server_cache_expire_rel( expires_rel = 60 ).

****Create a unique URL for the object and export URL

CONCATENATE ‘export_’ sy-datum sy-uzeit INTO ls_file_name.

CONCATENATE  ‘/sap/public/’  ls_file_name ‘.’ ‘xls’ INTO e_url.

****Cache the URL

  cl_http_server=>server_cache_upload( url     = e_url

                                       response = lo_cached_response ).

ENDFUNCTION.

Basically, we are getting data from table SAIRPORT and converting it into XSTRING and then building unique URL pointing to excel file location.

Now we will create OData Service to read the Airtport data and url

/wp-content/uploads/2014/04/ui5_xls1_439824.jpg

We need to redefine method AIRPORTCOLLECTIO_GET_ENTITYSET as below

method AIRPORTCOLLECTIO_GET_ENTITYSET.

  DATA: lt_airport TYPE STANDARD TABLE OF SAIRPORT,

        ls_airport LIKE LINE OF lt_airport,

        ls_entity   LIKE LINE OF et_entityset.

*Get data from SAIRPORT table

  SELECT * FROM SAIRPORT INTO TABLE lt_airport UP TO 50 ROWS.

*Fill ET_ENTITYSET

  LOOP AT lt_airport INTO  ls_airport .

    ls_entity-id   = ls_airport-id.

    ls_entity-name = ls_airport-NAME.

    ls_entity-time_zone = ls_airport-TIME_ZONE.

    APPEND ls_entity TO et_entityset.

  ENDLOOP.

endmethod.

And XLSCOLLECTION_GET_ENTITYSET as below

method XLSCOLLECTION_GET_ENTITYSET.

data:   ls_entity   LIKE LINE OF et_entityset,

        lv_url      TYPE string.

CALL FUNCTION ‘Z_TEST_XLS_DISPLAY’

IMPORTING

   E_URL        = lv_url       .

ls_entity-url = lv_url.

APPEND ls_entity TO et_entityset.

endmethod.

With this we are done with backend logic.

Now let’s create UI5 application. Below is the code of UI5 application.


<!DOCTYPE HTML>
<html>
  <head>
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
 <script src="https://openui5.hana.ondemand.com/resources/sap-ui-core.js"
  id="sap-ui-bootstrap"
  data-sap-ui-libs="sap.ui.commons,sap.ui.table"
  data-sap-ui-theme="sap_bluecrystal">
  </script>
  <!-- add sap.ui.table,sap.ui.ux3 and/or other libraries to 'data-sap-ui-libs' if required -->
  <script>
  //Root URL for the service
  function getUrl(sUrl) {
  if (sUrl == "")
  return sUrl;
  if (window.location.hostname == "localhost" )
  {
  return "proxy" + sUrl;
  }
  else
  {
  return sUrl;
  }
  }
  var sURI = getUrl('/sap/opu/odata/sap/ZTESTXLS_SRV/');
  oModel = new sap.ui.model.odata.ODataModel(sURI, false),
  sap.ui.getCore().setModel(oModel);
  // Airport Details Table
  var oTable = new sap.ui.table.Table({
             title: "SAPUI5 Table Export as Excel",
  width: "60%",
  visibleRowCount : 10,
  selectionMode : sap.ui.table.SelectionMode.Single,
  editable : false
  });
  //Create html instances
  var html = new sap.ui.core.HTML();
     //Set table toolbar buttons
  oTable.setToolbar(new sap.ui.commons.Toolbar({
  items : [ new sap.ui.commons.Button({
  text : "Export",
  icon:"https://sapes1.sapdevcenter.com/sap/public/bc/icons/s_X__XLS.gif",
  press : function(oEvent) {
      var sRead = "/XlsCollection" ;
      oModel.read( sRead, null, null, true, function(oData, oResponse){
  
      var fileURL =   'proxy'+ oData.results[0].url;
              html.setContent("<iframe src=" + fileURL + " width='0' height='0'></iframe>");
              html.placeAt("content");
         },function(){
             alert("Read failed");
             });           
  }
  })]
  }));
  oTable.addColumn(new sap.ui.table.Column({
  label : new sap.ui.commons.Label({ text : "Airport ID" }),
  template : new sap.ui.commons.TextField({ value : "{AirportID}" })
  }));
  oTable.addColumn(new sap.ui.table.Column({
  label : new sap.ui.commons.Label({ text : "Airport Name" }),
  template : new sap.ui.commons.TextField({ value : "{AirportName}" })
  }));
  oTable.addColumn(new sap.ui.table.Column({
  label : new sap.ui.commons.Label({ text : "Timezone" }),
  template : new sap.ui.commons.TextField({ value : "{TimeZone}" })
  }));
  oTable.bindRows("/AirportCollection");
  oTable.placeAt("content");
  </script>
  </head>
  <body class="sapUiBody" role="application">
  <div id="content"></div>
  </body>
</html>



End Result –


Now let’s execute application. On click of Export button, file will get downloaded as below.

/wp-content/uploads/2014/04/ui5_xls2_439901.jpg


Here we can see the file content same as table content. Each time, if we click export button, new file will get generated with current date and timestamp as unique filename.

/wp-content/uploads/2014/04/ui5_xls3_439902.jpg


Closing Remarks –


With the simple technique of creating temporary URL for the Excel file and then exposing that URL through GW service, we are building SAPUI5 application to consume GW service. We are then setting src property of an iframe with this URL and setting this content to HTML UI element.

This approach is almost similar to display PDF files as explained in my earlier blog Display Smartform (PDF) in SAPUI5

If you have experience of working on Webdynpro ABAP ALV then you will notice that this kind of exporting data to excel is provided as standard feature. of course, it is not possible in Client Side UI5 Application.


Just to be consistent with WDA, I am naming my files as export_<sy-datum><sy-uzeit>.xls

Just in case, if you are eager to know, you can observe WDA ALV export functionality in the standard application accessible with

url http://<host>:<port>/sap/bc/webdynpro/sap/wdt_alv?sap-language=EN

Observe that if you run UI5 application on IE then it will open modal dialog for excel file whereas on Chrome, you can export multiple files.

As usual, Please feel free to put your comments/suggestions and any other way to export excel files from backend!


Happy Learning and Coding! 🙂

To report this post you need to login first.

19 Comments

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

  1. Robin van het Hof

    Although I kinda liked your blog, the title is a somewhat misleading; instead of “downloading an SAPUI5 table as Excel” (whether generated on backend or not), it’s more a case of “displaying backend-generated data both as SAPUI5 table and an Excel file”

    But nevertheless, I could see the use-case for others 🙂

    (0) 
    1. Chandrashekhar Mahajan Post author

      Hi,

      Are you creating in backend system (where IW_BEP is installed)? in that case, the temporary url will get generated in backend system and hence will not be accessible in GW server system. To solve this problem, please use the approach of GW media steaming solution as explained in my blog Display Smartform (PDF) in SAPUI5

      Or 2nd option, export xstring from backend and then write logic to build temporary url on GW server.

      Regards,

      Chandra

      (0) 
  2. Mohsin Soudagar

    Hi Chandrashekar,

    I want an colourful formatted excel to be downloaded from SAP UI5, I have written the code in backend ( I have concatenated the XML code of the excel in the string. Then I followed up your methods for converting string to xstring and so on.

    I am calling the odata from UI5 from .js file and it is returning the url,  /sap/public/export.xls

    How to open the excel from .js file.

    Thanks and Regards,

    Mohsin.

    (0) 
    1. Chandrashekhar Mahajan Post author

      Hi Ekansh Saxena

      yes it is possible. you need to implement create_stream and then the file content will be available in the form of xstring which you need to convert to string table etc. but I guess only CSV files will work with this logic.

      Regards,

      Chandra

      (0) 
        1. Ekansh Saxena

          Hi,

          I just came across this blog again and saw my previous comment. I thought of updating this for the sake of information. It seems reasonably difficult to decode and parse the uploaded xstring of .xls/.xlsx file so I had to go through the csv route.

          I had shared the code to extract the data from csv xstring to internal table here.

          (0) 
  3. Gaurav Subramanian

    Hello Chandra,

    Do you have any blog or examples on how to get the data from Hana and display them in Excel format ? If so, please post it here.

    Thanks and Regards,

    Gaurav.

    (0) 
  4. Marcos Andrade

    Hello Chandrashekhar,

    I have the same requirement, however the data to be exported is already loaded in a table on the UI end. I was trying to call a Function import and pass back the UI table content as an array. However SAP Gateway works with OData 2.0 and does not support array as a parameter.

    I guess my only way is to read the data again in the back and then export it as you did.

    Do you have any idea how we could send back the table data to the gateway?

    Thanks in advance.

    Marcos

    (0) 

Leave a Reply