AG-Grid React Series #41: Exporting Data to CSV and Excel

Data visualization within a grid is invaluable, but often, users need to take that data outside the application for further analysis, reporting, or integration with other tools. This is where data export capabilities become crucial. AG-Grid for React provides robust and flexible options for exporting grid data to popular formats like CSV (Comma Separated Values) and Excel (XLSX).

In this installment of our AG-Grid React series, we'll dive deep into implementing these export features, covering both basic and advanced scenarios to give your users full control over their data.

Why Export Data from Your Grid?

Before we jump into the "how," let's quickly touch upon the "why." Providing export functionality empowers your users in several ways:

• Offline Analysis: Users can download data and work with it offline in spreadsheet software.
• Custom Reporting: Exported data can be integrated into custom reports or dashboards not available directly within your application.
• Data Sharing: Easily share specific datasets with colleagues or external stakeholders.
• Integration: Data can be imported into other systems that accept CSV or Excel formats.

AG-Grid Export Fundamentals

AG-Grid exposes its export functionality via the gridApi. You'll typically trigger these exports from a button click or another user interaction. For Excel export, you'll need the AG-Grid Enterprise version, as it leverages more complex file generation capabilities.

To use the export functions, you'll need access to the gridApi. This is commonly achieved by storing it in your component's state or a ref within the onGridReady callback.

import React, { useRef, useCallback, useState } from 'react';
import { AgGridReact } from 'ag-grid-react';

import 'ag-grid-community/styles/ag-grid.css';
import 'ag-grid-community/styles/ag-theme-alpine.css';

const MyGridComponent = () => {
    const gridRef = useRef();
    const [gridApi, setGridApi] = useState(null);

    const onGridReady = useCallback((params) => {
        setGridApi(params.api);
    }, []);

    // ... rest of your component
    return (
        <div className="ag-theme-alpine" style={{ height: 400, width: 600 }}>
            <AgGridReact
                ref={gridRef}
                onGridReady={onGridReady}
                // ... other props
            ></AgGridReact>
        </div>
    );
};
    

Exporting Data to CSV

CSV export is straightforward and available in both the Community and Enterprise versions of AG-Grid. The primary method is gridApi.exportDataAsCsv(params).

Basic CSV Export

To perform a basic export, you simply call the method. By default, it will export all visible rows and columns, respecting the current sort and filter applied to the grid.

const onBtnExportCsv = useCallback(() => {
    if (gridApi) {
        gridApi.exportDataAsCsv();
    }
}, [gridApi]);

// In your render method:
<button onClick={onBtnExportCsv}>Export to CSV</button>
    

Advanced CSV Export Options

The exportDataAsCsv() method accepts an optional parameters object that allows you to customize the export behavior significantly. Here are some commonly used parameters:

• fileName: (string) The name of the downloaded file. Defaults to "export.csv".
• columnKeys: (string[]) An array of column IDs to export. If omitted, all columns are exported.
• onlySelected: (boolean) Set to true to export only selected rows.
• skipHeader: (boolean) Set to true to exclude the column headers from the export.
• skipFooters: (boolean) Set to true to exclude grid footers (e.g., for `pivot` or `total` rows).
• skipGroups: (boolean) Set to true to exclude row grouping columns.
• skipPinnedTop: (boolean) Set to true to exclude pinned top rows.
• skipPinnedBottom: (boolean) Set to true to exclude pinned bottom rows.
• processCellCallback: (function) A callback function that allows you to transform cell values before they are exported.
• processHeaderCallback: (function) A callback function to transform header names.
• customHeader: (string) Custom text to prepend to the top of the CSV file.
• customFooter: (string) Custom text to append to the bottom of the CSV file.
• suppressQuotes: (boolean) If true, no quotes will be used in the output.
• allColumns: (boolean) Exports all columns, regardless of their visibility.

Let's look at an example demonstrating some of these options:

const onBtnExportCsvAdvanced = useCallback(() => {
    if (gridApi) {
        gridApi.exportDataAsCsv({
            fileName: 'my-custom-data-export.csv',
            columnKeys: ['athlete', 'age', 'country'], // Export only these columns
            onlySelected: true, // Export only selected rows
            skipHeader: false,
            processCellCallback: (params) => {
                // Prepend 'Age: ' to the age column values
                if (params.column.getColId() === 'age') {
                    return `Age: ${params.value}`;
                }
                return params.value;
            },
            processHeaderCallback: (params) => {
                // Capitalize header names
                return params.column.getColDef().headerName.toUpperCase();
            },
            customHeader: '## Custom Header For My Report ##\n',
            customFooter: '\n## End of Report ##'
        });
    }
}, [gridApi]);

// In your render method:
<button onClick={onBtnExportCsvAdvanced}>Export Selected (Custom CSV)</button>
    

Exporting Data to Excel (XLSX)

Exporting to Excel is a powerful feature, available with ag-grid-enterprise. It leverages the exportDataAsExcel(params) method, which shares many parameters with the CSV export, but also adds Excel-specific capabilities like sheet names and basic styling.

First, ensure you have the enterprise package installed:

npm install ag-grid-enterprise
# or
yarn add ag-grid-enterprise
    

And import it in your component:

import 'ag-grid-enterprise'; // Required for Excel export
    

Basic Excel Export

Similar to CSV, a basic Excel export is straightforward:

const onBtnExportExcel = useCallback(() => {
    if (gridApi) {
        gridApi.exportDataAsExcel();
    }
}, [gridApi]);

// In your render method:
<button onClick={onBtnExportExcel}>Export to Excel</button>
    

Advanced Excel Export Options

The exportDataAsExcel() method accepts similar parameters to CSV, plus some Excel-specific ones:

• sheetName: (string) The name of the worksheet in the Excel file. Defaults to "AG-Grid".
• processRowGroupCallback: (function) A callback to format row group values.
• autoConvertFormulas: (boolean) Set to true to convert strings starting with '=' into formulas in Excel.
• excelStyles: (ExcelStyle[]) An array of style definitions to be applied to cells. This is a powerful feature allowing fine-grained control over cell formatting, colors, fonts, etc. (Requires defining styles in your column definitions or using a callback).

Let's see an example with a custom sheet name and a `processCellCallback`:

const onBtnExportExcelAdvanced = useCallback(() => {
    if (gridApi) {
        gridApi.exportDataAsExcel({
            fileName: 'my-detailed-report.xlsx',
            sheetName: 'Athlete Data',
            columnKeys: ['athlete', 'gold', 'silver', 'bronze', 'total'],
            processCellCallback: (params) => {
                // Highlight athletes with 3 or more gold medals
                if (params.column.getColId() === 'athlete' && params.data.gold >= 3) {
                    return `${params.value} (High Achiever!)`;
                }
                return params.value;
            },
            processHeaderCallback: (params) => {
                return params.column.getColDef().headerName.replace(' ', '_').toUpperCase();
            }
            // For excelStyles, you would typically define an array of ExcelStyle objects
            // and potentially map them in a processCellCallback or defaultColDef.
        });
    }
}, [gridApi]);

// In your render method:
<button onClick={onBtnExportExcelAdvanced}>Export Detailed Excel</button>
    

Note on Excel Styles: Implementing detailed Excel styles requires defining an array of excelStyles and associating them with cells, either via colDef.excelStyles, defaultColDef.excelStyles, or a custom processCellCallback that returns style IDs. This can be quite extensive and is often covered in more depth in dedicated AG-Grid styling guides.

Putting It All Together: A React Component Example

Here's a complete, simplified React component demonstrating both CSV and Excel export buttons, along with sample data and column definitions.

import React, { useRef, useCallback, useState, useMemo } from 'react';
import { AgGridReact } from 'ag-grid-react';
import 'ag-grid-community/styles/ag-grid.css';
import 'ag-grid-community/styles/ag-theme-alpine.css';
import 'ag-grid-enterprise'; // Required for Excel export

const ExportingGrid = () => {
    const gridRef = useRef();
    const [gridApi, setGridApi] = useState(null);

    const columnDefs = useMemo(() => [
        { field: 'athlete', headerName: 'Athlete', sortable: true, filter: true, checkboxSelection: true },
        { field: 'age', headerName: 'Age', sortable: true, filter: true },
        { field: 'country', headerName: 'Country', sortable: true, filter: true },
        { field: 'year', headerName: 'Year', sortable: true, filter: true },
        { field: 'date', headerName: 'Date', sortable: true, filter: true },
        { field: 'sport', headerName: 'Sport', sortable: true, filter: true },
        { field: 'gold', headerName: 'Gold', sortable: true, filter: true },
        { field: 'silver', headerName: 'Silver', sortable: true, filter: true },
        { field: 'bronze', headerName: 'Bronze', sortable: true, filter: true },
        { field: 'total', headerName: 'Total', sortable: true, filter: true },
    ], []);

    const rowData = useMemo(() => ([
        { athlete: 'Michael Phelps', age: 23, country: 'USA', year: 2008, date: '24/08/2008', sport: 'Swimming', gold: 8, silver: 0, bronze: 0, total: 8 },
        { athlete: 'Usain Bolt', age: 22, country: 'Jamaica', year: 2008, date: '24/08/2008', sport: 'Athletics', gold: 3, silver: 0, bronze: 0, total: 3 },
        { athlete: 'Natalie Coughlin', age: 25, country: 'USA', year: 2008, date: '24/08/2008', sport: 'Swimming', gold: 1, silver: 2, bronze: 3, total: 6 },
        { athlete: 'Alina Alexandra Dumitru', age: 26, country: 'Romania', year: 2008, date: '24/08/2008', sport: 'Judo', gold: 1, silver: 0, bronze: 0, total: 1 },
        { athlete: 'Jamie Staff', age: 34, country: 'Great Britain', year: 2008, date: '24/08/2008', sport: 'Cycling', gold: 1, silver: 0, bronze: 0, total: 1 },
        { athlete: 'Lin Dan', age: 24, country: 'China', year: 2008, date: '24/08/2008', sport: 'Badminton', gold: 1, silver: 0, bronze: 0, total: 1 },
        { athlete: 'Lee Chong Wei', age: 25, country: 'Malaysia', year: 2008, date: '24/08/2008', sport: 'Badminton', gold: 0, silver: 1, bronze: 0, total: 1 },
        { athlete: 'Tony Estanguet', age: 36, country: 'France', year: 2012, date: '12/08/2012', sport: 'Canoeing', gold: 1, silver: 0, bronze: 0, total: 1 },
        { athlete: 'Andy Murray', age: 25, country: 'Great Britain', year: 2012, date: '12/08/2012', sport: 'Tennis', gold: 1, silver: 1, bronze: 0, total: 2 },
    ]), []);

    const defaultColDef = useMemo(() => ({
        flex: 1,
        minWidth: 100,
        resizable: true,
    }), []);

    const onGridReady = useCallback((params) => {
        setGridApi(params.api);
    }, []);

    const onBtnExportCsv = useCallback(() => {
        if (gridApi) {
            gridApi.exportDataAsCsv({
                fileName: 'athlete_data_full.csv',
                onlySelected: false,
                processCellCallback: (params) => {
                    // Example: Format date field
                    if (params.column.getColId() === 'date') {
                        const [day, month, year] = params.value.split('/');
                        return `${month}/${day}/${year}`; // MM/DD/YYYY format
                    }
                    return params.value;
                }
            });
        }
    }, [gridApi]);

    const onBtnExportExcelSelected = useCallback(() => {
        if (gridApi) {
            gridApi.exportDataAsExcel({
                fileName: 'selected_athletes.xlsx',
                sheetName: 'Selected Athletes',
                onlySelected: true, // Export only selected rows
                processHeaderCallback: (params) => {
                    return params.column.getColDef().headerName || '';
                }
            });
        }
    }, [gridApi]);

    return (
        <div style={{ width: '100%', height: '500px' }}>
            <div style={{ marginBottom: '10px' }}>
                <button onClick={onBtnExportCsv} style={{ marginRight: '5px' }}>
                    Export All to CSV
                </button>
                <button onClick={onBtnExportExcelSelected}>
                    Export Selected to Excel
                </button>
            </div>
            <div className="ag-theme-alpine" style={{ height: 'calc(100% - 40px)', width: '100%' }}>
                <AgGridReact
                    ref={gridRef}
                    rowData={rowData}
                    columnDefs={columnDefs}
                    defaultColDef={defaultColDef}
                    onGridReady={onGridReady}
                    rowSelection={'multiple'}
                ></AgGridReact>
            </div>
        </div>
    );
};

export default ExportingGrid;
    

Best Practices and Considerations

• Large Datasets: For extremely large datasets, consider performing the export on the server-side to avoid browser performance issues and potential memory limits. The AG-Grid export functions are great for client-side, but have practical limits.
• User Experience: Provide visual feedback to the user when an export is in progress (e.g., a loading spinner). File generation can take a few seconds for larger grids.
• Security: Ensure that users only export data they are authorized to see. This usually involves server-side authorization checks for the original data retrieval, but it's a good reminder for the export context as well.
• Error Handling: While AG-Grid handles the file generation, network issues or other unexpected errors could occur if you're fetching data for export. Implement appropriate error handling.
• Consistent Formatting: Use processCellCallback consistently across different exports if you need specific data transformations (e.g., date formats, currency symbols) to be applied uniformly.

Conclusion

AG-Grid's export capabilities provide a powerful way for users to interact with and utilize the data displayed in your React applications. Whether you need a simple CSV dump or a more structured Excel file with custom formatting, the API offers the flexibility to meet diverse requirements. By understanding and implementing these features, you significantly enhance the utility and user experience of your AG-Grid powered applications.