Cell Validation

Aspose.Cells Product Family
1

Hi

Is there any way to check if a cell with excel data validation rules is valid according to the rules using aspose?

My aim is to open a spreadsheet and decide whether there are any cells which do meet the validation rules for the cell.

Thanks

2

Hi,

Well, I am not very sure about your query. Could you elaborate it more and give us complete information about your requirements. What do you mean by excel data validation rules vs rules imposed by Aspose.Cells for Java? Do you use Data Validation or Conditional formattings feature of MS Excel? How could you perform this operation in MS Excel manually. We appreciate if you could create a sample template file manually to explain us how could you perform your task, we will check it soon.

Thank you.

3

Hi

The sheet I have contains some data validation on cells applied using the excel toolbar. I noticed that with a macro you can check the validity of cells using the type of approach in the this link:

http://support.microsoft.com/kb/213773

From this I gathered that for any cell you check check if it is valid or not as below.

If Not c.Validation.Value Then
I would like to be able to use aspose cells to check the validity of a cell in the same way.

Thanks you

4

Hi,

Your link does not respond. But, I 'm afraid Aspose.Cells does not support to create or manipulate macros/vba at the moment. Anyways we may check your requirement and get back to you soon.

Thank you.

5

Hi

Here is the content of the link below. I understand apose does not support macros but is there a way to check if a cell is valid. From the code below it seems in VB this can be done by checking:

If Not c.Validation.Value

Thank you



Sub AddValidationCirclesForPrinting()

    Dim DataRange As Range
    Dim c As Range
    Dim count As Integer
    Dim o As Shape

    'Set an object variable to all of the cells on the active
    'sheet that have data validation – if an error occurs, run
    'the error handler and end the procedure
    On Error GoTo errhandler
    Set DataRange = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
 
    count = 0
 
    'Loop through each cell that has data validation
    For Each c In DataRange
 
       'If the validation value for the cell is false, then draw
       'a circle around the cell. Set the circle’s fill to
       'invisible, the line color to red and the line weight to
       '1.25
       If Not c.Validation.Value Then
           Set o = ActiveSheet.Shapes.AddShape(msoShapeOval, 
               c.Left - 2, c.Top - 2, c.Width + 4, c.Height + 4)
           o.Fill.Visible = msoFalse
           o.Line.ForeColor.SchemeColor = 10
           o.Line.Weight = 1.25
 
           'Change the name of the shape to InvalidData + count
           count = count + 1
           o.Name = “InvalidData_” & count
       End If

   Next
   Exit Sub
 
 errhandler:

   MsgBox “There are no cells with data validation on this sheet.”

 
 End Sub



6

Hi,

Well, I 'm afraid your desired feature is not supported by Aspose.Cells at the moment. We may look into it later on (after completing some other important tasks on hand) but this cannot be supported soon.

Thank you.

7

Hi

Thanks for the info.