Skip to Content

New Excel export functionality available

  • 30.11.2018 – Added screen shot how to set “Use Export to Excel” using the SAP UI5 Visual Editor

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 or in newer releases of SAP Web IDE (as of Web IDE version 1809) the SAPUI5 Visual Editor to change the settings of the Smart Table control being used by selecting true from the Use Export To Excel drop down box.

For more Information see the blog post Adaptation Projects – it’s time for a new way to extend your Fiori Elements application of my colleague .

UI Adaptation Editor

SAPUI5 Visual Editor

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

 

 

 

 

 

26 Comments
You must be Logged on to comment or reply to a post.
  • 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

  • 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?

     

  • 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?

     

  • 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

    • 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

  • 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

  • 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

    • 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

       

       

       

       

      • 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

    • 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

  • 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

  • Hi Andre,

    thanks for your interesting blog.

    You write “If you press the button the data that you see in the UI will be downloaded in an Excel file.” in your blog. We use the App F2217 which has an excel download button and we have SAPUI5 version 1.52 installed.

    The table of the app uses grouping. If I download the filtered data I will get all the filtered rows but I don’t see these rows. Is it possible to download only the result rows?

    Best Regards

    Marco

  • I don’t see the option in web UI . I am trying to create a new app. has tis been modified in the latest version of SAP web UI or do we need licence for this capability

    • Hi Gagan,

      no, no need for an additional License ;-).

      Just use the SAPUI5 Visual Editor that is offered in the context menue which has replaced the UI Adaptation Editor as of SAP Web IDE 1809.

      I have updated my blog (see above) and thanks for the hint.

      Regards,

      Andre

       

  • Hi Andre,

    Can we export the excel sheet without filtering and sorting options (i dont want to have these options bydeafut.)? i don’t see any property or method in the api… https://sapui5.hana.ondemand.com/#/api/sap.ui.export.Spreadsheet .

     

     

    Thanks,

    Rajesh

     

    • Hi Rajesh,

      no, this is not possible and adding such a functionality would be a feature request.

      It is however not clear for me why one would not want to have the option to add filters on the results.

      By default nothing is selected, so it would be up to the user to add filters.

      Regards,

      Andre

       

  •  

    Thanks for this blog.

    I tried to get this but I am facing an issue.

     I am able to view the setting applied during the run in WEBIDE but after deployment they aren’t available. As per note, the UI5 version should be 1.52 and above and the system I am working for is 1.52.9

  • Hi Andre,

    I want to export the table without including some column(s). Is it possible to export the table excluding some columns. If yes, How?

    Thanks

    Vikas

    • As I wrote in my blog: “… only the columns that have been selected in the UI should be visible in the Excel file” with this new Excel export functionality .

      So the user has simply to de-select those columns that should not be exported before pressing the Excel Export button.

      Regards,

      Andre

       

  • Hi Andre,

    It’s wonderful this blog and thanks for sharing.
    But I’ve an issue: if I download the file with iOS (in an iPad or iPhone), the extension (.xlsx) is not present and it’s impossible to open it. With Android and Windows there isn’t the same problem.

    Any solutions or it’s not implemented this functionality with new List Report ?  

    Kind Regards,

    Sebastiano