Interface IDataValidation

Represents data validation for a worksheet range.

Inherited Members
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
System.String
Remarks

This can be a constant value, a string value, a cell reference, or a formula. The SecondFormula value must be greater than or equal to the FirstFormula value. 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
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
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();
        }

Extension Methods