After updating from 20.6.0 to 20.8.0 all validators start to fail.
During investigation i realized they do not work with formulas table[column] as it was before but only with table[@column] (with @ before column name)
Was it intentional ? Cause it blocks us from upgrading as we use formulas without @ in our calculations
[Fact]
public void Test()
{
const string validator = "=[Column A] > 10";
using var book = new Workbook();
var sheet = book.Worksheets.First();
sheet.Cells["A1"].Value = "Column A";
sheet.Cells["B1"].Value = "Column B";
sheet.Cells["A2"].Value = 10;
sheet.Cells["A3"].Value = 20;
sheet.Cells["A4"].Value = 30;
int index = sheet.ListObjects.Add(sheet.Cells.MinDataRow, sheet.Cells.MinDataColumn, sheet.Cells.MaxDataRow, sheet.Cells.MaxDataColumn, true);
var table = sheet.ListObjects[index];
var column = table.ListColumns.Last();
sheet.Cells["B2"].Formula = validator;
sheet.Cells["B3"].Formula = validator;
sheet.Cells["B4"].Formula = validator;
ValidationCollection validations = sheet.Validations;
CellArea area;
area.StartRow = column.Range.FirstRow + 1;
area.EndRow = column.Range.FirstRow + column.Range.RowCount - 1;
area.StartColumn = column.Range.FirstColumn;
area.EndColumn = column.Range.FirstColumn;
Validation validation = validations[validations.Add(area)];
validation.Type = ValidationType.Custom;
validation.ShowError = false;
validation.Formula1 = validator;
book.CalculateFormula();
Assert.False(sheet.Cells["B2"].BoolValue); // Passes in all versions
Assert.True(sheet.Cells["B3"].BoolValue); // Passes in all versions
Assert.True(sheet.Cells["B4"].BoolValue); // Passes in all versions
Assert.False(sheet.Cells["B2"].GetValidationValue()); // Passes in all versions
Assert.True(sheet.Cells["B3"].GetValidationValue()); // Passes in 20.6.0 and older versions but fails in 20.7.0 and 20.8.0
Assert.True(sheet.Cells["B4"].GetValidationValue()); // Passes in 20.6.0 and older versions but fails in 20.7.0 and 20.8.0
}
But if I add “@” to the column name then validators works properly and all tests pass in 20.7.0 and 20.8.0 as well:
const string validator = “=[@Column A] > 10”;
Please notice that formulas work properly in all versions both with and without "@"