Excel Export allows you to protect the exported worksheet so that users can only edit specific cells.
Data Protection Copy Link
Excel has two layers of protection:
- Cell Protection controls whether a cell is locked and whether a formula is hidden (
ExcelStyle.protection). - Worksheet Protection enables enforcement of the locked/unlocked cell states (
ExcelExportParams.protectSheet).
Cell locking only takes effect when the worksheet is protected. If you lock cells but do not enable worksheet protection, all cells will remain editable in Excel.
Enable worksheet protection by setting protectSheet in the Excel Export Params (or in defaultExcelExportParams):
const defaultExcelExportParams = useMemo(() => {
return {
protectSheet: true
};
}, []);
<AgGridReact defaultExcelExportParams={defaultExcelExportParams} />"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,
NumberFilterModule,
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[]>([
{ field: "athlete", minWidth: 200 },
{ field: "country", minWidth: 180 },
{ field: "sport", minWidth: 150 },
{ field: "gold", width: 100 },
{ field: "silver", width: 100 },
{ field: "bronze", width: 100 },
{ field: "total", width: 100 },
]);
const defaultColDef = useMemo<ColDef>(() => {
return {
filter: true,
minWidth: 100,
flex: 1,
};
}, []);
const defaultExcelExportParams = useMemo<ExcelExportParams>(() => {
return {
protectSheet: true,
};
}, []);
const { data, loading } = useFetchJson<IOlympicData>(
"https://www.ag-grid.com/example-assets/small-olympic-winners.json",
);
const onBtExport = useCallback(() => {
gridRef.current!.api.exportDataAsExcel();
}, []);
return (
<AgGridProvider modules={modules}>
<div style={containerStyle}>
<div className="container">
<div className="controls">
<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}
defaultExcelExportParams={defaultExcelExportParams}
/>
</div>
</div>
</div>
</div>
</AgGridProvider>
);
};
const root = createRoot(document.getElementById("root")!);
root.render(
<StrictMode>
<GridExample />
</StrictMode>,
);
.controls {
margin-bottom: 10px;
}
.grid-wrapper {
display: flex;
flex: 1 1 0px;
}
.grid-wrapper > div {
width: 100%;
height: 100%;
}
.container {
display: flex;
flex-direction: column;
height: 100%;
}
export interface IOlympicData {
athlete: string,
age: number,
country: string,
year: number,
date: string,
sport: string,
gold: number,
silver: number,
bronze: number,
total: number
}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 };
}; Worksheet Custom Protection Copy Link
To allow specific actions, or to require a password to unprotect the sheet, provide an ExcelSheetProtection config object:
const defaultExcelExportParams = useMemo(() => {
return {
protectSheet: {
password: 'secret',
autoFilter: true,
formatCells: true
}
};
}, []);
<AgGridReact defaultExcelExportParams={defaultExcelExportParams} />"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,
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,
NumberFilterModule,
ClientSideRowModelModule,
CsvExportModule,
ExcelExportModule,
ColumnMenuModule,
ContextMenuModule,
];
const isChecked = (selector: string): boolean =>
document.querySelector<HTMLInputElement>(selector)?.checked ?? false;
const getInputValue = (selector: string): string =>
document.querySelector<HTMLInputElement>(selector)?.value ?? "";
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: "country", minWidth: 180 },
{ field: "sport", minWidth: 150 },
{ field: "gold", width: 100 },
{ field: "silver", width: 100 },
{ field: "bronze", width: 100 },
{ field: "total", width: 100 },
]);
const defaultColDef = useMemo<ColDef>(() => {
return {
filter: true,
minWidth: 100,
flex: 1,
};
}, []);
const { data, loading } = useFetchJson<IOlympicData>(
"https://www.ag-grid.com/example-assets/small-olympic-winners.json",
);
const onBtExport = useCallback(() => {
const password = getInputValue("#worksheetPassword").trim() || undefined;
const autoFilter = isChecked("#allowAutoFilter");
const formatCells = isChecked("#allowFormatCells");
gridRef.current!.api.exportDataAsExcel({
protectSheet: {
password,
autoFilter,
formatCells,
},
});
}, [isChecked]);
return (
<AgGridProvider modules={modules}>
<div style={containerStyle}>
<div className="container">
<div className="controls">
<label className="option">
Worksheet password (optional):
<input type="text" id="worksheetPassword" defaultValue="secret" />
</label>
<label className="option">
<input type="checkbox" id="allowAutoFilter" />
Allow filtering (autoFilter)
</label>
<label className="option">
<input type="checkbox" id="allowFormatCells" />
Allow formatting cells (formatCells)
</label>
<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>,
);
.controls {
margin-bottom: 10px;
display: flex;
flex-wrap: wrap;
gap: 12px;
align-items: center;
}
.option {
display: flex;
gap: 6px;
align-items: center;
}
.option input[type='text'] {
width: 160px;
}
.grid-wrapper {
display: flex;
flex: 1 1 0px;
}
.grid-wrapper > div {
width: 100%;
height: 100%;
}
.container {
display: flex;
flex-direction: column;
height: 100%;
}
export interface IOlympicData {
athlete: string,
age: number,
country: string,
year: number,
date: string,
sport: string,
gold: number,
silver: number,
bronze: number,
total: number
}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 };
};Excel uses an obfuscation algorithm for worksheet protection passwords. It should not be treated as strong security.
Unlocking Cells Copy Link
When worksheet protection is enabled, all exported cells are locked by default. To unlock specific cells or columns, configure an Excel style with protection.protected = false and apply that style via cellClass / cellClassRules:
const [columnDefs, setColumnDefs] = useState([
{ field: 'athlete', cellClass: 'unlocked' },
{ field: 'country', cellClass: 'unlocked' }
]);
const excelStyles = useMemo(() => {
return [
{
id: 'unlocked',
protection: { protected: false, hideFormula: false }
}
];
}, []);
const defaultExcelExportParams = useMemo(() => {
return {
protectSheet: true
};
}, []);
<AgGridReact
columnDefs={columnDefs}
excelStyles={excelStyles}
defaultExcelExportParams={defaultExcelExportParams}
/>"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 {
CellStyleModule,
ClientSideRowModelModule,
ColDef,
ColGroupDef,
CsvExportModule,
ExcelExportParams,
ExcelStyle,
GridApi,
GridOptions,
ModuleRegistry,
NumberFilterModule,
TextEditorModule,
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 = [
CellStyleModule,
TextFilterModule,
TextEditorModule,
NumberFilterModule,
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 excelStyles = useMemo<ExcelStyle[]>(() => {
return [
{
id: "unlocked",
interior: {
color: "#C6EFCE",
pattern: "Solid",
},
protection: {
protected: false,
hideFormula: false,
},
},
];
}, []);
const [columnDefs, setColumnDefs] = useState<(ColDef | ColGroupDef)[]>([
{
headerName: "Editable (Unlocked)",
children: [
{
field: "athlete",
minWidth: 200,
cellClass: "unlocked",
editable: true,
},
{
field: "country",
minWidth: 200,
cellClass: "unlocked",
editable: true,
},
],
},
{
headerName: "Read Only (Locked)",
children: [
{ field: "sport", minWidth: 150 },
{ field: "gold" },
{ field: "silver" },
{ field: "bronze" },
{ field: "total" },
],
},
]);
const defaultColDef = useMemo<ColDef>(() => {
return {
filter: true,
minWidth: 100,
flex: 1,
};
}, []);
const defaultExcelExportParams = useMemo<ExcelExportParams>(() => {
return {
protectSheet: true,
};
}, []);
const { data, loading } = useFetchJson<IOlympicData>(
"https://www.ag-grid.com/example-assets/small-olympic-winners.json",
);
const onBtExport = useCallback(() => {
gridRef.current!.api.exportDataAsExcel();
}, []);
return (
<AgGridProvider modules={modules}>
<div style={containerStyle}>
<div className="container">
<div className="controls">
<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}
excelStyles={excelStyles}
columnDefs={columnDefs}
defaultColDef={defaultColDef}
defaultExcelExportParams={defaultExcelExportParams}
/>
</div>
</div>
</div>
</div>
</AgGridProvider>
);
};
const root = createRoot(document.getElementById("root")!);
root.render(
<StrictMode>
<GridExample />
</StrictMode>,
);
.controls {
margin-bottom: 10px;
}
.ag-cell.unlocked {
background-color: #c6efce;
color: black;
}
.grid-wrapper {
display: flex;
flex: 1 1 0px;
}
.grid-wrapper > div {
width: 100%;
height: 100%;
}
.container {
display: flex;
flex-direction: column;
height: 100%;
}
export interface IOlympicData {
athlete: string,
age: number,
country: string,
year: number,
date: string,
sport: string,
gold: number,
silver: number,
bronze: number,
total: number
}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 };
}; Interfaces Copy Link
ExcelExportParams Copy Link
interface ExcelExportParams {
// ...
protectSheet?: boolean | ExcelSheetProtection;
} ExcelSheetProtection Copy Link
Properties available on the ExcelSheetProtection interface.
Allow using AutoFilter when worksheet protection is enabled. |
Allow deleting columns when worksheet protection is enabled. |
Allow deleting rows when worksheet protection is enabled. |
Allow formatting cells when worksheet protection is enabled. |
Allow formatting columns when worksheet protection is enabled. |
Allow formatting rows when worksheet protection is enabled. |
Allow inserting columns when worksheet protection is enabled. |
Allow inserting hyperlinks when worksheet protection is enabled. |
Allow inserting rows when worksheet protection is enabled. |
Allow using PivotTables when worksheet protection is enabled. |
Allow selecting locked cells when worksheet protection is enabled. |
Allow selecting unlocked cells when worksheet protection is enabled. |
Optional password required to unprotect the worksheet.
|
ExcelStyle Copy Link
interface ExcelStyle {
// ...
protection?: ExcelProtection;
} ExcelProtection Copy Link
Properties available on the ExcelProtection interface.
Set to false to disable cell protection (locking) |
Set to true to hide formulas within protected cells. |