Data Types for Spreadsheet Export

Provides details about the data types supported by the spreadsheet export.

All data types supported by the spreadsheet export are listed in the sap.ui.export.EdmType enumeration. During the export, the values are converted to a value that is in compliance with the Office Open XML specification.

`String`

The type String handles textual values within cells. Strings are usually not formatted. The internal type text is the default type that gets applied if no other type is configured for a column.

It is possible to aggregate several property values into one column, for example, firstname and lastname are combined to fullname. This can be achieved by providing an array of property names within the property property of the column definition. In addition to that, the template property must be made available. This additional property has to be a non-empty string and can contain placeholders. A placeholder is a numerical index enclosed by curly brackets. The index must be greater than or equal zero and less than the length of the array that is assigned to the property property.

Additional Property	Type	Sample	Optional	Description
`inputFormat`	`string`	`"([0-9]{3})([0-9]{4})"`	Yes	Used as a regular expression that determines specific parts of the original value. This makes it possible to apply the template to a single property instead of multiple properties. `inputFormat` is ignored if no `template` is provided.
`template`	`string`	`"{0} (Company code {1})"`	Yes	A textual template that can be filled with multiple values from various business objects. Each placeholder is a number within curly brackets that represents an index of a property array.
`wrap`	`boolean`		Yes	A Boolean value that indicates if the text column supports wrapping of the cell content. Apart from automatic text wrapping depending on the cell width, it automatically converts all \n to \r\n line breaks. These manual line breaks are then visible in the generated xlsx file.

Additional Property

Type

Sample

Optional

Description

inputFormat

string

"([0-9]{3})([0-9]{4})"

Yes

Used as a regular expression that determines specific parts of the original value. This makes it possible to apply the template to a single property instead of multiple properties.

inputFormat is ignored if no template is provided.

template

string

"{0} (Company code {1})"

Yes

A textual template that can be filled with multiple values from various business objects. Each placeholder is a number within curly brackets that represents an index of a property array.

wrap

boolean

Yes

A Boolean value that indicates if the text column supports wrapping of the cell content. Apart from automatic text wrapping depending on the cell width, it automatically converts all \n to \r\n line breaks. These manual line breaks are then visible in the generated xlsx file.

Here is an example for a String column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
                property: ["Firstname", "Lastname"],
                label: "Full name",
                width: 25,
                template: "{1}, {0}"
            }
        ]
    }
}
 
 
// This will result in "Doe, John" if the line item is {Firstname: "John", Lastname: "Doe"}

`Boolean`

The type Boolean handles all variations of Boolean values. It allows for displaying these Boolean values in a pre-defined format. There are additional properties that are handled by this type to format their respective values. Since a Boolean type can be either true or false, the additional properties must be maintained for both cases for the type to take effect.

Additional Property	Type	Description
`trueValue`	`string`	Defines the textual representation of a Boolean type that has the value `true`.
`falseValue`	`string`	Defines the textual representation of a Boolean type that has the value `false`.

Here is an example for a Boolean column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
                property: "Onstock",
                label: "Availability",
                type: sap.ui.export.EdmType.Boolean,
                trueValue: "On stock",
                falseValue: "Out of stock"
            }
        ]
    }
}

`Number`

The type Number represents a simple numeric value without any specific formatting. The value is displayed the way it is. For further adjustment use the additional properties scale, delimiter, unit, and unitProperty.

Additional Property	Output Sample	Type	Optional	Description
`scale`	1234 1234.5 1234.56 1234.567	`number`	Yes	Sets a fixed number of decimals. The scale is applied to the whole column and displays exactly the number of decimals that is configured. If the actual value has fewer or more decimal places, it is filled with additional zeros or gets cut off to match the configured number of decimals. This property accepts a positive integer value. Negative values are treated like zero.
`autoScale`	17.493 kg 60 mp/h	`boolean`	Yes	Allows you to automatically apply a specific scale for the given unit of measure. This functionality requires a particular unit of measure that is set via the `unit` property or a `unitProperty` property that can be used to resolve the specific unit during runtime. In addition to that, a code list is required to evaluate the corresponding scale. These code lists are available when using `ODataListBinding` as `dataSource` during initialization of the `Spreadsheet` class. If no code list is available or the code list does not contain a particular unit of measure, the `scale` property is taken into account. If the `scale` property is not provided, the number is displayed in raw format.
`delimiter`	1,234 1,234,567	`boolean`	Yes	Specifies if the numeric value is shown in groups of thousands. If set to `true`, the thousands delimiter is shown. The delimiter is embedded in the number format and therefore requires a particular scale. If the `scale` property is not defined, this is treated like a scale of zero, and float values might get rounded down . Default value is `false`.
`unit`	623 kg 89 % 120 km/h	`string`	Yes	Specifies the unit of measurement (UoM). The UoM is shown next to the numeric value. Note The UoM is treated as a string and therefore has no influence on the value itself. For %, mio, or similar UoMs this can make a difference if the values are used in forms because multiplying by 150 % would mean x * 150 instead of x * 1.5.
`unitProperty`	623 kg 89 % 120 km/h	`string`	Yes	References a business object property that contains the UoM for this particular numeric type.

Here is an example for a Number column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
                property: "Weight",
                label: "Net weight (kg)",
                type: sap.ui.export.EdmType.Number,
                unit: "kg",
                scale: 3
            },
            {
                property: "Weight",
                label: "Net weight (g)",
                type: sap.ui.export.EdmType.Number,
                unit: "g",
                scale: 0,
                delimiter: true
            }
        ]
    }
}

`Date`, `Time`, and `DateTime`

The types Date, DateTime, and Time handle the date and time information. The application can pass additional parameters to adjust the visible representation of these types.

Type	Output Sample	Description
`Date`	03/24/2017 24.03.2017	Represents a date without time-related information. Due to the use of built-in formats, `Date` is displayed based on the user's locale in the operating system. This can lead to different representations for different users.
`DateTime`	08/31/2016 23:01 31.08.2016 23:01	Represents values that contain date- and time-related information. Due to the use of built-in formats, `DateTime` is displayed based on the user's locale in the operating system. This can lead to different representations for different users. The locale has no effect on any time zone formatting. All values in columns of type `DateTime` are related to UTC because it is not possible to pass time zone offset information into the Office Open XML standard representation of time stamps. For columns of type `DateTime`, a UTC suffix is automatically added to the column header.
`Time`	13:21:14	Represents values that contain time-related information only. Time information can use the following units: hours, minutes, seconds, and milliseconds. Contrary to `Date` and `DateTime`, the built-in formats for `Time` are the same for every locale in the operating system.

Type

Output Sample

Description

Date

03/24/2017

24.03.2017

Represents a date without time-related information. Due to the use of built-in formats, Date is displayed based on the user's locale in the operating system. This can lead to different representations for different users.

DateTime

08/31/2016 23:01

31.08.2016 23:01

Represents values that contain date- and time-related information. Due to the use of built-in formats, DateTime is displayed based on the user's locale in the operating system. This can lead to different representations for different users.

The locale has no effect on any time zone formatting. All values in columns of type DateTime are related to UTC because it is not possible to pass time zone offset information into the Office Open XML standard representation of time stamps. For columns of type DateTime, a UTC suffix is automatically added to the column header.

Time

13:21:14

Represents values that contain time-related information only. Time information can use the following units: hours, minutes, seconds, and milliseconds. Contrary to Date and DateTime, the built-in formats for Time are the same for every locale in the operating system.

Additional Property

Type

Optional

Description

calendar

string

Yes

Allows users to select a calendar other than the Gregorian calendar. The following values are possible:

Islamic
Japanese
Gregorian (default)

The calendar property supports values of type sap/base/i18n/date/CalendarType although not all options are available for the export. Islamic and Japanese representation only takes effect if the displayed Date/DateTime shows some date-related information. Therefore, it does not make sense to use it for the type Time.

If the calendar property has not been defined, the sap/base/i18n/Formatting.getCalendarType function is used to determine the default value. In a SAP Fiori launchpad environment, the sap/base/i18n/Formatting.getCalendarType function returns the configured sap/base/i18n/date/CalendarType. If this function does not provide any sap/base/i18n/date/CalendarType, it automatically uses sap/base/i18n/date/CalendarType.Gregorian as fallback. If no value has been set, Formatting.getCalendarType automatically uses the calendar type preferred by the current session locale.

format

string

Yes

Defines a specific format that gets applied to Date/DateTime/Time. The format overrides the default formatting of the respective type, so you can assign a format that shows only time-related information even to a column of type Date, which usually shows no time-related information.

If the format property has not been defined, the sap/base/i18n/Formatting.getDatePattern and sap/base/i18n/Formatting.getTimePattern functions are used to determine the default format pattern. In a SAP Fiori launchpad environment, these functions return the patterns configured in the settings of the SAP Fiori launchpad. While columns of type sap.ui.export.EdmType.Date require a date pattern and columns of type sap.ui.export.EdmType.Time require a time pattern, for columns of type sap.ui.export.EdmType.DateTime, both patterns need to be maintained. If no related pattern is available, the format property remains undefined.

The format template needs to match the following regular expression to be valid:

/^[gdhmsy\s-,.:/]+(AM\/PM)?$/

The list below shows some sample formats and their output:

Format Template	Output Sample
yyyy-mm-dd h:mm	2007-12-24 18:21
h:mm:ss AM/PM	9:32:24 AM
d-mmm-yy	12-Apr-17
dddd, d.mmmm yyyy	Wednesday, 22. April 2017

inputFormat

string

Yes

Allows you to parse a textual date representation based on a given pattern. The pattern has to be provided as a string with the literals y, m, d. Other literals are ignored.

Value	Input Format	Parsed Value
20200123	yyyymmdd	01/23/2020
2020-04-21	yyyy-mm-dd	04/21/2020

inputFormat is case-insensitive.

utc

boolean

Yes

The date and time information is exported based on the UTC time zone. The default value is true. If this value is set to false, the date and time information will be exported in the user's local time zone.

Note

This property only affects the DateTime type.

timezone

string

Yes

The date and time information is exported based on the given IANA (Internet Assigned Numbers Authority) time zone. Each cell contains a reference to the time zone that is applied. If no time zone is given or if the time zone is invalid, it will fall back on the user's local time zone or UTC, depending on the utc property of the column. The default value is undefined.

Note

This property only affects the DateTime type.

timezoneProperty

string

Yes

References the OData property that contains the IANA time zone, which is used to format the date and time information of a particular column per line item. This makes it possible to have different time zones within one column. If the referenced timezoneProperty does not contain a valid IANA time zone, it will fall back on the time zone that is assigned via the timezone property. The default value is undefined.

Note

This property only affects the DateTime type.

displayTimezone

boolean

Yes

Defines whether the IANA time zone is displayed within a cell. The default value is true.

Note

This property only affects the DateTime type in combination with timezoneProperty.

Here is an example for a Date/DateTime/Time column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
                property: "Duedate",
                label: "Due date (islamic)",
                type: sap.ui.export.EdmType.Date,
                calendar: "islamic"
            },
            {
                property: "Createdat",
                label: "Created at",
                type: sap.ui.export.EdmType.DateTime,
                format: "dddd, d.mmmm yyyy"
            },
            {
                property: "Dailymeeting",
                label: "Daily meeting",
                type: sap.ui.export.EdmType.Time,
            },
            {
                property: "stringDate",
                label: "Textual date representation",
                type: sap.ui.export.EdmType.Date,
                inputFormat: "yyyymmdd"
            }
        ]
    }
}

`Currency`

The type Currency handles currencies as an aggregation of a value and a particular UoM. This type might apply various styles on cell level because the scale of each currency cell depends on the corresponding UoM which in turn might vary for various cells in a currency column. The Currency type inherits from the number type but provides additional properties, including the unitProperty property as a mandatory property.

Additional Property	Type	Mandatory	Description
`unitProperty`	`string`	Yes	References the business object property that contains the UoM for this particular currency. This property is required even if the UoM is not displayed.
`displayUnit`	`boolean`	No	Defines if the UoM is shown in the column. If set to `true`, the UoM is displayed after the actual value. The default value is `true`.
`scale`	`integer`	No	Property that is equivalent to the `scale` property of the internal numeric type. It applies a fixed number of decimals to all cells within the currency column regardless of the corresponding UoM.

Here is an example for a Currency column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
             type: sap.ui.export.EdmType.Currency,
                property: "Amount",
                label: "Price",
                unitProperty: "Currency"
            }
        ]
    }
}

`Enumeration`

The type Enumeration is used for mapping values to a particular key. This is useful if your SAPUI5 application is using formatters instead of raw data to display meaningful content because formatters are not supported directly.

Additional Property	Type	Mandatory	Description
`valueMap`	`object\|map`	Yes	Contains `object` as an associative array or `map`, which holds all the key value pairs that are used for mapping the raw data to an explicit value. The raw data is used as a key to look up the actual value.

Here is an example for an Enumeration column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
              type: sap.ui.export.EdmType.Enumeration,
                property: "Shipping",
                valueMap: {
                    a: "Standard Shipping",
                    b: "Premium Shipping",
                    c: "Express Shipping"
                }
            }
        ]
    }
}

`BigNumber`

The type BigNumber is used to represent numbers that contain more than 15 digits. This data type is required because of the internal number representation of Microsoft Excel as defined by the IEEE (Institute of Electrical and Electronics Engineers). This means that all numbers that contain more than 15 digits are filled with zeros at the end. This affects precision of the values although the difference is really small compared to the total amount. The BigNumber type inherits from the Currency type and uses the same properties as Currency and its superordinate class Number. This type creates a textual output which is why it is not possible to do any calculation with these values.

`Percentage`

The type Percentage represents numeric values that are transformed into percentage. The raw value 1 corresponds to 100%.

Additional Property	Output Sample	Type	Description
`scale`	89.706%	`number`	Sets a fixed number of decimals. The scale is applied to the whole column and displays exactly the number of decimals that is configured. If the actual value has fewer or more decimal places, it is filled with additional zeros or gets cut off to match the configured number of decimals. This property accepts a positive integer value. Negative values are treated like zero.
`delimiter`	1,754%	`boolean`	Specifies if the numeric value is shown in groups of thousands. If set to `true`, the thousands delimiter is shown. The delimiter is embedded in the number format and therefore requires a particular scale. If the `scale` property is not defined, this is treated like a scale of zero, and float values might get rounded down . Default value is `false`.

Here is an example for a Percentage column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
	    	type: sap.ui.export.EdmType.Percentage,
                property: "Fraction",
                scale: 2,
		delimiter: false
            }
        ]
    }
}

`Timezone`

The type Timezone represents IANA time zone keys that are translated based on the current locale. This type behaves similar to Enumeration but does not require any additional properties to resolve the values.

Here is an example for a Timezone column:

var exportConfiguration = {
    workbook: {
        columns: [
            {
		label: "Timezone (Departure)",
		type: sap.ui.export.EdmType.Timezone,
		property: "TimezoneID",
		width: 25
            }
        ]
    }
}

Related information

API Reference: sap.ui.export.EdmType

Data Types for Spreadsheet Export

Data Types for Spreadsheet Export

String

Boolean

Number

Date, Time, and DateTime

Currency

Enumeration

BigNumber

Percentage

Timezone

In this section:

Related information

`String`

`Boolean`

`Number`

`Date`, `Time`, and `DateTime`

`Currency`

`Enumeration`

`BigNumber`

`Percentage`

`Timezone`