Export to Excel - prventing long numbers from scientific notation

9 posts, 1 answers
  1. DJ
    DJ avatar
    14 posts
    Member since:
    Jan 2008

    Posted 30 Jun 2008 Link to this post

    I imagine someone else must have run into this problem in the past.  I have a radGrid that has a phone number column.  Sometimes the phone numbers are long - 12 digits with no dashes or spaces.  Exporting the grid to excel converts the number to scientific notation which obviously isn't going to work.

    I've tried a couple things - setting the DataFormatString and DataType on the GridBoundColumn.

    Is there a good way around this?  Changing the format of the phone numbers is not an option.

    Thanks,
    DJ
  2. Esk
    Esk avatar
    5 posts
    Member since:
    Jun 2008

    Posted 30 Jun 2008 Link to this post

    I am facing the same issue and have not been able to find a way to fix that short of instructing the user to switch the column format in excel to number.

    Would be intresting to see if there is a way to prevent that from happening
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. DJ
    DJ avatar
    14 posts
    Member since:
    Jan 2008

    Posted 01 Jul 2008 Link to this post

    Here is an easy way to prevent this from happening - at least for a GridBoundColumn:

    DataFormatString="P: {0}" 

    Of course it would be nice to do this without adding extra text into the column. 

    I assume this is more of an excel issue, but thought someone else might have found a good workaround to the same issue.
  5. DJ
    DJ avatar
    14 posts
    Member since:
    Jan 2008

    Posted 01 Jul 2008 Link to this post

    This worked just as well - and is going to be good enough for my needs:

    DataFormatString=" {0}" 

  6. Answer
    Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 01 Jul 2008 Link to this post

    Hi DJ,

    Your last assumption is the right one. You can either set the DataFormatString in aspx or in code-behind using similar approach:
    protected void RadGrid1_ColumnCreated(object sender, GridColumnCreatedEventArgs e) 
    if (e.Column is GridBoundColumn) 
        (e.Column as GridBoundColumn).DataFormatString = " {0} "

    or alternatively on ItemDataBound like the following:
    if (e.Item.ItemType == GridItemType.Item || e.Item.ItemType == GridItemType.AlternatingItem && isExport) 
        foreach (GridTableCell gcell in e.Item.Cells) 
            gcell.Text = String.Format(" {0} ", gcell.Text); 

    Greetings,
    Daniel
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  7. Niklas Lundberg
    Niklas Lundberg avatar
    1 posts
    Member since:
    May 2008

    Posted 11 Jul 2008 Link to this post

    I found a way to achieve custom Excel number formatting.

    protected void MyGrid_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e) 
         StyleElement numberStyle = new StyleElement("MyNumberStyle"); 
         numberStyle.NumberFormat.Attributes.Add("ss:Format", "#,##0"); 
         e.Styles.Add(numberStyle); 


    You can find a formatting reference on http://www.ozgrid.com/Excel/CustomFormats.htm

    Niklas
  8. Mike
    Mike avatar
    338 posts
    Member since:
    Jun 2008

    Posted 09 Sep 2008 Link to this post

    How are you referencing this?

    I am using Telerik's examples and they export fine I just need to format one column to remove the decimals.
  9. Ishwarya
    Ishwarya avatar
    1 posts
    Member since:
    May 2016

    Posted 30 May in reply to Daniel Link to this post

    This works for GridBoundColumn. What about GridTemplateColumn?
  10. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 31 May Link to this post

    Hello Ishwarya,

    Since this thread is really old I'm not sure if you are asking for ExcelML or one of the other supported formats. Could you please clarify as the ExcelML format works by traversing the datasource so it won't export any value from a template column out of the box.
     
    Regards,
    Daniel
    Telerik
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017