Interface IDataValidation

Represents data validation for a worksheet range.

Inherited Members

IParentApplication.Application

IParentApplication.Parent

IOptimizedUpdate.BeginUpdate()

IOptimizedUpdate.EndUpdate()

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Base.dll

Syntax

public interface IDataValidation : IParentApplication, IOptimizedUpdate

Examples

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IDataValidation validation = sheet.Range["A3"].EntireColumn.DataValidation;
validation.BeginUpdate();
validation.DataRange = sheet.Range["D1:D56"];
validation.IsEmptyCellAllowed = true;
validation.IsListInFormula = false;
validation.EndUpdate();

Properties

AllowType

Gets or sets the data type validation for a range. AllowType is allows the data with restricted Datatypes or Rules using ExcelDataType enumeration.

Declaration

ExcelDataType AllowType { get; set; }

Property Value

Type	Description
ExcelDataType

Remarks

Specifies the type of validation test to be performed in conjunction with values. To know more about AllowType property refer this link.

Examples

The following code illustrates the use of AllowType property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Text Vaidation";
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Data validation for text length
           IDataValidation dataValidation = worksheet.Range["A2"].DataValidation;
           dataValidation.AllowType = ExcelDataType.TextLength; // Allows the data with restricted Datatypes/Rules
           dataValidation.CompareOperator = ExcelDataValidationComparisonOperator.GreaterOrEqual;
           dataValidation.FirstFormula = "5";

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time; // Allows the data with restricted Datatypes/Rules
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dataValidation2.FirstFormula = "10:00";
           dataValidation2.SecondFormula = "12:00";

           //Data validation for number
           IDataValidation dataValidation3 = worksheet.Range["E2"].DataValidation;
           dataValidation3.AllowType = ExcelDataType.Integer; // Allows the data with restricted Datatypes/Rules
           dataValidation3.CompareOperator = ExcelDataValidationComparisonOperator.NotBetween;
           dataValidation3.FirstFormula = "0";
           dataValidation3.SecondFormula = "10";

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

CompareOperator

Gets or sets the comparison operation to perform. CompareOperator for Data Validation works with ExcelDataValidationComparisonOperator enumeration and two formula values.

Declaration

ExcelDataValidationComparisonOperator CompareOperator { get; set; }

Property Value

Type	Description
ExcelDataValidationComparisonOperator

Remarks

Comparison operator between Two formula values. To know more about CompareOperator property refer this link.

Examples

The following code illustrates the use of CompareOperator property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Text Vaidation";
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Data validation for text length
           IDataValidation dataValidation = worksheet.Range["A2"].DataValidation;
           dataValidation.AllowType = ExcelDataType.TextLength;
           dataValidation.CompareOperator = ExcelDataValidationComparisonOperator.GreaterOrEqual; //Comparison operation for Data Validation
           dataValidation.FirstFormula = "5";

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time;
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between; //Comparison operation for Data Validation
           dataValidation2.FirstFormula = "10:00";
           dataValidation2.SecondFormula = "12:00";

           //Data validation for number
           IDataValidation dataValidation3 = worksheet.Range["E2"].DataValidation;
           dataValidation3.AllowType = ExcelDataType.Integer;
           dataValidation3.CompareOperator = ExcelDataValidationComparisonOperator.NotBetween; //Comparison operation for Data Validation
           dataValidation3.FirstFormula = "0";
           dataValidation3.SecondFormula = "10";

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

DataRange

Gets or sets the Range of possible values. The list of DataRange cell values are set as dropdown list of DataValidation Range.

Declaration

IRange DataRange { get; set; }

Property Value

Type	Description
IRange

Remarks

In Output of an excel the list of DataRange cell values are in dropdown list of DataValidation Range. To know more about DataRange property refer this link.

Examples

The following code illustrates the use of DataRange property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Select the Day in A2";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["D1"].Text = "Days in a Week";
           worksheet.Range["D2"].Text = "Monday";
           worksheet.Range["D3"].Text = "Tuesday";
           worksheet.Range["D4"].Text = "Wednesday";
           worksheet.Range["D5"].Text = "Thursday";
           worksheet.Range["D6"].Text = "Friday";
           worksheet.Range["D7"].Text = "Saturday";
           worksheet.Range["D8"].Text = "Sunday";
           worksheet.Range["D1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation validation = worksheet.Range["A2"].DataValidation;
           validation.DataRange = worksheet.Range["D2:D8"]; //DataRange for Data Validation
           validation.IsEmptyCellAllowed = true;
           validation.IsSuppressDropDownArrow = false;

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

ErrorBoxText

Gets or sets the error message in the error dialog. The user being alerted whenever enters an invalid data. The errorbox text can be edit based on the validation.

Declaration

string ErrorBoxText { get; set; }

Property Value

Type	Description
System.String

Remarks

The data validation error message will be shown whenever the user enters invalid data. To know more about ErrorBoxText property refer this link.

Examples

The following code illustrates the use of ErrorBoxText property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Text Vaidation";
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Data validation for text length
           IDataValidation dataValidation = worksheet.Range["A2"].DataValidation;
           dataValidation.AllowType = ExcelDataType.TextLength; 
           dataValidation.CompareOperator = ExcelDataValidationComparisonOperator.GreaterOrEqual;
           dataValidation.FirstFormula = "5";
           dataValidation.ShowErrorBox = true; 
           dataValidation.ErrorBoxText = "Text length should be greater than or equal to 5"; //Error message for text length validation
           dataValidation.ErrorBoxTitle = "ERROR";
           dataValidation.ErrorStyle = ExcelErrorStyle.Warning;

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time; 
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dataValidation2.FirstFormula = "10:00";
           dataValidation2.SecondFormula = "12:00";
           dataValidation2.ShowErrorBox = false;
           dataValidation2.ErrorBoxText = "Time between 10:00 and 12:00 'o Clock";  //Error message for time validation
           dataValidation2.ErrorBoxTitle = "ERROR";
           dataValidation2.ErrorStyle = ExcelErrorStyle.Info;

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

ErrorBoxTitle

Gets or sets the title of the error dialog. The errorbox title can be edit based on validation or user's needs.

Declaration

string ErrorBoxTitle { get; set; }

Property Value

Type	Description
System.String

Remarks

To know more about ErrorBoxTitle property refer this link.

Examples

The following code illustrates the use of ErrorBoxTitle property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Text Vaidation";
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Data validation for text length
           IDataValidation dataValidation = worksheet.Range["A2"].DataValidation;
           dataValidation.AllowType = ExcelDataType.TextLength; 
           dataValidation.CompareOperator = ExcelDataValidationComparisonOperator.GreaterOrEqual;
           dataValidation.FirstFormula = "5";
           dataValidation.ShowErrorBox = true; 
           dataValidation.ErrorBoxText = "Text length should be greater than or equal to 5"; 
           dataValidation.ErrorBoxTitle = "ERROR"; //Error box title for text length validation
           dataValidation.ErrorStyle = ExcelErrorStyle.Warning;

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time; 
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dataValidation2.FirstFormula = "10:00";
           dataValidation2.SecondFormula = "12:00";
           dataValidation2.ShowErrorBox = false;
           dataValidation2.ErrorBoxText = "Time between 10:00 and 12:00 'o Clock";
           dataValidation2.ErrorBoxTitle = "Alert"; //Error box title for time validation
           dataValidation2.ErrorStyle = ExcelErrorStyle.Info;

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

ErrorStyle

Gets or sets the type of error. Using ExcelDataValidationComparisonOperator enumeration we can change the Style/Type of an ErrorBox in ErrorStyle property.

Declaration

ExcelErrorStyle ErrorStyle { get; set; }

Property Value

Type	Description
ExcelErrorStyle

Remarks

To know more about ErrorStyle property refer this link.

Examples

The following code illustrates the use of ErrorStyle property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Text Vaidation";
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Data validation for text length
           IDataValidation dataValidation = worksheet.Range["A2"].DataValidation;
           dataValidation.AllowType = ExcelDataType.TextLength; 
           dataValidation.CompareOperator = ExcelDataValidationComparisonOperator.GreaterOrEqual;
           dataValidation.FirstFormula = "5";
           dataValidation.ShowErrorBox = true; 
           dataValidation.ErrorBoxText = "Text length should be greater than or equal to 5"; 
           dataValidation.ErrorBoxTitle = "ERROR"; 
           dataValidation.ErrorStyle = ExcelErrorStyle.Warning; //Warning Type error box

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time; 
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dataValidation2.FirstFormula = "10:00";
           dataValidation2.SecondFormula = "12:00";
           dataValidation2.ShowErrorBox = false;
           dataValidation2.ErrorBoxText = "Time between 10:00 and 12:00 'o Clock";
           dataValidation2.ErrorBoxTitle = "Alert";
           dataValidation2.ErrorStyle = ExcelErrorStyle.Info; //Information Type error box

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

FirstDateTime

Gets or sets the first formula's DateTime value. FirstDateTime must important to validate data in CompareOperator property.

Declaration

DateTime FirstDateTime { get; set; }

Property Value

Type	Description
System.DateTime

Remarks

Value of the property must be a DateTime. To know more about FirstDateTime property refer this link.

Examples

The following code illustrates the use of FirstDateTime property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["E1"].Text = "Enter the Date in E3";
           worksheet.Range["E1"].AutofitColumns();
           worksheet.Range["E3"].CellStyle.Color = System.Drawing.Color.LightGray;

           // DataValidation in Worksheet Range
           IDataValidation dateValidation = worksheet.Range["E3"].DataValidation;
           dateValidation.AllowType = ExcelDataType.Date;
           dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dateValidation.FirstDateTime = new DateTime(2003, 5, 10); // FirstDateTime Formula sample value.
           dateValidation.SecondDateTime = new DateTime(2004, 5, 10);
           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;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

FirstFormula

Gets or sets the value or expression associated with the data validation. Firstformula must important to validate data in CompareOperator property.

Declaration

string FirstFormula { get; set; }

Property Value

Type	Description
System.String

Remarks

This can be a constant value, a string value, a cell reference, or a formula. To know more about FirstFormula property refer this link.

Examples

The following code illustrates the use of FirstFormula property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Text Vaidation";
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Data validation for text length
           IDataValidation dataValidation = worksheet.Range["A2"].DataValidation;
           dataValidation.AllowType = ExcelDataType.TextLength; 
           dataValidation.CompareOperator = ExcelDataValidationComparisonOperator.GreaterOrEqual;
           dataValidation.FirstFormula = "5"; //DataValidation Value for First Formula

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time; 
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dataValidation2.FirstFormula = "10:00"; //DataValidation Value for First Formula
           dataValidation2.SecondFormula = "12:00";

           //Data validation for number
           IDataValidation dataValidation3 = worksheet.Range["E2"].DataValidation;
           dataValidation3.AllowType = ExcelDataType.Integer; 
           dataValidation3.CompareOperator = ExcelDataValidationComparisonOperator.NotBetween;
           dataValidation3.FirstFormula = "0"; //DataValidation Value for First Formula
           dataValidation3.SecondFormula = "10";

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

IsEmptyCellAllowed

True if empty values are permitted by the range data validation. otherwise False.

Declaration

bool IsEmptyCellAllowed { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

To know more about IsEmptyCellAllowed property refer this link.

Examples

The following code illustrates the use of IsEmptyCellAllowed property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Select the Day in A2";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["D1"].Text = "Days in a Week";
           worksheet.Range["D2"].Text = "Monday";
           worksheet.Range["D3"].Text = "Tuesday";
           worksheet.Range["D4"].Text = "Wednesday";
           worksheet.Range["D5"].Text = "Thursday";
           worksheet.Range["D6"].Text = "Friday";
           worksheet.Range["D7"].Text = "Saturday";
           worksheet.Range["D8"].Text = "Sunday";
           worksheet.Range["D1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation validation = worksheet.Range["A2"].DataValidation;
           validation.DataRange = worksheet.Range["D2:D8"];
           validation.IsEmptyCellAllowed = true; // Set true to allow Empty cells.
           validation.IsSuppressDropDownArrow = false;

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

IsListInFormula

True if formula contains list of values. otherwise False.

Declaration

bool IsListInFormula { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

Default value for IsListInFormula is false. Mostly the IsListInFormula property used for internal purpose. To know more about IsListInFormula property refer this link.

Examples

The following code illustrates the use of IsListInFormula property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };

           //True if dataValiadation's ListOfValues property has a value       
           if(dataValidation.IsListInFormula) 
           {
              //Your code here
           }
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

IsPromptBoxPositionFixed

True if prompt box position is fixed. otherwise False.

Declaration

bool IsPromptBoxPositionFixed { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

Default value for IsPromptBoxPositionFixed is false. Mostly the IsPromptBoxPositionFixed property used for internal purpose. To know more about IsPromptBoxPositionFixed property refer this link.

Examples

The following code illustrates the use of IsPromptBoxPositionFixed property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample"; 
           dataValidation.PromptBoxText = "Data validation for list";
           dataValidation.ShowPromptBox = true;
           dataValidation.PromptBoxHPosition = 25; 
           dataValidation.PromptBoxVPosition = 30; 
           dataValidation.IsPromptBoxPositionFixed = true; //True if prompt box position is fixed
           dataValidation.IsPromptBoxVisible = true; 
           dataValidation.IsListInFormula = true; 
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

IsPromptBoxVisible

True if prompt box is visible. otherwise False.

Declaration

bool IsPromptBoxVisible { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

Default value for IsPromptBoxVisible is false. Mostly the IsPromptBoxVisible property used for internal purpose. To know more about IsPromptBoxVisible property refer this link.

Examples

The following code illustrates the use of IsPromptBoxVisible property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample"; 
           dataValidation.PromptBoxText = "Data validation for list";
           dataValidation.ShowPromptBox = true;
           dataValidation.PromptBoxHPosition = 25; 
           dataValidation.PromptBoxVPosition = 30; 
           dataValidation.IsPromptBoxPositionFixed = true;
           dataValidation.IsPromptBoxVisible = true; //True if prompt box is visible
           dataValidation.IsListInFormula = true; 
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

IsSuppressDropDownArrow

True if the drop down arrow is invisible. otherwise False.

Declaration

bool IsSuppressDropDownArrow { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

To know more about IsSuppressDropDownArrow property refer this link.

Examples

The following code illustrates the use of IsSuppressDropDownArrow property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Select the Day in A2";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["D1"].Text = "Days in a Week";
           worksheet.Range["D2"].Text = "Monday";
           worksheet.Range["D3"].Text = "Tuesday";
           worksheet.Range["D4"].Text = "Wednesday";
           worksheet.Range["D5"].Text = "Thursday";
           worksheet.Range["D6"].Text = "Friday";
           worksheet.Range["D7"].Text = "Saturday";
           worksheet.Range["D8"].Text = "Sunday";
           worksheet.Range["D1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation validation = worksheet.Range["A2"].DataValidation;
           validation.DataRange = worksheet.Range["D2:D8"];
           validation.IsEmptyCellAllowed = true; 
           validation.IsSuppressDropDownArrow = false; //True if the drop down arrow is invisible.

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

ListOfValues

Gets or sets Array of possible values. The values of ListOfValues property must be in string format.

Declaration

string[] ListOfValues { get; set; }

Property Value

Type	Description
System.String[]

Remarks

This property should be used when the values in the Data Validation list are entered manually. To know more about ListOfValues property refer this link.

Examples

The following code illustrates the use of ListOfValues property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           // Sample list of values in string format
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample";
           dataValidation.PromptBoxText = "Data validation for list";
           dataValidation.ShowPromptBox = true;
           dataValidation.PromptBoxHPosition = 25;
           dataValidation.PromptBoxVPosition = 30;
           dataValidation.IsPromptBoxPositionFixed = true;
           dataValidation.IsPromptBoxVisible = true;
           dataValidation.IsListInFormula = true;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

PromptBoxHPosition

Gets or sets Horizontal position of the prompt box.

Declaration

int PromptBoxHPosition { get; set; }

Property Value

Type	Description
System.Int32

Remarks

Default value for PromptBoxHPosition is 0. Mostly the PromptBoxHPosition property used for internal purpose. To know more about PromptBoxHPosition property refer this link.

Examples

The following code illustrates the use of PromptBoxHPosition property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample"; 
           dataValidation.PromptBoxText = "Data validation for list";
           dataValidation.ShowPromptBox = true;
           dataValidation.PromptBoxHPosition = 25; // Set the promptBox horizontal position.
           dataValidation.PromptBoxVPosition = 30;
           dataValidation.IsPromptBoxPositionFixed = true;
           dataValidation.IsPromptBoxVisible = true;
           dataValidation.IsListInFormula = true;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

PromptBoxText

Gets or sets the text in the prompt box. The prompt box text can be edit based on the validation.

Declaration

string PromptBoxText { get; set; }

Property Value

Type	Description
System.String

Remarks

To know more about PromptBoxText property refer this link.

Examples

The following code illustrates the use of PromptBoxText property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample";
           dataValidation.PromptBoxText = "Data validation for list"; // Text of PromptBox
           dataValidation.ShowPromptBox = true;
           dataValidation.PromptBoxHPosition = 25;
           dataValidation.PromptBoxVPosition = 30;
           dataValidation.IsPromptBoxPositionFixed = true;
           dataValidation.IsPromptBoxVisible = true;
           dataValidation.IsListInFormula = true;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

PromptBoxTitle

Gets or sets the title of the prompt box. The prompt box title can be edit based on the validation.

Declaration

string PromptBoxTitle { get; set; }

Property Value

Type	Description
System.String

Remarks

To know more about PromptBoxTitle property refer this link.

Examples

The following code illustrates the use of PromptBoxTitle property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample"; // Title of PromptBox
           dataValidation.PromptBoxText = "Data validation for list";
           dataValidation.ShowPromptBox = true;
           dataValidation.PromptBoxHPosition = 25;
           dataValidation.PromptBoxVPosition = 30;
           dataValidation.IsPromptBoxPositionFixed = true;
           dataValidation.IsPromptBoxVisible = true;
           dataValidation.IsListInFormula = true;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

PromptBoxVPosition

Gets or sets Vertical position of the prompt box.

Declaration

int PromptBoxVPosition { get; set; }

Property Value

Type	Description
System.Int32

Remarks

Default value for PromptBoxVPosition is 0. Mostly the PromptBoxVPosition property used for internal purpose. To know more about PromptBoxVPosition property refer this link.

Examples

The following code illustrates the use of PromptBoxVPosition property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample"; 
           dataValidation.PromptBoxText = "Data validation for list";
           dataValidation.ShowPromptBox = true;
           dataValidation.PromptBoxHPosition = 25; 
           dataValidation.PromptBoxVPosition = 30; // Set the promptBox vertical position.
           dataValidation.IsPromptBoxPositionFixed = true;
           dataValidation.IsPromptBoxVisible = true;
           dataValidation.IsListInFormula = true;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

SecondDateTime

Gets or sets second formula's DateTime value. SecondDateTime must important to validate data in CompareOperator property.

Declaration

DateTime SecondDateTime { get; set; }

Property Value

Type	Description
System.DateTime

Remarks

Value of the property must be a DateTime. To know more about SecondDateTime property refer this link.

Examples

The following code illustrates the use of SecondDateTime property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["E1"].Text = "Enter the Date in E3";
           worksheet.Range["E1"].AutofitColumns();
           worksheet.Range["E3"].CellStyle.Color = System.Drawing.Color.LightGray;

           // DataValidation in Worksheet Range
           IDataValidation dateValidation = worksheet.Range["E3"].DataValidation;
           dateValidation.AllowType = ExcelDataType.Date;
           dateValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dateValidation.FirstDateTime = new DateTime(2003, 5, 10); 
           dateValidation.SecondDateTime = new DateTime(2004, 5, 10); // SecondDateTime Formula sample value.
           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;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

SecondFormula

Gets or sets the value or expression associated with the second part of the data validation. Second formula used only when CompareOperator have a value "Between"/"NotBetween";

Declaration

string SecondFormula { get; set; }

Property Value

Type	Description
System.String

Remarks

This can be a constant value, a string value, a cell reference, or a formula. To know more about SecondFormula property refer this link.

Examples

The following code illustrates the use of SecondFormula property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time; 
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dataValidation2.FirstFormula = "10:00"; 
           dataValidation2.SecondFormula = "12:00"; //DataValidation Value for Second Formula

           //Data validation for number
           IDataValidation dataValidation3 = worksheet.Range["E2"].DataValidation;
           dataValidation3.AllowType = ExcelDataType.Integer; 
           dataValidation3.CompareOperator = ExcelDataValidationComparisonOperator.NotBetween;
           dataValidation3.FirstFormula = "0"; 
           dataValidation3.SecondFormula = "10"; //DataValidation Value for Second Formula

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

ShowErrorBox

True if error dialog is shown. otherwise False. Default value is True.

Declaration

bool ShowErrorBox { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

The data validation error message will be shown whenever the user enters invalid data. To know more about ShowErrorBox property refer this link.

Examples

The following code illustrates the use of ShowErrorBox property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["A2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["E2"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["A1"].Text = "Text Vaidation";
           worksheet.Range["C1"].Text = "Time Vaidation";
           worksheet.Range["E1"].Text = "Number Vaidation";
           worksheet.Range["A1"].AutofitColumns();
           worksheet.Range["C1"].AutofitColumns();
           worksheet.Range["E1"].AutofitColumns();

           // Data validation for text length
           IDataValidation dataValidation = worksheet.Range["A2"].DataValidation;
           dataValidation.AllowType = ExcelDataType.TextLength; 
           dataValidation.CompareOperator = ExcelDataValidationComparisonOperator.GreaterOrEqual;
           dataValidation.FirstFormula = "5";
           dataValidation.ShowErrorBox = true; //Value set as true.
           dataValidation.ErrorBoxText = "Text length should be greater than or equal to 5";
           dataValidation.ErrorBoxTitle = "ERROR";
           dataValidation.ErrorStyle = ExcelErrorStyle.Warning;

           // Date Validation for Time 
           IDataValidation dataValidation2 = worksheet.Range["C2"].DataValidation;
           dataValidation2.AllowType = ExcelDataType.Time; 
           dataValidation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
           dataValidation2.FirstFormula = "10:00";
           dataValidation2.SecondFormula = "12:00";
           dataValidation2.ShowErrorBox = false; //Value set as false.
           dataValidation2.ErrorBoxText = "Time between 10:00 and 12:00 'o Clock";
           dataValidation2.ErrorBoxTitle = "ERROR";
           dataValidation2.ErrorStyle = ExcelErrorStyle.Info;

           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

ShowPromptBox

True if prompt box is shown. otherwise False. Default value is True.

Declaration

bool ShowPromptBox { get; set; }

Property Value

Type	Description
System.Boolean

Remarks

PromptBox shows whenever you click the Datavalidation cell. To know more about ShowPromptBox property refer this link.

Examples

The following code illustrates the use of ShowPromptBox property.

        using (ExcelEngine excelEngine = new ExcelEngine())
        {
           //Create a worksheet.        
           IApplication application = excelEngine.Excel;
           application.DefaultVersion = ExcelVersion.Excel2013;
           IWorkbook workbook = application.Workbooks.Create(1);
           IWorksheet worksheet = workbook.Worksheets[0];
           worksheet.Range["C3"].CellStyle.Color = System.Drawing.Color.LightGray;
           worksheet.Range["C1"].Text = "Data Validation List in C3";
           worksheet.Range["C1"].AutofitColumns();

           // DataValidation in Worksheet Range
           IDataValidation dataValidation = worksheet.Range["C3"].DataValidation;
           dataValidation.ListOfValues = new string[] { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" };
           dataValidation.PromptBoxTitle = "Sample";
           dataValidation.PromptBoxText = "Data validation for list";
           dataValidation.ShowPromptBox = true; //True if prompt box is shown
           dataValidation.PromptBoxHPosition = 25;
           dataValidation.PromptBoxVPosition = 30;
           dataValidation.IsPromptBoxPositionFixed = true;
           dataValidation.IsPromptBoxVisible = true;
           dataValidation.IsListInFormula = true;
           workbook.SaveAs("DataValidation.xlsx");
           workbook.Close();
           excelEngine.Dispose();
        }

WPF- v20.4.0.38

Interface IDataValidation

Inherited Members

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Base.dll

Syntax

Examples

Properties

AllowType

Declaration

Property Value

Remarks

Examples

CompareOperator

Declaration

Property Value

Remarks

Examples

DataRange

Declaration

Property Value

Remarks

Examples

ErrorBoxText

Declaration

Property Value

Remarks

Examples

ErrorBoxTitle

Declaration

Property Value

Remarks

Examples

ErrorStyle

Declaration

Property Value

Remarks

Examples

FirstDateTime

Declaration

Property Value

Remarks

Examples

FirstFormula

Declaration

Property Value

Remarks

Examples

IsEmptyCellAllowed

Declaration

Property Value

Remarks

Examples

IsListInFormula

Declaration

Property Value

Remarks

Examples

IsPromptBoxPositionFixed

Declaration

Property Value

Remarks

Examples

IsPromptBoxVisible

Declaration

Property Value

Remarks

Examples

IsSuppressDropDownArrow

Declaration

Property Value

Remarks

Examples

ListOfValues

Declaration

Property Value

Remarks

Examples

PromptBoxHPosition

Declaration