This challenge often arises when extending cloud applications such as SAP SuccessFactors or SAP Ariba, which offer only a general OData service, making it difficult to export filtered data into useful formats like Excel. But as our SAP Developer Architect, Petr Bečka, explains, there is an elegant solution using sap.ui.export.Spreadsheet.

The Challenge: No dedicated OData service

Many cloud applications do not provide a dedicated OData service for every use case. For example, in SAP SuccessFactors, you might not have an individual service for each dataset or report. But this doesn’t mean you can’t get your data in a usable format. By leveraging a generic OData service, we can capture filters and selection settings from the user and use them to export exactly what the user needs.

„Even though this scenario isn’t as straightforward as using a dedicated OData service, it still offers a quick and clean solution for exporting data to Excel,“ Petr Bečka notes.

The Solution: Using SmartTable and Excel Export

In SAPUI5, the SmartTable control is a popular way to display data, as it can be customized with filters, columns, and sorting options. Here’s how we can approach the Excel export process without a dedicated OData service:

1. Capture Filters and Selection Settings

   By subscribing to the beforeRebindTable event in SmartTable, we can capture the current filters and selected columns the user has set up. This allows us to dynamically export only the data that is relevant to the user’s current view.

2. Retrieving Data

   Once the user clicks the Export to Excel button, we can use the captured filters and selection parameters to call a generic OData service (such as the one in SAP SuccessFactors) and fetch the required data.

3. Storing Data in a JSONModel

   After retrieving the data, we store it in a JSONModel, which is compatible with the sap.ui.export.Spreadsheet.

4. Exporting to Excel

   Finally, the data is formatted and exported using the sap.ui.export.Spreadsheet API. We define the columns, data types, and file settings to generate a clear and accurate Excel file.

Step-by-Step Implementation

Advantages and Extensions

This solution is particularly useful when dealing with existing cloud applications where a specific OData service may not be available. By leveraging the generic OData service and using sap.ui.export.Spreadsheet, developers can quickly add Excel export functionality without the need for complex backend modifications.

Additional benefits include:

• Dynamic column definitions: You can define columns dynamically based on SmartTable metadata.
• Advanced formatting: Displaying icons or formatting cells is supported, though it requires additional configuration.
• Paging: For large datasets, consider adding paging or asynchronous data fetching to prevent overloading the servic

By using SAPUI5 and a generic OData service, we’ve created an elegant and efficient way to export data from cloud applications to Excel without needing a dedicated OData service. This approach is especially useful for SAP SuccessFactors or other cloud-based applications, where custom services are not always available.

„It improves efficiency, reduces the need for custom backend development, and provides end-users with the ability to export exactly what they see, in a format that is easy to work with,“ says Petr Bečka.

We look forward to hearing your thoughts on this approach and discussing further improvements!