Data validation in React Spreadsheet component
10 Jul 202624 minutes to read
Data validation restricts users from entering invalid data. 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.
- Ribbon UI: Select the Data tab, then choose Data Validation.
-
Programmatically: Use the
addDataValidationmethod.
Clear Validation
Clear validation using one of these methods:
- Ribbon UI: Select the Data tab, then choose Clear Validation.
-
Programmatically: Use the
removeDataValidationmethod.
Highlight Invalid Data
Highlight invalid data feature is used to highlight the previously entered invalid values.
- Ribbon UI: Select the Data tab, then choose Highlight Invalid Data.
-
Programmatically: Use the
addInvalidHighlightmethod.
Clear Highlighted Invalid Data
Clear highlight feature is used to remove the highlight from invalid cells.
- Ribbon UI: Select the Data tab, then choose Clear Highlight.
-
Programmatically: Use the
removeInvalidHighlightmethod.
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.
Example: Validate a number between 10 and 100 using the formula =AND(A1>10, A1<100), where A1 is the cell being validated. The Spreadsheet evaluates entered values against the formula and displays an alert if validation fails.
You can apply custom data validation using two methods.
- Ribbon UI: Navigate to Data tab, select Data Validation, then choose Custom from the Allow dropdown.
-
Programmatically: Use the
addDataValidationmethod.
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.