Skip to content

Export to Excel Extension

OData provider provides support for Excel type response format in its Export to Excel Extension. From this service, an entity collection can be exported (downloaded) as an Excel file with file type '.xlsx'.

Export to Excel extension has been exposed as a REST API end point separating it from OData provider core.

Architecture

In order to generate the Excel output, a custom OOXML-Excel serializer pluggable, developed using Apache POI - Streaming XSSF (SXSSF) API is used in-place of Apache Olingo serializers (ODataXmlSerializer/ ODataJsonSerializer). Generated excel file will be invalidated in 5 minutes by default or the setting specified by -Dodp.extension.xlsx.cache.ttl.mins JVM Property

There are two ways in using this extension

Using only an HTTP GET request

Using HTTP POST + GET request combination

In this method, a GUID is generated as the Cache Id.

After HTTP POST request, the Excel File Download from Cache URL (which is used in following HTTP GET request) will expire if it is unused. Once HTTP GET request is performed, the parameters in the cache are removed. Therefore you can put only one HTTP GET request for a corresponding HTTP POST request.

Supported Types for Export

Export of following object types are supported.

  • Single Entity
  • Entity Collections
  • Exporting Structures (Complex Types)
  • List of Structures (Complex Collections)

The Export to Excel Extension Service of OData Provider facilitates operations such as renaming column names of Entities/Structures (complex types), ordering columns of Entities and string concatenation of Entity attributes.
For instance, you can concatenate two or more attributes/expanded attributes of string or integer datatype, to one single column using expressions.

Excel Extension Modes

There are two main modes available in the Excel Extension as DEFAULT and STREAMED (Experimental). These can be enabled/disabled by changing the parameter EXCEL_EXTENSION_MODE which is available in the System Parameters.

  • In the DEFAULT mode temporary files will be created in the middle-tier while exporting Excel files. If the Excel file is large, this can lead to pod evictions due to exceeding pod storage limits. The maximum Excel file which can be exported with this method depends on the pod ephemeral storage limit defined for the OData pod. For example, for a 20MB csv data the size of the temp file becomes more than 1GB. See here.
  • The STREAMED mode was introduced to overcome the pod evictions happen when trying to export large Excel files by writing the Excel file directly to the response output stream which avoids creating temporary files in the middle-tier. However, this mode is still in an experimental state and may change at short notice. Also, The columns in the exported Excel file from this mode will not be auto sized to fit the content and it can be later done with the use of AutoFit Column Width function available in the Microsoft Excel.

Note: Both modes supports exporting Excel files containing upto 1 million rows and 16000 columns (These are the maximum limits of Microsoft Excel workbooks. See here). But the DEFAULT mode is further limited by the pod ephemeral storage limit.