cell validation

Posted by: patrickc on 10 September 2017, 10:57 am EST

  • Posted 10 September 2017, 10:57 am EST

    I don't know where to begin on validating user input.  I have written my own clipboard paste command that validates all of the clipboard data and where it will be pasted before the paste occurs, but I am lost on how to do the following:


    Single-cell edit:


        Typing data in while cell is in edit mode


            stop user from entering characters that would create a non-decimal value in a cell that is supposed to hold a decimal value.


            same for cells that are supposed to hold integer values.


            stop user from entering characters when a max-length limit has been reached.


     


        Pasting data into a cell that is in edit mode


            stop user from pasting characters that would create a non-decimal value in a cell that is supposed to hold a decimal value.


            same for cells that are supposed to hold integer values.


            stop user from pasting in data that would cause the value to exceed a max-length limit.


     If any or all of this is not possible by design or not at this time, has anyone come up with good-practice and user-friendly alternatives?  I know I can check the data at the edit end event, but the user has lost the original value at that point.


     


    Thanks!


     


    Patrick

  • Replied 10 September 2017, 10:57 am EST

    There is no support to validate input during cell editing.

    You may get CellEditor in EditCellStarting event, it is a TextBox, and to process your logic in TextChanged, or KeyDown/Up events of the CellEditor.

  • Replied 10 September 2017, 10:57 am EST

     
    Hello,
     

    There is no direct implementation to achieve this. As per your requirement to validate the Clipboard data before pasting, you can use the EditChange event for Spread Control. This event fires for any changes while the spread control is in edit mode. So you can check the contents from the Editor text and apply the required validation. If the vaildation fails, you can reset the original text before pasting the data. 


    Here is a suggested code implementation.


    void Spread1_EditChange(object sender, GrapeCity.Windows.SpreadSheet.UI.EditCellEventArgs e)
    {

         string cellText;
         cellText = Spread1.Sheets[0].ActiveCell.Text;
         string str = ((System.Windows.Controls.TextBox)(((GrapeCity.Windows.SpreadSheet.UI.SheetView)(sender)).CellEditor)).Text;

         if (str.Contains("%"))
         {
             MessageBox.Show("Invalid Character %");                
             ((System.Windows.Controls.TextBox)(((GrapeCity.Windows.SpreadSheet.UI.SheetView)(sender)).CellEditor)).Text = cellText;

         }            
    }



    You may have to modify the above code as per your requirement. Let us know if you have any doubts.
     
     
    Regards,
    Abhishek
  • Replied 10 September 2017, 10:57 am EST

    Eric,


    when using the cell editor events, I add an event handler like so in the editstarting event:


    //add event handlers for the editing textbox control.


    if (wkbkQuoteLines.View.CellEditor is TextBox)


    {


    TextBox tb = (TextBox)wkbkQuoteLines.View.CellEditor;


    tb.KeyDown -= new KeyEventHandler(EditingTextBox_KeyDown);


    tb.KeyDown += new KeyEventHandler(EditingTextBox_KeyDown);


    }


    In trying it, I tab over to a cell and push a key on the keyboard.  The event doesn't fire.  I push another key and the event then fires every time.  The event handler for the cell editor is the culprit; it isn't catching the first key.


     


    Still need to try the other method from Abhishek, but I would like to know if there is a way to get the event to fire on the first key for the celleditor.  That seems like a bug.

  • Replied 10 September 2017, 10:57 am EST

    Hello,

    Edittor is not invoked until you get into edit mode. The first time you press a key it does start the editing and hence make the editor active.You can catch the event for editor after this.

     

    Thanks,

  • Replied 10 September 2017, 10:57 am EST

    Combined the following to catch that pesky first key.  For the record, I don't think it makes sense to miss that first key with the event handler of the cell editor textbox; a key was pressed causing the cell editor textbox to get focus WITH the key in it.  It stands to reason that we would want code responding to key presses to run for that first key, not just subsequent keys.


    When a cell is active but not in edit mode, the following will check the first key using the EditChange event (thanks Abhishek!).  If the first key is not an integer, the cell editor will be cleared, effectively suppressing the key.  The cell is now in edit mode, so the event handler for the cell editor textbox will fire before the EditChange event.  This allows us to set a global Boolean variable to suppress the EditChange event from happening again while this instance of the cell editor textbox is open.  I am hedging my bets by setting the global variable back to false (to stop further suppression of the EditChange event) in both the editend and the editstarting events for spread.


     I haven't beat up on this in a working environment, and I need to first get it working against only my integer columns, then make it handle decimal columns, as well.  Any constructive criticism or suggestions of more eloquent code appreciated.


    private void wkbkQuoteLines_EditStarting(object sender, GrapeCity.Windows.SpreadSheet.UI.EditCellStartingEventArgs e)


    { holdEditChange = false; }


    private void wkbkQuoteLines_EditEnd(object sender, GrapeCity.Windows.SpreadSheet.UI.EditCellEventArgs e)


    { holdEditChange = false; }


    private void EditingTextBox_KeyDown(object sender, System.Windows.Input.KeyEventArgs e)


    {


        holdEditChange = true;


        if (!Char.IsDigit((char)System.Windows.Input.KeyInterop.VirtualKeyFromKey(e.Key)) && !integerkeylist.Contains(e.Key))


            e.Handled = true;


    }


    private void wkbkQuoteLines_EditChange(object sender, GrapeCity.Windows.SpreadSheet.UI.EditCellEventArgs e)


    {


        if (!holdEditChange)


        {


            string val = ((System.Windows.Controls.TextBox)(((GrapeCity.Windows.SpreadSheet.UI.SheetView)(sender)).CellEditor)).Text;


            int num;


            if (!int.TryParse(val, out num))


                ((TextBox)(((GrapeCity.Windows.SpreadSheet.UI.SheetView)(sender)).CellEditor)).Text = "";


        }


    }


     

  • Replied 10 September 2017, 10:57 am EST

    Your way is good way for the first key issue by current structure of control. Why the first key won't fire the keydown event of cell editor is because focus is on the control and the cell editor isn't on visual tree yet at that time. I am afraid that might be a limitation of current structure. So generally cell validation is recommended to do at occasion of leaving cell, each cell has DataValidator property to be set a DataValidator object which can be used to verify the value inputted in cell, and ValidationError event of control will be fired if inputted text is invalid. If you really need real time validation during cell editing, I thinkk your way is a good solution.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels