JavaScript Grid Excel

Exporting ag-Grid Data to Excel

Excel Export allows exporting ag-Grid data to Excel using Open XML format (xlsx) or Excel's own XML format.
Using this format allows for rich Excel files to be created with the following:

1. The column width from your grid is exported to Excel, so the columns in Excel will have the same width as your web application
2. You can specify Excel styles (colors, fonts, borders etc) to be included in the Excel file.
3. The data types of your columns are passed to Excel as part of the export so that if you can to work with the data within Excel in the correct format.
4. The cells of the column header groups are merged in the same manner as the group headers in ag-Grid.

API

The export is performed by calling the following API. Note that this API is similar to the CSV Export API, so you can use similar config for both.

• exportDataAsExcel(params): Does the full export and triggers the download of the file in the browser automatically so the user can open immediately.
• getDataAsExcel(params): Returns the Excel XML that represents the export performed by exportDataAsExcel(params).
  This can then be used by your web application, e.g. to send the data to the server for storing or sending via email etc.
  Note: This forces exportMode to xml.

Each of these methods takes an optional params object that can take the following:

• exportMode: Defaults to "xlsx" and uses the Open XML standards. It can be set to "xml" to use legacy Excel's own XML format.
• skipHeader: Set to true if you don't want the first line to be column header names.
• columnGroups: Set to true to include header column groupings.
• skipGroups: Set to true to skip row group headers and footers if grouping rows. No impact if not grouping rows.
• skipFooters: Set to true to skip footers only if grouping. No impact if not grouping or if not using footers in grouping.
• fileName: String to use as the file name. If missing, the file name 'export.xls' will be used.
• allColumns: If true, all columns will be exported in the order they appear in columnDefs. Otherwise only the columns currently showing in the grid, and in that order, are exported.
• onlySelected: Only export selected rows.
• onlySelectedAllPages: Only export selected rows including other pages (only applicable when using pagination).
• columnKeys: Provide a list (an array) of column keys to export specific columns.
• shouldRowBeSkipped: Allows you to skip entire rows from the export.
• processCellCallback: Allows you to process (typically format) cells for the export.
• processHeaderCallback: Allows you to create custom header values for the export.
• customHeader: If you want to put some rows at the top of the xls file, stick it here. The format of this rows is specified below in the section custom rows.
• customFooter: Same as customHeader, but for the end of the file.
• sheetName: The name of the sheet in excel where the grid will get exported. If not specified defaults to 'ag-grid'.
  Note: 31 charecters max.
• suppressTextAsCDATA: Since v17 the default behaviour of exporting text is to include CDATA tags to avoid any text parsing issues, but if this is incompatible to you current approach, you can switch this off by setting this to true.
  Note: Only relevant if exportMode is set to "xml".
• rowHeight: The height (in px) of all rows. If not specified it will take the Excel default value.
• headerRowHeight: The height (in px) of header rows. If not specified it will take the rowHeight value.

shouldRowBeSkipped()

This callback allows you to entirely skip a row to be exported. The example below has an option 'Skip Group R' which will entirely skip all the rows which Group=R.

The callback params has the following attributes: node, api, context.

processCellCallback()

This callback allows you to format the cells for the export. The example below has an option 'Use Cell Callback' which puts all the items into upper case. This can be useful if, for example, you need to format date cells to be read by Excel.

The callback params has the following attributes: value, node, column, api, columnApi, context.

processHeaderCallback()

If you don't like the header names the grid provides then you can provide your own header names. For example, you have grouped columns and you want to include the columns parent groups.

The callback params has the following attributes: column, api, columnApi, context.

You can assign default export parameters to your Excel export by setting the property defaultExportParams in your gridOptions. This is useful if you are planning the user to let export the data via the contextual menu.

Custom rows

You can pass your custom rows in the properties customHeader amd customFooter. This properties are expected to contain an array of array of ExcelCell objects, which itself contains ExcelData objects.

Each item in the array is considered to be a row in the excel export

Find below the definition of these interfaces and an example

ExcelCell

ExcelData

Example

The following example shows 4 custom rows, note that:

• The first and the last row are empty '[]'
• The second row spans 2 columns
• The third row has 2 cells. The first cell is a label (string) and the second one a total (number)
• All cells have styles associated. These styles need to be specified as part of the gridOptions. See below 'Export with Styles'

What Gets Exported

The data in the grid, similar to CSV Export, gets exported. However unlike CSV Export, you also get to export styles. The details of how to specify styles with Excel are explained in the last example on this page.

Regardless, the following needs to be taken into consideration

• The raw values, and not the result of cell renderer, will get used, meaning:
  • Cell Renderers will NOT be used.
  • Value Getters will be used.
  • Cell Formatters will NOT be used (use processCellCallback instead).
• If row grouping, all data will be exported regardless of groups open or closed.
• If row grouping with footers (groupIncludeFooter=true) the footers will NOT be used - this is a GUI addition that happens for displaying the data in the grid.

Example 1 - Export Without Styles

The example below demonstrates exporting the data without any styling. Note that the grid has CSS Class Rules for changing the background color of some cells. The Excel Export does not replicate the HTML styling. How to get similar formatting in your Excel is explained in the second example. The following items can be noted from the example:

• The column grouping is exported.
• Filtered rows are not included in the export.
• The sort order is maintained in the export.
• The order of the columns is maintained in the export.
• Only visible columns are exported.
• Value getters are used to work out the value to export (the 'Group' col in the example below uses a value getter to take the first letter of the country name)
• Aggregated values are exported.
• For groups, the first exported value (column) will always have the group key.

Export with Styles

The main reason to export to Excel instead of CSV is so that the look and feel remain as consistent as possible with your ag-Grid application. In order to simplify the configuration the Excel Export reuses the cellClassRules and the cellClass from the column definition. Whatever resultant class is applicable to the cell then is expected to be provided as an Excel Style to the ExcelStyles property in the gridOptions.

The configuration maps to the Microsoft Excel XML format. This is why the configuration below deviates away from what is used elsewhere in ag-Grid.

Excel Style Definition

An Excel style object has the following properties:

• id (mandatory): The id of the style, this has to be a unique string and has to match the name of the style from the cellClassRules
• alignment (optional): Vertical and horizontal alignmen:
  • horizontal: String one of Automatic, Left, Center, Right, Fill, Justify, CenterAcrossSelection, Distributed, and JustifyDistributed
  • indent: Number of indents
  • readingOrder: String one of RightToLeft, LeftToRight, and Context
  • rotate: Number. Specifies the rotation of the text within the cell. Values range between 0 and 180.
  • shrinkToFit: Boolean. True means that the text size should be shrunk so that all of the text fits within the cell. False means that the font within the cell should behave normally
  • vertical: String one of Automatic, Top, Bottom, Center, Justify, Distributed, and JustifyDistributed
  • wrapText: Boolean. Specifies whether the text in this cell should wrap at the cell boundary. False means that text either spills or gets truncated at the cell boundary (depending on whether the adjacent cell(s) have content).
• borders (optional): All the 4 borders must be specified (explained in next section):
  • borderBottom
  • borderLeft
  • borderTop
  • borderRight
• font (optional): The color must be declared:
  • bold. Boolean
  • color. A color in hexadecimal format
  • fontName. String
  • italic. Boolean
  • outline. Boolean
  • shadow. Boolean
  • size. Number. Size of the font in points
  • strikeThrough. Boolean.
  • underline. One of None, Subscript, and Superscript.
  • charSet. Number. Win32-dependent character set value.
  • family. String. Win32-dependent font family. One of Automatic, Decorative, Modern, Roman, Script, and Swiss
• interior (optional): The color and pattern must be declared:
  • color: A color in hexadecimal format
  • pattern: One of the following strings: None, Solid, Gray75, Gray50, Gray25, Gray125, Gray0625, HorzStripe, VertStripe, ReverseDiagStripe, DiagStripe, DiagCross, ThickDiagCross, ThinHorzStripe, ThinVertStripe, ThinReverseDiagStripe, ThinDiagStripe, ThinHorzCross, and ThinDiagCross
  • patternColor: A color in hexadecimal format
• numberFormat (optional): A javascript object with one property called format, this is any valid Excel format like: #,##0.00 (This formatting is used in the example below in the age column)
• protection (optional): A javascript object with the following properties:
  • protected: Boolean. This attribute indicates whether or not this cell is protected. When the worksheet is unprotected, cell-level protection has no effect. When a cell is protected, it will not allow the user to enter information into it.
  • hideFormula: Boolean. This attribute indicates whether or not this cell's formula should be hidden when worksheet protection is enabled.
• dataType (optional): One of (string, number, boolean, dateTime, error). In most cases this is not necessary since this value is guessed based in weather the cell content is numeric or not. This is helpful if you want to fix the type of the cell. ie. If your cell content is 003, this cell will be default be interpreted as numeric, and in Excel, it will show up as 3. But if you want to keep your original formatting, you can do so by setting this property to string.

Excel borders

The borderBottom, borderLeft, borderTop, borderRight properties are objects composed of the following mandatory properties:

• lineStyle: One of the following strings: "None", "Continuous", "Dash", "Dot", "DashDot", "DashDotDot", "SlantDashDot", and "Double".
• weight: A number representing the thickness of the border in pixels.
  0. hair
  1. thin
  2. medium
  3. thick

Note: weight values will only be considered with lineStyle "Continuous"

Note 2: Line styles "Dash", "DashDot", "DashDotDot" accept weight 0 (default) and weight 2 (medium).

• color: A color in hexadecimal format.

Excel Style Definition Example

Resolving Excel Styles

All the defined classes from cellClass and all the classes resultant of evaluating the cellClassRules are applied to each cell when exporting to Excel. Normally these styles map to CSS classes when the grid is doing normal rendering. In Excel Export, the styles are mapped against the Excel styles that you have provided. If more than one Excel style is found, the results are merged (similar to how CSS classes are merged by the browser when multiple classes are applied).

Headers are a special case, headers are exported to Excel as normal rows, so in order to allow you to style them you can provide an ExcelStyle with id and name "header". If you do so, the headers will have that style applied to them when exported. You can see this is the second example below in this page.

Dealing With Errors In Excel

If you get an error when opening the Excel file, the most likely reason is that there is an error in the definition of the styles. If that is the case, since the generated xls file is a plain XML text file, we recommend you to edit the contents manually and see if any of the styles specified have any error according to the Microsoft specification for the Excel XML format.

Some of the most likely errors you can encounter when exporting to Excel are:

• Not specifying all the attributes of an Excel Style property. If you specify the interior for an Excel style and don't provide a pattern, just color, Excel will fail to open the spreadsheet
• Using invalid characters in attributes, we recommend you not to use special characters.
• Not specifying the style associated to a cell, if a cell has an style that is not passed as part of the grid options, Excel won't fail opening the spreadsheet but the column won't be formatted.
• Specifying an invalid enumerated property. It is also important to realise that Excel is case sensitive, so Solid is a valid pattern, but SOLID or solid are not

Example 2 - Export With Styles

This example illustrates the following features from the Excel export.

• Cells with only one style will be exported to Excel, as you can see in the Country and Gold columns
• Styles can be combined it a similar fashion than CSS, this can be seen in the column age where athletes less than 20 years old get two styles applied (greenBackground and redFont)
• A default columnDef containing cellClassRules can be specified and it will be exported to Excel. You can see this is in the styling of the oddRows of the grid (boldBorders)
• Its possible to export borders as specified in the gold column (boldBorders)
• If a cell has an style but there isn't an associated Excel Style defined, the style for that cell won't get exported. This is the case in this example of the year column which has the style notInExcel, but since it hasn't been specified in the gridOptions, the column then gets exported without formatting.
• Note that there is an Excel Style with name and id header that gets automatically applied to the ag-Grid headers when exported to Excel
• As you can see in the column "Group", the Excel styles can be combined into cellClassRules and cellClass
• Note that there are specific to Excel styles applied, the age column has a number formatting style applied and the group column uses italic and bold font
• The silver column has a style with dataType=string. This forces this column to be rendered as text in Excel even though all of their cells are numeric

Example 3 - Data types

The following example demonstrates how to use other data types for your export. Note that:

• Boolean works off using 1 for true
• The date time format for excel follows this format yyyy-mm-ddThh:MM:ss.mmm:
• If you try to pass data that is not compatible with the underlying data type Excel will throw an error
• When using dataType: 'dateTime' Excel doesn't format the resultant value, in this example it shows 39923. You need to add the formatting inside Excel

Export to Excel with iPad

It is not possible to download files directly from JavaScript to an iPad. This is a restriction on iOS and not something wrong with ag-Grid. For this reason, the download links in the context menu are removed when running on iPad. If you do want to download on iPad, then it is recommended you use the api function getDataAsCsv() to get the export data and then send this to the server to allow building an endpoint for doing the download.