How to mimic the Excel-like behavior for the cells that have the Format string in WinForm GridControl?

Solution

In Excel, when the current cell enters into the edit mode it has the Value string and not the formatted string. The Excel formula bar always displays the Value String and not the formatted string. These behaviors can be achieved in the WinForms GridControl by installing a handler for CurrentCellInitializeControlText event and by setting the ControlText to Value string instead of the formatted string. This sets the GridAwareTextBox with value string consistently.

The following code example is for handling the CurrentCellInitializeText event.

this.gridControl1.CurrentCellInitializeControlText += new Syncfusion.Windows.Forms.Grid.GridCurrentCellInitializeControlTextEventHandler(gridControl1_CurrentCellInitializeControlText);

void gridControl1_CurrentCellInitializeControlText(object sender, Syncfusion.Windows.Forms.Grid.GridCurrentCellInitializeControlTextEventArgs e)
{
 //initialize the control text with value string instead of formatted string.            e.ControlText = e.Style.GetText(e.CellValue);
}

AddHandler Me.gridControl1.CurrentCellInitializeControlText, AddressOf gridControl1_CurrentCellInitializeControlText

Private Sub gridControl1_CurrentCellInitializeControlText(ByVal sender As Object, ByVal e As Syncfusion.Windows.Forms.Grid.GridCurrentCellInitializeControlTextEventArgs)
  'initialize the control text with value string instead of formatted string.
  e.ControlText = e.Style.GetText(e.CellValue)
End Sub

Other different behaviors and options

GridAwareTextBox always with FormattedText

The GridAwareTextBox is implemented by following a consistent pattern and it always displays the .Text. When the CurrentCell is activated and GridCurrentCell is changed, the GridAwareTextBox text is set with the CurrentCell.Renderer.ControlText that is the formatted string, since by default the ControlText is initialized with the formatted text. So, when you press Escape key while editing a cell, the GridAwareTextBox is again set with .Text that is not the formatted text. When you want to display the FormattedText consistently in the GridAwareTextBox then you can create sub class for the GridAwareTextBox, override the GridCurrentCellRejectedChanges and set the .FormattedText instead of the .Text

The following code example is for GridCurrentCellRejectedChanges event.

protected override void GridCurrentCellRejectedChanges(object sender, EventArgs e)
 {
   GridControlBase grid = sender as GridControlBase;
   GridCurrentCell cc = grid.CurrentCell;
               //Always display FormattedText
   SetText(grid.Model[cc.RowIndex, cc.ColIndex].FormattedText);//.Text);
 }

Protected Overrides Sub GridCurrentCellRejectedChanges(ByVal sender As Object, ByVal e As EventArgs)
   Dim grid As GridControlBase = TryCast(sender, GridControlBase)
   Dim cc As GridCurrentCell = grid.CurrentCell
  'Always display FormattedText
  SetText(grid.Model(cc.RowIndex, cc.ColIndex).FormattedText) '.Text);
End Sub

Parsing Formats

To save the underlying cell value from the formatted text, the ParseCommonFormats / SaveCellFormattedText event can be handled. The attached sample populates Text with formats when you miss any format character and also parses the formatted string and saves the underlying cell value, in the ParseCommonFormats event handler.

this.gridControl1.ParseCommonFormats += new GridCellTextEventHandler(gridControl1_ParseCommonFormats);

void gridControl1_ParseCommonFormats(object sender, GridCellTextEventArgs e)
 {
   if (e.Style.CellIdentity.ColIndex == 1 && !this.checkBox1.Checked)
   {
     //populate the formats if user had missed by chance 
     //eg. if the user missed any one bracket. Entered Cell Value: "(45.00" .
     string text = e.Text;
     text = text.Replace("(", "");
     text = text.Replace(")", "");
     Decimal value;
     if (Decimal.TryParse(text, out value))
     {
       System.Globalization.CultureInfo ci = e.Style.CultureInfo;
       System.Globalization.NumberFormatInfo nfi = ci != null ? ci.NumberFormat : null;
       e.Style.BeginUpdate();
       e.Text = GridCellValueConvert.FormatValue(value, e.Style.CellValueType, e.Style.Format, ci,   nfi);
       e.Style.CellValue = value;
       e.Style.EndUpdate();
       e.Handled = true;
      }
     else
      {
        MessageBox.Show(e.Text + " is invalid value!", "Invalid Type/ Format");
        e.Handled = true;
        if (this.gridControl1.CurrentCell != null)
        this.gridControl1.CurrentCell.CancelEdit();
        //SendKeys.Send("{Esc}");
       }
     }
}

Private Sub gridControl1_ParseCommonFormats(ByVal sender As Object, ByVal e As GridCellTextEventArgs)
  If e.Style.CellIdentity.ColIndex = 1 AndAlso (Not Me.checkBox1.Checked) Then
     'populate the formats if user had missed by chance 
     'eg. if the user missed any one bracket. Entered Cell Value: "(45.00" .
     Dim text As String = e.Text
     text = text.Replace("(", "")
     text = text.Replace(")", "")
     Dim value As Decimal
     If Decimal.TryParse(text, value) Then
        Dim ci As System.Globalization.CultureInfo = e.Style.CultureInfo
        Dim nfi As System.Globalization.NumberFormatInfo = If(ci IsNot Nothing, ci.NumberFormat, Nothing)
        e.Style.BeginUpdate()
        e.Text = GridCellValueConvert.FormatValue(value, e.Style.CellValueType, e.Style.Format, ci, nfi)
        e.Style.CellValue = value
        e.Style.EndUpdate()
        e.Handled = True
     Else
        MessageBox.Show(e.Text & " is invalid value!", "Invalid Type/ Format")
        e.Handled = True
        If Me.gridControl1.CurrentCell IsNot Nothing Then
            Me.gridControl1.CurrentCell.CancelEdit()
        End If
    'SendKeys.Send("{Esc}");
     End If
  End If
End Sub

Sample Link: Excel_like_behavior

Conclusion

I hope you enjoyed learning about on how to mimic the Excel-like behavior for the cells that have the Format string.

You can refer to our WinForms GridControl feature tour page to know about its other groundbreaking feature representations and WinForms GridControl documentation, and how to quickly get started for configuration specifications.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!

Did you find this information helpful?

Yes

Comments (0)

How to mimic the Excel-like behavior for the cells that have the Format string in WinForm GridControl?

Access denied