Format datetime and numeric columns RadGridView

16 posts, 0 answers
  1. Victoria F
    Victoria F avatar
    46 posts
    Member since:
    May 2010

    Posted 04 Aug 2010 Link to this post

    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.

  2. Alexander
    Admin
    Alexander avatar
    306 posts

    Posted 06 Aug 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. Victoria F
    Victoria F avatar
    46 posts
    Member since:
    May 2010

    Posted 10 Aug 2010 Link to this post

    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);

                }

            }

  4. Svett
    Admin
    Svett avatar
    728 posts

    Posted 13 Aug 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  5. Duy
    Duy avatar
    5 posts
    Member since:
    Nov 2008

    Posted 18 Sep 2010 Link to this post

    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
  6. Svett
    Admin
    Svett avatar
    728 posts

    Posted 22 Sep 2010 Link to this post

    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

     

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  7. Victoria F
    Victoria F avatar
    46 posts
    Member since:
    May 2010

    Posted 06 Oct 2010 Link to this post

    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.
  8. Emanuel Varga
    Emanuel Varga avatar
    1336 posts
    Member since:
    May 2010

    Posted 06 Oct 2010 Link to this post

    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
  9. Svett
    Admin
    Svett avatar
    728 posts

    Posted 11 Oct 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  10. Khanh
    Khanh avatar
    17 posts
    Member since:
    May 2018

    Posted 19 Jul 2018 Link to this post

    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.

  11. Hristo
    Admin
    Hristo avatar
    1520 posts

    Posted 20 Jul 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  12. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 09 Mar Link to this post

    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"
  13. Nadya
    Admin
    Nadya avatar
    379 posts

    Posted 10 Mar Link to this post

    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

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  14. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 10 Mar in reply to Nadya Link to this post

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

  15. Nadya
    Admin
    Nadya avatar
    379 posts

    Posted 12 Mar Link to this post

    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

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  16. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 07 Apr in reply to Nadya Link to this post

    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.

Back to Top