Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Andre_Fischer
Product and Topic Expert
Product and Topic Expert
35,593

  • 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 jessica.merz .

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

 

 

 

 

 
57 Comments
former_member182048
Active Contributor
0 Kudos
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
DouglasCezar
Contributor
0 Kudos
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
jsancho
Participant
0 Kudos
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?

 
former_member214867
Participant
0 Kudos
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 Kudos
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
Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos
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
Former Member
0 Kudos
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
mushtaq_shaik
Participant
0 Kudos
Dear andre.fischer,

 

This is an excellent document.  Thank you.

 

Regards,
jayakanth_r3
Participant
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

Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos
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

 

 

 

 
Former Member
0 Kudos
Hi Andre, nice blog.

 

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

 

Best Regards, Lucas.
jayakanth_r3
Participant
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
Steven_UM
Contributor
0 Kudos

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

Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos
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 Kudos
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
former_member597181
Discoverer
0 Kudos
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
Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

 

0 Kudos
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

 
Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos
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

Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

0 Kudos
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
Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos
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

 
smarchesini
Active Contributor
0 Kudos

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

 

VijayCR
Active Contributor
0 Kudos
Hallo  andre.fischer ,

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

Thanks,
Vijay
Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos
Can you explain how this question is related to this post?
The downloads are read only.
JonoThomas
Explorer
0 Kudos
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
Hubert
Advisor
Advisor
0 Kudos
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
ravishgarg
Participant
0 Kudos
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.
0 Kudos
Hi Rajesh

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

 

Thanks,

Chandran
C5227016
Employee
Employee
0 Kudos
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
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
0 Kudos
Hi  andre.fischer,

 

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

 

Thank you!

 

Regards,

Nino
Andre_Fischer
Product and Topic Expert
Product and Topic Expert
0 Kudos
What do you mean by "protection"?

 
former_member638853
Discoverer
0 Kudos

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

 

 

0 Kudos
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.
chphaniv
Explorer
0 Kudos
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?
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.
former_member364945
Discoverer
0 Kudos
Hi All,

  Can I derive data from more than 1 property and also add customer text to the values?


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.
0 Kudos
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
former_member756765
Discoverer
0 Kudos
Hello, were you able to solve this problem?
former_member762513
Discoverer
0 Kudos
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
former_member762513
Discoverer
0 Kudos
Hi Jayakanth,

Was the export to excel button issue resolved?

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

 

Thank you

Bhagyashree
0 Kudos
Hello Andre,

this is really a cool feature. But why do we always get an error by downloading an Excel-File from the Smart-Table when there are columns containing long texts (like more then 255 letters)? This is important for our customer also to export a description or a reason in text.

Thanks and kind regars

Sven
HerzogIgzorn
Associate
Associate
Hello Sven,

are you getting an error when downloading the exported file or does this error show up once you try to open the file? The UI5 client export functionality does not apply a certain limit and according to the specification, the content is limited to 32,767 characters per cell.

The fact that we generate Office Open XML Spreadsheets means, that all vendor specific limits apply: Excel specifications and limits (microsoft.com)

If you have a specific scenario where the issue can be reproduced, I'd recommend to contact SAP Support. The experts can further investigate the root cause of your issue.

Regards Sebastian
HerzogIgzorn
Associate
Associate
0 Kudos
Hello Pratik,

maybe the context property on the export settings might be the right thing for you:

Spreadsheet Export Configuration - Documentation - Demo Kit - SAPUI5 SDK (Scroll down to "Context Information")

Here you can add certain groups with key-value pairs which will be on a separate sheet in the same workbook.

Regards Sebastian
HerzogIgzorn
Associate
Associate
0 Kudos
Hello Phani,

when you are using the SmartTable with custom columns, you will have to maintain the "p13nData" customData. This is necessary because the columns are already created when the SmartTable is being initialized and they will not be managed by the SmartTable. The p13nData ensures that the SmartTable can use those columns for built-in features like the personalization, export, sort, filter, etc.

The UI5 client export can also handle multiple properties within one column but it requires correct configuration. Further information can be found in the SmartTable FAQ (Question 1).

Your custom column (2 String properties) needs the following properties in the p13nData:

  • columnKey

  • leadingProperty

  • description

  • displayBehaviour


A sample can be found here.

Regards Sebastian
HerzogIgzorn
Associate
Associate
0 Kudos
Hello Bhargava,

the error on the console should be a warning in more recent UI5 versions because it only affects the export experience (less requests) but does not prevent the export.

You mentioned that the export functionality "keeps loading the data" which is expected in certain scenarios. The UI5 client export will utilize the dataSource.count on the export settings as maximum amount of line items that will be requested. This amount will ideally be fetched in 5 request with a minimum of 200 and a maximum of 5,000 per request. This ensures that the load on the OData service will be kept to a minimum and also provides a smooth export experience for the end user.

When no count is available, it will request line items until either the OData service returns an empty response or the maximum amount of line items is reached (1,048,575). This requires that the service handles $skip and $top correctly. Otherwise the service will return the same data several times and the export process will not stop until the maximum amount of line items has been requested.

Regards Sebatian
HerzogIgzorn
Associate
Associate
0 Kudos
Hello Nino,

no this is not supported by the UI5 client export. Having a simple sheet protection does not make sense without a digital signature because it can easily be bypassed. A digital signature is also not available for the UI5 client export for obvious reasons.

Regards Sebastian
HerzogIgzorn
Associate
Associate
0 Kudos
Hello Sebastiano,

we addressed this issue with Apple and it was solved with iOS 13 and newer. It is also documented in the SAPUI5 dev guide: Spreadsheet Export Restrictions - Documentation - Demo Kit - SAPUI5 SDK

Regards Sebastian