Skip to Content

Introduction

When displaying data in SAP Fiori applications users very often want to download the data in the Excel format. Already quite some time there was an option available that allowed to download data in the xlsx format by adding the query parameter $format=xlsx to the OData request.

This export functionality had however several restrictions as described in the SAP Online Help.

What’s new

One of the most wanted features by customers was that the column headers in the Smart Table Control should be used as heading for the Excel file and only the columns that have been selected in the UI should be visible in the Excel file.

As of SAP UI 1.52 there is now a method setExportType available to leverage the UI5 client-side spreadsheet export service in the Smart Table control. Since the UI control “knows” about all the user specific UI settings it is able to apply them when exporting the data to Excel.

As described in the SAP UI5 online help setExportType sets a new value for property exportType which specifies the type of export to be used in the SmartTable control. The default value is UI5Client.

The “old” Gateway backend spreadsheet export service wasn’t able to enrich the exported Excel with this information.

If you now set the property useExportToExcel to true a button will be displayed that allows you to export data to a spreadsheet application, for example Microsoft Excel. But instead of using the “old” Excel Export functionality the new UI5 client-side spreadsheet export service will be used instead.

How to use the new feature in SAP Fiori elements List Reporting template

When you generate a SAP Fiori elements app using the List Reporting template using version SAP Innovation (1.54) it turns out that there is no button for exporting the data to Excel by default.

You can however use the UI adaptation editor to change the settings of the Smart Table control being used by selecting true from the Use Export To Excel drop down box.

When you now run your application you will notice a new button on the right hand side.

If you press the button the data that you see in the UI will be downloaded in an Excel file.

Please note, that the headings of the columns now have the same order and the same description as the headings of the Smart Table control. (Here: Image, Product ID, Category, …)

 

If you change the order of the colums in your app this change is immediately reflected when exporting the data to Excel as well. (Here we have moved “Image” to the third column, so that we now have the following order: Product ID, Category, Image…)

 

 

Hope this will help.

Best regards,

Andre

 

 

 

 

 

To report this post you need to login first.

14 Comments

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

  1. John Patterson

    Hi Andre

    Thanks for the update

    We been using this feature for a while now and a couple of quick gotchas i found worth sharing

    Formatting dates – by default the download will give a JSON date, if you use the applicable edm.type it will get formatted to something more user friendly

    <core:CustomData key="p13nData"	value='\{"columnKey": "Period", "columnIndex":"4", 
    "edmType":"Edm.DateTime", ..}'/>

    Also it is possible to download values derived via a navigation property using the following logic in the personalisation customdata

    "leadingProperty": "<NavigationProperty>/<Property>",

    JSP

    (0) 
  2. Jorge Sancho Royo

    Does this new functionallity solve the problem regarding how the different browsers manage HTTP GET requests?

    Recently we have opened an incident to SAP OSS regarding exporting Excel files directly from a SmartTable, we have developed a custom Fiori App which uses SmartTable and SmartFilterBar for displaying a report, and in this table we have activated the option UseExportToExcel = true.

    The problem is that when user filters in SmartFilterBar with several values althought the SmartTable is correctly displayed (because it uses a POST for retrieving data) when he clicks on button for downloading to Excel (which tries to retrieve the whole list [no pagination]) a new Tab is opened in the Browser because a GET request is executed and the URL is truncated and performs an Exception in SAP Gateway because the URL is trunctated depending on the browser; for instance, with Chrome there is no request at all because it returns an error directly in the browser but in Edge or IE the URL is truncated and sent to SAP who says that there are some errors because the URL truncates some filters in some properties.

    SAP as response to the incident says that althougth we are using a SAPUI5 component due to the fact that is a custom Fiori App the solution for that is not supported and it must be changed to POST, but for that whe have to develop our own component for downloading to Excel.

    Is this wrong behaviour fixed?

     

    (0) 
  3. Alexander K

    Hello, Andre.

    Thanks for blog, but I have a question

    How to implement this functionality in GW? We have type: sap.ui.comp.smarttable.ExportType.GW. Can I use some Excel template from smw0 or oaor?

     

    (0) 
  4. Ravi Dasari

    Hi,

    Nice document!!….I have  a problem when I export my fiori list report to excel. Its giving me all the columns in from the model to excel export even though I have selected very few columns in the list report. Do you know why ?

    Thanks,

    Ravi Dasari

    (0) 
    1. Andre Fischer
      Post author

      No.

      However you should check your SAP UI5 Version though to see whether you are using the “old” Excel export functionality that uses an OData request using the query Option $Format=xlsx or the new one where the Excel Sheet is rendered in the SAPUI5 app.

      You can also use transaction /n/iwfnd/traces to perform a payload trace to see which requests are sent to your backend.

      In case the error persists open a ticket and add the Information about the SAPUI5 Version and your findings in the trace to the customer message.

      For any other question please do not use the comment section but post a question here

      https://answers.sap.com/questions/ask.html

      Regards,

      Andre

      (0) 
  5. Mohamed Boussaid

    Hello,

     

    Thank you for your blog. I have a scenario with SAP Solution Manager 7.2 in ITSM. I want to export in xlsx the list of incidents shown in Resolve and Dispatch incidents fiori APP. Can I do this? Can this enhancement be used for my scenario?

     

    Thank you for your help.

    Best regards,

    Mohammed

    (0) 
  6. Jayakanth Ramakanthan

    Dear Andre,

    Thank you. It was a good blog. I have a issue during implmentation. I was able to successfully get the button displayed in SAP Web IDE and it is working perfectly fine. However when i try to deploy this to my on premise system, i do not see the export to excel icon which was enabled in Adapt UI. Can you please let me know if i have missed something here?

    My App is complete new custom app with a list report.

    Regards,

    Jay

    (0) 
    1. Andre Fischer
      Post author

      Hi Jay,

      this is weird, deployment of level 0 change should work property without any additional Setup.

      Could you please open an incident for the CA-UI5-FL component?

      Best Regards,

      Andre

       

       

       

       

      (0) 
      1. Jayakanth Ramakanthan

        Hi Andre,

         

        I have already raised a OSS incident. (453672 / 2018).

        It is in CA-WDE-BLD component.

        Can you please help ASAP? We have a UAT signoff pending due to this issue.

         

        Thanks and Regards,

        Jay

        (0) 
    1. Andre Fischer
      Post author

      The OData Service has to support the annotation „hierarchy-level-for“.
      This annotation should be evaluated by the SmartTable automatically.
      The information should be handed over to the Excel export then.

      Best Regards,

      Andre

      (0) 
  7. Steven De Saeger

    Hi Andre,

     

    Thanks for the blog … works fine out of the box 🙂

    Just one additional question though …

    When we have the multi-select capabilities enabled on the grid … is it possible to only download the selected lines ( instead of the whole table ) ?  And if not that would be a nice feature 😛

     

    Kind Regards,

    Steven

    (0) 

Leave a Reply