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. 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();
}