Remove Data Validation in Excel in C#

Spire.XLS supports to add data validation to restrict input that users can or should insert to a cell. It also supports to remove data validations from the specified ranges or the whole worksheet. Following code snippets demonstrate how to remove validations using Spire.XLS in C#.

Source File:

In the source file, there are drop-down list validations in the cells where R&D appears.

Remove Data Validation in Excel in C#

Remove validation in specific ranges

using Spire.Xls;
using System.Drawing;
namespace RemoveData
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an object of Workbook class
            Workbook wb = new Workbook();

            //Load a source document 
            wb.LoadFromFile("Data Validation.xlsx");

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

            //Assign value to the first element of the array. This rectangle specifies the cells from A1 to B3.
            rectangles[0] = new Rectangle(0, 0, 1, 2);

            //Remove validations in the ranges represented by rectangles
            wb.Worksheets[0].DVTable.Remove(rectangles);

            //Save the file
            wb.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
        }
    }
}

Output:

As is shown in the screenshot below, drop-down list validations in the range A1 to B3 have been removed.

Remove Data Validation in Excel in C#

Remove validations in the whole worksheet

using Spire.Xls;
namespace RemoveData
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create an object of Workbook class
            Workbook wb = new Workbook();

            //Load a source document 
            wb.LoadFromFile("Data Validation.xlsx");

            //Call DVTable.Clear() method to remove all validations in the worksheet
            wb.Worksheets[0].DVTable.Clear();

            //Save the file
            wb.SaveToFile("Output.xlsx", ExcelVersion.Version2013);

        }
    }
}

Output:

Remove Data Validation in Excel in C#