List validation example problem

Aspose.Cells Product Family
1

This is a follow-up to last year’s question here.

We have recently purchased the latest version of Aspose.Cells for .NET, and we’re experiencing a problem with the example as listed in the question above or on this documentation page here. This is my code:

            ValidationList vl = (ValidationList)ssa_ValidationListObject;

            ValidationCollection vc = ((Workbook)ssa_Workbook).Worksheets[ssa_Index].Validations;
            CellArea caDummy = new CellArea
            {
                StartRow = 0,
                EndRow = 0,
                StartColumn = 0,
                EndColumn = 0
            };

            Validation v = vc[vc.Add(caDummy)];

            v.Type = Aspose.Cells.ValidationType.List;
            v.Operator = OperatorType.None;
            v.InCellDropDown = true;
            v.Formula1 = "=" + vl.range.Name;
            v.ShowError = true;
            v.AlertStyle = ValidationAlertType.Stop;
            v.IgnoreBlank = ssa_IgnoreBlank;

            CellsHelper.CellNameToIndex(ssa_StartLocation, out int startRow, out int column);

            CellArea caDest = new CellArea
            {
                StartRow = startRow,
                EndRow = startRow + ((ssa_RowCount > 0) ? ssa_RowCount : ((Workbook)ssa_Workbook).Worksheets[ssa_Index].Cells.MaxRow + 1),
                StartColumn = column,
                EndColumn = column
            };

            v.AddArea(caDest);

It basically works, except for one thing: cell A1 also always gets the validation applied, in addition to the specified range. I somehow expect the caDummy to be responsible, but since my code is basically the example as specified, I’m not sure what I did wrong. Can someone help me here?

2

@khs

Thank you for providing the code snippet and for following up on this implementation.

I have successfully reproduced the issue you described. My tests confirm that when a secondary CellArea is added to the validation, the initial area (A1) is incorrectly retained. This results in the validation logic being applied to cell A1 even when it is intended only for the newly defined areas.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-60095

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
3

Thanks for the quick reply. Now I know this is a bug in Aspose.Cells, I can easily create a workaround using RemoveACell after applying the above. I do hope there’s a bugfix in the foreseeable future though :slight_smile:.

4

@khs

Thank you for the update. We are glad to hear that you were able to implement a temporary workaround using the RemoveACell method to maintain your project’s progress.

Please be assured that the issue is currently logged in our internal tracking system and is being investigated by our development team. We understand the importance of a permanent fix for your workflow and are working toward a resolution.

We will keep you informed of any progress regarding a fix in future releases. Thank you for your patience and for choosing Aspose.Cells.

1 Like
5

@khs

It is not a bug but the designed behavior for adding applied range for Validation. One validation can be applied to multiple ranges, it is just the reason why apis of Validation.Areas/AddArea… exist. As a component we cannot know whether one existing range is needed by the user or not, so user has to determine that and remove those ranges that they do no want them to be applied for the validation(by api RemoveArea(CellArea), RemoveACell(int, int) or RemoveAreas(CellArea[])).

And generally you do not need to use a dummy range to initialize Validation. For some situations, such as when you use formulas with relative references to validate data, you even should not use the dummy range. Changing the range may cause your specified formula be changed too, that may be not intended for you. So the best way to initialize a validation is to use the proper range from begin( ValidationCollection.Add(CellArea)) and then set other properties such as Formula1 for the added Validation.

6

Hi Johnson,

Note that I basically just copy/pasted the example from your website. If this is not a bug in the product, it is surely an error in the example, so perhaps you should update your documentation, as it doesn’t work for a validation that doesn’t include the 0,0 cell.

Just to be sure, is this the code you are suggesting I use?

            ValidationList vl = (ValidationList)ssa_ValidationListObject;

            ValidationCollection vc = ((Workbook)ssa_Workbook).Worksheets[ssa_Index].Validations;

            CellsHelper.CellNameToIndex(ssa_StartLocation, out int startRow, out int column);

            CellArea caDest = new CellArea
            {
                StartRow = startRow,
                EndRow = startRow + ((ssa_RowCount > 0) ? ssa_RowCount : ((Workbook)ssa_Workbook).Worksheets[ssa_Index].Cells.MaxRow + 1),
                StartColumn = column,
                EndColumn = column
            };

            Validation v = vc[vc.Add(caDest)];

            v.Type = Aspose.Cells.ValidationType.List;
            v.Operator = OperatorType.None;
            v.InCellDropDown = true;
            v.Formula1 = "=" + vl.range.Name;
            v.ShowError = true;
            v.AlertStyle = ValidationAlertType.Stop;
            v.IgnoreBlank = ssa_IgnoreBlank;

or do you mean something else?

7

@khs

Thank you for your feedback regarding the documentation. We found in the example code this part is improper and may cause confusion:

// Applying the validation to a range of cells from A1 to B2 using the
// CellArea structure
CellArea area;
area.StartRow = 0;
...

Here it should be something like “Applying the validation to B2 too” or “Add B2 as another range that applys the validation”. We will update the document accordingly. If you find any other part in the document that may cause confusion, please let us know and we will fix them soon, thank you.

1 Like
8

@khs

And for your code:

Your new code looks well, the dummy range is not needed, you may use caDest to create the Validation directly.

1 Like
9

Thank you, that indeed works as expected.

10

@khs

Thank you for the update. We are glad to hear that the revised approach for creating the validation directly with the cell area works as expected for your project.

It was a pleasure assisting you with this documentation improvement. Please feel free to reach out if you have any further questions or need additional assistance.