Data Validation

17 Mar 2023 / 4 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 allowDataValidation property is true.

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.

Source

Preview

app.ts

index.html

import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';

let sheet: SheetModel[] = [
{
    name: 'PriceDetails',
    rows: [
        {
                index: 0,
                cells: [{ index: 0, value: 'Seller Name', style:{fontWeight : "bold", textAlign: "center"} },
                { index: 1, value: 'Customer Id', style:{fontWeight : "bold", textAlign: "center"} },
                { index: 2, value: 'Customer Name', style:{fontWeight : "bold", textAlign: "center"} },
                { index: 3, value: 'Product Name', style:{fontWeight : "bold", textAlign: "center"} },
                { index: 4, value: 'Product Price', style:{fontWeight : "bold", textAlign: "center"} },
                { index: 5, value: 'Sales Date', style:{fontWeight : "bold", textAlign: "center"} },
                { index: 5, value: 'Billing Time', style:{fontWeight : "bold", textAlign: "center"} },
                { index: 6, value: 'Total Price', style:{fontWeight : "bold", textAlign: "center"} }]
            },
            {
                index: 1,
                cells: [{ index: 0, value: 'John'},
                { index: 1, value: '1', validation: { type: 'WholeNumber', operator: 'NotEqualTo', value1: '1'}},
                { index: 2, value: 'Nash'},
                { index: 3, value: 'Digger', validation:{ type: 'List', value1: 'Digger, Digger, Cherrypicker' }},
                { index: 4, value: '50000', validation:{ type: 'List', value1: '50000,50000,45000' }},
                { index: 5, value: '04/11/2019'},
                { index: 6, value: '11:34:32 AM'},
                { index: 7, value: '1,45,000.00'}]
            },
            {
                index: 2,
                cells: [{ index: 0, value: 'Mike'},
                { index: 1, value: '2', validation: { type: 'WholeNumber', operator: 'NotEqualTo', value1: '1'}},
                { index: 2, value: 'Jim'},
                { index: 3, value: 'Cherrypicker', validation:{ type: 'List', value1: 'Cherrypicker, JCB, Wheelbarrow' }},
                { index: 4, value: '45000', validation:{ type: 'List', value1: '45000,90000,40' }},
                { index: 5, value: '04/11/2019'},
                { index: 6, value: '10:15:00 AM'},
                { index: 7, value: '1,40,040.00'}]
            },
            {
                index: 3,
                cells: [{ index: 0, value: 'shane' },
                { index: 1, value: '3', validation: { type: 'WholeNumber', operator: 'NotEqualTo', value1: '1'}},
                { index: 2, value: 'Sean'},
                { index: 3, value: 'Kango', validation:{ type: 'List', value1: 'Kango, Ropes' }},
                { index: 4, value: '450', validation:{ type: 'List', value1: '450, 95' }},
                { index: 5, value: '06/25/2019'},
                { index: 6, value: '01:30:11 PM'},
                { index: 7, value: '545.00'}]
            },
            {
                index: 4,
                cells: [{ index: 0, value: 'John' },
                { index: 1, value: '1', validation: { type: 'WholeNumber', operator: 'NotEqualTo', value1: '1'}},
                { index: 2, value: 'Nash'},
                { index: 3, value: 'JCB', validation:{ type: 'List', value1: 'JCB, Ropes, scaffolding' }},
                { index: 4, value: '90000', validation:{ type: 'List', value1: '90000, 95, 10000' }},
                { index: 5, value: '09/22/2019'},
                { index: 6, value: '12:30:02 PM'},
                { index: 7, value: '1,00,095.00' }]
            }
    ],
    columns: [
        { width: 88,  }, { width: 88 }, { width: 106 }, { width: 98 }, { width: 88 }, { width: 86 }, { width: 107 }, { width: 81}
    ]
}
];

//Initialize the SpreadSheet control
let spreadsheet: Spreadsheet = new Spreadsheet({
  sheets: sheet,
  created: () => {
        //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');
    }
});

spreadsheet.appendTo('#spreadsheet');

<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Data Validation

Apply Validation

Clear Validation

Highlight Invalid Data

Clear Highlighted Invalid Data

See Also