The validation is not correct, when the validation depends on other cell

Aspose.Cells Product Family
, , ,
1

Hallo Aspose-Team

I have found a bug in Validation for a specific cell in excel.
The validation is not correct, when the validation depends on other cell.

I have the following code:

    var book = new Workbook("C:\\Projekte\\Test_Aspose.xlsx");

    Worksheet sheetBla = book.Worksheets["Erläuterungen der VNB"];

    Cell cell1 = sheetBla.Cells["D7"];
    Cell cell2 = sheetBla.Cells["F7"];

    cell1.PutValue("Unternehmensdaten", false, false);
    cell2.PutValue("1.2", false, false);

    TestContext.WriteLine("Result D7: " + cell1.Value);
    TestContext.WriteLine("Result F7: " + cell2.Value);

    int i = 0;
    object p_object = cell2.GetValidation().Value1;
    if (p_object is object[])
    {
        foreach (object curObj in (object[])p_object)
        {
            if (i==3)
                break;
            TestContext.WriteLine("Validation for F7: " + curObj);
            i = i + 1;
        }
    }


    Cell cell3 = sheetBla.Cells["D8"];
    Cell cell4 = sheetBla.Cells["F8"];

    cell3.PutValue("GMK und AGS", false, false);
    cell4.PutValue("5.2", false, false);

    TestContext.WriteLine("Result D8: " + cell3.Value);
    TestContext.WriteLine("Result F8: " + cell4.Value);

    i = 0;
    p_object = cell4.GetValidation().Value1;
    if (p_object is object[])
    {
        foreach (object curObj in (object[])p_object)
        {
            if (i == 3)
                break;
            TestContext.WriteLine("Validation for F8: " + curObj);
            i = i + 1;
        }
    }

and this result:

Result D7: Unternehmensdaten
Result F7: 1.2
Validation for F7: 1.
Validation for F7: 1.1
Validation for F7: 1.2

Result D8: GMK und AGS
Result F8: 5.2
Validation for F8: 1.
Validation for F8: 1.1
Validation for F8: 1.2

but I expect this result:

Result D7: Unternehmensdaten
Result F7: 1.2
Validation for F7: 1.
Validation for F7: 1.1
Validation for F7: 1.2

Result D8: GMK und AGS
Result F8: 5.2
Validation for F8: 5.
Validation for F8: 5.1
Validation for F8: 5.2

This is the excle-file:
Test_Aspose.zip (5.0 MB)

We are using the following versions in out projekt:

Aspose.Cells.dll -> Version: 20.6.6
Aspose.Cells.GridWeb.dll -> Version: 17.7.0.0

Thanks in advance for your help
Matthias

2

@Matthias_Winzer,

Please notice, I am able to reproduce the issue as you mentioned. I am able to reproduce the issue as the user has mentioned by using his sample code with his template file. I found the validation is not correct for your mentioned cell (F8) when the validation depends on other cell. I have logged a ticket with an id “CELLSNET-47942” for your issue. We will look into it soon.

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

3

@Matthias_Winzer,
Because the specified list for the validation is formula and the results of the formula vary with the applied cell, the cell must be specified when obtaining the list. For such kind of validations, please use Validation.GetListValue(int row, int column) instead. The returned object is ReferredArea object in this case and you may access all items in the list with it.

4

Hallo ahsaniqbalsidiqui

Thank you very much, that seems to work for us.

Matthias

5

@Matthias_Winzer,

Good to know that the suggested API resolves your issue for now. In the event of further queries or issue, please feel free to write us back and we will be happy to assist you soon.