The data validation example at Data Validation|Documentation has the following line:
Validation validation = validations[validations.Add(ca)];
However, this doesn’t compile, as there is no ValidationCollection.Add that takes a CellArea as input.
EDIT: We’re using a pretty old version of Aspose, perhaps that’s the problem?
It seems that you are encountering a compilation issue with the data validation example provided in the Aspose documentation. The line in question:
Validation validation = validations[validations.Add(ca)];
is indeed problematic because the ValidationCollection.Add method does not accept a CellArea as a parameter directly.
To resolve this, you should first create a CellArea object, and then use the Add method to add the validation to the collection. Here’s a corrected version of the code snippet:
// Create a CellArea
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Obtain the existing Validations collection
ValidationCollection validations = ExcelWorkSheet.Validations;
// Create a validation object by adding to the collection
int index = validations.Add(ca);
Validation validation = validations[index];
// Set the validation type
validation.Type = ValidationType.Decimal;
// Specify the operator
validation.Operator = OperatorType.Between;
// Set the lower and upper limits
validation.Formula1 = Decimal.MinValue.ToString();
validation.Formula2 = Decimal.MaxValue.ToString();
// Set the error message
validation.ErrorMessage = "Please enter a valid integer or decimal number";
// Specify the validation area of cells
CellArea area;
area.StartRow = 0;
area.EndRow = 9;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the area
validation.AddArea(area);
// Save the workbook
workbook.Save(dataDir + "output.out.xls");
In this corrected code, we first add the CellArea to the ValidationCollection and then retrieve the Validation object using the index returned by the Add method. This should compile successfully and work as intended.
For further details, you can refer to the official Aspose documentation on data validation here.
If you have any more questions or need further assistance, feel free to ask!
@khs
We added validations.Add(ca) method and obsoleted validations.Add() method since JANUARY 2015.
If you are using very old version, you only can validations.Add() to add validation.
But you’d better use the latest Aspose Cells version.
Yes, we’re going to evaluate the latest version soon, as we’re having some stability problems as well. Thanks for the (human) response. The AI response doesn’t make sense…
@khs,
Please note that our documentation is regularly updated with examples based on the latest set of APIs. If you are using an older version of Aspose.Cells APIs, certain lines of code in the examples may not function as expected. We kindly recommend upgrading to the newer versions of the component, which are more robust and feature-rich.
Yes, we’ll definitely upgrade in the near future, but for now I’m stuck with the (very old) version. Am I right in assuming that using the Add() method, the code should look like this?
Validation validation = validations[validations.Add()];
validation.AddArea(ca);
Because I don’t seem to get it to work. It compiles fine, but the validations are not added to the cells.
@khs,
Could you please share your standalone (runnable) code snippet and sample Excel files (you may zip prior attaching here). Also, which version of Aspose.Cells for .NET you are using? We will check it and try to help you out (if possible). Mind you, in case this was a bug in older version regarding validations, we might not fix it in older version or help you to fix it and only alternative would be upgrade to latest APIs set.
Well, I basically just copy/pasted the example from here (list data validation), and changed the part that didn’t work into what I think should be the equivalent. If that’s a known bug in the (very) old version I have, that’s fine, but at least I can stop trying to make it work before I install a more modern version of Aspose.
Note there’s at least on bug in the example code: everywhere (there are multiple examples) where it says something like:
CellArea area;
area.StartRow = 0;
This should very likely be:
CellArea area = new CellArea();
area.StartRow = 0;
As without it, the following:
validation.AddArea(cellArea);
leads to the validations collection to lose/delete it’s element (I tested with the Count) property.
Regardless, even when I fix that, I have no success yet in applying validations.
EDIT: No, I was wrong, even without the new, after adding the area, the validations collection goes from 1 to 0 elements. Scratching my head now…
@khs,
Please see and try the following sample code using your older version if it works fine.
// Get the first worksheet.
Worksheet worksheet = workbook.Worksheets[0];
//Accessing the Validations collection of the worksheet
ValidationCollection validations = worksheet.Validations;
//Creating a Validation object
Validation validation = validations[validations.Add()];
//Setting the validation type to whole number
validation.Type = ValidationType.WholeNumber;
//Setting the operator for validation to Between
validation.Operator = OperatorType.Between;
//Setting the minimum value for the validation
validation.Formula1 = "10";
//Setting the maximum value for the validation
validation.Formula2 = "1000";
//Applying the validation to a range of cells from A1 to B2 using the
//CellArea structure
CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;
//Adding the cell area to Validation
validation.AreaList.Add(area);
In case, you still find validation is no applied or some other issue, kindly give us details on which (older) version of Aspose.Cells you are using? We will then test using your exact version and assist you accordingly.
Yes, this works! It seems the validation.AreaList.Add did the trick. Thank you very much!
@khs,
It’s good to hear that the suggested line of code works for your needs with the older version. Please don’t hesitate to reach out to us if you have any additional questions or feedback.