Hi, I created a drop-down list with the values TRUE and FALSE.
If the user is typing (not using the mouse for editing), the he must type in upper case, in other way the value will not be accepted. There is any possibility to override the case validation?
I want the user to be allowed to enter the values in any case (and then the application will modify it to upper case), like "TrUE" or "tRUe" or "True" or "true".
Hi,
Well, we will check if could enhance List Validation according to your need. I think you may try to use Boolean Validataion for your task as It don't have match case problem.
e.g.,
cell = cells["C7"];
cell.CreateValidation(ValidationType.Boolean, true);
Thank you.
Hi,
I’ll have to wait because “True” was only an example, there is many other words used in drop-down lists in my app.
We will think about this issue and give a solution soon.
Hi,
I think that you may use OnCellErrorClientFunction event to handle the client validating error and convert the cell's value to upper case.
Hi,
I’ll try to use this events.
Thanks!
Hello all,
I know this is a pretty old messagethread, but it kind of matches the problem I am having. I am also trying to create a boolean validation on a cell, but I have problems getting the ‘TRUE’ and ‘FALSE’ values in the dropdown list.
It seems that the CreateValidation method would EXACTLY fix my problem, however the method seems to have been dropped as it is no longer available on the cell-class.
Alternatively I try to create thelist myself but it will just never be populated as boolen, it’s always just strings.
The code I am using:
ValidationCollection validations = sheet.Validations;
Validation validation = validations[validations.Add()];
validation.Type = ValidationType.List;
validation.Formula1 = “1,0”;
columnCellValue.Value = true;
validation.AreaList.Add(columnCellValue.GetCellArea());
GetCellArea() is an extension that returns a valid area.
I have tried the following spellings:
validation.Formula1 = “True,False”;
validation.Formula1 = “true,false”;
validation.Formula1 = “yes,no”;
validation.Formula1 = “Yes,No”;
They all show up as a list, just not of the correct datatype.
Please inform me what the correct way is to populate a boolean validation
(Note I am working with the Aspose.Cells version of november 2012)
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
Please study the following article and see if it resolves your issue. The article explains how to do data validation using Aspose.Cells.
Hello,
No the article does not solve the problem as it does not mention how to handle boolean values. Jugding by this thread, Aspose.Cells used to have native support for this, any particular reason why it has been removed?
Another symptom of the problem is that the items in formula1 does not show up as a list. It does not matter if I use “,” or “;” as a separator.
However, if I open the generated file in Excel and open the Cells’ validation editor, it turns into a list after I closed the popup (without making any changes).
It may be worth noting that I am running an english windows version, however a dutch language pack and office version have been installed.
If you could give a code demo on how to properly initalize a validation for booleanvalidation, I would be quite happy.
Thank you.
Hi,
For the line used in one of the above post, i.e…,
cell.CreateValidation(ValidationType.Boolean, true);
This is the GridWeb control’s API and not Aspose.Cells API. The GridWeb is a separate and independent Web grid control which has his own Validation types, e.g
- AnyValue
- DropDownList
- List
- FreeList
- CustomExpression
- Boolean
- Number
- Integer
- Date
- DateTime
- Time
- TextLength
- CustomString
- CustomFunction
- CheckBox
Please see the demo for complete reference:
Working with Validations in Worksheets
If you are using GridWeb control then you may use any validation type as mentioned in the above list.
Now come towards Aspose.Cells, Well, Aspose.Cells is a class library used to manage Excel files. It follows MS Excel standards and supports almost all the features that Ms Excel (97-2010) supports. I don’t think there is any Boolean validation type (please see the article for the supported types as per Ms Excel shared by my fellow colleague Shakeel) in MS Excel. If you still think there is any relative type and you may do it according to your needs in MS Excel, then please create your desired validation in Ms Excel manually and save the Excel and post it here, we will check it and see how to accomplish this via Aspose.Cells APIs.
Thank you.
Edit: doublepost.
Hi,
First, sorry for posting in the wrong forum. I was trying to search in only ‘Cells’ but I must have messed that up.
Second, it does seem to be a (imo fundamental) flaw of excel that there is no native support boor boolean validations. Which does not mean it can not be worked around, but it is not pretty.
I ended up doing the following:
// Create the validation for the cell.
ValidationCollection validations = sheet.Validations;
Validation validation = validations[validations.Add()];
validation.Type = ValidationType.List;
validation.Formula1 = “=TRUTHTABLE”;
columnCellValue.Value = true;
validation.AreaList.Add(columnCellValue.GetCellArea());
// Then create a lookup region.
cell1.Formula = “FALSE()”;
cell2.Formula = “TRUE()”;
cell1.Worksheet.Cells.CreateRange(cell1.Name, cell2.Name).Name = “TRUTHTABLE”;
Sadly… this works.
Thanks for your time.
Bas
Hi,
Good to know that your have sorted out your issue a bit. I am not sure if there can be any better workaround in MS Excel to achieve the requirements. Anyways, you may go for your workaround if it suits you a bit.
Thank you.