C#/VB.NET: Apply or Remove Data Validation in Excel

PM> Install-Package Spire.XLS

using System;
using Spire.Xls;

namespace ApplyDataValidation
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Insert text in cells
            sheet.Range["B2"].Text = "Number Validation:";
            sheet.Range["B4"].Text = "Date Validation:";
            sheet.Range["B6"].Text = "Text Length Validation:";
            sheet.Range["B8"].Text = "List Validation:";
            sheet.Range["B10"].Text = "Time Validation:";

            //Add a number validation to C2
            CellRange rangeNumber = sheet.Range["C2"];
            rangeNumber.DataValidation.AllowType = CellDataType.Integer;
            rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between;
            rangeNumber.DataValidation.Formula1 = "1";
            rangeNumber.DataValidation.Formula2 = "10";
            rangeNumber.DataValidation.InputMessage = "Enter a number between 1 and 10";
            rangeNumber.Style.KnownColor = ExcelColors.Gray25Percent;

            //Add a date validation to C4
            CellRange rangeDate = sheet.Range["C4"];
            rangeDate.DataValidation.AllowType = CellDataType.Date;
            rangeDate.DataValidation.CompareOperator = ValidationComparisonOperator.Between;
            rangeDate.DataValidation.Formula1 = "1/1/2010";
            rangeDate.DataValidation.Formula2 = "12/31/2020";
            rangeDate.DataValidation.InputMessage = "Enter a date between 1/1/2010 and 12/31/2020";
            rangeDate.Style.KnownColor = ExcelColors.Gray25Percent;

            //Add a text length validation to C6
            CellRange rangeTextLength = sheet.Range["C6"];
            rangeTextLength.DataValidation.AllowType = CellDataType.TextLength;
            rangeTextLength.DataValidation.CompareOperator = ValidationComparisonOperator.LessOrEqual;
            rangeTextLength.DataValidation.Formula1 ="5";
            rangeTextLength.DataValidation.InputMessage = "Enter text lesser than 5 characters";
            rangeTextLength.Style.KnownColor = ExcelColors.Gray25Percent;

            //Apply a list validation to C8
            CellRange rangeList = sheet.Range["C8"];
            rangeList.DataValidation.Values = new String[] { "United States", "Canada", "United Kingdom", "Germany" };
            rangeList.DataValidation.IsSuppressDropDownArrow = false;
            rangeList.DataValidation.InputMessage ="Choose an item from the list";
            rangeList.Style.KnownColor =ExcelColors.Gray25Percent;

            //Apply a time validation to C10
            CellRange rangeTime = sheet.Range["C10"];
            rangeTime.DataValidation.AllowType = CellDataType.Time;
            rangeTime.DataValidation.CompareOperator = ValidationComparisonOperator.Between;
            rangeTime.DataValidation.Formula1 = "9:00";
            rangeTime.DataValidation.Formula2 = "12:00";
            rangeTime.DataValidation.InputMessage = "Enter a time between 9:00 and 12:00";
            rangeTime.Style.KnownColor = ExcelColors.Gray25Percent;

            //Auto fit width of column 2
            sheet.AutoFitColumn(2);

            //Set the width of column 3
            sheet.Columns[2].ColumnWidth = 20;

            //Save to file
            workbook.SaveToFile("ApplyDataValidation.xlsx", ExcelVersion.Version2016);
        }
    }
}

Imports System
Imports Spire.Xls
 
Namespace ApplyDataValidation
    Class Program
        Shared  Sub Main(ByVal args() As String)
            'Create a Workbook object
            Dim workbook As Workbook =  New Workbook() 
 
            'Get the first worksheet
            Dim sheet As Worksheet =  workbook.Worksheets(0) 
 
            'Insert text in cells
            sheet.Range("B2").Text = "Number Validation:"
            sheet.Range("B4").Text = "Date Validation:"
            sheet.Range("B6").Text = "Text Length Validation:"
            sheet.Range("B8").Text = "List Validation:"
            sheet.Range("B10").Text = "Time Validation:"
 
            'Add a number validation to C2
            Dim rangeNumber As CellRange =  sheet.Range("C2") 
            rangeNumber.DataValidation.AllowType = CellDataType.Integer
            rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between
            rangeNumber.DataValidation.Formula1 = "1"
            rangeNumber.DataValidation.Formula2 = "10"
            rangeNumber.DataValidation.InputMessage = "Enter a number between 1 and 10"
            rangeNumber.Style.KnownColor = ExcelColors.Gray25Percent
 
            'Add a date validation to C4
            Dim rangeDate As CellRange =  sheet.Range("C4") 
            rangeDate.DataValidation.AllowType = CellDataType.Date
            rangeDate.DataValidation.CompareOperator = ValidationComparisonOperator.Between
            rangeDate.DataValidation.Formula1 = "1/1/2010"
            rangeDate.DataValidation.Formula2 = "12/31/2020"
            rangeDate.DataValidation.InputMessage = "Enter a date between 1/1/2010 and 12/31/2020"
            rangeDate.Style.KnownColor = ExcelColors.Gray25Percent
 
            'Add a text length validation to C6
            Dim rangeTextLength As CellRange =  sheet.Range("C6") 
            rangeTextLength.DataValidation.AllowType = CellDataType.TextLength
            rangeTextLength.DataValidation.CompareOperator = ValidationComparisonOperator.LessOrEqual
            rangeTextLength.DataValidation.Formula1 ="5"
            rangeTextLength.DataValidation.InputMessage = "Enter text lesser than 5 characters"
            rangeTextLength.Style.KnownColor = ExcelColors.Gray25Percent
 
            'Apply a list validation to C8
            Dim rangeList As CellRange =  sheet.Range("C8") 
            rangeList.DataValidation.Values = New String() 
            {
            	 "United States", "Canada", "United Kingdom", "Germany" 
            }

            rangeList.DataValidation.IsSuppressDropDownArrow = False
            rangeList.DataValidation.InputMessage ="Choose an item from the list"
            rangeList.Style.KnownColor =ExcelColors.Gray25Percent
 
            'Apply a time validation to C10
            Dim rangeTime As CellRange =  sheet.Range("C10") 
            rangeTime.DataValidation.AllowType = CellDataType.Time
            rangeTime.DataValidation.CompareOperator = ValidationComparisonOperator.Between
            rangeTime.DataValidation.Formula1 = "9:00"
            rangeTime.DataValidation.Formula2 = "12:00"
            rangeTime.DataValidation.InputMessage = "Enter a time between 9:00 and 12:00"
            rangeTime.Style.KnownColor = ExcelColors.Gray25Percent
 
            'Auto fit width of column 2
            sheet.AutoFitColumn(2)
 
            'Set the width of column 3
            sheet.Columns(2).ColumnWidth = 20
 
            'Save to file
            workbook.SaveToFile("ApplyDataValidation.xlsx", ExcelVersion.Version2016)
        End Sub
    End Class
End Namespace

using Spire.Xls;
using System.Drawing;

namespace RemoveDataValidation
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\ApplyDataValidation.xlsx");

            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Create an array of rectangles, which is used to locate the ranges in worksheet
            Rectangle[] rectangles = new Rectangle[]{

                //one Rectangle(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex) specifies a  cell range to remove data validation
                //the column or row index starts at 0
                new Rectangle(0, 0, 2, 9)
            };

            //Remove the data validation from the selected cells
            worksheet.DVTable.Remove(rectangles);

            //Save the workbook to an Excel file
            workbook.SaveToFile("RemoveDataValidation.xlsx");
        }
    }
}

Imports Spire.Xls
Imports System.Drawing
 
Namespace RemoveDataValidation
    Class Program
        Shared  Sub Main(ByVal args() As String)
            'Create a Workbook object
            Dim workbook As Workbook =  New Workbook() 
 
            'Load a sample Excel file
            workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\ApplyDataValidation.xlsx")
 
            'Get the first worksheet
            Dim worksheet As Worksheet =  workbook.Worksheets(0) 
 
            'Create an array of rectangles, which is used to locate the ranges in worksheet
            Dim rectangles() As Rectangle =  New Rectangle() { 'one Rectangle(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex) specifies a  cell range to remove data validation'the column or row index starts at 0New Rectangle(0, 0, 2, 9)}

 
            'Remove the data validation from the selected cells
            worksheet.DVTable.Remove(rectangles)
 
            'Save the workbook to an Excel file
            workbook.SaveToFile("RemoveDataValidation.xlsx")
        End Sub
    End Class
End Namespace