Export

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 populates the Export dropdown with User Reports (and the shipped System Reports) and allows you to export the associated data 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 Definition essentially contains 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

User can provide their own reports, either at design-time (through Export State) or at run-time (via the Report Wizard).

Each User Report will have its own column definition and row query.

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;
},
},
]
}

Export Destinations

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

  • Excel
  • CSV
  • Clipboard
  • JSON
tip

The Export to JSON sends the data using the ReportData format

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

note

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

Custom Destinations

If the 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, needs to 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:

  • with an onExport function that is invoked by AdapTable automatically

  • via 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:

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, context: ExportButtonContext) => {
const subject: any = context.formData?.['subject'];
return subject!= ''
},
onClick: (button: AdaptableButton, 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.

Entitlements

Export Entitlement Rules:

  • Full: All Reports can be run and User Reports can be created / updated / deleted

  • Hidden: Reports can be run but not edited

  • ReadOnly: The Module is completely hidden from the User

Export Options

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

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
exportDestinationsExport destinations to use; leave blank for all, empty array for none'Excel', 'CSV', 'Clipboard', 'JSON'
exportFormatTypeFormat of exported values; can be set either for whole grid or specifically for each column typerawValue
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 Export Predefined Config allows developers to pre-populate their State with Reports, Custom Destinations and much else.

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

The Export Api 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
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