React Data GridExcel Export - Multiple Sheets

Version 36.2.0

Enterprise

Excel Export provides a way to export an Excel file with multiple sheets. This can be useful when you need to export data from different grids into a single Excel file.

How it works Copy Link

Exporting the grid into different sheets follows a specific process:

You start the process by calling the getSheetDataForExcel method on a grid instance to get the data exported for a specific sheet.
You call this method multiple times either on the same grid with different data (or different export params) or on different instances of the grid, and you store each exported data set as an element of an Array.
Once all the needed sheets have been stored in the Array, call the exportMultipleSheetsAsExcel or getMultipleSheetsAsExcel methods to package them in a single Excel workbook.

Calling getSheetDataForExcel starts a Multiple Sheet export process, that can only be ended by calling the exportMultipleSheetsAsExcel or getMultipleSheetsAsExcel methods. Until one of these two methods is called to complete the process, no data can be exported from the grid using exportDataAsExcel or getDataAsExcel.

Using Selected Rows Copy Link

In this example, we use the onlySelected=true property to segment the grid data into multiple sheets, each containing 100 data rows. Specifically:

We manually select 100 rows at a time using setNodesSelected.
We then use getSheetDataForExcel with the onlySelected option to generate sheet data for these selected nodes only.
We then deselect rows again to avoid affecting the UI.

Note the following:

The header is exported on each page, so each page will contain 101 records (including the header).
Because each export did not have a specified sheetName, they will be named ag-grid, ag-grid_1, ag-grid_2 and so on.

index.tsx

"use client";

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import "./styles.css";
import {
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  IRowNode,
  ModuleRegistry,
  NumberFilterModule,
  RowApiModule,
  RowSelectionModule,
  RowSelectionOptions,
  TextFilterModule,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ContextMenuModule,
  ExcelExportModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
import { useFetchJson } from "./useFetchJson";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

const modules = [
  TextFilterModule,
  NumberFilterModule,
  RowSelectionModule,
  RowApiModule,
  ClientSideRowModelModule,
  ExcelExportModule,
  ColumnMenuModule,
  ContextMenuModule,
];

const GridExample = () => {
  const gridRef = useRef<AgGridReact<IOlympicData>>(null);
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    { field: "athlete", minWidth: 200 },
    { field: "age" },
    { field: "country", minWidth: 200 },
    { field: "year" },
    { field: "date", minWidth: 150 },
    { field: "sport", minWidth: 150 },
    { field: "gold" },
    { field: "silver" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      filter: true,
      minWidth: 100,
      flex: 1,
    };
  }, []);
  const rowSelection = useMemo<
    RowSelectionOptions | "single" | "multiple"
  >(() => {
    return {
      mode: "multiRow",
      checkboxes: false,
      headerCheckbox: false,
    };
  }, []);

  const { data, loading } = useFetchJson<IOlympicData>(
    "https://www.ag-grid.com/example-assets/olympic-winners.json",
  );

  const onBtExport = useCallback(() => {
    const spreadsheets: string[] = [];
    let nodesToExport: IRowNode[] = [];
    gridRef.current!.api.forEachNode((node, index) => {
      nodesToExport.push(node);
      if (index % 100 === 99) {
        gridRef.current!.api.setNodesSelected({
          nodes: nodesToExport,
          newValue: true,
        });
        spreadsheets.push(
          gridRef.current!.api.getSheetDataForExcel({
            onlySelected: true,
          })!,
        );
        gridRef.current!.api.deselectAll();
        nodesToExport = [];
      }
    });
    // check if the last page was exported
    if (gridRef.current!.api.getSelectedNodes().length) {
      spreadsheets.push(
        gridRef.current!.api.getSheetDataForExcel({
          onlySelected: true,
        })!,
      );
      gridRef.current!.api.deselectAll();
    }
    gridRef.current!.api.exportMultipleSheetsAsExcel({
      data: spreadsheets,
      fileName: "ag-grid.xlsx",
    });
  }, []);

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div className="container">
          <div>
            <button
              onClick={onBtExport}
              style={{ marginBottom: "5px", fontWeight: "bold" }}
            >
              Export to Excel
            </button>
          </div>
          <div className="grid-wrapper">
            <div style={gridStyle}>
              <AgGridReact<IOlympicData>
                ref={gridRef}
                rowData={data}
                loading={loading}
                columnDefs={columnDefs}
                defaultColDef={defaultColDef}
                rowSelection={rowSelection}
              />
            </div>
          </div>
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

styles.css

.grid-wrapper {
    display: flex;
    flex: 1 1 0px;
    flex-grow: 1;
}
.grid-wrapper > div {
    width: 100%;
    height: 100%;
}
.container {
    display: flex;
    flex-direction: column;
    height: 100%;
}
.columns {
    display: flex;
    flex-direction: row;
    gap: 16px;
}

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

useFetchJson.tsx

import { useState, useEffect } from 'react';

/**
 * Fetch example Json data
 * Not recommended for production use!
 */
export const useFetchJson = <T,>(url:string, limit?: number) => {
    const [data, setData] = useState<T[]>();
    const [loading, setLoading] = useState(true);
    useEffect(() => {
        // StrictMode runs this effect twice: drop the superseded run's response rather than applying both.
        let cancelled = false;
        const fetchData = async () => {
            setLoading(true);

            // Note error handling is omitted here for brevity
            const response = await fetch(url);
            const json = await response.json();
            const data = limit ? json.slice(0, limit) : json;
            if (cancelled) {
                return;
            }
            setData(data);
            setLoading(false);
        };
        fetchData();
        return () => {
            cancelled = true;
        };
    }, [url, limit]);
    return { data, loading };
};

Using Data Filtering Copy Link

In this example, we filter on the sport column to segment the grid data into multiple sheets, each containing all the data for a specific sport value.

Note the following:

The exported Excel file will contain one sheet for each sport result.
Each sheet was exported using the sport name as the name of the sheet.

index.tsx

"use client";

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import "./styles.css";
import {
  ClientSideRowModelModule,
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  ModuleRegistry,
  NumberFilterModule,
  RowApiModule,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ContextMenuModule,
  ExcelExportModule,
  SetFilterModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
import { useFetchJson } from "./useFetchJson";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

const modules = [
  NumberFilterModule,
  RowApiModule,
  ClientSideRowModelModule,
  ExcelExportModule,
  ColumnMenuModule,
  ContextMenuModule,
  SetFilterModule,
];

const GridExample = () => {
  const gridRef = useRef<AgGridReact<IOlympicData>>(null);
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    { field: "athlete", minWidth: 200 },
    { field: "age" },
    { field: "country", minWidth: 200 },
    { field: "year" },
    { field: "date", minWidth: 150 },
    { field: "sport", minWidth: 150 },
    { field: "gold" },
    { field: "silver" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      filter: true,
      minWidth: 100,
      flex: 1,
    };
  }, []);

  const { data, loading } = useFetchJson<IOlympicData>(
    "https://www.ag-grid.com/example-assets/olympic-winners.json",
  );

  const onBtExport = useCallback(() => {
    const sports: Record<string, boolean> = {};
    gridRef.current!.api.forEachNode(function (node) {
      if (!sports[node.data!.sport]) {
        sports[node.data!.sport] = true;
      }
    });
    let spreadsheets: string[] = [];
    const performExport = async () => {
      for (const sport in sports) {
        await gridRef.current!.api.setColumnFilterModel("sport", {
          values: [sport],
        });
        gridRef.current!.api.onFilterChanged();
        if (gridRef.current!.api.getColumnFilterModel("sport") == null) {
          throw new Error("Example error: Filter not applied");
        }
        const sheet = gridRef.current!.api.getSheetDataForExcel({
          sheetName: sport,
        });
        if (sheet) {
          spreadsheets.push(sheet);
        }
      }
      await gridRef.current!.api.setColumnFilterModel("sport", null);
      gridRef.current!.api.onFilterChanged();
      gridRef.current!.api.exportMultipleSheetsAsExcel({
        data: spreadsheets,
        fileName: "ag-grid.xlsx",
      });
      spreadsheets = [];
    };
    performExport();
  }, []);

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div className="container">
          <div>
            <button
              onClick={onBtExport}
              style={{ marginBottom: "5px", fontWeight: "bold" }}
            >
              Export to Excel
            </button>
          </div>
          <div className="grid-wrapper">
            <div style={gridStyle}>
              <AgGridReact<IOlympicData>
                ref={gridRef}
                rowData={data}
                loading={loading}
                columnDefs={columnDefs}
                defaultColDef={defaultColDef}
              />
            </div>
          </div>
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

styles.css

.grid-wrapper {
    display: flex;
    flex: 1 1 0px;
    flex-grow: 1;
}

.grid-wrapper > div {
    width: 100%;
    height: 100%;
}

.container {
    display: flex;
    flex-direction: column;
    height: 100%;
}

.columns {
    display: flex;
    flex-direction: row;
    gap: 16px;
}

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

useFetchJson.tsx

import { useState, useEffect } from 'react';

/**
 * Fetch example Json data
 * Not recommended for production use!
 */
export const useFetchJson = <T,>(url:string, limit?: number) => {
    const [data, setData] = useState<T[]>();
    const [loading, setLoading] = useState(true);
    useEffect(() => {
        // StrictMode runs this effect twice: drop the superseded run's response rather than applying both.
        let cancelled = false;
        const fetchData = async () => {
            setLoading(true);

            // Note error handling is omitted here for brevity
            const response = await fetch(url);
            const json = await response.json();
            const data = limit ? json.slice(0, limit) : json;
            if (cancelled) {
                return;
            }
            setData(data);
            setLoading(false);
        };
        fetchData();
        return () => {
            cancelled = true;
        };
    }, [url, limit]);
    return { data, loading };
};

Multiple Grids to Multiple Sheets Copy Link

In this example, we export two grids, each into a separate sheet of the same Excel file. Drag a few rows from the grid on the left into the grid on the right and click the export button above the grid.

Note the following:

The contents of the Athletes grid will be exported to the Athletes sheet.
The contents of the Selected Athletes grid will be exported to the Selected Athletes sheet.
Only the onExcelExport method is relevant to Excel Export

index.tsx

'use client';
import React, { StrictMode, useCallback, useEffect, useState } from "react";
import { createRoot } from "react-dom/client";

import type {
  ColDef,
  GetRowIdParams,
  GridApi,
  GridReadyEvent,
  RowDragEndEvent,
  RowSelectionOptions,
} from "ag-grid-community";
import {
  ClientSideRowModelApiModule,
  ClientSideRowModelModule,
  CsvExportModule,
  RowDragModule,
  RowSelectionModule,
  TextFilterModule,
  enableDevValidations,
} from "ag-grid-community";
import {
  ExcelExportModule,
  exportMultipleSheetsAsExcel,
} from "ag-grid-enterprise";
import type { CustomCellRendererProps } from "ag-grid-react";
import { AgGridProvider, AgGridReact } from "ag-grid-react";

import "./styles.css";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

const modules = [
  ClientSideRowModelApiModule,
  TextFilterModule,
  RowDragModule,
  RowSelectionModule,
  ClientSideRowModelModule,
  CsvExportModule,
  ExcelExportModule,
];

const SportRenderer = (props: CustomCellRendererProps) => {
  return (
    <i
      className="far fa-trash-alt"
      style={{ cursor: "pointer" }}
      onClick={() => props.api.applyTransaction({ remove: [props.node.data] })}
    ></i>
  );
};

const leftColumns: ColDef[] = [
  {
    rowDrag: true,
    maxWidth: 50,
    suppressHeaderMenuButton: true,
    suppressHeaderFilterButton: true,
    rowDragText: (params, dragItemCount) => {
      if (dragItemCount > 1) {
        return dragItemCount + " athletes";
      }
      return params.rowNode!.data.athlete;
    },
  },
  { field: "athlete" },
  { field: "sport" },
];

const rightColumns: ColDef[] = [
  {
    rowDrag: true,
    maxWidth: 50,
    suppressHeaderMenuButton: true,
    suppressHeaderFilterButton: true,
    rowDragText: (params, dragItemCount) => {
      if (dragItemCount > 1) {
        return dragItemCount + " athletes";
      }
      return params.rowNode!.data.athlete;
    },
  },
  { field: "athlete" },
  { field: "sport" },
  {
    suppressHeaderMenuButton: true,
    suppressHeaderFilterButton: true,
    maxWidth: 50,
    cellRenderer: SportRenderer,
  },
];

const defaultColDef: ColDef = {
  flex: 1,
  minWidth: 100,
  filter: true,
};

const rowSelection: RowSelectionOptions = {
  mode: "multiRow",
};

const GridExample = () => {
  const [leftApi, setLeftApi] = useState<GridApi | null>(null);
  const [rightApi, setRightApi] = useState<GridApi | null>(null);
  const [rawData, setRawData] = useState<any[]>([]);
  const [leftRowData, setLeftRowData] = useState<any[] | null>(null);
  const [rightRowData, setRightRowData] = useState<any[]>([]);

  useEffect(() => {
    if (!rawData.length) {
      fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
        .then((resp) => resp.json())
        .then((data) => {
          const athletes: any[] = [];
          let i = 0;

          while (athletes.length < 20 && i < data.length) {
            var pos = i++;
            if (athletes.some((rec) => rec.athlete === data[pos].athlete)) {
              continue;
            }
            athletes.push(data[pos]);
          }
          setRawData(athletes);
        });
    }
  }, [rawData]);

  const loadGrids = useCallback(() => {
    setLeftRowData([...rawData.slice(0, rawData.length / 2)]);
    setRightRowData([...rawData.slice(rawData.length / 2)]);
    leftApi?.deselectAll();
  }, [leftApi, rawData]);

  useEffect(() => {
    if (rawData.length) {
      loadGrids();
    }
  }, [rawData, loadGrids]);

  const reset = () => {
    loadGrids();
  };

  const onExcelExport = () => {
    const spreadsheets: any[] = [];

    spreadsheets.push(
      leftApi?.getSheetDataForExcel({ sheetName: "Athletes" }),
      rightApi?.getSheetDataForExcel({ sheetName: "Selected Athletes" }),
    );

    exportMultipleSheetsAsExcel({
      data: spreadsheets,
      fileName: "ag-grid.xlsx",
    });
  };

  const getRowId = (params: GetRowIdParams) => params.data.athlete;

  const onDragStop = useCallback(
    (params: RowDragEndEvent) => {
      const nodes = params.nodes;

      leftApi?.applyTransaction({
        remove: nodes.map(function (node) {
          return node.data;
        }),
      });
    },
    [leftApi],
  );

  useEffect(() => {
    if (!leftApi || !rightApi) {
      return;
    }
    const dropZoneParams = rightApi.getRowDropZoneParams({ onDragStop });

    leftApi.removeRowDropZone(dropZoneParams);
    leftApi.addRowDropZone(dropZoneParams);
  }, [leftApi, rightApi, onDragStop]);

  const onGridReady = (params: GridReadyEvent, side: number) => {
    if (side === 0) {
      setLeftApi(params.api);
    }

    if (side === 1) {
      setRightApi(params.api);
    }
  };

  const getTopToolBar = () => (
    <div>
      <button
        type="button"
        className="btn btn-default excel"
        style={{ marginRight: 5 }}
        onClick={onExcelExport}
      >
        <i
          className="far fa-file-excel"
          style={{ marginRight: 5, color: "green" }}
        ></i>
        Export to Excel
      </button>
      <button type="button" className="btn btn-default reset" onClick={reset}>
        <i className="fas fa-redo" style={{ marginRight: 5 }}></i>Reset
      </button>
    </div>
  );

  const getGridWrapper = (id: number) => (
    <div className="panel panel-primary" style={{ marginRight: "10px" }}>
      <div className="panel-heading">
        {id === 0 ? "Athletes" : "Selected Athletes"}
      </div>
      <div
        id={id === 0 ? "eLeftGrid" : "eRightGrid"}
        className="panel-body"
        style={{ height: "100%" }}
      >
        <AgGridReact
          defaultColDef={defaultColDef}
          getRowId={getRowId}
          rowDragManaged={true}
          rowSelection={id === 0 ? rowSelection : undefined}
          rowDragMultiRow={id === 0}
          suppressMoveWhenRowDragging={id === 0}
          rowData={id === 0 ? leftRowData : rightRowData}
          columnDefs={id === 0 ? leftColumns : rightColumns}
          onGridReady={(params) => onGridReady(params, id)}
        />
      </div>
    </div>
  );

  return (
    <AgGridProvider modules={modules}>
      <div className="top-container">
        {getTopToolBar()}
        <div className="grid-wrapper">
          {getGridWrapper(0)}
          {getGridWrapper(1)}
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

style.css

.top-container {
    height: 100%;
    display: flex;
    flex-direction: column;
}

.panel-body > input:not(:first-of-type) {
    margin-left: 10px;
}

.grid-wrapper {
    display: flex;
    flex: 1 1 auto;
    margin-top: 5px;
}

.grid-wrapper .panel {
    flex: 1 1 50%;
    display: flex;
    flex-direction: column;
    overflow: hidden;
}

.grid-wrapper .panel-body {
    flex: 1 1 auto;
    overflow: hidden;
    padding: 0;
    display: flex;
}

.grid-wrapper .panel-body > div {
    width: 100%;
}

styles.css

.top-container {
    height: 100%;
    display: flex;
    flex-direction: column;
}

.panel-body input {
    margin-right: 2px !important;
}

.panel-body label {
    margin-right: 10px;
}

.grid-wrapper {
    display: flex;
    flex: 1 1 auto;
    margin-top: 5px;
}

.grid-wrapper .panel {
    flex: 1 1 50%;
    display: flex;
    flex-direction: column;
    overflow: hidden;
}

.grid-wrapper .panel-body {
    flex: 1 1 auto;
    overflow: hidden;
    padding: 0;
    display: flex;
}

.grid-wrapper .panel-body > div {
    width: 100%;
}

API Copy Link

API Methods Copy Link

Function

This is method to be used to get the grid's data as a sheet, that will later be exported either by getMultipleSheetsAsExcel() or exportMultipleSheetsAsExcel().

ExcelExportModule

Function

Downloads an Excel export of multiple sheets in one file.

ExcelExportModule

Function

Similar to exportMultipleSheetsAsExcel, except instead of downloading a file, it will return a Blob to be processed by the user.

ExcelExportModule