Format datetime and numeric columns RadGridView

The cells of GridViewDateTimeColumn format their values depending on the application culture. Nevertheless, the RadDateTimeEditor of the column is not affected. This behavior appears for most of the editors used in the RadGridView. We know about this lack of functionality. We will address it in one of our next releases.

Regards,

Dear admins,

I have an issue with numeric format string of RadGridView, please give me your suggestions. 

Example, I have a list data in English (en-US) and all values in cells have DataType is numeric:

                              Column1              Column2              Column3             
Row 1                    1234.12                5678.12               10000
Row 2                    1234.123              5678.123             20000
Row 3                    1234.1234            5678.1234           30000   

And I want to display the above list on a data gridview as follow:
+ If language is English (en-US):
                              Column1              Column2              Column3             
Row 1                    1,234.12               5,678.12               10,000
Row 2                    1,234.123             5,678.123             20,000
Row 3                    1,234.1234           5,678.1234           30,000  


+ If language is French (fr-CA):
                              Column1              Column2              Column3             
Row 1                    1 234,12               5 678,12               10 000
Row 2                    1 234,123             5 678,123             20 000
Row 3                    1 234,1234           5 678,1234           30 000  

May I use FormatString of GridViewDecimalColumn for that ?
Ex:
var gridColumn = new GridViewDecimalColumn(column.ColumnName);
gridColumn.FormatString = "{0:n}";

If you want to see easier, please view the attachment.

Thanks so much.

I am trying to format a RadGridViewData column as numeric with 4 decimals. The underlying SQL data column is nVarChar(20) with a value of 1234567.0001.  The code I am using is below. The column in the grid shows '1234567.0001' where I would like it to show '1,234,567.0001'.

What am I doing wrong?  Thanks.

Hello Brendan,

If you have a numeric column it is suitable to use GridViewDecimalColumn that allows decimal data to be displayed and edited. GridViewDecimalColumn has a property called DecimalPlaces which determines how many decimals should be displayed when the cell is edited. Additionally,  you can use the FormatString property of the column, which is used to format the cells according to your custom format when they are not in edit mode:

Dim decimalCol As GridViewDecimalColumn = TryCast(radGridView1.Columns("DecimalColumn"), GridViewDecimalColumn)
    If decimalCol IsNot Nothing Then
        decimalCol.FormatInfo = New System.Globalization.CultureInfo("en-US")
        decimalCol.FormatString = "{0:#,##0.0000}"
        decimalCol.DecimalPlaces = 4
    End If

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Nadya
Progress Telerik

Thanks Nadya, but the data is returned via SQL query to a DataTable and the grid.datasource is set to this table.  So I do not set the columns discretely.

As mentioned, the underlying column in the SQL table is nVarChar(20) which is set/created as a GridViewTextBoxColumn when the DataTable is bound to the grid.datasource.

Is it possible to format the GridViewTextBoxColumn as I have indicated? eg: col.FormatString = "{0:#,##0.0000}"

Hello Brendan,

Since GridViewTextBoxColumn works with strings it is necessary first to change the editor in order to use GridSpinEditor and display numeric values. Then you can format the value in the CellFormatting event. Please refer to the following code snippet:

private void RadGridView1_CellFormatting(object sender, CellFormattingEventArgs e)
{
    if (e.Column.Name == "TextBoxColumn" && e.CellElement.Value != null)
    {
        decimal number = decimal.Parse(e.CellElement.Value.ToString());
        e.CellElement.Text = string.Format("{0:#,##0.0000}", number);
    }
}

private void RadGridView1_EditorRequired(object sender, EditorRequiredEventArgs e)
{
    if (this.radGridView1.CurrentColumn.Name == "DecimalColumn")
    {
        e.EditorType = typeof(GridSpinEditor);
    }
}

private void radGridView1_CellEditorInitialized(object sender, GridViewCellEventArgs e)
{
    GridSpinEditor spinEditor = e.ActiveEditor as GridSpinEditor;
    if (spinEditor != null)
    {
        spinEditor.DecimalPlaces = 4;
        spinEditor.MinValue = int.MinValue;
        spinEditor.MaxValue = int.MaxValue;
        spinEditor.Value = e.Value;
    }
}

I hope this helps.  Let me know if you have other questions.

Regards,
Nadya
Progress Telerik

Thanks Nadya, sorry for the late reply.

I only had to use the Cell_Formatting event, the other two were not necessary.  I now have commas in my 'text' cell.  The decimal.parse did the trick.

You may mark the column as read only by setting the ReadOnly property to true: