When applying a custom validation formula to a column (e.g., =MOD($G2,2)=0 for even-number validation in G2:G6 ), Aspose.Cells shifts the reference incorrectly. Instead of applying the formula as =MOD($G2,2)=0 in G2 , it applies it as =MOD(G1,2)=0 , or worse, as =MOD(QW6,2)=0 when using RC notation (RC7 instead of $G2).
It seems you are experiencing an issue with the application of custom validation formulas in Aspose.Cells, where the references are being shifted incorrectly. This can happen when the formula is not being interpreted as expected, leading to incorrect cell references.
To address this, ensure that you are using absolute references correctly. For example, when you want to validate even numbers in the range G2:G6 using the formula =MOD($G2,2)=0, it should be set up in a way that the $G2 reference remains fixed for each cell in the specified range.
If you are using R1C1 notation and encountering issues like =MOD(G1,2)=0 or =MOD(QW6,2)=0, it may be beneficial to double-check how the formula is being set in your code. The formula should be set explicitly for each cell in the range to avoid any unintended shifts.
Here’s a sample code snippet that demonstrates how to set a validation formula correctly:
// Create a workbook and access the first worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Create a validation object
Validation validation = worksheet.Validations[worksheet.Validations.Add()];
// Set the validation type and formula
validation.Type = ValidationType.Custom;
validation.Formula1 = "=MOD($G2,2)=0"; // Ensure the reference is absolute
validation.AreaList.Add("G2:G6"); // Apply to the desired range
// Save the workbook
workbook.Save("output.xlsx");
Make sure to adjust the formula and the area list according to your specific needs. If the problem persists, consider checking for any updates or patches for Aspose.Cells that might address this issue.
If you continue to face difficulties, I recommend checking the official documentation or reaching out to the support community for further assistance.
Sources:
[1]: Validation.Formula1 | Aspose.Cells for .NET API Reference
Please note, because for validation the formula is using relative references instead of the absolute cell address, please always add the applied range firstly before setting formulas. In fact what the robot replied for your post has the same issue too which may produce unexpected result. The proper code sequence should be:
validation.AreaList.Add("G2:G6");
...
validation.Formula1 = "=MOD($G2,2)=0";
If it is not your issue, please provide us your sample code to reproduce the issue so we can figure the issue out for you.
1 Like
‘Validation’ does not contain a definition for ‘AreaList’ ?
Johnson.shi,
Thank you!
This solved my issue. I was adding the validation area at the end.
Is there a way to query the existing validation area? I want to be able to overwrite an existing validation without changing it’s index but this call no longer works:
validation.AreaList.Clear() since ‘Validation’ does not contain a definition for ‘AreaList’
Please see the following sample code segment for your reference.
Worksheet worksheet = workbook.Worksheets[0];
// Get all validations in the worksheet
ValidationCollection validations = worksheet.Validations;
for (int i = 0; i < validations.Count; i++)
{
Validation validation = validations[i];
// Get the existing validation areas
CellArea[] areas = validation.Areas;
//...
//your code goes here.
//..
}
1 Like
Perfect. Say you have 4 validations set to a worksheet: indices 0-3
Is it possible to delete the validation at index 2 without forcing the last validation (index 3) to adjust down to index 2?
If not, how do you manage validations outside an index reference?
If you need to remove a validation, you can remove it via its indexed position.
worksheet.Validations.RemoveAt(index);
Also, you may remove validations in a specified range.
e.g.,
//Remove all the validations in the range A1:C15
worksheet.Validations.RemoveArea(CellArea.CreateCellArea("A1", "C15"));
Hope, this helps a bit.
1 Like
amjad,
Does that mean I can use the RemoveArea method to remove the entire area of the validation while leaving it defined.
i.e. No effect in the Excel file except for that one additional validation index is used?
@BRGQST
ValidationCollection.RemoveArea method will remove all validation setting on the range. Would you be like to give examples to describe your needs in detail? If you could provide sample files and expected results, it would be very helpful for us to locate the issue and analyze the requirements. We will check it soon.