Expand All

  Getting Started

  Reference

  Features

  Row Models

  Themes

  Components

  Examples

  Third Party

Misc

Github stars make projects look great. Please help, donate a star, it's free.
Read about ag-Grid's Partnership with webpack.
Get informed on releases and other ag-Grid news only - never spam.
Follow on Twitter

JavaScript Grid Excel

Exporting ag-Grid Data to Excel

Excel Export allows exporting ag-Grid data to Excel using 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.

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

  • 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.

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

styleId

(Optional) The associated excel style Id to be applied to this cell. This MUST be an existing excel style in your gridOptions definition

data

An object of type ExcelData. See section below.

mergeAcross

(Optional). The number of cells to span across

ExcelData

type

One of {String, Number}. This is case sensitive

value

The value to show in the cell

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'
[
    [],
    [{styleId:'bigHeader', data:{type:'String', value:'Summary'}}],
    [
        {styleId:'label', data:{type:'String', value:'Sales'}, mergeAcross:2},
        {styleId:'amount', data:{type:'Number', value:'3695.36'}}
    ],
    []
]

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 cellRenderer, will get used, meaning:
    • cellRenderers will NOT be used.
    • valueGetters will be used.
    • cellFormatters 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. 90 is straight up, 0 is horizontal, and -90 is straight down
    • 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
    • verticalText: Boolean. Specifies whether the text is drawn "downwards", whereby each letter is drawn horizontally, one above the other.
    • 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 or number). 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.
  • color: A color in hexadecimal format.

Excel Style Definition Example

var columnDef = {
    ...,
    // The same cellClassRules and cellClass can be used for CSS and Excel
    cellClassRules: {
        greenBackground: function(params) { return params.value < 23}
    },
    cellClass: 'redFont'
}

// In this example we can see how we merge the styles in Excel.
// Everyone less than 23 will have a green background, and a light green color font (#e0ffc1)
// also because redFont is set in cellClass, it will always be applied

var gridOptions = {
    ...,
    ExcelStyles: [
        // The base style, red font.
        {
            id: "redFont",
            interior: {
                color: "#FF0000", pattern: 'Solid'
            }
        },
        // The cellClassStyle: background is green and font color is light green,
        // note that since this excel style it's defined after redFont
        // it will override the red font color obtained through cellClass:'red'
        {
            id: "greenBackground",
            alignment: {
                horizontal: 'Right', vertical: 'Bottom'
            },
            borders: {
                borderBottom: {
                    color: "#000000", lineStyle: 'Continuous', weight: 1
                },
                borderLeft: {
                    color: "#000000", lineStyle: 'Continuous', weight: 1
                },
                borderRight: {
                    color: "#000000", lineStyle: 'Continuous', weight: 1
                },
                borderTop: {
                    color: "#000000", lineStyle: 'Continuous', weight: 1
                }
            },
            font: { color: "#e0ffc1"},
            interior: {
                color: "#008000", pattern: 'Solid'
            }
        }

    ]
}

    

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

Exporting To XLSX

The xls files that we create are based on Excel own XML specification. This is not compatible with the xlsx format, which is the preferred format of newer MS Excel versions.

Because of that when you open one of our exported files in a recent MS office version you might see this error:

If you want to export to xlsx, you can reuse the XML that we generate and pass it onto a third party library that would convert it into XLSX.

As specified in the API section above, api.getDataAsExcel(params) is the method that you need to call to obtain the XML that we generate

The following example shows how this can be achieved by using SheetJs

SheetJs Custom XLSX Export Example - Without styles

In the following example note that:

  • sheetJs Is included as a third party library
  • The "Export to Excel (xlsx)" button reuses the XML and passes it to sheetJs to generate a xlsx
  •     var content = gridOptions.api.getDataAsExcel(params);
        var workbook = XLSX.read(content, {type: 'binary'});
        var xlsxContent = XLSX.write(workbook, {bookType: 'xlsx', type: 'base64'});
  • There is some code to handle the conversion from base64 to blob adapted from stackOverflow
  • There is some code to handle the download of the blob:
    function download (params, content){
        var fileNamePresent = params && params.fileName && params.fileName.length !== 0;
        var fileName = fileNamePresent ? params.fileName : 'noWarning.xlsx';
    
    
        var blobObject = b64toBlob(content, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    
    
        if (window.navigator.msSaveOrOpenBlob) {
            // Internet Explorer
            window.navigator.msSaveOrOpenBlob(blobObject, fileName);
        } else {
            // Chrome
            var downloadLink = document.createElement("a");
            downloadLink.href = URL.createObjectURL(blobObject);
            downloadLink.download = fileName;
    
            document.body.appendChild(downloadLink);
            downloadLink.click();
            document.body.removeChild(downloadLink);
    }
  • Note that this example doesnt't import the styles to xls. To add styling to the xlsx, the logic could be extended to read the XML styling information received from gridOptions.api.getDataAsExcel(params), and it could thn be passed into SheetJs through the object returned by XLSX.read(content, {type: 'binary'}). The reason this example is not exporting styles is because that it will go beyond of the purpose of demonstrating that you can reuse the XML we provide anyway you want.