Using the “Export to Spreadsheet” function to get merged data into individual columns

Hello community!

In this blog post I will show you a simple way to separate merged data before you even download the spreadsheet. This way you don’t need to use formulas to split the cells anymore.

By default, the “Export to Spreadsheet” button is enabled in list report tables. In the object page, the feature is available by default if the copy/paste feature is also available. Otherwise, the feature is disabled in the object page. When enabled, the ‘Export to Spreadsheet’ button is displayed in the table toolbar. It triggers the export of the values in the table.

This topic is currently only applicable to SAP Fiori elements for OData V4.

Sometimes, when exporting to excel requires formulas to separate data to be able to use it. This is because the standard export to excel does not split cells with multiple values. However there’s an option that allows you to split these mutiple values before downloading it. This is very useful, once that saves time and automates the process.

In order to get the merged data into the individual columns, please follow the steps below:

1. Click on the arrow down button next to the “Export to Spreadsheet” button.

2. Select “Export As…” from the drop-down menu.

3. Tick the “Split cells with multiple values” checkbox (when selecting this option, the exported spreadsheet has one column per value. If one column contains multiple values in a cell (for example, amount and currency/unit), this results in two separate columns in the spreadsheet.).

4. Confirm your selection by clicking on “Export” button.

The data from the app will be exported to the spreadsheet and the columns will be separated. You should be able to apply the filter on any individual column.

Note that, once you click in “Split cells with multiple values”, it will apply to all columns available for viewing in the app and some columns can be duplicated. In case you don’t want to have the description column duplicated in the spreadsheet, you can deactivate it in your personalized settings. You can do this by clicking on the “Settings” icon/button and deselecting corresponding check-box. 

Restrictions

• The columns containing a DataFieldForAnnotation that points to Charts or a DataFieldForAction are not exported.
• The numbers that exceed 15 digits are rounded in the spreadsheet file (this is a JavaScript restriction).

Hope this information is useful for you! I would greatly appreciate if you could share your feedbacks and thoughts in the comments.

Also, I encourage you to browse for other Community Topics that may be useful for you.

Thank you so much!

Regards,

Tayane.