Information validation lets you outline guidelines that limit the kind, size, and information values that may be entered into an Excel cell. By implementing information validation, you’ll be able to stop information entry errors, implement consistency, and enhance the general high quality of your Excel workbooks. Whether or not you’re constructing a knowledge entry kind, creating templates, or automating information import processes, including information validation utilizing C# gives a strong and versatile resolution.
Syncfusion Excel library (XlsIO) is a C# library that gives a high-level API for creating and modifying Excel information. It gives assist for including information validation to your Excel paperwork.
This weblog publish will present you how one can use the Syncfusion Excel Library so as to add information validation to an Excel worksheet. We’ll cowl the next information validation contexts:
Dropdown Record
A dropdown checklist is a typical worksheet function that may be very useful for proscribing a cell worth to a predefined set of values. The consumer can present an array of values for the checklist individually or specify them from a cell vary contained in the workbook. This ensures that the cell worth is at all times one of many anticipated values.
The next code instance illustrates how one can create a dropdown checklist information validation:
//Record information validation
IDataValidation listValidation = worksheet.Vary["C3"].DataValidation;
listValidation.ListOfValues = new string[] { "ListItem1", "ListItem2", "ListItem3" };
Quantity
Quantity validation allows you to specify whether or not the cell ought to settle for solely integers or decimal numbers with particular limits.
The next code instance illustrates how one can set quantity validation:
//Information Validation for Numbers IDataValidation numberValidation = worksheet.Vary["D3"].DataValidation; numberValidation.AllowType = ExcelDataType.Integer; numberValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between; numberValidation.FirstFormula = "0"; numberValidation.SecondFormula = "10";
Textual content size
Textual content size validation is helpful to restrict the variety of characters entered in a cell. By specifying the minimal and most size, you’ll be able to be certain that solely legitimate textual content is entered by the customers.
Right here’s an instance of organising textual content size validation:
//Textual content size information validation IDataValidation txtLengthValidation = worksheet.Vary["A3"].DataValidation; txtLengthValidation.AllowType = ExcelDataType.TextLength; txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between; txtLengthValidation.FirstFormula = "0"; txtLengthValidation.SecondFormula = "5";
Date or time
Limiting date or time values is essential in lots of functions. You possibly can validate and restrict the entered date or time vary to make sure information accuracy. For instance, begin date, finish date, purchase time, and promote time are essential key values that must be correct for processing information and producing a finance report.
The next code instance illustrates how one can set date and time information validation:
// Date validation IDataValidation dateValidation = worksheet.Vary["E3"].DataValidation; dateValidation.AllowType = ExcelDataType.Date; dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between; dateValidation.FirstDateTime = new DateTime(2003, 5, 10); dateValidation.SecondDateTime = new DateTime(2004, 5, 10); // Time validation IDataValidation timeValidation = worksheet.Vary["B3"].DataValidation; timeValidation.AllowType = ExcelDataType.Time; timeValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between; timeValidation.FirstFormula = "10.00"; timeValidation.SecondFormula = "12.00";
Method
In circumstances the place you could consider cell values based mostly on customized situations, method validation could be useful. Excel formulation could be employed to validate cell values in opposition to particular standards.
The next code instance illustrates how one can set method validation:
// Customized information validation IDataValidation dataValidation = worksheet.Vary["A3"].DataValidation; // Set the info validation sort to Method dataValidation.AllowType = ExcelDataType.Method; // Set the method for validation dataValidation.FirstFormula = "=A3>0";
The next instance reveals the code for including information validation to an Excel doc:
utilizing (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication software = excelEngine.Excel;
software.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = software.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Including checklist validation
IDataValidation listValidation = sheet.Vary["C7"].DataValidation;
sheet.Vary["B7"].Textual content = "Choose an merchandise from the dropdown checklist";
listValidation.ListOfValues = new string[] { "Model", "Value", "Product" };
listValidation.PromptBoxText = "Record validation";
listValidation.IsPromptBoxVisible = true;
listValidation.ShowPromptBox = true;
//Including quantity validation
IDataValidation numbervalidation = sheet.Vary["C9"].DataValidation;
sheet.Vary["B9"].Textual content = "Enter a quantity between 0 to 10";
numbervalidation.AllowType = ExcelDataType.Integer;
numbervalidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
numbervalidation.FirstFormula = "0";
numbervalidation.SecondFormula = "10";
numbervalidation.ShowErrorBox = true;
numbervalidation.ErrorBoxText = "Enter worth between solely 0 to 10";
numbervalidation.ErrorBoxTitle = "ERROR";
numbervalidation.PromptBoxText = "Quantity validation";
numbervalidation.ShowPromptBox = true;
//Including date validation
IDataValidation dateValidation = sheet.Vary["C11"].DataValidation;
sheet.Vary["B11"].Textual content = "Enter a date between 5/10/2003 to five/10/2004";
dateValidation.AllowType = ExcelDataType.Date;
dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
dateValidation.FirstDateTime = new DateTime(2003, 5, 10);
dateValidation.SecondDateTime = new DateTime(2004, 5, 10);
dateValidation.ShowErrorBox = true;
dateValidation.ErrorBoxText = "Enter worth between 5/10/2003 to five/10/2004";
dateValidation.ErrorBoxTitle = "ERROR";
dateValidation.PromptBoxText = "Date validation";
dateValidation.ShowPromptBox = true;
//Including textual content size validation
IDataValidation textValidation = sheet.Vary["C13"].DataValidation;
sheet.Vary["B13"].Textual content = "Enter a textual content of 6 characters or much less";
textValidation.AllowType = ExcelDataType.TextLength;
textValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
textValidation.FirstFormula = "1";
textValidation.SecondFormula = "6";
textValidation.ShowErrorBox = true;
textValidation.ErrorBoxText = "Enter a textual content with size of most 6 characters";
textValidation.ErrorBoxTitle = "ERROR";
textValidation.PromptBoxText = "Textual content size validation";
textValidation.ShowPromptBox = true;
//Including time validation
IDataValidation timeValidation = sheet.Vary["C15"].DataValidation;
sheet.Vary["B15"].Textual content = "Enter a time between 10:00 AM to 12:00 PM";
timeValidation.AllowType = ExcelDataType.Time;
timeValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
timeValidation.FirstFormula = "10";
timeValidation.SecondFormula = "12";
timeValidation.ShowErrorBox = true;
timeValidation.ErrorBoxText = "Enter the time between 10 to 12 ";
timeValidation.ErrorBoxTitle = "ERROR";
timeValidation.PromptBoxText = "Time validation";
timeValidation.ShowPromptBox = true;
//Including time validation
IDataValidation formulaValidation = sheet.Vary["C17"].DataValidation;
sheet.Vary["B17"].Textual content = "Enter a unfavourable quantity";
formulaValidation.AllowType = ExcelDataType.Method;
formulaValidation.FirstFormula = "=C17 < 0";
formulaValidation.ShowErrorBox = true;
formulaValidation.ErrorBoxText = "Enter solely unfavourable numbers";
formulaValidation.ErrorBoxTitle = "ERROR";
formulaValidation.PromptBoxText = "Method validation";
formulaValidation.ShowPromptBox = true;
sheet.Vary["B2:C2"].Merge();
sheet.Vary["B2"].Textual content = "Information validation";
sheet.Vary["B5"].Textual content = "Validation standards";
sheet.Vary["C5"].Textual content = "Validation";
sheet.Vary["B5"].CellStyle.Font.Daring = true;
sheet.Vary["C5"].CellStyle.Font.Daring = true;
sheet.Vary["B2"].CellStyle.Font.Daring = true;
sheet.Vary["B2"].CellStyle.Font.Measurement = 16;
sheet.Vary["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.UsedRange.AutofitColumns();
sheet.UsedRange.AutofitRows();
//Saving the workbook
FileStream outputStream = new FileStream("DataValidation.xlsx", FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
outputStream.Dispose();
}
GitHub samples
You possibly can obtain examples of information validation in C# from this GitHub web page.
Conclusion
As you’ll be able to see, the Syncfusion Excel (XlsIO) Library helps information validation in C#. Take a second to peruse the documentation, the place you’ll discover different Excel choices and options like conditional formatting, tables, pivot tables, and charts.
Utilizing the XlsIO library, you’ll be able to export Excel information to PDF, picture, information desk, CSV, TSV, HTML, collections of objects, ODS, JSON, and extra file codecs.
In case you are new to our Excel Library, following our getting began information is extremely really useful.
Are you already a Syncfusion consumer? You possibly can obtain the product setup right here. In case you’re not a Syncfusion consumer but, you’ll be able to obtain a free 30-day trial right here.
Please tell us within the feedback part under if in case you have any questions on these options. Additionally, you’ll be able to contact us by our assist discussion board, assist portal, or suggestions portal. We’re at all times completely satisfied to help you!
