Prevent actions without read-only and protection in React Spreadsheet
22 Jul 202616 minutes to read
In Spreadsheet, the read-only feature makes a range of cells, rows, or columns completely non-editable and restricts all spreadsheet actions on those cells. Similarly, the sheet protection feature locks the entire sheet and restricts all spreadsheet actions on the sheet. It does not allow actions such as formatting cells, rows, or columns, selecting cells, or inserting hyperlinks—unless these options are explicitly enabled in the protectSettings.
If your requirement is to prevent actions (such as cut, paste, autofill, formatting, and validation) without locking the entire sheet using the protectSheet method or making the cells read-only via the setRangeReadOnly method, you can achieve this through event-based customization. This approach allows you to restrict specific actions on selected cells while keeping the rest of the sheet fully interactive.
Events to Use
To achieve this requirement, the following events can be used:
-
cellEdit→ To prevent editing for specific cells. -
actionBegin→ To prevent spreadsheet actions such as cut, paste, autofill, formatting, etc.
Prevent editing for specific cells
To prevent editing for specific cells, use the cellEdit event, which triggers whenever a cell enters edit mode. By checking the column index and setting args.cancel = true, you can prevent editing for those columns. This ensures that users cannot modify the cell content in those columns.
// Triggers when cell editing starts in the spreadsheet.
const cellEdit = (args: any) =>{
var addressRange = getCellIndexes(args.address.split('!')[1]);
// preventing cellEditing from the readOnly columns
if (readOnlyColumns.includes(addressRange[1])) {
args.cancel = true;
}
}
<SpreadsheetComponent ref={spreadsheetRef} cellEdit={cellEdit}>Prevent specific spreadsheet actions
To prevent specific action after preventing the cell editing, you need to use the actionBegin event. This event triggers before any action starts (such as cut, paste, autofill, formatting, etc.). In this event:
- Fetch the target address based on the type of action being performed using
args.actionproperty. - Verify if the target range includes the restricted columns.
- If the column is in the restricted list, cancel the action by setting
cancelproperty totrue.
This approach ensures that spreadsheet actions such as cut, paste, autofill, formatting, validation, and conditional formatting are prevented for specific cells without protecting the sheet or making the cells read-only.
Note: In this example, column indexes are used to restrict actions. You can also apply the same restrictions using row indexes or specific cell addresses. This is a purely component-level customization and, unlike sheet protection, it will not be preserved in the exported file.
The following example demonstrates how to prevent actions such as cut, paste, autofill, formatting, validation, and conditional formatting for specific cells(in the first and third columns) in the spreadsheet without protecting the sheet or making the cells read-only. You can also restrict additional actions by following the same approach.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, getCellIndexes, getRangeIndexes } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
// Columns to be prevented editing.
const readOnlyColumns = [0,2];
// Triggers when cell editing starts in the spreadsheet.
const cellEdit = (args) =>{
var addressRange = getCellIndexes(args.address.split('!')[1]);
// preventing cellEditing from the readOnly columns
if (readOnlyColumns.includes(addressRange[1])) {
args.cancel = true;
}
}
// Triggers whenever any action begins in spreadsheet.
const actionBegin = (args) =>{
var address;
if (args.action == "clipboard") {
address = args.args.eventArgs.pastedRange;
}
else if (args.action == "autofill") {
address = args.args.eventArgs.fillRange;
}
else if (args.action == "format" || args.action == "validation" || args.action == "conditionalFormat") {
address = args.args.eventArgs.range;
}
else if (args.action == "cut") {
address = args.args.copiedRange
}
if (address) {
var addressRange = getRangeIndexes(address);
var colStart = addressRange[1];
var colEnd = addressRange[3];
// preventing other actions from the readOnly columns
for (var col = colStart; col <= colEnd; col++) {
if (readOnlyColumns.includes(col)) {
if (args.args.action == "cut") {
args.args.cancel = true;
} else {
args.args.eventArgs.cancel = true;
}
break;
}
}
}
}
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} cellEdit={cellEdit} actionBegin={actionBegin}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root'));
root.render(<App />);import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, getCellIndexes, getRangeIndexes } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
// Columns to be prevented editing.
const readOnlyColumns: number[] = [0,2];
// Triggers when cell editing starts in the spreadsheet.
const cellEdit = (args: any) =>{
const addressRange: number[] = getCellIndexes(args.address.split('!')[1]);
// preventing cellEditing from the readOnly columns
if (readOnlyColumns.includes(addressRange[1])) {
args.cancel = true;
}
}
// Triggers whenever any action begins in spreadsheet.
const actionBegin = (args: any) =>{
const address: string;
if (args.action == "clipboard") {
address = args.args.eventArgs.pastedRange;
}
else if (args.action == "autofill") {
address = args.args.eventArgs.fillRange;
}
else if (args.action == "format" || args.action == "validation" || args.action == "conditionalFormat") {
address = args.args.eventArgs.range;
}
else if (args.action == "cut") {
address = args.args.copiedRange
}
if (address) {
const addressRange: number[] = getRangeIndexes(address);
const colStart: number = addressRange[1];
const colEnd: number = addressRange[3];
// preventing other actions from the readOnly columns
for (var col: number = colStart; col <= colEnd; col++) {
if (readOnlyColumns.includes(col)) {
if (args.args.action == "cut") {
args.args.cancel = true;
} else {
args.args.eventArgs.cancel = true;
}
break;
}
}
}
}
return (
<div>
<SpreadsheetComponent ref={spreadsheetRef} cellEdit={cellEdit} actionBegin={actionBegin}>
<SheetsDirective>
<SheetDirective>
<RangesDirective>
<RangeDirective dataSource={data}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={130}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);