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 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
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
Also it is possible to download values derived via a navigation property using the following logic in the personalisation customdata
JSP
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.
Hello John,
you can also use the beforeExport event on the SmartTable control to adjust the export settings as desired.
For further details see:
sap.ui.comp.smarttable.SmartTable - API Reference - Demo Kit - SAPUI5 SDK
Spreadsheet Export Configuration - Documentation - Demo Kit - SAPUI5 SDK
Data Types for Spreadsheet Export - Documentation - Demo Kit - SAPUI5 SDK
Regards Sebastian
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
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 Jorge,
in case the export button on your SmartTable is opening a new browser window/tab, you are using the SAP Gateway based export and not the UI5 client export. This can also be seen when looking at the default export button on the SmartTable control. If it is a plain button, it's the Gateway XLSX export. The UI5 client export is showing a MenuButton where you can also us the "Export As" option.
I'd recommend checking the exportType property on your SmartTable.
For further details see:
sap.ui.comp.smarttable.SmartTable - API Reference - Demo Kit - SAPUI5 SDK
sap.ui.comp.smarttable.ExportType - API Reference - Demo Kit - SAPUI5 SDK
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?
Hello Alexander,
is there a reason why you'd prefer a GW based export instead of the UI5 client export?
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 Fischer,
This is an excellent document. Thank you.
Regards,
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
Hello, were you able to solve this problem?Hi Jayakanth,
Was the export to excel button issue resolved?
Even I am facing a similar issue, can you please help?
Thank you
Bhagyashree
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
Hi Andre, nice blog.
I want to ask if this feature is possible for tree table with odata?
Best Regards, Lucas.
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,
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
Hello Marcel,
the UI5 client export is based on flat lists because it is the only thing that can be represented in Microsoft Excel (technically). The TreeTable is based on the sap.ui.table.Table (Grid Table) which also requires a list of data elements. Everything on top is just visual effects and some glue code on binding level.
In theory it is possible to export tree structures as well, but highly depends on the structure of the data that is provided by the OData service. As of now, the TreeBinding cannot provide a reliable count because it is always dependent on the number of expanded levels and therefore the export will request data as long as the service can provide it.
And here comes the tricky part: During export, the requests receive a certain $skip and $top but there is no max count where the export stops. This means that your service needs to implement $skip and $top properly, otherwise it will fetch the same data over and over again.
Regards Sebastian
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
Hi Rajesh
Did you found any workaround? Stucked with same issue. Kindly share if you got anything to remove filters in export.
Thanks,
Chandran
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.
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
Then I suggest to raise a question in the SAPUI5 Q&A area.
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
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
Hallo Andre Fischer ,
Can we a make a line item field Editable Using adaptation editor.
Thanks,
Vijay
Can you explain how this question is related to this post?
The downloads are read only.
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
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.
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
Hello Hubert,
this is not supported for a simple reason. The UI5 client export enables sort and filter options in the column headers by default. If certain columns are getting merged, it will prevent the sort and filter unless all merged cells have the same size.
Regards Sebastian
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.
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
Hello Dmitri,
this is possible with SAPUI5 1.100 and later. Further details can be found here:
Date,Time, andDateTime)Please keep in mind that the utc property will be true by default. In case you want the users local timezone, you will have to set it to false explicitly. Beside that, it is possible to assign a custom timezone or reference a property on the line item that provides a valid IANA timezone. This allows scenarios like presenting flight data, where departure and arrival are line item specific because of the geographical location of the airports.
Regards Sebastian
Hi Andre Fischer,
Is it possible to protect the sheet that would be exported?
Thank you!
Regards,
Nino
What do you mean by "protection"?
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
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
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
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?
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:
A sample can be found here.
Regards Sebastian
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
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
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
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