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 gridOptions = {
defaultExcelExportParams: {
protectSheet: true
},
// other grid options ...
}import {
ClientSideRowModelModule,
CsvExportModule,
GridApi,
GridOptions,
ModuleRegistry,
NumberFilterModule,
TextFilterModule,
createGrid,
enableDevValidations,
} from "ag-grid-community";
import {
ColumnMenuModule,
ContextMenuModule,
ExcelExportModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableDevValidations();
}
ModuleRegistry.registerModules([
TextFilterModule,
NumberFilterModule,
ClientSideRowModelModule,
CsvExportModule,
ExcelExportModule,
ColumnMenuModule,
ContextMenuModule,
]);
let gridApi: GridApi<IOlympicData>;
const gridOptions: GridOptions<IOlympicData> = {
columnDefs: [
{ 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 },
],
defaultColDef: {
filter: true,
minWidth: 100,
flex: 1,
},
defaultExcelExportParams: {
protectSheet: true,
},
};
function onBtExport() {
gridApi!.exportDataAsExcel();
}
const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);
fetch("https://www.ag-grid.com/example-assets/small-olympic-winners.json")
.then((response) => response.json())
.then(function (data) {
gridApi!.setGridOption("rowData", data);
});
if (typeof window !== "undefined") {
// Attach external event handlers to window so they can be called from index.html
(<any>window).onBtExport = onBtExport;
}
.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%;
}
<div class="container">
<div class="controls">
<button onclick="onBtExport()" style="margin-bottom: 5px; font-weight: bold">Export to Excel</button>
</div>
<div class="grid-wrapper">
<div id="myGrid"></div>
</div>
</div>
export interface IOlympicData {
athlete: string,
age: number,
country: string,
year: number,
date: string,
sport: string,
gold: number,
silver: number,
bronze: number,
total: number
} Worksheet Custom Protection Copy Link
To allow specific actions, or to require a password to unprotect the sheet, provide an ExcelSheetProtection config object:
const gridOptions = {
defaultExcelExportParams: {
protectSheet: {
password: 'secret',
autoFilter: true,
formatCells: true
}
},
// other grid options ...
}import {
ClientSideRowModelModule,
CsvExportModule,
GridApi,
GridOptions,
ModuleRegistry,
NumberFilterModule,
TextFilterModule,
createGrid,
enableDevValidations,
} from "ag-grid-community";
import {
ColumnMenuModule,
ContextMenuModule,
ExcelExportModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableDevValidations();
}
ModuleRegistry.registerModules([
TextFilterModule,
NumberFilterModule,
ClientSideRowModelModule,
CsvExportModule,
ExcelExportModule,
ColumnMenuModule,
ContextMenuModule,
]);
let gridApi: GridApi<IOlympicData>;
const isChecked = (selector: string): boolean =>
document.querySelector<HTMLInputElement>(selector)?.checked ?? false;
const getInputValue = (selector: string): string =>
document.querySelector<HTMLInputElement>(selector)?.value ?? "";
const gridOptions: GridOptions<IOlympicData> = {
columnDefs: [
{ 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 },
],
defaultColDef: {
filter: true,
minWidth: 100,
flex: 1,
},
};
function onBtExport() {
const password = getInputValue("#worksheetPassword").trim() || undefined;
const autoFilter = isChecked("#allowAutoFilter");
const formatCells = isChecked("#allowFormatCells");
gridApi!.exportDataAsExcel({
protectSheet: {
password,
autoFilter,
formatCells,
},
});
}
const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);
fetch("https://www.ag-grid.com/example-assets/small-olympic-winners.json")
.then((response) => response.json())
.then(function (data) {
gridApi!.setGridOption("rowData", data);
});
if (typeof window !== "undefined") {
// Attach external event handlers to window so they can be called from index.html
(<any>window).onBtExport = onBtExport;
}
.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%;
}
<div class="container">
<div class="controls">
<label class="option">
Worksheet password (optional):
<input type="text" id="worksheetPassword" value="secret" />
</label>
<label class="option">
<input type="checkbox" id="allowAutoFilter" />
Allow filtering (autoFilter)
</label>
<label class="option">
<input type="checkbox" id="allowFormatCells" />
Allow formatting cells (formatCells)
</label>
<button onclick="onBtExport()" style="margin-bottom: 5px; font-weight: bold">Export to Excel</button>
</div>
<div class="grid-wrapper">
<div id="myGrid"></div>
</div>
</div>
export interface IOlympicData {
athlete: string,
age: number,
country: string,
year: number,
date: string,
sport: string,
gold: number,
silver: number,
bronze: number,
total: number
}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 gridOptions = {
columnDefs: [
{ field: 'athlete', cellClass: 'unlocked' },
{ field: 'country', cellClass: 'unlocked' }
],
excelStyles: [
{
id: 'unlocked',
protection: { protected: false, hideFormula: false }
}
],
defaultExcelExportParams: {
protectSheet: true
},
// other grid options ...
}import {
CellStyleModule,
ClientSideRowModelModule,
CsvExportModule,
ExcelStyle,
GridApi,
GridOptions,
ModuleRegistry,
NumberFilterModule,
TextEditorModule,
TextFilterModule,
createGrid,
enableDevValidations,
} from "ag-grid-community";
import {
ColumnMenuModule,
ContextMenuModule,
ExcelExportModule,
} from "ag-grid-enterprise";
import { IOlympicData } from "./interfaces";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableDevValidations();
}
ModuleRegistry.registerModules([
CellStyleModule,
TextFilterModule,
TextEditorModule,
NumberFilterModule,
ClientSideRowModelModule,
CsvExportModule,
ExcelExportModule,
ColumnMenuModule,
ContextMenuModule,
]);
let gridApi: GridApi<IOlympicData>;
const excelStyles: ExcelStyle[] = [
{
id: "unlocked",
interior: {
color: "#C6EFCE",
pattern: "Solid",
},
protection: {
protected: false,
hideFormula: false,
},
},
];
const gridOptions: GridOptions<IOlympicData> = {
columnDefs: [
{
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" },
],
},
],
defaultColDef: {
filter: true,
minWidth: 100,
flex: 1,
},
excelStyles,
defaultExcelExportParams: {
protectSheet: true,
},
};
function onBtExport() {
gridApi!.exportDataAsExcel();
}
const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);
fetch("https://www.ag-grid.com/example-assets/small-olympic-winners.json")
.then((response) => response.json())
.then(function (data) {
gridApi!.setGridOption("rowData", data);
});
if (typeof window !== "undefined") {
// Attach external event handlers to window so they can be called from index.html
(<any>window).onBtExport = onBtExport;
}
.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%;
}
<div class="container">
<div class="controls">
<button onclick="onBtExport()" style="margin-bottom: 5px; font-weight: bold">Export to Excel</button>
</div>
<div class="grid-wrapper">
<div id="myGrid"></div>
</div>
</div>
export interface IOlympicData {
athlete: string,
age: number,
country: string,
year: number,
date: string,
sport: string,
gold: number,
silver: number,
bronze: number,
total: number
} 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. |