Updated: December 8, 2023

Export to Spreadsheet

sap.ui.export.Spreadsheet

Intro

“Export to Spreadsheet” is a utility for exporting data from an app to a spreadsheet, enabling users to work with the data in common spreadsheet applications. Typical use cases are to mix the data with other sources, perform complex calculations, or change the layout of the data (for example, to present the content differently).

Usage

Use “Export to Spreadsheet” if:

  • Users need to work with the data in common spreadsheet applications.
  • Exported data should be the same as displayed (for example, taking visible columns, column order, filter, and sort settings into account).
  • The amount of data to be exported is limited.

Do not use “Export to Spreadsheet” if:

  • You want to export a complete database table. Use custom-built exporters instead.
  • You need to export large tables. Use custom-built exporters instead.

Behavior and Interaction

“Export to Spreadsheet” can be used in all cases where the exported data can be represented in a tabular format. The export is independent of the control used to display the data in the app. It can be used not only for tables, but also for all other controls that work internally with tabular or hierarchical data, such as charts.

Content Formatting

The exported file contains only simple read-only text content. Basic data type information can be configured per column, such as text, integer number, floating point number, percentage, date/time (UTC or local time), and so on. Since spreadsheet applications provide their own formatting for cell content,  SAPUI5 formatters (for example, for dates, times, and numbers) are not taken into account.

You can combine several data points and display them in one cell, or spread them over several cells.

In the spreadsheet files, tree structures are displayed using the grouping functionality (first seven levels) and double indentation (all levels).

Exporting Readable Texts

By default, the Export to Spreadsheet feature only uses data from the back end. Front-end formatting is not considered. For example, if the back-end database value is “F” and the value displayed in the front end is “Finished”, the exported value is still “F”.

To ensure that the data is exported as it is displayed on the screen, you can define key value pairs to overwrite the back-end values for a given column or columns (such as “F” for “Finished”).

If you opt to use this feature, bear the following in mind:

  • The correct translated text must be provided by the application. It isn’t done by the framework.
  • The text is just replaced, without additional formatting.

In this sample the exported texts for the Enumeration column in the exported file are identical with those shown on the UI, even though the database values are 1, 2, and 3.

Exporting

During the export, a modal progress dialog is displayed. The user can cancel the export at any time.

As soon as the spreadsheet file has been created, it is offered for download automatically.

Warning
The file size of the exported file is limited by the available browser memory. Exporting large tables can therefore lead to memory overflows and browser crashes.

The recommended maximum table size is 1 million table cells for desktop browsers or 100,000 table cells on tablets and phones.

For larger tables, consider using custom-built, specialized export solutions instead.

Exporting to a spreadsheet
Exporting to a spreadsheet

Components

Menu Button

Provide a split menu button for triggering the export (for example, on the toolbar of the corresponding control).

Design the menu button as follows:

  • The export icon starts the export with default settings, or custom settings chosen previously by the user in the Export As dialog.
  • The dropdown arrow opens a menu with two options:
    • Export

Exports the table with default settings, or custom settings chosen by the user via the Export As dialog.

    • Export As…

Opens a dialog for specifying the export settings. The settings are stored until the user changes them again.

For Export As…, use the shortcut: Shift+Ctrl+E

The tooltip label of the button is “Export Table

'Export to Spreadsheet' menu button
'Export to Spreadsheet' menu button

Export As Dialog

The dialog allows the users to define export settings, including:

  • The exported file name
  • The format for the exported file
  • An option to include the current filter settings. If the user selects this option, the current filter is included on a second sheet in the exported file.
  • An option to split cells with multiple values into separate columns.

Selected, the option exports data best suited for calculating and filtering it in a spreadsheet application.

When the option is unselected, the results in exported data with formatting best suited for presentations.

The primary action label is “Export” or, if cloud is enabled and selected as the destination, “Export To…”.

After the primary action is triggered, the settings are stored until the user changes them again.

Developer Hint
For smart tables, the menu button and dialog are available out of the box.

For freestyle apps, however, both the dialog and the triggering menu button must be implemented by the app team. The “export to spreadsheet” function provides only the underlying export functionality.

'Export As' dialog
'Export As' dialog

Export to Cloud

When cloud is enabled on the client’s instance, the dialog displays the Destination select field below the Format field. It opens a menu where the users can select Cloud, the default, or Local.

For the cloud destination, the primary action label in the dialog is “Export To…” and opens a cloud file browser where users choose the target location for the exported file through a remote repository.

Exporting to cloud with the cloud file browser
Exporting to cloud with the cloud file browser

Cloud File Browser

The cloud file browser is a tool that allows users to browse through a remote repository. It can be used to export data to the cloud or import files from the cloud to an SAP instance.

The control is composed of the following:

  • A file share select field
  • A file name field
  • A breadcrumb navigation
  • A responsive table with files and folders
  • A New Folder feature
  • The Export finalizing action and Cancel

After the users have chosen the file format in the Export As dialog, they cannot change it via the file browser. Instead, they need to cancel the file browser and return to the settings dialog.

To update an existing file, users can select the file in the list and export the updated version.

Cloud file browser with a selected file.
Cloud file browser with a selected file.

Depending on the file type, a Warning may inform the users that the file already exists and request user confirmation of the export.

Information
A future version of the guidelines will include an article on the cloud file browser.
Warning message for file update
Warning message for file update

Guidelines

Default Configuration

When you configure the exporter, ensure that your data is converted to a tabular representation. For example, when exporting a list item, configure the exporter to display each data point in a separate column.

For tables export the data as it is displayed on the screen:

  • Export all visible columns in the same order.
  • Combine data points as they are combined on the screen (for example text and ID in one cell).
  • Take the view settings (sort, group, filter, …) into account.
  • Use the same column header texts.
  • Do not include the filter settings.

When exporting, convert non-text elements to a text-only representation. Non-text elements include icons, images, micro charts, or controls like checkboxes and buttons. Converting them to text ensures that the data is not lost in the exported spreadsheet file.

Resources

Want to dive deeper? Follow the links below to find out more about related controls, the SAPUI5 implementation, and the visual design.

Elements and Controls

Implementation