gembox-spreadsheet

is it possible to obtain the results of a custom validation?


Say I have a custom data validation setup like this for a cell in Excel:

enter image description here

I then set the value of the cell in c# using Gembox Spreadsheet. At this point, is there a way to verify (from c#) if the validation linked to this cell was successful or not?

What has been tried: I did manage to find the DataValidation object linked to the cell via:

private DataValidation FindDatataValidationForCell(ExcelCell requiredCell)
{
    foreach (DataValidation dv in requiredCell.Worksheet.DataValidations)
    {
        foreach (CellRange range in dv.CellRanges)
        {
            foreach (ExcelCell foundCell in range)
            {
                if (foundCell == requiredCell)
                    return dv;
            }
        }
    }

    return null;
}

But in the case of a custom validation, not sure where to go from here. A workaround might be to write the formula read from the DataValidation object into a new (temporary) cell, and read the result, like this:

public bool IsValid(ExcelCell cell)
{
    DataValidation dv = FindDatataValidationForCell(cell);
    if (dv != null)
    {
        if (dv.Type == DataValidationType.Custom)
        {
            string str = dv.Formula1 as string;
            if (str != null && str.StartsWith("="))
            {
                // dodgy: use a cell which is known to be unused somewhere on the worksheet.                        
                var dummyCell = cell.Worksheet.Cells[100, 0];

                dummyCell.Formula = str;
                dummyCell.Calculate();                        
                bool res = dummyCell.BoolValue;
                dummyCell.Formula = null;   // no longer required. Reset. 

                return res; 
            }
        }
    }
    return true;
}

This does seem to work, but hoping there is a better way. Or failing that, maybe a better way to work out a temporary dummy cell location.


Solution

  • Try using this latest bugfix version:
    https://www.gemboxsoftware.com/spreadsheet/nightlybuilds/GBS49v1171.zip

    Or this latest NuGet package:
    Install-Package GemBox.Spreadsheet -Version 49.0.1171-hotfix

    Now there is a DataValidation.Validate(ExcelCell) method that you can use.