DataValidationCollection.GetInvalidCells() Method

Returns cells that do not meet the data validation criteria.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.1.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

IEnumerable<Cell> GetInvalidCells()

VB.NET

Function GetInvalidCells As IEnumerable(Of Cell)

Returns

Type	Description
IEnumerable<Cell>	A IEnumerable<T><Cell,> collection of cells containing invalid data.

Remarks

Note that the GetInvalidCells property returns only first 255 invalid cells, other invalid entries are ignored. If there are no data validation errors in a worksheet, the GetInvalidCells property returns an empty collection.

The code sample below shows how to use the GetInvalidCells method to check whether loaded document contains invalid cells:

C#

static void Main(string[] args)
{
    Workbook workbook = new Workbook();
    workbook.LoadDocument("DataValidation_template.xlsx");
    IsDocumentValid(workbook);
}

private static void IsDocumentValid(Workbook workbook)
{
    foreach (Worksheet workSheet in workbook.Worksheets)
    {
        var invalidCells = workSheet.DataValidations.GetInvalidCells().Count();

        if (invalidCells == 0)
        {
            Console.WriteLine(string.Format("The {0} worksheet does not contain invalid cells", workSheet.Name));
            Console.ReadLine();
        }
        else
        {
            Console.WriteLine(string.Format("The {0} worksheet contains {1} invalid cells",workSheet.Name, invalidCells));
            Console.ReadLine();
        }
    }
}

VB.NET

Private Shared Sub Main(ByVal args As String())
    Dim workbook As Workbook = New Workbook()
    workbook.LoadDocument("DataValidation_template.xlsx")
    IsDocumentValid(workbook)
End Sub

Private Shared Sub IsDocumentValid(ByVal workBook As Workbook)
    For Each workSheet As Worksheet In workBook.Worksheets
        Dim invalidCells = workSheet.DataValidations.GetInvalidCells().Count()

        If invalidCells = 0 Then
            Console.WriteLine(String.Format("The {0} worksheet does not contain invalid cells", workSheet.Name))
            Console.ReadLine()
        Else
            Console.WriteLine(String.Format("The {0} worksheet contains {1} invalid cells", workSheet.Name, invalidCells))
        End If

        Console.ReadLine()
    Next
End Sub