Show TOC

Excel SupportLocate this document in the navigation structure

The SAP Gateway framework provides Excel support, that is, support of XLSX.

For OData requests reading an entity set an additional format is supported by SAP Gateway:
  • Provided via URL parameter $format=xlsx
  • Or provided via HTTP header accept = application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

In this case the response is not a JSON or an Atom/XML but the binary representation of an Excel file of type *.xlsx.

Features

For entity sets the client can request the format XLSX and then retrieves an XLSX instead of a "normal" OData response.

For column headings the property labels are used. If they are initial property names are used.

Complex types are supported. The corresponding properties are shown like simple properties, that is, the table is flattened.

Entity sets for entity types that have a base type are supported.

$select is supported. Only columns as requested via $select are added. Note that $select does not support properties of complex types. But it does support complex properties "on the first level".

The order of $select for "not inherited" simple properties is used as the order of the columns.
  • Columns for properties of the base type of an entity will been shown in the end (in front of complex types).
  • Columns for properties of complex types are always shown in the end.

The headings for properties of complex types are prefixed with the label/name of the corresponding complex property.

The entity container in the $metadata document of all services has a new annotation listing the supported formats: 
sap:supported-formats="atom json xlsx"

The time for the XLSX generation is shown in the SAP Gateway performance trace.

The SAP Gateway Client offers $format=xlsx via the Add URI Option button. For XLSX the SAP Gateway Client shows in the response area the text "Click "Response in Browser" to display data in the right format."

Common HTTP headers (such as sap-metadata-last-modified) are returned together with the XLSX file.

Currency and quantity references are provided to the ABAP List Viewer export tool and used there to show the correct number of decimal points.

Properties with the SAP-semantic "URL" are created as "Link to URL" columns in the ABAP List Viewer export tool. The corresponding Excel cells will then contain hyperlinks.

Warnings returned by the ABAP List Viewer export tool are written into the application log. Errors returned by the ABAB List Viewer export tool are returned as HTTP 500 errors.

The HTTP response header content-disposition is filled to suggest as file name the entity set name to the client: 
content-disposition: attachment; filename=Employees.xlsx
Restrictions
Note the following restrictions when using XLSX:
  • XLSX is only supported for entity sets.
  • XLSX is not supported for batch requests.
  • XLSX is not support for $expand.
  • XLSX is only supported if the SAP Gateway server is on a SAP NetWeaver 7.40 release.
  • There will be no special handling of next links. For example, the framework will not try to create a "larger" Excel by calling the provider application again if the first response contained a NEXT link.
  • For column headings the property labels are used. If a label is initial the property’s name is used. The heading annotations are not used.
  • $select does not support properties of complex types.
  • The order of the properties in the $select (controlling the column order) is only considered for "not inherited" simple properties.
    • It is not considered for properties of complex types.
    • It is not considered for properties inherited from a base type.
  • The data formatting in the XLSX file is not identical to the formatting in the OData XML or OData JSON response.
    • It does not reflect the EDM type definition but is only based on an internal SAP Gateway ABAP representation of the data..
    • Examples:
      • GUID: '005056BA3A451ED48D9E3C2C2FDEB559' vs. '005056BA-3A45-1ED4-8D9E-40ED5ADC3559'
      • Time: '361' vs. '6:01'
      • Left aligned numbers vs. right aligned numbers. Currency and unit amount fields are, for example, left-aligned and not marked as numbers in the XLSX file if the provider application leaves the conversion to the framework.
      • NUMC fields are shown as text in the Excel