Working with Data Validation 

13 Dec 202423 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.

C# [Cross-platform]

//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";

C# [Windows-specific]

//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";

VB.NET [Windows-specific]

'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.

C# [Cross-platform]

//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";

C# [Windows-specific]

//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";

VB.NET [Windows-specific]

'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.

C# [Cross-platform]

//Data validation for list
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };

C# [Windows-specific]

//Data validation for list
IDataValidation validation = sheet.Range["A3"].DataValidation;
validation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };

VB.NET [Windows-specific]

'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.

Number Validation

The following code snippet illustrates how to set number validation.

C# [Cross-platform]

//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";

C# [Windows-specific]

//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";

VB.NET [Windows-specific]

'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.

C# [Cross-platform]

//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);

C# [Windows-specific]

//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);

VB.NET [Windows-specific]

'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.

C# [Cross-platform]

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

C# [Windows-specific]

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

VB.NET [Windows-specific]

'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.

C# [Cross-platform]

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;

  FileStream file = new FileStream("DataValidation.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(file);
  file.Dispose();
}

C# [Windows-specific]

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");
}

VB.NET [Windows-specific]

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