React Data GridExcel Export - Freezing Content

Version 36.2.0

Enterprise

Excel Export allows you to freeze parts of the exported content.

By default, all rows and columns exported to Excel are scrollable. However, you can easily control the content you want to freeze to make them always visible.

Freezing Headers Copy Link

You can freeze all column headers and group column headers in the Excel export by setting the freezeRows property to headers.

Note the following:

The exported sheet will have all column headers frozen in place.

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,
  CsvExportModule,
  ExcelExportParams,
  GridApi,
  GridOptions,
  ModuleRegistry,
  NumberFilterModule,
  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 = [
  ClientSideRowModelModule,
  CsvExportModule,
  ExcelExportModule,
  ColumnMenuModule,
  ContextMenuModule,
  TextFilterModule,
  NumberFilterModule,
];

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 | ColGroupDef)[]>([
    {
      headerName: "Athlete Details",
      children: [
        {
          field: "athlete",
          width: 180,
          filter: "agTextColumnFilter",
        },
        {
          field: "age",
          width: 90,
          filter: "agNumberColumnFilter",
        },
        { headerName: "Country", field: "country", width: 140 },
      ],
    },
    {
      headerName: "Sports Results",
      children: [
        { field: "sport", width: 140 },
        {
          columnGroupShow: "closed",
          field: "total",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "gold",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "silver",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "bronze",
          width: 100,
          filter: "agNumberColumnFilter",
        },
      ],
    },
  ]);
  const defaultExcelExportParams = useMemo<ExcelExportParams>(() => {
    return {
      freezeRows: "headers",
    };
  }, []);

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

  const onBtExport = useCallback(() => {
    gridRef.current!.api.exportDataAsExcel();
  }, []);

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

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

styles.css

.option {
    display: block;
    margin: 5px 10px 5px 0;
}

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

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

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

.columns {
    display: flex;
    flex-direction: row;
    align-items: center;
    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 };
};

Freezing Pinned Rows Copy Link

Grouped columns can be exported to Excel as grouped columns. However, there are a few points to keep in mind to configure this correctly:

You can freeze all rows that are pinned to the top of the grid in the Excel export by setting the freezeRows property to headersAndPinnedRows.

Note the following:

The exported sheet will have all headers and pinned top rows of the grid frozen in place.

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,
  CsvExportModule,
  ExcelExportParams,
  GridApi,
  GridOptions,
  ModuleRegistry,
  NumberFilterModule,
  PinnedRowModule,
  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 = [
  PinnedRowModule,
  ClientSideRowModelModule,
  CsvExportModule,
  ExcelExportModule,
  ColumnMenuModule,
  ContextMenuModule,
  TextFilterModule,
  NumberFilterModule,
];

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 | ColGroupDef)[]>([
    {
      headerName: "Athlete Details",
      children: [
        {
          field: "athlete",
          width: 180,
          filter: "agTextColumnFilter",
        },
        {
          field: "age",
          width: 90,
          filter: "agNumberColumnFilter",
        },
        { headerName: "Country", field: "country", width: 140 },
      ],
    },
    {
      headerName: "Sports Results",
      children: [
        { field: "sport", width: 140 },
        {
          columnGroupShow: "closed",
          field: "total",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "gold",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "silver",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "bronze",
          width: 100,
          filter: "agNumberColumnFilter",
        },
      ],
    },
  ]);
  const pinnedTopRowData = useMemo<any[]>(() => {
    return [
      {
        athlete: "TOP (athlete)",
        country: "TOP (country)",
        sport: "TOP (sport)",
      },
    ];
  }, []);
  const pinnedBottomRowData = useMemo<any[]>(() => {
    return [
      {
        athlete: "BOTTOM (athlete)",
        country: "BOTTOM (country)",
        sport: "BOTTOM (sport)",
      },
    ];
  }, []);
  const defaultExcelExportParams = useMemo<ExcelExportParams>(() => {
    return {
      freezeRows: "headersAndPinnedRows",
    };
  }, []);

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

  const onBtExport = useCallback(() => {
    gridRef.current!.api.exportDataAsExcel();
  }, []);

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

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

styles.css

.option {
    display: block;
    margin: 5px 10px 5px 0;
}

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

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

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

.columns {
    display: flex;
    flex-direction: row;
    align-items: center;
    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 };
};

Freezing Pinned Columns Copy Link

You can freeze all pinned columns in the Excel export by setting the freezeColumns property to pinned.

Note the following:

The exported sheet will have all columns pinned at the start (left) of the grid frozen in place.

If you are using RTL - Right To Left, columns pinned to the right will be frozen in place.

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,
  CsvExportModule,
  ExcelExportParams,
  GridApi,
  GridOptions,
  ModuleRegistry,
  NumberFilterModule,
  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,
  ClientSideRowModelModule,
  CsvExportModule,
  ExcelExportModule,
  ColumnMenuModule,
  ContextMenuModule,
  NumberFilterModule,
];

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 | ColGroupDef)[]>([
    {
      headerName: "Athlete Details",
      children: [
        {
          field: "athlete",
          width: 180,
          pinned: "left",
        },
        {
          field: "age",
          width: 90,
        },
        { headerName: "Country", field: "country", width: 140 },
      ],
    },
    {
      headerName: "Sports Results",
      children: [
        { field: "sport", width: 140 },
        {
          columnGroupShow: "closed",
          field: "total",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "gold",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "silver",
          width: 100,
          filter: "agNumberColumnFilter",
        },
        {
          columnGroupShow: "open",
          field: "bronze",
          width: 100,
          filter: "agNumberColumnFilter",
        },
      ],
    },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      filter: true,
    };
  }, []);
  const defaultExcelExportParams = useMemo<ExcelExportParams>(() => {
    return {
      freezeColumns: "pinned",
    };
  }, []);

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

  const onBtExport = useCallback(() => {
    gridRef.current!.api.exportDataAsExcel();
  }, []);

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

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

styles.css

.option {
    display: block;
    margin: 5px 10px 5px 0;
}

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

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

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

.columns {
    display: flex;
    flex-direction: row;
    align-items: center;
    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 };
};

Freeze Callback Copy Link

For cases where you need to freeze more than just headers and pinned columns, both properties freezeColumns and freezeRows take a callback function. These callback function will be called for each column and each rows of the grid content will be frozen in place as long as true is returned, once false is returned the function will no longer be called and content will be scrollable.

Note the following:

The top 20 rows are exported as frozen.
All columns until sport are exported as frozen.

The callback function for rows are only called for grid rows, which means that when using this feature, all headers are automatically frozen.

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,
  CsvExportModule,
  ExcelExportParams,
  GridApi,
  GridOptions,
  ModuleRegistry,
  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 = [
  ClientSideRowModelModule,
  CsvExportModule,
  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 | ColGroupDef)[]>([
    {
      headerName: "Athlete Details",
      children: [
        {
          field: "athlete",
          width: 180,
        },
        {
          field: "age",
          width: 90,
        },
        { headerName: "Country", field: "country", width: 140 },
      ],
    },
    {
      headerName: "Sports Results",
      children: [
        { field: "sport", width: 140 },
        {
          columnGroupShow: "closed",
          field: "total",
          width: 100,
        },
        {
          columnGroupShow: "open",
          field: "gold",
          width: 100,
        },
        {
          columnGroupShow: "open",
          field: "silver",
          width: 100,
        },
        {
          columnGroupShow: "open",
          field: "bronze",
          width: 100,
        },
      ],
    },
  ]);
  const defaultExcelExportParams = useMemo<ExcelExportParams>(() => {
    return {
      freezeRows: (params) => {
        const node = params.node;
        if (node == null) {
          return true;
        }
        return node.rowIndex! < 20;
      },
      freezeColumns: (params) => params.column.getColId() !== "sport",
    };
  }, []);

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

  const onBtExport = useCallback(() => {
    gridRef.current!.api.exportDataAsExcel();
  }, []);

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

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

styles.css

.option {
    display: block;
    margin: 5px 10px 5px 0;
}

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

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

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

.columns {
    display: flex;
    flex-direction: row;
    align-items: center;
    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 };
};

Getting Started

AI Features

Layout & Styling

Charting

Core Features

Advanced Features

React Data GridExcel Export - Freezing Content

Freezing Headers Copy Link

Freezing Pinned Rows Copy Link

Freezing Pinned Columns Copy Link

Freeze Callback Copy Link