Export to Excel - prventing long numbers from scientific notation

[DJ]

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

[Esk]

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

[DJ]

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.

[DJ]

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

DataFormatString=" {0}"

[Daniel]

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

[Niklas Lundberg]

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

[Mike]

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.

[Ishwarya]

This works for GridBoundColumn. What about GridTemplateColumn?

[Daniel]

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