Report Format Types - Visual Excel

Summary

Reports in AdapTable are not WYSIWYG (What You See is What You Get).

It is the Grid's underlying raw data, rather than display values, styles or grouping, which is exported.

Note

Developers do, however, have some control over the Formatting of other Reports
They can set how to format dates and choose between exporting cell raw (the default) or display values

The one exception to this "data-only" rule is the VisualExcel Report Format Type.

This exports Columns that contain styles so that they appear in Excel the same way as they do in AdapTable.

The VisualExcel Report Format Type includes the following elements:

defined in AG Grid:
- Value Getters
- Cell Formatters
defined in AdapTable:
- Formatted Columns (which contain AdapTable Styles)
- Row Groups

Exporting Styles

AdapTable will export styles that have been created in AG Grid or AdapTable into Excel format.

This means that if there are any AG Grid Excel Styles defined, Adaptable will try to render them.

Caution

Any AdapTable Styles defined with the Format Column will always take precedence over AG Grid styles

Hint

We recommend using AdapTable's versatile Format Columns instead of AG Grid's standard value formatters
It offers ingrained styling support for Excel and ensures the Format Column definition is the sole source of truth

VisualExcel Report Format Type

Fork Fork

In this example AdapTable has been set to use the VisualExcel Report Format Type (and Current Layout report)
We added a number of features which all get included in the Export:

Try It Out

Switch to Grouped Layout - note how the Column Formats are preserved

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'VisualExcel Report Format',
  initialState: {
    Dashboard: {
      Tabs: [
        {
          Name: 'Demo',
          Toolbars: ['Export', 'Layout'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Export', 'Layout'],
        },
      ],
    },
    Export: {
      CurrentReport: 'Current Layout',
      CurrentFormat: 'VisualExcel',
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-github_stars',
          Rule: {
            Predicates: [
              {
                PredicateId: 'GreaterThan',
                Inputs: [10000],
              },
            ],
          },
          Scope: {
            ColumnIds: ['github_stars'],
          },
          Style: {
            BackColor: 'Yellow',
          },
        },
        {
          Name: 'formatColumn-name',
          Scope: {
            ColumnIds: ['name'],
          },
          Style: {
            BackColor: 'Brown',
            ForeColor: 'White',
          },
        },
        {
          Name: 'formatColumn-license',
          Scope: {
            ColumnIds: ['license'],
          },
          Style: {
            FontStyle: 'Italic',
          },
        },
      ],
    },
    StyledColumn: {
      StyledColumns: [
        {
          ColumnId: 'github_watchers',
          GradientStyle: {
            RangeValueType: 'Number',
            CellRanges: [{Min: 'Col-Min', Max: 'Col-Max', Color: '#a52a2a'}],
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'license',
            'github_watchers',
            'has_projects',
            'has_pages',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'week_issue_change',
          ],
          TableAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'week_issue_change',
              AggFunc: 'min',
            },
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
          GrandTotalRow: 'top',
        },
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'license',
            'github_watchers',
            'has_projects',
            'has_pages',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'week_issue_change',
          ],
          RowGroupedColumns: ['language'],
          TableAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'week_issue_change',
              AggFunc: 'min',
            },
          ],
          Name: 'Grouped Layout',
          AutoSizeColumns: true,
          GrandTotalRow: 'bottom',
        },
      ],
    },
  },
};

Exporting Formats

The VisualExcel Report Format always exports any Display Formats currently rendered in AdapTable.

Caution

The exportDataFormat property in Export Options evaluates whether to export raw value or display value
But (only) the VisualExcel Report Format Type ignores this since it only exports display values

Exporting Excel Dates

When using the VisualExcel Report Format Type, Date columns can be explicitly exported as Excel Date cells.

Hint

This means that Excel will see the Column as a Date and treat it as such when providing Filters and other functionality

To achieve this, the following are required:

the Column has a Cell Data Type of date
a custom Date format is provided. This can be either:
- set using the (global) exportDateFormat property in Export Options; or
- via the exportDataFormat property in Export Options which is set to formattedValue for Dates
Note
This option also requires the Column to have an active Format Column using DateFormatterOptions

Exporting Styled Columns

The VisualExcel Report Format Type will only export the Gradient Style Styled Column.

This is because it simply contains a back colour which is easy to send to Excel.

Caution

The VisualExcel Report Format Type will not export Percent Bar or Badge or Sparkline Column Styled Columns

PreviousExcel NextCSV

Framework: