Hi,
I'm evaluating aspose.cells right now but don't see a way to verify that the contents of a cell match the cell data validation rules applied to it. Is that possible, and if so, how do I do it?
Many thanks!
Hi Glynn,
Thanks for your posting and considering Aspose.Cells.
It seems to be a New Feature, please explain your requirements in detail with some Excel files and screenshots. It will help us look into your issue precisely and closely and we will be able to implement it in our future versions.
However, you can retrieve the validation applied on any cell using Worksheet.Validations.GetValidationInCell(row, column) method.
Please see the following code and its Debug Output. I have also attached the source Excel file used in this code for your reference
C#
string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
Cell cell = worksheet.Cells[“F1”];
Validation validation = worksheet.Validations.GetValidationInCell(cell.Row, cell.Column);
Debug.WriteLine("Validation Type: " + validation.Type);
Debug.WriteLine("Validation Formula1: " + validation.Formula1);
workbook.Save(“output.xlsx”);
Debug Output:
Validation Type: List
Validation Formula1: =$A$1:$A$4
Hi Shakeel,
Thanks for your response. I had already written code to retrieve the validation of cells, and also to copy validation one from cell to another using the methods you mention above. That all works fine. However as you probably know, a user can bypass cell validation rules simply by pasting (rather than typing) a value into the cell. Excel will not validate the pasted value. I am importing protected workbooks that have been filled by users and need to verify that the value in each cell satisfies any validation rules applied to that cell. I don't know in advance what the validation rules are, so can't hardcode the checks. The check is possible using Excel automation, as follows:
worksheet.cells(row, col).validation.value()
which returns a boolean indicating whether the cell's value satisfies the validation rules applied to it. Other third party Excel components offer the same capability too.
Aspose.Cells supports cell validation, but it's a critical weakness for me if it can't check that the values in cells meet their validation rules. Apart from that, there's nothing else that I've yet found missing in the component, which would replace native Excel automation in a sophisticated BI solution used across a large multi-national.
I hope this additional information fully explains the need. Thanks again!
Sorry, I should have clarified in my previous explanation (although it might be obvious): to bypass cell validation by pasting, you can either (a) Paste Special (Values) - which inserts the non-compliant value whilst retaining the ineffective cell validation rule (b) Paste - which inserts the non-compliant value and overwrites the cell validation too.
My application currently detects (a) and (b). It's the capability of detecting (a) that is missing from Aspose.Cells. In the case of (b), I am able to compare the target cell to a locked 'reference' cell, therefore seeing that the target cell's data validation has been overwritten. I then re-apply the cell validation by copying it from the reference cell, then check that its value complies with the validation rule. A neat function is provided in another third party component which checks that the same validation rule is applied to all the cells within a range; presumably they implemented this as a workaround for the pasting problem I describe above. Unfortunately that third party component misses some other essential functionality hence me looking at Aspose.
A clumsy workaround in my case would be to write a function that reads the validation rules of the target cell and evaluates the cell's value against those rules. However, if 'Custom' validation is applied, I would need to evaluate whatever formula that has been entered, respecting relative references to the target cell. I haven't seen yet whether its possible in Aspose.Cells to evaluate a formula on-the-fly without first entering that formula into a cell. If that's not possible then I'm stumped.
Hi Glynn,
Thanks for your explanation and considering Aspose.Cells.
We have a logged a New Feature request in our database for this issue. We will look into it and see if it could be implemented in our next versions. Once, there is a fix for you or if there is any update, we will let you know asap.
This issue has been logged as CELLSNET-42935.
Hi Glynn,
Thanks for using Aspose.Cells.
We have evaluated this feature. We will support this feature in the next month because we have to handle some
urgent issues (such as Enterprice And Priotity issues) now.
Thanks for your understanding.
No problem Shekeel. I have coded a temporary workaround to the problem which checks that cell values comply with validation rules, including custom formulas. It does some swapping of cell contents to update relative references. I'm still evaluating so the eventual solution would be cleaner using built-in Apose capability. Please do let me know when it's implemented. A snippet looks likes this:
'get the validation formula from the reference cell (C1):
vld = wks.Validations.GetValidationInCell(0, 2)
strValidForm = vld.Formula1
ob = wks.Cells(0, 2).Value
wks.Cells(0, 2).Formula = vld.Formula1
strValidForm = wks.Cells(0, 2).R1C1Formula
wks.Cells(0, 2).Value = ob
'now check that cells beneath comply with the validation rule
'of the reference cell - no need to check whether each cell has
'validation
For irow = 1 To 100
'adjust the relative references of the formula for the target cell
ob = wks.Cells(irow, 2).Value
wks.Cells(irow, 2).R1C1Formula = strValidForm
strTargetValidFormula = wks.Cells(irow, 2).Formula
wks.Cells(irow, 2).Value = ob
'validate the value in the target cell; True = Compliant
If wks.CalculateFormula(strTargetValidFormula) = True Then iCompliant += 1 Else iNonCompliant += 1
Next irow'get the validation formula from the reference cell:
vld = wks.Validations.GetValidationInCell(0, 2)
strValidForm = vld.Formula1
ob = wks.Cells(0, 2).Value
wks.Cells(0, 2).Formula = vld.Formula1
strValidForm = wks.Cells(0, 2).R1C1Formula
wks.Cells(0, 2).Value = ob
For irow = 1 To 100
'adjust the relative references of the formula for the target cell
ob = wks.Cells(irow, 2).Value
wks.Cells(irow, 2).R1C1Formula = strValidForm
strTargetValidFormula = wks.Cells(irow, 2).Formula
wks.Cells(irow, 2).Value = ob
'validate the value in the target cell; True = Compliant
bValid=wks.CalculateFormula(strTargetValidFormula)
Next irow
Hi Glynn,
Thank you for sharing your devised workaround on the public forum for other users to be get benefit from.
The ticket against your request is attached to this thread, therefore, as soon as the fix is available for public use, you will be automatically notified here with the download link to the upgraded API. In the meanwhile, we will keep you posted with progress in this regard.
The issues you have found earlier (filed as CELLSNET-42935) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
Unfortunately I don't think this fix works. To verify, insert the value "hello" into cell A1. Apply Data Validation to A1, Whole Number between 0 and 10. GetValidationValue returns True in the code below, however "hello" is not a whole number between 0 and 10.
Dim wb As New Workbook(strPath)
Dim ws As Worksheet = wb.Worksheets(0)
Dim ocell As Cell = ws.Cells(0, 0)
If ocell.GetValidationValue() Then
Stop
End If
Hi,
I observed the issue as you mentioned. I used the following sample code to verify the cell value's data validation but it returns true.
e.g
Sample code:
//Instantiate a new workbook
Workbook workbook = new Workbook();
//Accessing the ValidationCollection collection of the worksheet
ValidationCollection validations = workbook.Worksheets[0].Validations;
//Creating a Validation object
Validation validation = validations[validations.Add()];
//Setting the validation type to whole number
validation.Type = Aspose.Cells.ValidationType.WholeNumber;
//Setting the operator for validation to Between
validation.Operator = OperatorType.Between;
//Setting the minimum value for the validation
validation.Formula1 = "0";
//Setting the maximum value for the validation
validation.Formula2 = "10";
validation.ErrorMessage = "Error....";
//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);
//Access Cell A1
//It can take only the values Between 0 and 10
Cell cell = workbook.Worksheets[0].Cells["A1"];
//Enter "hello" inside this cell
//Since it is not between 0 and 10, it should fail the validation
cell.PutValue("hello");
//Check if number satisfies the Data Validation rule applied on this cell
Console.WriteLine("Is hello a Valid Value for this Cell: " + cell.GetValidationValue()); //True - Not Ok
I have reopened your issue "CELLSNET-42935" now. We will look into it soon.
Once we have any update on it, we will let you know here.
Thank you.
Hi, I just received the response below to this topic which I think has been subsequently withdrawn (by babar.raza). The response below describes the behaviour of the Excel user interface which allows existing values to remain when contradictory data validation rules are subsequently applied to the target cells. It does not however reflect the behaviour of Excel automation, whereby the Validation.Value method in Excel will return False indicating that the existing value fails validation. Whether it's Aspose or Excel automation, the code will not know which came first - the value or the validation rule. If this is intended behaviour, then GetValidationValue will have no use because it will always return True. In fact, I have tested adding the cell value "Hello" AFTER the validation rule is applied to the cell, and GetValidationValue still returns True.
If you disagree with my arguments above then I would appreciate if you could illustrate a scenario whereby GetValidationValue returns False. I haven't managed to get a False value yet.
Thanks!
Hi Glynn,
Thank you for contacting Aspose support.
This is the default behavior of MS Excel application that when a cell has any value, and you apply a data validation rule to it, MS Excel does not complain about that value in that particular cell. Means, MS Excel accepts any value as valid entry if it already exists at the time of applying data validation rule. Please check the attached spreadsheet, A1 has "hello" string in it with data validation rule of a whole number between 0 - 9.
Aspose.Cells follow MS Excel's guideline and recommendation in it's implementation therefore the presented behavior is correct in Aspose.Cells perspective.
Hi Glynn,
I have withdrawn my response upon discussing the matter internally. We have to look further into the situation to provide you with a valid justification for the said behavior. As narrated by Amjad, the relevant ticket has been re-opened, and we will shortly get back to you with updates in this regard.
Hi all,
Are you able to provide an update on this issue? If you expect the issue to remain for some time, then I will need to code a workaround. Please let me know.
Thanks,
Glynn
Hi,
I am afraid, there is no update on your issue at the moment. Our concerned developers are on their holidays (they will come back after 3-5 days or so). Once they come back, we will schedule it and figure your issue out, hopefully, it will be fixed soon.
Keep in touch.
Thank you.
Hi,
Please try our latest version/fix: Aspose.Cells for .NET v8.2.1.4
We have fixed your issue now.
Let us know your feedback.
Thanks,
Hi Amjad,
Sorry for the delay in responding. I tested the fix this morning (in version 8.2.2) and the problem appears to be fixed. I tested integer and custom validation and both worked fine.
Thanks for your help,
Glynn
Hi,
Thanks for your feedback.
Good to know that your issue is resolved by the fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
Thank you.
Hi Amjad,
Unfortunately there is still a problem. A call to GetValidationValue returns NullReferenceException within the following example:
Within Excel, in a new workbook, enter these values to the cells in range Sheet2!A1:A3: A, B, C
Define a name to the range Sheet2!A1:A3, for example "List1"
Add a validation rule to cell Sheet1!A1 - type List, Source = List1.
Cell Sheet1!A1 will now only allow the values A, B, C from it's drop-down list.
In Aspose.Cells, call GetValidationValue() for Sheet1!A1. It will return a NullReferenceException, irrespective of whether the cell contains a value.
Please advise... thanks,
Glynn
Hi,
Thanks for providing details.
After an initial test, I observed the issue as you mentioned by using the following
sample code with a simple template file (attached) that is created manually in MS Excel as
per your instructions.
sample code with a simple template file (attached) that is created manually in MS Excel as
per your instructions.
I got the error on the last line of code:
Object reference not set to an instance of an object.
e.g
Sample code:
Workbook workbook = new Workbook("e:\\test2\\Validationcheck1.xlsx");
//Access Cell A1
Cell cell = workbook.Worksheets[0].Cells["A1"];
//Check if number satisfies the Data Validation rule applied on this cell
Console.WriteLine("Whether a Valid Value for this Cell: " + cell.GetValidationValue()); //Error
Sample code:
Workbook workbook = new Workbook("e:\\test2\\Validationcheck1.xlsx");
//Access Cell A1
Cell cell = workbook.Worksheets[0].Cells["A1"];
//Check if number satisfies the Data Validation rule applied on this cell
Console.WriteLine("Whether a Valid Value for this Cell: " + cell.GetValidationValue()); //Error
I have logged a separate ticket with an id "CELLSNET-43122" for your issue. We will look into it soon.
Once we have any update on it, we will let you know here.
Thank you.