Format datetime and numeric columns RadGridView

[Victoria F]

Could you, please help me to find code examples how to format numeric and datetime fields(columns) in RadGridView .

We need comma separator for numeric fields in RadGridView columns.

We also need short datetime format for datetime fields in RadGridView.

The problem is that we adding dynamically all columns from the c# code.

So we have to format columns on a fly.

[Alexander]

Hello Victoria,

Thank you for your question.

The RadGridView control uses the current culture of the application to format its data. The decimal separator will be used when it is the default one for the application's culture, for example in German culture:

Application.CurrentCulture = new System.Globalization.CultureInfo("de-DE");

You can define the culture, used by a specific column using:

GridViewDecimalColumn decimalColumn = new GridViewDecimalColumn();
decimalColumn.FormatInfo = new System.Globalization.CultureInfo("de-DE");

The first approach is more recommended because it will affect both format of the column data and the editor of that column.

The FormatString of the DateTime column defines the formatting of its data. For short datetime format you can use:

GridViewDateTimeColumn dateTimeColumn = new GridViewDateTimeColumn();
dateTimeColumn.DataType = typeof(DateTime);
dateTimeColumn.FormatString = "{0: M/d/yyyy}";

I hope it helps.

Kind regards,
Alexander
the Telerik team

[Duy]

Dear Alexander,

You said: "The RadGridView control uses the current culture of the application to format its data. The decimal separator will be used when it is the default one for the application's culture, for example in German culture"

I found that it was not true. DateTime column doesn't respect the application culture format. I have to set it directly for every columns, this is very poor feature of RadGridView

Regards,
Duy

[Svett]

Hi Duy,

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,

Svett
the Telerik team

 

[Khanh]

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.

[Hristo]

Hello Khanh,

The GridViewDataColumn class exposes a FormatInfo property which you can set to a particular CultureInfo object, in your case US or French culture. The specified culture will determine how the cell values will be formatted. Please check my code snippet below: 

GridViewDecimalColumn col = this.radGridView1.Columns["Decimal"] as GridViewDecimalColumn;
col.FormatInfo = new System.Globalization.CultureInfo("en-US");
//col.FormatInfo = new System.Globalization.CultureInfo("fr-FR");
col.FormatString = "{0:n}";

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

Regards,
Hristo
Progress Telerik

[Brendan]

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.

For Each col As GridViewDataColumn
       col.FormatString = "{0:#,##0.0000}"
       col.ExcelExportFormatString = "#,##0.0000"

[Nadya | Tech Support Engineer]

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

[Brendan]

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}"

[Nadya | Tech Support Engineer]

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

[Brendan]

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.

[Victoria F]

Thank you ,

Actually I found a way how to do this :

private void Format_GridColumns(RadGridView dgv_format)

        {

          

            foreach (GridViewDataColumn dCol in dgv_format.Columns)

            {

                if (dCol.DataType == typeof(DateTime))

                {

                    if (dCol.FormatString.ToLower() == "{0}")

                    {

                        dCol.FormatString = "{0:d}";

                    }

                }

                if (dCol.DataType == typeof(Decimal))

                {

                    if (dCol.FormatString.ToLower() == "{0}")

                    {

                        dCol.FormatString = "{0:N0}";  //"{0:N2}"

                    }

                }

            }

        }

 

private void radGridView_1_CellFormatting(object sender, CellFormattingEventArgs e)

        {

            if (e.CellElement.ColumnIndex > 2)

            {

                e.CellElement.Text = String.Format("{0:N2}", ((GridDataCellElement)e.CellElement).Value);

            }

        }

[Svett]

Hello Victoria,

We are glad that you resolved the issue yourself. Thank you for sharing the solution with the community.

Best wishes,
Svett
the Telerik team

[Victoria F]

Can I not only format the column but also disable this column and color it differently?
I was able to disable it but color it ... at the same time ... that's a problem.
Could you , please help.

Thank you,
Victoria.

[Emanuel Varga]

Hello Victoria,

There are a few discussions out there, where it was asked how to override the default disable color for the controls, you could check out this article here, it should provide the necessary information,

Hope this helps, if you have any other questions or comments, please let me know,

Best Regards,
Emanuel Varga

[Svett]

Hi Victoria F,

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

this.radGridView1.Columns["YourColumn"].ReadOnly = true;

You can use the CellFormatting event to change the cell appearance. You can read more about that in the online documentation.

Kind regards,
Svett
the Telerik team