Say I have a custom data validation setup like this for a cell in Excel:
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.
