Exporting to excel with proper cell formatting on ag-grid

Question

When I export a table to excel using ag-grid's own exportDataAsExcel() the resulting excel contains dates as General data type instead of Date.

[]

I have used this:

exportDataAsExcel({
   processCellCallback: ({col, val}) => {( /*date value formatting here*/ )}
})

to format both Date, string with proper date formatting (DD/MM/YYYY) but I can't make excel properly recognize these cells as Date instead of General

This is reproducible with the excel export examples on their website: https://www.ag-grid.com/javascript-grid-excel/?framework=all#gsc.tab=0

Answer 1

I've used this code to apply value formatter in excell as well:

this.gridOptions.api.exportDataAsExcel({
  processCellCallback: (params) => {
    const colDef = params.column.getColDef()
    // try to reuse valueFormatter from the colDef
    if (colDef.valueFormatter) {
      const valueFormatterParams: ValueFormatterParams = {
        ...params,
        data: params.node.data,
        node: params.node!,
        colDef: params.column.getColDef()
      };
      return colDef.valueFormatter(valueFormatterParams);
    }
    return params.value;
  },
});

Answer 2

You need to do three things:

1. When instantiating the Ag-Grid, you need to add (thanks to another user's answer elsewhere)

    let excelStyles = [
        {
            id: "ExcelDateTime",
            dataType: "dateTime",
            numberFormat: { format: "yyyy-mm-dd hh:mm:ss;;;" }
        },
        {
            id: "ExcelDate",
            dataType: "dateTime",
            numberFormat: { format: "yyyy-mm-dd;;;" }
        }
    ];
    ...
    <AgGridReact
        ... //such as   rowData={rowData}
        excelStyles={excelStyles}
        ...
    />;
   

2. For columns that are dates:

    colDef.cellClass = "ExcelDateTime";
   

   or

    colDef.cellClass = "ExcelDate";
   

   as appropriate, and

3. in your processCellCallback, format the date as an ISO date: "yyyy-mm-ddTHH:MM:ss"

Answer 3

Assuming that you are in control of the server-side application, I have found it easier to do the export from the server-side for any non-trivial exports / formatting.

By doing it on the server, you are in complete control of the data and the production of the Excel file, and not dependent on the limits of Ag-Grid's implementation.