What You Will Need
- .NET 6.0+ application
- Java 8.0+ application
Controls Referenced
- Document Solutions for Excel, .NET (DsExcel) - C# Excel Library
- Documentation | Online Demos
- Document Solutions for Excel, Java (DsExcel) - Java API Library
- Documentation | Online Demos
Tutorial Concept
Learn how to programmatically add Data Validation to an Excel workbook in .NET or Java applications using Excel API libraries.
Validation keeps data uniform and makes analysis simpler.
Data validation helps ensure that data entered in the cell is correct and adheres to predefined rules. In Excel, validation rules provide users with guidance on what data is acceptable, helping them enter data correctly. This minimizes the risk of inaccuracies and maintains data integrity. By implementing robust data validation rules, you can significantly improve the reliability and efficiency of your data-driven processes.
With Document Solutions for Excel (DsExcel), you can programmatically create and modify Excel files with features like data validation, formula calculation, and formatting. It simplifies the automation of Excel tasks in .NET and Java applications. DsExcel offers a variety of data validation types to prevent users from entering invalid data in Excel. In this blog post, we will work with the following product inventory data and apply different validation rules to it using DsExcel.
How to Add Validation with DsExcel:
To add validation on the cell/range using DsExcel, use the Add method of the IValidation interface. You can access this interface via the Validation property of the range.
Below is the complete syntax of the Add method with a description:
void Add(
ValidationType type,
ValidationAlertStyle alertStyle,
ValidationOperator validationOperator,
System.object formula1,
System.object formula2
)
Where:
ValidationType: This specifies the type of validation performed on the data.
ValidationAlertStyle: This specifies the message boxes displayed during validation.
ValidationOperator: This specifies the operator to compare a formula against the value in a cell.
formula1, formula2: This specifies the formula values as per operator. For Between and NotBetween operators, both of these parameters are used.
You can additionally customize error messages and input suggestion dialogs using the ErrorMessage and InputMessage properties of the IValidation interface.
Below is the list of all the validations that we can apply using the DsExcel API:
- Whole Number Validation
- Decimal Validation
- List Validation
- Date Validation
- Time Validation
- Text Length Validation
- Custom Validation
Whole Number Validation
Whole Number Validation allows you to restrict the input in a cell or range of cells to whole numbers only. This is useful in scenarios where only integers are acceptable inputs, such as counting items, setting quantities, or specifying age. To use this validation using DsExcel, you can use ValidationType Whole.
Let’s apply this validation on the Quantity field to restrict the user to adding at least ten pieces of any product. The DsExcel formula for this validation is as follows:
//Add whole number validation
worksheet.Range["D2:D17"].Validation.Add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.GreaterEqual, 10);
IValidation validation = worksheet.Range["D2:D17"].Validation;
validation.IgnoreBlank = true;
validation.InputTitle = "Tips";
validation.InputMessage = "Input a value greater than or equal to 10, please";
validation.ErrorTitle = "Error";
validation.ErrorMessage = "input value is less than 10";
validation.ShowInputMessage = true;
validation.ShowError = true;
Decimal Validation
Decimal Validation allows you to restrict the input in a cell or range of cells to a decimal number. This is particularly useful in scenarios where precise decimal values are required, such as financial calculations, scientific data entry, or any context where exact decimal values are critical. To use this validation using DsExcel, you can use ValidationType Decimal.
In our data, let’s apply this validation on the UnitPrice field to restrict the user to inserting the precise unit price of products in decimals. The DsExcel formula for this validation is as follows:
//Add Decimal validation
worksheet.Range["E2:E17"].Validation.Add(ValidationType.Decimal, ValidationAlertStyle.Stop, ValidationOperator.Between, 0, 100);
validation = worksheet.Range["E2:E17"].Validation;
validation.IgnoreBlank = true;
validation.InputTitle = "Tips";
validation.InputMessage = "Input a value less than 100, please";
validation.ErrorTitle = "Error";
validation.ErrorMessage = "input value is greater than 100";
validation.ShowInputMessage = true;
validation.ShowError = true;
List Validation
List Validation allows you to control the data entered into a cell or range of cells by providing a predefined list of entries. This feature is often utilized for creating drop-down lists in cells, which provides users with a list of choices to select from rather than typing in the data manually. For example, you can create a list of categories, priorities, departments, and so on. To use this validation using DsExcel, you can use ValidationType List and set the InCellDropdown property of the IValidation to true.
Let’s apply this validation on the Category field to restrict the user from inserting values from a predefined category list. The DsExcel formula to apply this validation is as follows:
//Add List Validation
worksheet.Range["C2:C17"].Validation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "Accessories, Gadgets, Tools");
//Display list dropdown
IValidation dvalidation = worksheet.Range["C2:C17"].Validation;
dvalidation.InCellDropdown = true;
Date Validation
Date Validation allows you to restrict the input in a cell or range to valid dates. This is particularly useful in scenarios where dates need to be within a specific date range, such as scheduling events and setting deadlines. To use this validation using DsExcel, you can use ValidationType Date.
Let’s apply this validation on the Expiry Date field to restrict the user not to enter past dates. The DsExcel formula for this validation is as follows:
//Add Date validation
worksheet.Range["G2:G17"].Validation.Add(ValidationType.Date, ValidationAlertStyle.Stop, ValidationOperator.Greater, DateTime.Now);
validation = worksheet.Range["G2:G17"].Validation;
validation.IgnoreBlank = true;
validation.InputTitle = "Tips";
validation.InputMessage = "Expiry date should be greater than current date, please";
validation.ErrorTitle = "Error";
validation.ErrorMessage = "Expiry can't be old date";
validation.ShowInputMessage = true;
validation.ShowError = true;
Time Validation
Time Validation allows you to restrict the input in a cell or range to valid times. This is particularly useful in scenarios where time entries need to be controlled, such as logging work hours or setting deadlines within a specific timeline. To use this validation using DsExcel, you can use ValidationType Time.
Let’s apply this validation on the Last Restocked field to allow users to enter time only within a particular timeframe. The DsExcel formula to apply this validation is below:
//Add Time Validation
worksheet.Range["I2:I17"].Validation.Add(ValidationType.Time, ValidationAlertStyle.Stop, ValidationOperator.Between, new TimeSpan(8, 0, 0), new TimeSpan(18, 0, 0));
validation = worksheet.Range["I2:I17"].Validation;
validation.IgnoreBlank = true;
validation.InputTitle = "Tips";
validation.InputMessage = "Restock should be done from 8:00 am - 6:00 pm , please";
validation.ErrorTitle = "Error";
validation.ErrorMessage = "Time does not fall under 8:00 am - 6:00 pm ";
validation.ShowInputMessage = true;
validation.ShowError = true;
Text Length Validation
Text Length Validation allows you to control the length of the text entered into a cell or range. This ensures that the text input meets specific length requirements, which is particularly useful in scenarios where text entries need to be within predefined limits, such as form fields, ID numbers, and so on. To use this validation using DsExcel, you can use ValidationType TextLength.
Let’s apply this validation on the Description field to restrict the user only to entering specific length information. The DsExcel formula for this validation is as follows:
//Add Text Length Validation
worksheet.Range["H2:H17"].Validation.Add(ValidationType.TextLength, ValidationAlertStyle.Stop, ValidationOperator.Between, 10, 50);
validation = worksheet.Range["H2:H17"].Validation;
validation.IgnoreBlank = true;
validation.InputTitle = "Tips";
validation.InputMessage = "Description should be 8-30 characters long , please";
validation.ErrorTitle = "Error";
validation.ErrorMessage = "Description does not fall under 8-30 characters";
validation.ShowInputMessage = true;
validation.ShowError = true;
Custom Validation
Custom Validation allows you to create your own data validation rules using formulas. This feature provides flexibility beyond the standard validation options, enabling you to enforce complex data entry rules based on specific criteria. To use this validation using DsExcel, you can use ValidationType Custom.ValidationOperator and formula2 parameters are ignored when using this validation.
Let’s use the formula to apply this validation on the Weight field to restrict users from entering products with more than 2 kg of weight. The DsExcel formula for this validation is as follows:
worksheet.Range["J2:J17"].Validation.Add(ValidationType.Custom, ValidationAlertStyle.Information, formula1: "=J2:J17<=2");
validation = worksheet.Range["J2:J17"].Validation;
validation.IgnoreBlank = true;
validation.InputTitle = "Tips";
validation.InputMessage = "The weight of the product should be less than 2 kg, please";
validation.ErrorTitle = "Error";
validation.ErrorMessage = "The weight is not less than 2 kg";
validation.ShowInputMessage = true;
validation.ShowError = true;
And that's it! See the final result in Excel below:
You can download the blog sample to try it out.
Conclusion
In this blog, we explored how to apply different validation rules to Excel for correct data input with suggestion messages. Document Solutions for Excel enhances your Excel capabilities just by adding some simple code. Check out our demos to discover the full capabilities of DsExcel and enhance your Excel experience.
Do you have questions about this tutorial? Drop them in the comments below!
More References:
Top comments (0)