VOOZH about

URL: https://help.syncfusion.com/document-processing/excel/excel-library/net/working-with-data-validation

⇱ Working with Data Validation | Syncfusion

Syncfusion AI Assistant

How can I help you?

Working with Data Validation 

18 Dec 202524 minutes to read

Data Validation is a list of rules to the data that can be entered in a cell. This can be applied by using IDataValidation interface. XlsIO supports following validation types.

  • Text Length Validation
  • Time Validation
  • List Validation
  • Number Validation
  • Date Validation
  • Custom Validation

To quickly get started on creating data validation in an Excel document, please check out this video:

Text Length Validation

The following code snippet illustrates how to set text length validation.

//Data validation for text length
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.TextLength;

//Text length should be lesser than 5 characters
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "0";
validation.SecondFormula = "5";
//Data validation for text length
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.TextLength;

//Text length should be lesser than 5 characters
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "0";
validation.SecondFormula = "5";
'Data validation for text length
Dim validation As IDataValidation = sheet.Range("A3").DataValidation
validation.AllowType = ExcelDataType.TextLength

'Text length should be lesser than 5 characters
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between
validation.FirstFormula = "0"
validation.SecondFormula = "5"

A complete working example for text length data validation in C# is present on this GitHub page.

Time Validation

The following code snippet illustrates how to set time validation.

//Data validation for time
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Time;

//Time between 10:00 and 12:00 'o Clock
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "10.00";
validation.SecondFormula = "12.00";
//Data validation for time
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Time;

//Time between 10:00 and 12:00 'o Clock
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "10.00";
validation.SecondFormula = "12.00";
'Data validation for time
Dim validation As IDataValidation = sheet.Range("A3").DataValidation
validation.AllowType = ExcelDataType.Time

'Time between 10:00 and 12:00 'o Clock
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between
validation.FirstFormula = "10.00"
validation.SecondFormula = "12.00"

A complete working example for time data validation in C# is present on this GitHub page.

List Validation

The following code snippet illustrates how to set list validation.

//Data validation for list
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };
//Data validation for list
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };
'Data validation for list
Dim validation As IDataValidation = sheet.Range("A3").DataValidation
validation.ListOfValues = New String() {"ListItem1", "ListItem2", "ListItem3"}

A complete working example for list data validation in C# is present on this GitHub page.

NOTE

The ListOfValues property should be used when the values in the Data Validation list are entered manually whose limit is only 255 characters including separators.

List Validation with User-defined Range

The following code snippet illustrates how to set list validation for a user-defined range.

//Data validation for the user-defined range
IDataValidation validation = worksheet.Range["C3"].DataValidation;
validation.AllowType = ExcelDataType.User;
validation.FirstFormula = "=Sheet1!$B$1:$B$3";
//Data validation for the user-defined range
IDataValidation validation = worksheet.Range["C3"].DataValidation;
validation.AllowType = ExcelDataType.User;
validation.FirstFormula = "=Sheet1!$B$1:$B$3";
'Data validation for the user-defined range
Dim validation As IDataValidation = worksheet.Range("C3").DataValidation
validation.AllowType = ExcelDataType.User
validation.FirstFormula = "=Sheet1!$B$1:$B$3"

A complete working example of list validation for a user-defined range in C# is present on this GitHub page.

Number Validation

The following code snippet illustrates how to set number validation.

//Data validation for number
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Integer;

//Value between 0 to 10
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "0";
validation.SecondFormula = "10";
//Data validation for number
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Integer;

//Value between 0 to 10
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "0";
validation.SecondFormula = "10";
'Data validation for number
Dim validation As IDataValidation = sheet.Range("A3").DataValidation
validation.AllowType = ExcelDataType.Integer

'Value between 0 to 10
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between
validation.FirstFormula = "0"
validation.SecondFormula = "10"

A complete working example for number data validation in C# is present on this GitHub page.

Date Validation

The following code snippet illustrates how to set date validation.

//Data validation for date
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Date;

//Date between 10/5/2003 to 10/5/2004
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstDateTime = new DateTime(2003, 5, 10);
validation.SecondDateTime = new DateTime(2004, 5, 10);
//Data validation for date
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Date;

//Date between 10/5/2003 to 10/5/2004
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstDateTime = new DateTime(2003, 5, 10);
validation.SecondDateTime = new DateTime(2004, 5, 10);
'Data validation for date
Dim validation As IDataValidation = sheet.Range("A3").DataValidation
validation.AllowType = ExcelDataType.Date

'Date between 10/5/2003 to 10/5/2004
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between
validation.FirstDateTime = New DateTime(2003, 5, 10)
validation.SecondDateTime = New DateTime(2004, 5, 10)

A complete working example for date data validation in C# is present on this GitHub page.

Custom Validation

Custom validation can be set to a cell with its AllowType as User. The following code snippet illustrates how to set custom validation.

//Data validation for custom data
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Formula;
validation.FirstFormula = "=A1>10";
//Data validation for custom data
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.AllowType = ExcelDataType.Formula;
validation.FirstFormula = "=A1>10";
'Data validation for custom data
Dim validation As IDataValidation = sheet.Range("A3").DataValidation
validation.AllowType = ExcelDataType.Formula
validation.FirstFormula = "=A1>10"

The following code snippet shows all the data validation supports discussed previously.

using (ExcelEngine excelEngine = new ExcelEngine())
{
 IApplication application = excelEngine.Excel;
 application.DefaultVersion = ExcelVersion.Excel2013;
 IWorkbook workbook = application.Workbooks.Create(1);
 IWorksheet worksheet = workbook.Worksheets[0];

 //Data Validation for Text Length
 IDataValidation txtLengthValidation = worksheet.Range["A3"].DataValidation;
 worksheet.Range["A1"].Text = "Enter the Text in A3";
 worksheet.Range["A1"].AutofitColumns();
 txtLengthValidation.AllowType = ExcelDataType.TextLength;
 txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 txtLengthValidation.FirstFormula = "0";
 txtLengthValidation.SecondFormula = "5";

 //Shows the error message
 txtLengthValidation.ShowErrorBox = true;
 txtLengthValidation.ErrorBoxText = "Text length should be lesser than 5 characters";
 txtLengthValidation.ErrorBoxTitle = "ERROR";
 txtLengthValidation.PromptBoxText = "Data validation for text length";
 txtLengthValidation.ShowPromptBox = true;

 //Data Validation for the Time
 IDataValidation timeValidation = worksheet.Range["B3"].DataValidation;
 worksheet.Range["B1"].Text = "Enter the time between 10:00 and 12:00 'o Clock in B3";
 worksheet.Range["B1"].AutofitColumns();
 timeValidation.AllowType = ExcelDataType.Time;
 timeValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 timeValidation.FirstFormula = "10.00";
 timeValidation.SecondFormula = "12.00";

 //Shows the error message
 timeValidation.ShowErrorBox = true;
 timeValidation.ErrorBoxText = "Enter a correct time";
 timeValidation.ErrorBoxTitle = "ERROR";
 timeValidation.PromptBoxText = "Data validation for time";
 timeValidation.ShowPromptBox = true;

 //Data Validation for the List
 IDataValidation listValidation = worksheet.Range["C3"].DataValidation;
 worksheet.Range["C1"].Text = "Data Validation List in C3";
 worksheet.Range["C1"].AutofitColumns();
 listValidation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };

 //Shows the error message
 listValidation.ErrorBoxText = "Choose the value from the list";
 listValidation.ErrorBoxTitle = "ERROR";
 listValidation.PromptBoxText = "Data validation for list";
 listValidation.IsPromptBoxVisible = true;
 listValidation.ShowPromptBox = true;

 //Data Validation for Numbers
 IDataValidation numberValidation = worksheet.Range["D3"].DataValidation;
 worksheet.Range["D1"].Text = "Enter the Number in D3";
 worksheet.Range["D1"].AutofitColumns();
 numberValidation.AllowType = ExcelDataType.Integer;
 numberValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 numberValidation.FirstFormula = "0";
 numberValidation.SecondFormula = "10";

 //Shows the error message
 numberValidation.ShowErrorBox = true;
 numberValidation.ErrorBoxText = "Enter a value between 0 to 10";
 numberValidation.ErrorBoxTitle = "ERROR";
 numberValidation.PromptBoxText = "Data validation for numbers";
 numberValidation.ShowPromptBox = true;

 //Data Validation for Date
 IDataValidation dateValidation = worksheet.Range["E3"].DataValidation;
 worksheet.Range["E1"].Text = "Enter the Date in E3";
 worksheet.Range["E1"].AutofitColumns();
 dateValidation.AllowType = ExcelDataType.Date;
 dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 dateValidation.FirstDateTime = new DateTime(2003, 5, 10);
 dateValidation.SecondDateTime = new DateTime(2004, 5, 10);

 //Shows the error message
 dateValidation.ShowErrorBox = true;
 dateValidation.ErrorBoxText = "Enter a value between 10/5/2003 to 10/5/2004";
 dateValidation.ErrorBoxTitle = "ERROR";
 dateValidation.PromptBoxText = "Data validation for date";
 dateValidation.ShowPromptBox = true;

 //Data validation for custom data
 IDataValidation validation = worksheet.Range["A3"].DataValidation;
 validation.AllowType = ExcelDataType.Formula;
 validation.FirstFormula = "=A1>10";

 //Shows the error message
 validation.ErrorBoxText = "Enter a value greater than 10 in A1";
 validation.ErrorBoxTitle = "ERROR";
 validation.PromptBoxText = "Custom DataValidation";
 validation.ShowPromptBox = true;

 workbook.SaveAs("DataValidation.xlsx");
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
 IApplication application = excelEngine.Excel;
 application.DefaultVersion = ExcelVersion.Excel2013;
 IWorkbook workbook = application.Workbooks.Create(1);
 IWorksheet worksheet = workbook.Worksheets[0];

 //Data Validation for Text Length
 IDataValidation txtLengthValidation = worksheet.Range["A3"].DataValidation;
 worksheet.Range["A1"].Text = "Enter the Text in A3";
 worksheet.Range["A1"].AutofitColumns();
 txtLengthValidation.AllowType = ExcelDataType.TextLength;
 txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 txtLengthValidation.FirstFormula = "0";
 txtLengthValidation.SecondFormula = "5";

 //Shows the error message
 txtLengthValidation.ShowErrorBox = true;
 txtLengthValidation.ErrorBoxText = "Text length should be lesser than 5 characters";
 txtLengthValidation.ErrorBoxTitle = "ERROR";
 txtLengthValidation.PromptBoxText = "Data validation for text length";
 txtLengthValidation.ShowPromptBox = true;

 //Data Validation for the Time
 IDataValidation timeValidation = worksheet.Range["B3"].DataValidation;
 worksheet.Range["B1"].Text = "Enter the time between 10:00 and 12:00 'o Clock in B3";
 worksheet.Range["B1"].AutofitColumns();
 timeValidation.AllowType = ExcelDataType.Time;
 timeValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 timeValidation.FirstFormula = "10.00";
 timeValidation.SecondFormula = "12.00";

 //Shows the error message
 timeValidation.ShowErrorBox = true;
 timeValidation.ErrorBoxText = "Enter a correct time";
 timeValidation.ErrorBoxTitle = "ERROR";
 timeValidation.PromptBoxText = "Data validation for time";
 timeValidation.ShowPromptBox = true;

 //Data Validation for the List
 IDataValidation listValidation = worksheet.Range["C3"].DataValidation;
 worksheet.Range["C1"].Text = "Data Validation List in C3";
 worksheet.Range["C1"].AutofitColumns();
 listValidation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };

 //Shows the error message
 listValidation.ErrorBoxText = "Choose the value from the list";
 listValidation.ErrorBoxTitle = "ERROR";
 listValidation.PromptBoxText = "Data validation for list";
 listValidation.IsPromptBoxVisible = true;
 listValidation.ShowPromptBox = true;

 //Data Validation for Numbers
 IDataValidation numberValidation = worksheet.Range["D3"].DataValidation;
 worksheet.Range["D1"].Text = "Enter the Number in D3";
 worksheet.Range["D1"].AutofitColumns();
 numberValidation.AllowType = ExcelDataType.Integer;
 numberValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 numberValidation.FirstFormula = "0";
 numberValidation.SecondFormula = "10";

 //Shows the error message
 numberValidation.ShowErrorBox = true;
 numberValidation.ErrorBoxText = "Enter a value between 0 to 10";
 numberValidation.ErrorBoxTitle = "ERROR";
 numberValidation.PromptBoxText = "Data validation for numbers";
 numberValidation.ShowPromptBox = true;

 //Data Validation for Date
 IDataValidation dateValidation = worksheet.Range["E3"].DataValidation;
 worksheet.Range["E1"].Text = "Enter the Date in E3";
 worksheet.Range["E1"].AutofitColumns();
 dateValidation.AllowType = ExcelDataType.Date;
 dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
 dateValidation.FirstDateTime = new DateTime(2003, 5, 10);
 dateValidation.SecondDateTime = new DateTime(2004, 5, 10);

 //Shows the error message
 dateValidation.ShowErrorBox = true;
 dateValidation.ErrorBoxText = "Enter a value between 10/5/2003 to 10/5/2004";
 dateValidation.ErrorBoxTitle = "ERROR";
 dateValidation.PromptBoxText = "Data validation for date";
 dateValidation.ShowPromptBox = true;

 //Data validation for custom data
 IDataValidation validation = worksheet.Range["A3"].DataValidation;
 validation.AllowType = ExcelDataType.Formula;
 validation.FirstFormula = "=A1>10";

 //Shows the error message
 validation.ErrorBoxText = "Enter a value greater than 10 in A1";
 validation.ErrorBoxTitle = "ERROR";
 validation.PromptBoxText = "Custom DataValidation";
 validation.ShowPromptBox = true;

 workbook.SaveAs("DataValidation.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
 Dim application As IApplication = excelEngine.Excel
 application.DefaultVersion = ExcelVersion.Excel2013
 Dim workbook As IWorkbook = application.Workbooks.Create(1)
 Dim worksheet As IWorksheet = workbook.Worksheets(0)

 'Data Validation for Text Length
 Dim txtLengthValidation As IDataValidation = worksheet.Range("A3").DataValidation
 worksheet.Range("A1").Text = "Enter the Text in A3"
 worksheet.Range("A1").AutofitColumns()
 txtLengthValidation.AllowType = ExcelDataType.TextLength
 txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between
 txtLengthValidation.FirstFormula = "0"
 txtLengthValidation.SecondFormula = "5"

 'Shows the error message
 txtLengthValidation.ShowErrorBox = True
 txtLengthValidation.ErrorBoxText = "Text length should be lesser than 5 characters"
 txtLengthValidation.ErrorBoxTitle = "ERROR"
 txtLengthValidation.PromptBoxText = "Data validation for text length"
 txtLengthValidation.ShowPromptBox = True

 'Data Validation for the Time
 Dim timeValidation As IDataValidation = worksheet.Range("B3").DataValidation
 worksheet.Range("B1").Text = "Enter the time between 10:00 and 12:00 'o Clock in B3"
 worksheet.Range("B1").AutofitColumns()
 timeValidation.AllowType = ExcelDataType.Time
 timeValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between
 timeValidation.FirstFormula = "10.00"
 timeValidation.SecondFormula = "12.00"

 'Shows the error message
 timeValidation.ShowErrorBox = True
 timeValidation.ErrorBoxText = "Enter a correct time"
 timeValidation.ErrorBoxTitle = "ERROR"
 timeValidation.PromptBoxText = "Data validation for time"
 timeValidation.ShowPromptBox = True

 'Data Validation for the List
 Dim listValidation As IDataValidation = worksheet.Range("C3").DataValidation
 worksheet.Range("C1").Text = "Data Validation List in C3"
 worksheet.Range("C1").AutofitColumns()
 listValidation.ListOfValues = New String() {"ListItem1", "ListItem2", "ListItem3"}

 'Shows the error message
 listValidation.ErrorBoxText = "Choose the value from the list"
 listValidation.ErrorBoxTitle = "ERROR"
 listValidation.PromptBoxText = "Data validation for list"
 listValidation.IsPromptBoxVisible = True
 listValidation.ShowPromptBox = True

 'Data Validation for Numbers
 Dim numberValidation As IDataValidation = worksheet.Range("D3").DataValidation
 worksheet.Range("D1").Text = "Enter the Number in D3"
 worksheet.Range("D1").AutofitColumns()
 numberValidation.AllowType = ExcelDataType.Integer
 numberValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between
 numberValidation.FirstFormula = "0"
 numberValidation.SecondFormula = "10"

 'Shows the error message
 numberValidation.ShowErrorBox = True
 numberValidation.ErrorBoxText = "Enter a value between 0 to 10"
 numberValidation.ErrorBoxTitle = "ERROR"
 numberValidation.PromptBoxText = "Data validation for numbers"
 numberValidation.ShowPromptBox = True

 'Data Validation for Date
 Dim dateValidation As IDataValidation = worksheet.Range("E3").DataValidation
 worksheet.Range("E1").Text = "Enter the Date in E3"
 worksheet.Range("E1").AutofitColumns()
 dateValidation.AllowType = ExcelDataType.Date
 dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between
 dateValidation.FirstDateTime = New DateTime(2003, 5, 10)
 dateValidation.SecondDateTime = New DateTime(2004, 5, 10)

 'Shows the error message
 dateValidation.ShowErrorBox = True
 dateValidation.ErrorBoxText = "Enter a value between 10/5/2003 to 10/5/2004"
 dateValidation.ErrorBoxTitle = "ERROR"
 dateValidation.PromptBoxText = "Data validation for date"
 dateValidation.ShowPromptBox = True

 'Data validation for custom data
 Dim validation As IDataValidation = worksheet.Range("A3").DataValidation
 validation.AllowType = ExcelDataType.Formula
 validation.FirstFormula = "=A1>10"

 'Shows the error message
 validation.ErrorBoxText = "Enter a value greater than 10 in A1"
 validation.ErrorBoxTitle = "ERROR"
 validation.PromptBoxText = "Custom DataValidation"
 validation.ShowPromptBox = True

 workbook.SaveAs("DataValidation.xlsx")
End Using

Remove Data Validation

The following code snippet illustrates how to remove data validation rules from a worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
 IApplication application = excelEngine.Excel;
 application.DefaultVersion = ExcelVersion.Xlsx;
 IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
 IWorksheet worksheet = workbook.Worksheets[0];

 //Removing data validation from the worksheet
 worksheet.UsedRange.Clear(ExcelClearOptions.ClearDataValidations);

 //Saving the workbook
 workbook.SaveAs(Path.GetFullPath(@"Output/Output.xlsx"));
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
 IApplication application = excelEngine.Excel;
 application.DefaultVersion = ExcelVersion.Xlsx;
 IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
 IWorksheet worksheet = workbook.Worksheets[0];

 //Removing data validation from the worksheet
 worksheet.UsedRange.Clear(ExcelClearOptions.ClearDataValidations);

 //Saving the workbook
 workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
 Dim application As IApplication = excelEngine.Excel
 application.DefaultVersion = ExcelVersion.Xlsx
 Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
 Dim worksheet As IWorksheet = workbook.Worksheets(0)

 'Removing data validation from the worksheet
 worksheet.UsedRange.Clear(ExcelClearOptions.ClearDataValidations)

 'Saving the workbook 
 workbook.SaveAs("Output.xlsx")
End Using

A complete working example to remove validations from a worksheet in C# is present on this GitHub page.

Help us improve this page

Please provide additional information

Please provide additional information

Please provide additional information

Please provide additional information

Please provide additional information
Please provide additional information
×