How can I help you?
Data validation in EJ2 React Spreadsheet control
17 Feb 202624 minutes to read
Data Validation is used to restrict the user from entering the invalid data. You can use the allowDataValidation property to enable or disable data validation.
- The default value for
allowDataValidationproperty istrue.
Apply Validation
You can apply data validation to restrict the type of data or the values that users enter into a cell.
You can apply data validation by using one of the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Data Validation item.
- Use the
addDataValidation()method programmatically.
Clear Validation
Clear validation feature is used to remove data validations from the specified ranges or the whole worksheet.
You can clear data validation rule by one of the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Clear Validation item.
- Use the
removeDataValidation()method programmatically.
Highlight Invalid Data
Highlight invalid data feature is used to highlight the previously entered invalid values.
You can highlight an invalid data by using one of the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Highlight Invalid Data item.
- Use the
addInvalidHighlight()method programmatically.
Clear Highlighted Invalid Data
Clear highlight feature is used to remove the highlight from invalid cells.
You can clear the highlighted invalid data by using the following ways,
- Select the Data tab in the Ribbon toolbar, and then choose the Clear Highlight item.
- Use the
removeInvalidHighlight()method programmatically.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef(null);
const boldCenter = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Data validation to range.
spreadsheet.addDataValidation({ type: 'TextLength', operator: 'LessThanOrEqualTo', value1: '4' }, 'A2:A5');
spreadsheet.addDataValidation({ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }, 'B2:B5');
spreadsheet.addDataValidation({ type: 'Date', operator: 'NotEqualTo', value1: '04/11/2019' }, 'F2:F5');
spreadsheet.addDataValidation({ type: 'Time', operator: 'Between', value1: '10:00:00 AM', value2: '11:00:00 AM' }, 'G2:G5');
spreadsheet.addDataValidation({ type: 'Decimal', operator: 'LessThan', value1: '100000.00' }, 'H2:H5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('A1:H5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Sales Date'} style={boldCenter}></CellDirective>
<CellDirective index={6} value={'Billing Time'} style={boldCenter}></CellDirective>
<CellDirective index={7} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation=></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'} validation=></CellDirective>
<CellDirective index={4} value={'50000'} validation=></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'2'} validation=></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'} validation=></CellDirective>
<CellDirective index={4} value={'45000'} validation=></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'3'} validation=></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'} validation=></CellDirective>
<CellDirective index={4} value={'450'} validation=></CellDirective>
<CellDirective index={5} value={'06/25/2019'}></CellDirective>
<CellDirective index={6} value={'01:30:11 PM'}></CellDirective>
<CellDirective index={7} value={'545.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation=></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'} validation=></CellDirective>
<CellDirective index={4} value={'90000'} validation=></CellDirective>
<CellDirective index={5} value={'09/22/2019'}></CellDirective>
<CellDirective index={6} value={'12:30:02 PM'}></CellDirective>
<CellDirective index={7} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={86}></ColumnDirective>
<ColumnDirective width={107}></ColumnDirective>
<ColumnDirective width={81}></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, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const boldCenter: CellStyleModel = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Data validation to range.
spreadsheet.addDataValidation({ type: 'TextLength', operator: 'LessThanOrEqualTo', value1: '4' }, 'A2:A5');
spreadsheet.addDataValidation({ type: 'WholeNumber', operator: 'NotEqualTo', value1: '1' }, 'B2:B5');
spreadsheet.addDataValidation({ type: 'Date', operator: 'NotEqualTo', value1: '04/11/2019' }, 'F2:F5');
spreadsheet.addDataValidation({ type: 'Time', operator: 'Between', value1: '10:00:00 AM', value2: '11:00:00 AM' }, 'G2:G5');
spreadsheet.addDataValidation({ type: 'Decimal', operator: 'LessThan', value1: '100000.00' }, 'H2:H5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('A1:H5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Sales Date'} style={boldCenter}></CellDirective>
<CellDirective index={6} value={'Billing Time'} style={boldCenter}></CellDirective>
<CellDirective index={7} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation=></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'} validation=></CellDirective>
<CellDirective index={4} value={'50000'} validation=></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'2'} validation=></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'} validation=></CellDirective>
<CellDirective index={4} value={'45000'} validation=></CellDirective>
<CellDirective index={5} value={'04/11/2019'}></CellDirective>
<CellDirective index={6} value={'11:34:32 AM'}></CellDirective>
<CellDirective index={7} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'3'} validation=></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'} validation=></CellDirective>
<CellDirective index={4} value={'450'} validation=></CellDirective>
<CellDirective index={5} value={'06/25/2019'}></CellDirective>
<CellDirective index={6} value={'01:30:11 PM'}></CellDirective>
<CellDirective index={7} value={'545.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'1'} validation=></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'} validation=></CellDirective>
<CellDirective index={4} value={'90000'} validation=></CellDirective>
<CellDirective index={5} value={'09/22/2019'}></CellDirective>
<CellDirective index={6} value={'12:30:02 PM'}></CellDirective>
<CellDirective index={7} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={86}></ColumnDirective>
<ColumnDirective width={107}></ColumnDirective>
<ColumnDirective width={81}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);Custom Data validation
The Spreadsheet supports custom data validation, allowing users to define their own validation rules for specific cells or ranges. This feature enables you to set conditions that the entered data must meet, making it particularly useful when predefined validation options, such as numbers, dates, or lists, are insufficient.
With custom validation, you can enforce rules using logical expressions or formulas, ensuring that only valid data is entered into the Spreadsheet.
For example, consider a scenario where you want to ensure that a cell contains a number between 10 and 100. To achieve this, define a validation rule using a formula that checks if the entered value is greater than 10 and less than 100. The formula for this validation is =AND(A1>10, A1<100), where A1 refers to the cell being validated.
When this rule is applied, the Spreadsheet evaluates the entered value against the formula. If a user enters a value outside the specified range, an alert notifies them of the invalid input. This helps users correct errors efficiently and ensures that only desired values are accepted.
You can apply custom data validation using two methods.
- The first is through the Data Validation dialog in the Ribbon toolbar. Navigate to the Data tab, select the Data Validation option, and choose the Custom type from the Allow dropdown menu.
- The second method is programmatically, using the
addDataValidation()method, which allows developers to set custom rules dynamically via code.
The following code example demonstrates how to add custom data validation with a formula in a Spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef(null);
const boldCenter = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Custom Data validation to range.
spreadsheet.addDataValidation({ type: 'Custom', value1: '=AND(B2>10, B2<100)' }, 'E2:E5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('E2:E5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'}></CellDirective>
<CellDirective index={4} value={'50000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'25'}></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'}></CellDirective>
<CellDirective index={4} value={'45000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'35'}></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'}></CellDirective>
<CellDirective index={4} value={'35000'}></CellDirective>
<CellDirective index={5} value={'1,54,500.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'}></CellDirective>
<CellDirective index={4} value={'90000'}></CellDirective>
<CellDirective index={5} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={81}></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, RowsDirective, RowDirective, CellsDirective, CellDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const boldCenter: CellStyleModel = { fontWeight: 'bold', textAlign: 'center' };
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
//Add Custom Data validation to range.
spreadsheet.addDataValidation({ type: 'Custom', value1: '=AND(B2>10, B2<100)' }, 'E2:E5');
//Highlight Invalid Data.
spreadsheet.addInvalidHighlight('E2:E5');
}
}, []);
return (
<div> <SpreadsheetComponent ref={spreadsheetRef} showFormulaBar={false}>
<SheetsDirective>
<SheetDirective name={'PriceDetails'}>
<RowsDirective>
<RowDirective index={0}>
<CellsDirective>
<CellDirective index={0} value={'Seller Name'} style={boldCenter}></CellDirective>
<CellDirective index={1} value={'Customer Id'} style={boldCenter}></CellDirective>
<CellDirective index={2} value={'Customer Name'} style={boldCenter}></CellDirective>
<CellDirective index={3} value={'Product Name'} style={boldCenter}></CellDirective>
<CellDirective index={4} value={'Product Price'} style={boldCenter}></CellDirective>
<CellDirective index={5} value={'Total Price'} style={boldCenter}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={1}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'Digger'}></CellDirective>
<CellDirective index={4} value={'50000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={2}>
<CellsDirective>
<CellDirective index={0} value={'Mike'}></CellDirective>
<CellDirective index={1} value={'25'}></CellDirective>
<CellDirective index={2} value={'Jim'}></CellDirective>
<CellDirective index={3} value={'Cherrypicker'}></CellDirective>
<CellDirective index={4} value={'45000'}></CellDirective>
<CellDirective index={5} value={'1,45,000.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={3}>
<CellsDirective>
<CellDirective index={0} value={'shane'}></CellDirective>
<CellDirective index={1} value={'35'}></CellDirective>
<CellDirective index={2} value={'Sean'}></CellDirective>
<CellDirective index={3} value={'Kango'}></CellDirective>
<CellDirective index={4} value={'35000'}></CellDirective>
<CellDirective index={5} value={'1,54,500.00'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective index={4}>
<CellsDirective>
<CellDirective index={0} value={'John'}></CellDirective>
<CellDirective index={1} value={'101'}></CellDirective>
<CellDirective index={2} value={'Nash'}></CellDirective>
<CellDirective index={3} value={'JCB'}></CellDirective>
<CellDirective index={4} value={'90000'}></CellDirective>
<CellDirective index={5} value={'1,00,095.00'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<ColumnsDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={106}></ColumnDirective>
<ColumnDirective width={98}></ColumnDirective>
<ColumnDirective width={88}></ColumnDirective>
<ColumnDirective width={81}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);Limitations of Data validation
The following features have some limitations in Data Validation:
- Entire row data validation.
- Insert row between the data validation.
- Copy/paste with data validation.
- Delete cells between data validation applied range.