Displaying backend data both as SAPUI5 Table and an Excel File

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

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.

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.

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! 🙂