We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback


Trusted by the world’s leading companies

Syncfusion Trusted Companies

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 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#

Here is an example of how to add text length validation in the Excel Library using 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#

Here is an example of how to add time validation in Excel Library using 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.

How to implement data validation for a dropdown list in C#

Here is an example of how to add dropdown list validation in Excel Library using 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#

Here is an example of how to add number validation in Excel Library using 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#

Here is an example of how to add date validation in Excel Library using 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#

Here is an example of how to add the custom data validation in Excel Library using 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);
    }
}



Awards

Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion is proud to hold the following industry awards.

Scroll up icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon