Skip to Content
Author's profile photo Andre Fischer

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

 

 

 

 

 

Assigned Tags

      43 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo John Patterson
      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

      Author's profile photo Shaik Fayaz
      Shaik Fayaz

      Hi All,

        Can I derive data from more than 1 property and also add customer text to the valueslike%20this?

      like this

      now I need all these values to be exported to excel.

      'Delivery Date' is a text and the date comes from OData Entity.

      Author's profile photo Douglas Cezar Kuchler
      Douglas Cezar Kuchler

      Great to see it. Thank you for sharing Andre Fischer!

      This is something that I've been asked about more than once by SAP customers using Fiori or UI5 apps.

       

      Kind regards,

      Douglas

      Author's profile photo Jorge Sancho Royo
      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?

       

      Author's profile photo Alexander K
      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?

       

      Author's profile photo Ravi Dasari
      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

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog 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

      Author's profile photo Mohamed Boussaid
      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

      Author's profile photo Mushtaq Ahmed
      Mushtaq Ahmed

      Dear Andre Fischer,

       

      This is an excellent document.  Thank you.

       

      Regards,

      Author's profile photo Jayakanth Ramakanthan
      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

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog 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

       

       

       

       

      Author's profile photo Jayakanth Ramakanthan
      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

      Author's profile photo Cristian Duran
      Cristian Duran
      Hello, were you able to solve this problem?
      Author's profile photo Bhagyashree Puran Janyani
      Bhagyashree Puran Janyani

      Hi Jayakanth,

      Was the export to excel button issue resolved?

      Even I am facing a similar issue, can you please help?

       

      Thank you

      Bhagyashree

      Author's profile photo Bhagyashree Puran Janyani
      Bhagyashree Puran Janyani

      Hi Andre,

      I am facing something weird. I created a custom List report. The export to excel is available in DEV system. When I transported to the Quality system, I could see the export excel button and others don't see it even after cache. Can you please help as this is very urgent.

      When I check in Visual editor, the excel feature is set to 'True'

      Thank you

      Bhagyashree

      Author's profile photo Lucas Bravo
      Lucas Bravo

      Hi Andre, nice blog.

       

      I want to ask if this feature is possible for tree table with odata?

       

      Best Regards, Lucas.

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog 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

      Author's profile photo Marcel Beckmann
      Marcel Beckmann

      Hi Andre,

      do you have an example for the hierarchy download? In my case it loads unfortunately several times the complete result into the file.

      Many thanks!

       

      BR Marcel

      Author's profile photo Steven De Saeger
      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

      Author's profile photo Marco Röwekämper
      Marco Röwekämper

      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

      Author's profile photo Gagan Kasana
      Gagan Kasana

      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

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog Post Author

      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

       

      Author's profile photo Rajesh Karlapati
      Rajesh Karlapati

      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

       

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog Post Author

      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

       

      Author's profile photo Chandran Bala
      Chandran Bala

      Hi Rajesh

      Did you found any workaround? Stucked with same issue.  Kindly share if you got anything to remove filters in export.

       

      Thanks,

      Chandran

      Author's profile photo Former Member
      Former Member

      In Visual editor, change the option in smart table with export type from UI5 Client to GW. this will download data from gateway and no filter will be available.

      Author's profile photo Praseeda Aramadaka
      Praseeda Aramadaka

       

      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

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog Post Author

      Then I suggest to raise a question in the SAPUI5 Q&A area.

      Author's profile photo VIKAS KUMAR
      VIKAS KUMAR

      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

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog Post Author

      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

       

      Author's profile photo Sebastiano Marchesini
      Sebastiano Marchesini

      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

       

      Author's profile photo Vijaya Simha Chintarlapalli Reddy
      Vijaya Simha Chintarlapalli Reddy

      Hallo  Andre Fischer ,

      Can we a make a line item field Editable  Using adaptation editor.

      Thanks,
      Vijay

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog Post Author

      Can you explain how this question is related to this post?
      The downloads are read only.

      Author's profile photo Jono Thomas Kannankara
      Jono Thomas Kannankara

      Hi Andre Fischer,

      Does it have any option to consider the formmater while downloading the data.

      I'm using the formmater to display "Y" and "N" as "Yes" and "No" using the custom column in smart table. But while downloading excel will have the Y & N indicator instead of Yes/No.

       

      Thank You

      Jono

      Author's profile photo Pratik Gaikar
      Pratik Gaikar

      Hi Jono ,

       

      This is happening because , the export to excel is retrieving the data directly from back end.

      so that's the reason while downloading excel you will see the Y & N indicator instead of Yes/No.

      Author's profile photo Hubert Falkowski
      Hubert Falkowski

      Hi Andre Fischer,

      Is it possible to implement merge used in the smart table? I have something like this, and want to have it also in XLSX

      Best Regards,

      Hubert

      Author's profile photo Ravish Garg
      Ravish Garg

      Hi Andre

      If using a Master-Detail-Detail template for Fiori Elements, how can the export to excel option be enabled for the subsequent tables? I am able to set the option to true on the level 0 table, but don't see any such option on the level 1 & 2 tables.

      Author's profile photo Dzmitry Buriak
      Dzmitry Buriak

      Hello Andre,

       

      Thanks a lot for this post! Just wondering is it possible to get dates to be in Excel not in UTC, but in client time zone?

       

      Thanks,

      Dmitri

      Author's profile photo JOSE NINO RICAPLAZA
      JOSE NINO RICAPLAZA

      Hi  Andre Fischer,

       

      Is it possible to protect the sheet that would be exported?

       

      Thank you!

       

      Regards,

      Nino

      Author's profile photo Andre Fischer
      Andre Fischer
      Blog Post Author

      What do you mean by "protection"?

       

      Author's profile photo BDU BDU
      BDU BDU

      Hallo Andre,

       

      Thanks a lot for the your post! it is very helpful.

      After deploying my app on the launchpad, the export functionality is not working.

      after clicking on the export excel button , it was  triggering but it keeps loading the data…… as  shown below and error in console.

      can you please have any Suggestion?

       

       

      Regards,

      Bhargava

       

       

      Author's profile photo Phani Vasabattula
      Phani Vasabattula

      Thank you Andre for the nice blog. I am having an issue. In our smart table, we have a custom column with two attributes in it - Customer and City. When downloading, the gateway call has both fields requested. But when downloaded to Excel, only Customer is getting written. is there a way to fix it?

      Author's profile photo Pratik Kumar
      Pratik Kumar

      Hello Andre,

      Is it possible to add some header details to the Excel sheet at the top which is being downloaded? Header in the sense like title, current date, current time etc.

      Thanks,

      Pratik