Skip to Content
Product Information
Author's profile photo Tayane Mazzarino

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.).

Screenshot

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.

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo ANURAG PRADHAN
      ANURAG PRADHAN

      Hello Tayane,

      Can we default (Marked as X) checkbox for 'split cells with multiple values' while executing 'Export As'.

      Similar to the screen shot mentioned in the blog.

      Regards,

      Anurag.

      Author's profile photo Tayane Mazzarino
      Tayane Mazzarino
      Blog Post Author

      Dear Anurag,

       

      Currently, the field "Split cells with multiple values" is available as an option that always need to be checked and it's not possible to set it as default.

      However, this is really a valid request and you can register an enhancement request using the Customer Influence and Adopt site. You can check the following documentation:

      How to: Enhancement Request Process using the Customer Influence and Adopt site

       

      Best regards,

      Tayane,

      SAP Support.