Photo by Lance Anderson on Unsplash

Using hidden columns to customize export files in ag-Grid

I work with web development, and the heart of the application I am working with right now is a complex interactive table. This table organizes the available information in a clear and systematic form and allows for different forms of editing, such as in-cell editing or more complex editing via pop-up windows.

The table we are using is ag-Grid (https://www.ag-grid.com/), a highly customizable data grid that allows creating complex tables with great performance. It is designed for all major JavaScript frameworks (Angular, in our case) and delivers a large feature set, especially for the enterprise version.

One of the enterprise features of ag-Grid that we use in our project, is export as an Excel file (the default export format is .csv). It is used to support a scenario when a user

• exports the grid data to Excel file,
• edits the data, and
• imports the data back to the grid.

This scenario gets complicated when the grid has complex data or utilizes more advanced ways of cell editing than simple in-cell editing. In this article, I will show how we can customize the out-of-the-box Excel export and expand its functionality to take care of the more complex user cases by using hidden columns.

Excel export in ag-Grid

By default, the exported file includes only the columns currently showing in the grid. This can be changed to exporting all the columns in the order they appear in columnDefby setting the allColumns parameter of theexportDataAsCsv(params) export method totrue (for details, see https://www.ag-grid.com/javascript-grid-export/).

However, the choice between either all or only the visible columns is not always sufficient and we may need to define a custom set of the exported columns. This happens when a user has control over the displayed columns and wants to include in the export file only the columns currently showing in the grid (i.e. a user doesn’t want to export columns that are intentionally hidden via GUI) and at the same time the export file should include extra columns that should never appear in the grid itself. I have encountered this in two situations:

1. When exporting columns with Cell Renderers and Value Formatters

2. When exporting grid with the information from the detail view

Let's take a closer look at each of these two cases and at the workaround I have used to meet both of the challenges mentioned above.

Exporting columns with Cell Renderers and Value Formatters

GUI representation of the data in ag-Grid is not exported via export functionality. This means that only Value Getters — but not Cell Renderers or Value Formatters — are applied to the row data when exporting. One way to still apply formatting to the cell value in Excel file is by using processCellCallback.

However, in my case, the task was to export information originally shown in one cell as two separate columns. Our cell editor for the currency amount in ag-Grid combines editing amount and currency in the same cell:

Data representation in ag-Grid

The resulting export file should contain one column for the amount and one for the currency:

Table data exported as Excel file

In this way, it is easier for users to edit this information than if it was shown in one cell. This also contributes to the better validation of fields when importing the Excel document back to the grid.

Exporting grid with the information from the detail view

“Master Detail refers to a top level grid called a Master Grid having rows that expand. When the row is expanded, another grid is displayed with more details related to the expanded row. The grid that appears is known as the Detail Grid.” (https://www.ag-grid.com/javascript-grid-master-detail/)

Because each of the detail grids constitutes a separate grid on its own, they are not included in the export file of the master grid. Depending on the size and nature of the detail grid, its information may be incorporated into the master export file. This is a case when we want to build a detail grid that in fact represents information that naturally belongs to the master grid row data but is chosen to be shown in a separate view. This may be done, for example, to limit the number of columns in the master grid. Furthermore, in the case of two-dimensional data, a separate grid will provide a better overview.

For example, let us consider the grid that contains, among other things, information about current and future interest rates in different banks. Instead of adding extra columns to the master grid, we may transform this information into custom row data for the detail grid:

Detail view for the grid that includes information about bank rates that is shown under the main row

This grid includes only two rows, one for the current and one for the future interest rates. The first column of this grid indicates the type of rate, other columns represent different banks.

If we want to include this information in the export file, we can add extra columns in the master grid that will always be hidden and will be used only for the export:

Transformation of the detail grid to the Excel export file

Modifying column definitions of the grid

ag-grid-custom-excel-export - StackBlitz

This StackBlitz illustrates a simple workaround that in both cases can be used to include all the necessary information in the export file, without modifying the GUI version. The code example focuses only on the export itself and is not showing cell editing or detail view. The workaround consists of the following three steps:

1 Add column definitions for the extra columns that will be included in the export file. Make them hidden and make sure they don’t appear in the columns or filters tool panel of the grid. We do this by adding the following parameters to the column definition:

hide: true,
suppressColumnsToolPanel: true,
suppressFiltersToolPanel: true

2 Generate columns for excel export each time exportDataAsExcel(params) is called on the grid API. First, we get IDs for all the displayed columns (in case a user has hidden irrelevant columns via GUI). Then, we find the position where we want to insert the new column(s). For example, here I add column for currency right after column for the amount.

private generateColumnsForExcel(): string[] {
const keys = this.gridColumnApi
    .getAllDisplayedColumns()
    .map(column => column.getColId())

  const amountIndex: number = keys.findIndex(column => column === 'amount');
    keys.splice(amountIndex + 1, 0, 'currency');
 
  return keys;
}

3 Override the default ag-Grid export to include columns generated in the previous step

public exportAsExcel(filename?: string): void {

  this.gridApi.exportDataAsExcel({
    columnKeys: this.generateColumnsForExcel()})
}

In this way, we can create a custom export file that includes all the necessary information from GUI in an Excel-friendly format.