Export Module

The Export Module provides a way to send data from AdapTable via saveable Reports that can be run either manually or on a schedule.

note

AdapTable will, by default, export the raw (i.e. underlying) value of any cell unless this is explicitly overriden

Adaptable exports both User-created Reports and pre-shipped System Reports to a number of destinations.

caution

Export is not a WYSIWYG operation - AdapTable exports report data and not styles or formats.

tip

The one exception to this is the Visual Data report which exports - to Excel only - row groups, formatted cells and anything style-related created in AdapTable (e.g. Conditional Styles, Format Column etc.)

Each Report has Row and Column Scope to allow you define precisely which Rows and Columns are exported.

Reports

There are 3 types of reports in AdapTable that you can export to multiple destinations.

  1. System Reports

  2. User Reports

  3. Custom Reports

Each Report contains a Definition which is essentially comprised of 2 details:

  1. Which Columns will be exported
  2. Which Rows will be exported

System Reports

AdapTable ships with 5 System (i.e. predefined) Reports designed for frequently used exports:

ReportContainsNotes
All DataAll Data in the Grid's DatasetIncludes filtered columns and rows
Current DataAll currently filtered columns and rowsIncludes data not in current viewport
Visual DataAll currently filtered columns and rowsExports styles and colours (to Excel only)
Selected CellsCurrently selected cells in the GridDo not need to be contiguous
Selected RowsCurrently selected rows in the GridDo not need to be contiguous
note

By default all 5 System Reports will be present; use the systemReportNames of Export Options to change this by providing a custom list.

adaptableOptions.exportOptions = {
// Don't use the 'Visual Data', 'Selected Cells' or 'Selected Rows' System Reports
systemReportNames: ['All Data', 'Current Data'],
}
tip

Leave the property empty to see all 5 System reports, or provide an empty array to have none available

User Reports

Users can provide their own reports which AdapTable will export to all available destinations.

User Reports can be either provided at design-time (through Export State) or created at run-time (via the Report Wizard).

Each User Report Definition will specify which columns and rows to export:

export default {
Export: {
CurrentReport: 'My Team Big Invoice',
Reports: [
{
Name: 'My Team Big Invoice',
ReportRowScope: 'ExpressionRows',
Query:{
BooleanExpression: '[currency]="EUR'
}
ReportColumnScope: 'ScopeColumns',
Scope: {
ColumnIds: [
'OrderId',
'ChangeLastOrder',
'ContactName',
'InvoicedCost',
'ItemCost',
'ItemCount',
'OrderCost',
'OrderDate',
],
}
},
],
}
}, as PredefinedConfig;

Custom Reports

Custom Reports are different to System and User Reports as they are entirely generated at run-time by the User with no input from AdapTable.

caution

Custon Reports do not need to include data (or columns) present in the grid in the exported data set.

Instead the data in the export is fetched each time the report is run via a function provided in Export Options

tip

This allows you to run bespoke reports while leveraging AdapTable state, scheduling & report destinations

To define a Custom Report you need to do 4 things:

  1. Set the ReportColumnScope to CustomColumns in Export Config
  2. Set the ReportRowScope to CustomRows in Export Config
  3. Provide an entry for the CustomReport in the customReports section of Export Options

The CustomReport is a basic object with just 2 properties:

PropertyDescription
nameName of the Report
onRunReportFunction invoked to return the data (in the form of a ReportData object)

The onRunReport JavaScript function has no parameters and returns a ReportData:

onRunReport: () => ReportData;

ReportData Object

The ReportData object is defined as follows:

PropertyDescription
columnsColumns in the Report
rowsRows in the Report

The columns are of type AdaptableColumnBase which is a reduced version of Adaptable Column with just these properties:

PropertyDescription
columnIdName of Column in underlying grid (e.g. field or colId)
dataTypeDataType of the column
friendlyNameHow Column is referred to in Adaptable UI; 'Caption' or 'Header' property in underlying grid

The rows property is defined as follows:

rows: Record<string, any>[];

Example

// In Predefined Config define the Report
// and provide a name for the CustomReport Function
export default {
Export: {
Reports: [
{
ReportColumnScope: 'CustomColumns',
ReportRowScope: 'CustomRows',
Name: 'People Report',
},
],
CurrentReport: 'People Report',
}
}, as PredefinedConfig;
// In Export Options provide report implementation
adaptableOptions.exportOptions = {
customReports: [
{
name: 'People Report',
onRunReport: () => {
// this typically calls the server but here we just provide dummy data
// takes the form of a ReportData object, with 'columns' & 'rows' sections
const reportData: ReportData = {
columns: [
{
ColumnId: 'Name',
FriendlyName: 'Name',
DataType: 'String',
},
{
ColumnId: 'Age',
FriendlyName: 'Age',
DataType: 'Number',
},
{
ColumnId: 'Location',
FriendlyName: 'Location',
DataType: 'String',
},
],
rows: [
{ Name: 'Joe', Age: 52, Location: 'London' },
{ Name: 'Dawn', Age: 39, Location: 'New York' },
{ Name: 'Peter', Location: 'France' },
{ Name: 'Eleanor', Age: 525 },
],
};
return reportData;
},
},
]
}

System Export Destinations

A report can be sent to a number of different locations including:

  • Excel

    warning

    Excel Export is only available when using AG Grid Enterprise and the correct modules are loaded

  • CSV

  • JSON

    tip

    The Export to JSON sends the data using the ReportData format

  • Clipboard

and also - depending on whether the appropriate plugin is loaded:

By default all 4 Export Destinations will be available; use the systemExportDestinations of Export Options to change this by providing a custom list:

adaptableOptions.exportOptions = {
// Only make the 'Excel' and 'CSV' system destinations available
systemExportDestinations: ['Excel', 'CSV'],
}

Custom Export Destinations

If the System Export Destinations offered by AdapTable are insufficient, developers can easily add their own custom report destinations via Export Options.

It contains a collection of CustomDestination objects defined as follows:

PropertyDescription
formOptional Adaptable Form - if provided, it must include Buttons that will execute the export
nameName of Custom Destination
onExportFunction invoked when export is applied (used if no form is supplied)

As can be seen there are 2 ways of providing Custom Destination information:

  • an onExport function that is invoked by AdapTable automatically

  • an Adaptable Form

Automated Export

If no custom information is required by the user at run-time to perform the export to the custom destination, simply provide an implementation for the onExport function.

It is defined as follows:

onExport: (report: Report, reportData: ReportData) => void;
note

The reportData property uses the same ReportData object as Custom Reports

And is used like this:

exportOptions: {
customDestinations: [
{
name: 'Rest',
onExport: (report: Report, reportData: ReportData) => {
this.sendToRestEndpoint(report, reportData)
},
},
],
},

AdapTable will automatically invoke the function whenever the Custom Destination is selected in the toolbar.

Custom Destination Form

Sometimes it might be necessary to request users to provide additional information regarding the custom destination e.g. an email address.

This is done by including an Adaptable Form definition in the Custom Destination.

note

AdapTable will read this form definition metadata and create a form dynamically (including default values).

The form will typically include fields - with optional default values and validation - and a Button to perform the Export via its onClick function.

caution

When providing a form AdapTable will not perform the Export automatically; its your responsibility to provide a button and an onClick function which will export the data

The Button is an AdaptableButton which has the usual disabled, hidden and onClick functions.

Each of these receive as a parameter the ExportButtonContext object which is defined as follows:

PropertyDescription
customDestinationCustom Export destination
formDataAdaptable Form Data
reportThe exported report
reportDataThe data in the report
note

The reportData property uses the same ReportData object as Custom Reports

exportOptions: {
customDestinations: [
{
name: 'Email',
form: {
title: 'Email Settings',
description: 'Provide email details',
fields: [
{
name: 'emailAddress',
label: 'Email Address',
fieldType: 'select',
options: [
{
value: 'support@adaptabletools.com',
label: 'Support',
},
{
value: 'sales@adaptabletools.com',
label: 'Sales',
},
],
defaultValue: 'support@adaptabletools.com',
},
{
name: 'subject',
label: 'Email Subject',
fieldType: 'text',
defaultValue: 'AdapTable Report Data',
},
{
name: 'body',
label: 'Email Body',
fieldType: 'text',
},
],
buttons: [
{
label: 'Cancel',
},
{
label: 'Export',
buttonStyle: {
tone: 'success',
variant: 'raised',
},
validate: (button: AdaptableButton<ExportButtonContext>,
context: ExportButtonContext) => {
const subject: any = context.formData?.['subject'];
return subject!= ''
},
onClick: (button: AdaptableButton<ExportButtonContext>,
context: ExportButtonContext) => {
// send the report to an email address
// the context contains the report data
},
},
],
},
},
],
},
important

If the Custom Destination forms part of a Schedule export, the same dynamic form and form fields will be displayed in the Schedule Wizard

Scheduling Reports

You can schedule your exports to run at at time of your choosing or export manually whenever you want, e.g. you can create an 'End of Day' Report to run every weekeday at 17:30.

note

Details of the schedule are stored in the Schedule section of Adaptable State.

export default {
//Define the Report in the Export Section
Export: {
CurrentReport: 'My Team Big Invoice',
Reports: [
{
Name: 'End of Day',
ReportRowScope: 'VisibleRows',
ReportColumnScope: 'AllColumns',
},
],
},
// Define the Schedule in the Schedule Section - the Report Name should match
Schedule: {
ReportSchedules: [
{
ScheduleType: 'Report',
ReportName: 'End of Day',
ExportDestination: 'Excel',
Schedule: {
DaysOfWeek: [1, 2, 3, 4, 5],
Hour: 17,
Minute: 30,
},
},
],
}, as PredefinedConfig;

UI Elements

Export includes the following UI Elements:

  • Popup - Shows all the available Reports together with an option to export to a destination of the User's choice. Plus an Add button to start the Export Wizard.

  • Toolbar - Shows all the available Reports together with an option to export to a destination of the User's choice. Also includes a Schedule button to enable reports to be run on Schedules.

  • Tool Panel - Same as Toolbar above.

UI Entitlements

UI Entitlements behaviour is as expected for Full and Hidden Access Levels.

The ReadOnly Entitlement behaviour is that Reports can be run but Users cannot manage or suspend them.

Export Options

Export Options provides a number of properties that allow you to set how Export works:

These include:

The full definition is:

PropertyDescriptionDefault
appendFileTimestampWhether to add a timestamp ('yyyyMMdd_HHmmss') as a suffix to exported file namefalse
customDestinationsUser-provided Report Destinations (in addition to those shipped in AdapTable)
customReportsReports run entirely by users (and not AdapTable)
exportDateFormatOptional custom format for Date columns when exportingundefined
exportFormatTypeFormat of exported values; can be set either for whole grid or specifically for each column typerawValue
systemExportDestinationsExport destinations to use; leave blank for all, empty array for none'Excel', 'CSV', 'Clipboard', 'JSON'
systemReportNamesSystem Reports to use; leave blank for all, empty array for none'Visual Data', 'All Data', 'Current Data', 'Selected Cells', 'Selected Rows',

A few of these properties are of interest:

File Name

By default the name of the file created is that of the Report which has been exported.

This can be amended slightly - via the appendFileTimestamp property in Export Options - which will append a Timestamp to the name of the exported File:

exportOptions: {
appendFileTimestamp: true,
},
note

The timestamp takes the form 'yyyyMMdd_HHmmss', e.g. 'All_Data_20210523_160123.xls'

Formatted Data

By default, AdapTable exports the underlying raw data of the Grid.

important

This is by design as Export is primarily a data export function and (with the exception of the Visual Data report) not a WYSIWYG operation

This is generally the preferred behaviour, so that a value of 12.59 which has been formatted as "ยฃ12.59 (GBP)" will have the raw value sent to Excel, so that it can be identified as a number and treated accordingly.

However sometimes you might wish to export the formatted value of cells instead of the raw value.

This can be achieved by setting the exportFormatType property in Export Options.

note

The default value for this propety is rawValue

The simplest way is to set this to formattedValue:

exportOptions: {
exportFormatType: 'formattedValue'
},
tip

This will export whatever the formatted value of the cell is - whether provided by a ValueFormatter or ValueGetter (provided from AG Grid) or via AdapTable's Format Column Module

Alternatively you can set this property separately for each of date, number and string so that some data types will export formatted values and others the raw value:

exportOptions: {
exportFormatType: {
string: 'formattedValue',
date: 'rawValue',
number: 'formattedValue',
},
},

Date Formats

Dates can offer additional complexity in terms of formatting.

Sometimes it might be the case that you require to export all Dates in a particular format, irrespective of whether they are formatted or not in AdapTable.

This can be achieved by setting the exportDateFormat property of Export Options.

caution

If defined, this property takes precedence, and its value will be applied to all exported Date columns, regardless of the exportFormatType (raw/formatted) or if the column has a valueFormatter defined

Export Predefined Config

The Predefined Configuration for Export contains all User-created Reports, together with the currently selected Report and Destination.

PropertyDescription
CurrentDestinationCurrently selected Report Destination in Export Toolbar & Tool Panel
CurrentReportCurrently selected Report - in Export Toolbar & Tool Panel
ReportsUser-created Reports; each has Name and Row and Column Scope
tip

The Reports property refers to User Reports; use the systemReportNames property in Export Options to set which System Reports will be automatically available to users


Report Object

The Report object has these properties:

PropertyDescription
NameName of Report
QueryQuery to use; only required if ReportRowScope is 'ExpressionRows'
ReportColumnScopeColumns to display in Report
ReportRowScopeRows to export when Report runs
ScopeColumns Scope; only required if ReportColumnScope is 'ScopeColumns'

Report Row Scope

The ReportRowScope property defines which rows are included in the report.

The options are:

  • AllRows - all rows in the DataSource

  • VisibleRows - all rows in the Grid when the Report is run

    note

    Includes all filtered rows, i.e. rows not in the current view port but which can be scrolled to

  • SelectedCells - all cells currently selected

    tip

    Selected Cells do not need to be contiguous

  • SelectedRows - all cells in all currently selected rows

    tip

    Selected Rows do not need to be contiguous

  • ExpressionRows - the Query to run to evaluate which ows to include in the exported data

  • CustomRows - used when creating a Custom Report


Report Column Scope

The ReportColumnScope property defines which columns are included in the report.

The options are:

  • AllColumns - all columns in the DataSource

  • VisibleColumns - all columns in the Grid when the Report is run

    note

    Includes all columns that can be scrolled to, even if they are not in the current view port

  • SelectedColumns - all columns which are currently selected

  • ScopeColumns - list of Columns provided by the User which uses the Scope object

    tip

    If the Report is built using the UI Wizard a separate page appears to facilitate this column selection

  • CustomColumns - used when creating a Custom Report

export default {
Export: {
CurrentReport: 'My Team Big Invoice',
Reports: [
{
Query:{
BooleanExpression: '[currency]="EUR'
}
Name: 'My Team Big Invoice',
ReportColumnScope: 'AllColumns',
ReportRowScope: 'ExpressionRows',
},
{
Name: 'End of Day',
ReportColumnScope: 'ScopeColumns',
ReportRowScope: 'VisibleRows',
Scope: {
ColumnIds: [
'OrderId',
'ChangeLastOrder',
'ContactName',
'InvoicedCost',
'ItemCost',
'ItemCount',
'OrderCost',
'OrderDate',
],
}
},
],
},
Schedule: {
ReportSchedules: [
{
ScheduleType: 'Report',
ReportName: 'End of Day',
ExportDestination: 'Excel',
Schedule: {
DaysOfWeek: [1, 2, 3, 4, 5],
Hour: 17,
Minute: 30,
},
},
],
}, as PredefinedConfig;

In this example we have created 2 Reports

  • 'My Team Big Invoice' (the currently selected one) - which exports ALL Columns and any rows where the 'InvoicedCost' Column > 1000 AND the 'Employee' column value is one of 'Robert King', 'Margaret Peacock' or 'Anne Dodsworth'
  • 'End of Day' - which exports 8 named Columns and ALL Rows. Note: we have also defined a Schedule so that the 'End of Day' Report will export to Excel automatically every weekday at 17:30

Export API

This section of Adaptable API has a number of useful export-related functions:

MethodDescription
canExportToExcel()If this AdapTable instance can to export to Excel; if false, the Export to Excel option will not be visible
editReport(report)Edits an existing report
editReports(reports)Edits existing reports
exportDataToExcel(reportData, fileName)Exports data to Excel
exportVisualDataToExcel()Exports data currently in grid to Excel as What-You-See-Is-What-You-Get
getAllCustomDestination()Retrieves all Custom Destinations from Export Options
getAllReports()Retrieves all Reports in State - both System and User-created Reports
getAvailableExportDestinations()Retrieves the available export destinations
getAvailableSystemReports()Retrieves System Reports section of Export State
getCurrentReport()Retrieves currently selected Report
getCurrentReportName()Retrieves name of currently selected Report
getDestinationByName(destinationName)Retrieves Destination with the given name
getExportDestinationForm(destinationName)Form Data entered by the User in the UI for a Custom Destination
getExportState()Retrieves Export section from Adaptable State
getReportById(id)Retrieves Report by Id
getReportByName(reportName)Retrieves Report with the given name
getReportSchedules()Retrieves all Report Schedules
isDataChangeInReport(dataChangedInfo, report)Whether given data change affects given report
isExportDestinationCustom(destinationName)If the given destination is a Custom one
runCustomReport(reportName)Runs the report function of the CustomReport with the given reportName
sendReport(reportName, destination)Sends a Report to a given destination
showExportPopup()Opens Export popup screen

FAQ

Is the export 'live' - will it update when the grid data changes?

No - export is a one time only snapshot.

However AdapTable does have other Modules (all available as Plugins) which do offer Live Data e.g. ipushpull, Glue42 and OpenFin

Do you include column names in the exported data?

Yes, column names are always included in the report.

Is there an option when just copying to the clipboard to exclude column names?

Not at present but this feature will be added in future releases.

Is there a limit on the number of reports that I can create?

No, reports like all other Adaptable Objects are unlimited.

Can I export the display formatted cell value in the column and not the raw value?

Yes by setting the exportFormatType property in Export Options.

Why can I not see the Excel destination?

If you are using AG Grid then you need to be using either AG Grid Enterprise or have the 'Excel' module loaded for this option to be available.

Can I run a Report even though it will display data not in AdapTable?

Yes, use a Custom Report. Simply supply the function that will called each time the report runs and provide any data you require.

Can I hide some of the shipped Reports or Destinations?

Yes, via the systemReportNames and exportDestinations properties in Export Options

More Information