Data Types for Spreadsheet Export

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 requirements.

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.

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 amount 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 amount of decimals. This property accepts a positive integer value. Negative values are treated like zero.

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.

Additional Property

Type

Optional

Description

calendar

string

Yes

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

  • islamic

  • japanese

  • gregorian (default)

Choosing a calendar other than the Gregorian calendar overrides all type settings, such as the type or format template, for various reasons. 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. Due to the fact that these special representations rely on specific formats, it is not possible to merge them with built-in formats for date, dateTime, time, or even a custom format.

format

string

Yes

Defines a specific format that gets applied to date/dateTime/time. The format overrides the default formatting of the respective type which means that 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.

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

/^[dhmsy\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

Note

Office Open Spreadsheet dates can't handle time zone offset information.

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.

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: [
            {
                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: [
            {
                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.

Related information