CODEWITHSUNDEEP
javascriptexcelag-grid
Albert Vàzquez
Albert Vàzquez

Reputation: 93

Exporting to excel with proper cell formatting on ag-grid

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.

[excel pic]

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

Upvotes: 9

Views: 28486

Answers (3)

David I. McIntosh
David I. McIntosh

Reputation: 2130

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"

Upvotes: 6

Liero
Liero

Reputation: 27360

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;
  },
});

Upvotes: 18

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

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.

Upvotes: 3

Related Questions