GridWeb validation issues

Aspose.Cells Product Family
1

Hi,

I’m here to figure out the validation in GridWeb control.

Let’s assume we have a cell with a validation defined as follow:

  //Create cell validation 
  ValidationCollection validations = worksheet.Validations;
  CellArea ca = new CellArea() { StartRow = cell.Row, EndRow = cell.Row, StartColumn = cell.Column, EndColumn = cell.Column };
  Validation validation = validations[validations.Add(ca)];
  validation.Type = Aspose.Cells.ValidationType.List;
  validation.Operator = OperatorType.Between;
  validation.InCellDropDown = true;
  validation.Formula1 = string.Join(",", values);
  validation.ErrorMessage = "Value not valid";
  validation.ErrorTitle = "Error";

GridWeb displays a dropdown with the different possible values. Everything looks correct.

However when I type a “wrong” value in this cell, I got no error message. The only thing I can see is red cross in the cell.

Moreover the OnCellModifiedOnAjax handler is not called. This means that I have no possibility to have a custom handling of this error.

In fact, when the value is not “correct” I need to display a dialog with an error message and automatically restore cell to its previous value. That is exactly the Excel behaviour so why it’s not the same for GridWeb ?

Thanks,

2

@lswe,

Thanks for providing us sample code and details.

Well, Aspose.Cells.GridWeb has its own interface and style. It has its own way to handle data validation in the sheet. So, if an Excel file has data validation applied, it won’t display message box or alert, rather if you input wrong value it will show red crosses. You may see the error message and title as a tool tip message though when you place the mouse cursor over the cell. You can also use ForceValidation Boolean attribute of GridWeb if you need validation should be compulsory. Moreover, invalid value won’t be stored too.

3

Thanks for your answer,

Well, it’s not exactly what I see in the tooltip on IE 11.

In my web application, the submission of data is not made by GridWeb submit button. It’s just a custom button. My data validation is computed in OnCellModifiedOnAjax handler using the GridComment class and then handled client side by OnCellUpdatedClientFunction by displaying an error dialog. In this case, it does not work because this event is not fired when the input cell value is not correct.

As I previously said, my need is to copy the Excel behaviour: when the value is not “correct” the GridWeb should display a dialog with an error message and automatically restore cell to its previous value.

Is there a way to do it ?

Thanks,

4

@lswe,

Thanks for sharing further details and screenshot.

I have logged your feature request as an improvement to existing feature regarding data validation. I have logged the ticket as following:

  • CELLSNET-45928 - Data validation - GridWeb should display a dialog with an error message if inserting wrong value and automatically restore cell to its previous value

Our concerned developer from product team will evaluate your issue soon.

Once we have an update on it, we will let you know here.

5

Thanks for opening this ticket,

Do you have any idea about when you will release this improvement ?

6

@lswe,

We can’t say or commit anything at the moment. The feature itself demands thorough evaluation and investigation. Our Chinese team is only holidays these days on their spring festival. We will try to evaluate it in the next week (if possible), then we might be able to comment on it.

7

Thanks for your answer,

Do you have any workaround that will help me to wait this fix ? I mean for example overriding one of your javascript function to make a postback that will call the OnCellModifiedOnAjax server handler ?

Thanks,

8

@lswe,

I am afraid, I have no workaround to cope with it, we will further check to try to find one though. Please spare us some time so our concerned developer from product team could evaluate your requested feature in details.

Once we have any new information, we will share it with you.

9

@lswe,

We have found a workaround. Well, you can perform your desired task when validation fails. You can write your business logic on client side. Please use/handle OnCellErrorClientFunction which gets or sets the client side function name to be called when a cell’s validation is failed.
e.g
Sample code:

 /// <summary>
        /// Gets or sets the client side function name to be called when a cell's validation is failed.
        /// The client function should be declared like this:<br/>
        /// function MyOnCellError(cell)<br/>
        /// {<br/>
        ///  alert(GridWeb1.getCellValueByCell(cell));<br/>
        /// }<br/>
        /// <br/>Note: You may use the "this" pointer in the client function to point the grid control which fires the event.
        /// </summary>

Hope, this helps a bit.

10

Thanks for your workaround,

It looks nice but is there a way to restore previous cell value from its code ?

11

@lswe,

We will check but if cell validation fails and you either submit the web page or navigate to other sheets, the older (valid) value in the cell would be set automatically.

12

No way to do it using Javascript when this event is fired ?

14

@lswe,

Since we already logged a ticket with an id “CELLSNET-45928” for your issue, so, kindly let the investigation/evaluation of your issue is complete by the concerned developer from product team. Hopefully, we will update you in the next week.

15

@lswe,

Could you try this way, it should work as we tested. You need to use OnCellSelectedClientFunction and OnCellErrorClientFunction.
e.g
Sample code:

...........
<script type="text/javascript">
         var lastselectvalue = null;
 function myCellSelect(cell) {
             var value = this.getCellValueByCell(cell);
             lastselectvalue = value;
             console.log("myonCellSelect:" + value + " row" + this.getCellRow(cell) + ",col:" + this.getCellColumn(cell));
         }
         function myValidateError(cell) {
             // 
             var who = this;
             //async raise alert
             setTimeout(function () {
                 alert("the input is invalid!");
             }, 2000);
            //restore value
             who.setValid(cell);
             setInnerText(cell.children[0], lastselectvalue);
            // this.setCellValueByCell(cell, lastselectvalue);
         }
 </script>

<acw:gridweb  .....  OnCellSelectedClientFunction="myCellSelect"
                               OnCellErrorClientFunction="myValidateError"....

Hope, this helps a bit.

16

Hi,

Thanks for your answer,

However, because the error message must be computed on server side (OnCellModifiedOnAjax), your suggestion does not fit for my needs.

17

@lswe,

Your issue has been resolved but I need to get confirmation from concerned developer from product team if it will suit your needs or not.

Hopefully we will get back to you soon.

18

@lswe,

As you need the error messages must be computed on server side so, we need to reconsider your requirements, it would be completely different implementation way. I have reopened your ticket “CELLSNET-45928” again. We will try to support your needs soon.

Once we have any new information, we will share it with you.

19

Thanks for your answer,

Please let me know when the fix will be available,

20

@lswe,

Please spare us little time (1-2 weeks or so) to evaluate your issue and to support your needs.

Once we have any new information, we will share it with you.

21

@lswe,

We will add: GridValidationType.CustomServerFunction in one of our upcoming fixes to support your requirements. The code could be something like following:
e.g
Sample code:

//first you need to implement the interface:GridCustomServerValidation
  class myservervali : GridCustomServerValidation
    {
       //if return string.empty the validate is passed,else you can return any related message which can be deal in ClientCallback function
        public string Validate(GridWorksheet sheet, int row, int col, string value)
        {
            if (row == 1)
            {//validate fail
                return "not passed";
                
            }

            else
            {//validate ok
                return string.Empty;
            }
        }
    }
 protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack&&!GridWeb45928.IsPostBack)
            {
            var gridValidationCollection = this.GridWeb45928.ActiveSheet.Validations;
            GridValidation gv = gridValidationCollection.Add();
            gv.AddArea(new GridCellArea(1,1,2,2)); 
            gv.ValidationType = GridValidationType.CustomServerFunction;
            gv.ServerValidation = new myservervali();

          //also need to set ClientValidationFunction  to deal with client logic when server validation fails and return message
            gv.ClientValidationFunction = "ValidationErrorClientFunction";
            }
        }

.aspx page:

 <script type="text/javascript" language="javascript">
         var lastselectvalue = null;
         function myCellSelect(cell) {
             var value = this.getCellValueByCell(cell);
             lastselectvalue = value;
             console.log("myonCellSelect:" + value + " row" + this.getCellRow(cell) + ",col:" + this.getCellColumn(cell));
         }
//decalare like this 
         function ValidationErrorClientFunction(cell,msg) {
             //Showing an alert message where "this" refers to GridWeb
             alert(this.id + msg);
             console.log("myonCellSelect:" + this.getCellRow(cell) + ",col:" + this.getCellColumn(cell));
             // 
             var who = this;
             //async raise alert 
             
             //restore to valid value 
             who.setValid(cell);
             setInnerText(cell.children[0], lastselectvalue);
             // this.setCellValueByCell(cell, lastselectvalue); 
         }

    </script>

......
 <cc1:GridWeb ID="GridWeb45928" runat="server" OnCellSelectedClientFunction="myCellSelect" >

You will also need jquery js lib.

Hope, this helps a bit.