Data Validation | .NET Excel Library

Overview

The Syncfusion .NET Excel Library (XlsIO) offers comprehensive support for data validation, enabling you to enforce predefined rules that restrict the type and format of data that users can enter into a cell.

.NET Excel Library data validation.

Text length validation

Text length validation allows you to set constraints on the length of text that can be entered in a cell. You can specify the minimum and maximum length of the text, ensuring that the entered value meets the defined criteria.

How to implement data validation for text length in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Create an IWorksheet instance to access the sheet in Excel Library.
Create an instance for data validation using IDataValidation.
Set the AllowType property of the IDataValidation instance to TextLength.
Save the workbook object to a FileStream object.

Here is an example of how to add text length validation in the Excel Library using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    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 less than 5 characters";
    txtLengthValidation.ErrorBoxTitle = "ERROR";
    txtLengthValidation.PromptBoxText = "Data validation for text length";
    txtLengthValidation.ShowPromptBox = true;
 
    //Saving the workbook as stream.
    using (FileStream outputStream = new FileStream("TextLengthValidation.xlsx", FileMode.Create, FileAccess.Write))
    {
        workbook.SaveAs(outputStream);
    }
}

Time validation

Validate time values entered in a cell. You can define criteria such as minimum and maximum time limits, ensuring that the entered time falls within the specified range.

How to implement data validation for time in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Create an IWorksheet instance to access the sheet in Excel Library.
Create an instance for data validation using IDataValidation.
Set the AllowType property of the IDataValidation instance to Time.
Save the workbook object to a FileStream object.

Here is an example of how to add time validation in Excel Library using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Data validation for 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;
 
    //Saving the workbook as stream.
    using (FileStream outputStream = new FileStream("TimeValidation.xlsx", FileMode.Create, FileAccess.Write))
    {
        workbook.SaveAs(outputStream);
    }
}

A dropdown list is a common worksheet feature that is very helpful for restricting a cell value to a predefined set of values. The user can provide an array of values for the list separately or specify them from a cell range inside the workbook. This ensures that the cell value is always one of the expected values.

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Create an IWorksheet instance to access the sheet in Excel Library.
Create an instance for data validation using IDataValidation.
The ListOfValues property should be used when the values in the data validation list are entered manually.
Save the workbook object to a FileStream object.

Here is an example of how to add dropdown list validation in Excel Library using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Data validation for lists.
    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;
 
    //Saving the workbook as stream.
    using (FileStream outputStream = new FileStream("ListValidation.xlsx", FileMode.Create, 
FileAccess.Write))
    {
        workbook.SaveAs(outputStream);
    }
}

Number validation

Number validation enables you to validate numeric inputs in a cell. You can set criteria such as minimum and maximum values, decimal places, or specific numeric formats.

How to implement data validation for numbers in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Create an IWorksheet instance to access the sheet in Excel.
Create an instance for data validation using IDataValidation.
Set the AllowType property of the IDataValidation instance to Integer.
Save the workbook object to a FileStream object.

Here is an example of how to add number validation in Excel Library using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
 {
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //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 Value between 0 to 10";
    numberValidation.ErrorBoxTitle = "ERROR";
    numberValidation.PromptBoxText = "Data validation for numbers";
    numberValidation.ShowPromptBox = true;

    //Saving the workbook as stream.
    using (FileStream outputStream = new FileStream("NumberValidation.xlsx", FileMode.Create, FileAccess.Write))
    {
        workbook.SaveAs(outputStream);
    }
}

Date validation

Date validation allows you to validate date values in a cell. You can define criteria such as minimum and maximum dates, specific date formats, or date ranges.

How to implement data validation for dates in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Create an IWorksheet instance to access the sheet in Excel Library.
Create an instance for data validation using IDataValidation.
Set the AllowType property of the IDataValidation instance to Date.
Save the workbook object to a FileStream object.

Here is an example of how to add date validation in Excel Library using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{  
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Data validation for dates.
    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(2013, 5, 10);
    dateValidation.SecondDateTime = new DateTime(2023, 5, 10);

    //Shows the error message.
    dateValidation.ShowErrorBox = true;
    dateValidation.ErrorBoxText = "Enter Value between 10/5/2003 to 10/5/2004";
    dateValidation.ErrorBoxTitle = "ERROR";
    dateValidation.PromptBoxText = "Data validation for date";
    dateValidation.ShowPromptBox = true;
 
    //Saving the workbook as stream.
    using (FileStream outputStream = new FileStream("DateValidation.xlsx", FileMode.Create, FileAccess.Write))
    {
        workbook.SaveAs(outputStream);
    }
}

Custom or formula validation

Custom Validation offers the flexibility to define custom rules and conditions for validating cell input. You can create your own validation logic based on specific requirements, such as complex formulas, conditional statements, or data comparisons.

How to implement custom data validation in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Create an IWorksheet instance to access the sheet in Excel Library.
Create an instance for data validation using IDataValidation.
Set the AllowType property of the IDataValidation instance to Formula.
Save the workbook object to a FileStream object.

Here is an example of how to add the custom data validation in Excel Library using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
  
    //Data validation for custom data.
    IDataValidation validation = sheet.Range["A3"].DataValidation;
    validation.AllowType = ExcelDataType.Formula;
    validation.FirstFormula = "=A1>10";
   
    //Shows the error message.
    numberValidation.ShowErrorBox = true;
    numberValidation.ErrorBoxText = "A1 value is lesser than 10";
    numberValidation.ErrorBoxTitle = "ERROR";
    numberValidation.PromptBoxText = "Custom Data validation";
    numberValidation.ShowPromptBox = true;
  
    //Saving the workbook as stream.
    using (FileStream outputStream = new FileStream("CustomValidation.xlsx", FileMode.Create, FileAccess.Write))
    {
        workbook.SaveAs(outputStream);
    }
}