We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Validators start to fail after upgrade to 20.8.0

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 "@"

[Steiners] answer is irrelevant to the issue

@komloshij,

You are right, we noticed the behavior as you pointed out. We found formula validators fail in newer version when not using “@” before column names. We have logged a ticket with an id “CELLSNET-47554”. We will investigate the issue and provide more details soon.

Once we have an update on it, we will let you know.

@komloshij,
In recent versions we fixed one bug of range reference for Validation. For table column references, we use the same model for calculation. We have tried to use table references in Data Validation in ms excel, unfortunately ms excel does not allow the operation. So we cannot verify what is the correct behavior for such kind of operation. If you can create such kind of Validation in ms excel, please send us one sample xlsx file created by ms excel and we will make further investigation and give the same behavior with ms excel. If at last the truth is that ms excel does not allow such kind of operation, maybe we should forbid such kind of formulas for Validation too.

Let us know your feedback.