Format and export telephone number (stored in DB as string) from RadGrid to excel.

[LamKhoa]

Hi,

I have a column field in SQL Server database which is a string file, which contain phone number (11 digits, for example: 12223334444). In RadGrid, I used following code to format the number to display in RadGrid as a proper telephone number:

 

protected void RadGridResult_ItemDataBound(object sender, GridItemEventArgs e)

{

    //Format string to phone number.

    if (e.Item is GridDataItem)

     {

 

         GridDataItem item = (GridDataItem) e.Item;

        Object ob = item["ConfirmationLogPhone"].Text;

        if (!Convert.IsDBNull(ob))

         {

 

                Int64 iParsedValue = 0;

                if (Int64.TryParse(ob.ToString(), out iParsedValue))

                 {

                        TableCell cell = item["ConfirmationLogPhone"];

                         cell.Text =

                String.Format(System.Globalization.CultureInfo.CurrentCulture, "{0:#-(###) ###-####}", new object[] { iParsedValue });

                 }

         }

     }

}

 

However, when I export the grid to excel, the number is display merely just a string (Ex: 12223334444). MS Excel even report error that "Number stored as text field" on every cell in the column.

Would you please tell me how to format and export string of telephone number to excel properly?

Thanks

LamK

[Princy]

Hello Lamkhoa,

I cannot exactly reproduce the issue at my end. Here is the code that I tried which worked as expected in my end.

aspx:

<telerik:RadGrid ID="RadGrid1" runat="server" DataSourceID="SqlDataSource1" >
<ExportSettings Excel-Format="Html" ExportOnlyData="true" IgnorePaging="true">
</ExportSettings>
 <MasterTableView  CommandItemDisplay="Top">
   <Columns>
       <telerik:GridBoundColumn UniqueName="Extension" HeaderText="Extension"  DataField="Extension" DataFormatString="{0:#-###}">
       </telerik:GridBoundColumn>
   </Columns>
 </MasterTableView>
</telerik:RadGrid>

If you are using "ExcelML" format, check the following help documentation which explains setting number format in desired type.
ExcelML basics.

Thanks,
Princy.

[LamKhoa]

It's ExcelML format like you said. I tried the method that you provided, but it didn't work, the phone number still display merely a string on Excel file. The link ExcelML Basic isn't work.
Thanks

LamK

[Daniel]

Hello LamK,

There is an empty space character in the URL of the previous link. Please try this one:
ExcelML basics

Best regards,
Daniel
the Telerik team

[LamKhoa]

I tried the suggestion in the link:

 

Phone.NumberFormat.Attributes["ss:Format"] = "$-$$$-$$$-$$$$";
and
Phone.NumberFormat.Attributes["ss:Format"] = "#-###-###-####";
and
Phone.NumberFormat.Attributes["ss:Format"] = "0-000-0000-0000";

None of them work.

Do you have any idea?

Thanks
LamK

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[Daniel]

Hello LamK,

Please test the attached demo and let me know if it works for you.

Regards,
Daniel
the Telerik team