WPF

Code Examples Upgrade Guide User Guide Demos Support Forums Download
  • Code Examples
  • Upgrade Guide
  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Interface IDataValidation

    Show / Hide Table of Contents

    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();
            }
    Back to top Generated by DocFX
    Copyright © 2001 - 2023 Syncfusion Inc. All Rights Reserved