AdapTable Version:

Framework:

Formatting Report Data

Summary

AdapTable provides a range of options to allow developers to configure how report data is formatted
These include choosing whether to export the "display" (rather than "raw") value of the cell
Additionally Dates can be given a bespoke format

AdapTable allows developers to configure how the data in exported Reports is formatted.

These is done via 2 (similar-sounding) properties in Export Options:

exportDataFormat - use the Display Value (instead of the Raw Value) - for all, or a subset of, Columns
exportDateFormat - provides a bespoke export format for Date columns

Hint

See Processing Reports for an even more fine-grained approach, where the exported values can also be changed

Display Values

By default AdapTable exports the underlying raw data in AG Grid.

Note

A value of 12.59 which has been formatted as "£12.59 (GBP)" will have the raw value sent to Excel
This means that it can be identified as a number and treated accordingly

This is by design: AdapTable provides a data export function not a WYSIWYG operation.

Hint

The sole exception is when using the VisualExcel Report Format Type which always and only exports Display Values

If this behaviour is not wanted, it can be changed using the exportDataFormat property in Export Options.

This property allows you configuring exporting using the Display Value for:

All Columns in all exports
by Column Data Type (i.e. different rules for string, number and date Columns)
on a per-Column basis (by using a function)

exportDataFormat

Default: 'rawValue'

DataFormatType | DataFormatDataType

Format of exported data

By default AdapTable exports the underlying raw data in AG Grid.

Note

This property is ignored when using the VisualExcel Report Format Type as it only exports Display Format

Set the exportDataFormat property to formattedValue to export the Display Value of the column instead.

Hint

The display value could be supplied by an AG Grid ValueFormatter or ValueGetter
Alternatively - and preferably - it can use AdapTable's Column Formatting

As can be seen from the property definition, it provides 3 possible return types:

exportDataFormat?:
  | DataFormatType
  | DataFormatDataType
  | ((context: AdaptableColumnContext) => DataFormatType);

1. DataFormatType

The most straightforward is to return a DataFormatType object which will be applied to every column being exported.

This object returns either rawValue (the default) or formattedValue (which returns the display value).

// Set the Format Type to be `formattedValue' in place of the default of `rawValue`
const adaptableOptions: AdaptableOptions = {
    exportOptions: {
        exportDataFormat: 'formattedValue'
    }
}

2. DataFormatDataType

Alternatively you can set the exportDataFormat property separately for each of the date, number and text Data Types.

This is done by returning a DataFormatDataType object defined as follows:

Property	Description
date	Data Format type for Date columns
number	Data Format type for Number columns
text	Data Format type for String / Text columns

In this case, all columns with a Data Type specified with formattedValue will export display values.

// Set text and number columns to use `formattedValue'
const adaptableOptions: AdaptableOptions = {
    exportOptions: {
        exportDataFormat: {
            text: 'formattedValue',
            date: 'rawValue', // not actually required as this is default value
            number: 'formattedValue',
        },
    }
}

Caution

The exportDateFormat property has higher precedence than exportDataFormat
It will be applied to all exported Date columns even if both properties have been set

3. Function returning a DataFormatType

The 3rd option is to return a DataFormatType object but via a function.

Every column which the function returns as 'formattedValue' will be exported with its Display Value.

The function receives a AdaptableColumnContext which contains the column to check:

Property	Description
column	The current Column
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`

// Set the Format Type to be `formattedValue' for Language column and all numeric columns
const adaptableOptions: AdaptableOptions = {
  exportOptions: {
    exportDataFormat: (context: AdaptableColumnContext) => {
      return context.column.dataType == 'number' || context.column.columnId == 'language'
        ? 'formattedValue'
        : 'rawValue';
    },
  }
}

Formatting Report Values

Fork Fork

In this demo we set up export-formatting in Export Options to export the formatted (i.e. display) cell value rather than the underlying value
We have set 3 Display Formats in the Grid and in each case the display value is exported:
- Language column is in upper case
- Created and Pushed columns have a format of 'yyyyMMdd'
- All numeric columns display 8 digits

Try It Out

Run a Report and notice that the Language, Created, Pushed and numeric columns keep the Display Format they were given in Initial State

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Export Formatting',
  exportOptions: {
    exportDataFormat: 'formattedValue',
  },
  initialState: {
    Dashboard: {
      Tabs: [
        {
          Name: 'Demo',
          Toolbars: ['Export'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Export'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-language',
          Scope: {ColumnIds: ['language']},
          DisplayFormat: {
            Formatter: 'StringFormatter',
            Options: {
              Case: 'Upper',
            },
          },
        },
        {
          Name: 'formatColumn-created_at',
          Scope: {ColumnIds: ['created_at', 'pushed_at']},
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyyMMdd',
            },
          },
        },
        {
          Name: 'formatColumn-number',
          Scope: {DataTypes: ['number']},
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              IntegerDigits: 8,
              IntegerSeparator: '',
            },
          },
        },
      ],
    },
    Export: {
      CurrentReport: 'Current Layout',
      CurrentFormat: 'Excel',
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'created_at',
            'updated_at',
            'pushed_at',
            'license',
            'has_wiki',
            'github_watchers',
            'description',
            'open_issues_count',
            'closed_issues_count',
            'open_pr_count',
            'closed_pr_count',
            'has_projects',
            'has_pages',
            'week_issue_change',
            'topics',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

Formatting Dates

Dates can offer additional complexity in terms of formatting export data.

It is not unusual that a user will want to export a date differently both to the raw value and to its display value.

For this reason AdapTable provides the exportDateFormat property in Export Options.

exportDateFormat

string

Custom format for exporting Date columns

This property enables you to export all dates in a custom format, irrespective of how they are formatted in AG Grid.

Caution

This property is ignored when running the VisualExcel Report Format Type as it exports the Display Format

// Provide a custom format for date columns
const adaptableOptions: AdaptableOptions = {
    exportOptions: {
        exportDateFormat: 'yyyy-dd-MM',
    }
}

AdapTable exports all dates in the custom format provided, irrespective of how they are formatted in AG Grid.

Note

The exportDateFormat property has higher precedence than exportDataFormat
It will be applied to all exported Date columns (including if the column has a valueFormatter)

Formatting Dates in Reports

Fork Fork

This demo illustrates how to use the exportDateFormat property in Export Options to override any formatted dates:
- The Created and Pushed columns have a Display Format of 'yyyyMMdd'
- The Updated Column has no Display Format
We have set exportDateFormat to 'yyyy-dd-MM'
This takes precedence over the formats used in AG Grid (even though we also set exportDataFormat to be formattedValue)

Try It Out

Export the data from the Grid and notice that the Language column keeps the format it was given in Initial State (since we export display values)
But the date columns all use the format giving in Export Options, overriding what is displayed in AG Grid and what formats they have been given

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Export Date Formatting',
  exportOptions: {
    exportDateFormat: 'dd-MMM-yyyy',
    exportDataFormat: 'formattedValue',
  },
  initialState: {
    Dashboard: {
      Tabs: [
        {
          Name: 'Demo',
          Toolbars: ['Export'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Export'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-language',
          Scope: {ColumnIds: ['language']},
          DisplayFormat: {
            Formatter: 'StringFormatter',
            Options: {
              Case: 'Upper',
            },
          },
        },
        {
          Name: 'formatColumn-created_at',
          Scope: {ColumnIds: ['created_at', 'pushed_at']},
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyyMMdd',
            },
          },
        },
      ],
    },
    Export: {
      CurrentReport: 'Current Layout',
      CurrentFormat: 'Excel',
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'created_at',
            'updated_at',
            'pushed_at',
            'license',
            'has_wiki',
            'github_watchers',
            'description',
            'open_issues_count',
            'closed_issues_count',
            'open_pr_count',
            'closed_pr_count',
            'has_projects',
            'has_pages',
            'week_issue_change',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

PreviousCustom Destinations NextProcessing Reports

Framework: