Export to Excel: Force Text Cell Format

[Wai Wai Chan]

Hi There,

When I was trying to export an "number-like" text column, Excel (HTML) automatically treat it as a number.
For example: column "UserID" with data "000579" will be truncated to "579" in Excel.

Below are my functions. Is that any way to handle the cell format (force it to be TEXT) programmatically when it went to Excel?

' Add column to RadGrid1 Programmatically
Private Sub AddNewColumnToRadGrid(ByVal UniqueName As String, ByVal HeaderText As String,  ByVal DataField As String,  ByVal SortExpression As String)
        Dim boundColumn As New GridBoundColumn()
        RadGrid1.MasterTableView.Columns.Add(boundColumn)
        boundColumn.UniqueName = UniqueName
        boundColumn.HeaderText = HeaderText
        If DataField.Length > 0 Then boundColumn.DataField = DataField
        If SortExpression.Length > 0 Then boundColumn.SortExpression = SortExpression
End Sub

' Simple Export to Excel Function
Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
        RadGrid1.ExportSettings.FileName = "Report"
        RadGrid1.ExportSettings.ExportOnlyData = True
        RadGrid1.ExportSettings.IgnorePaging = True
        RadGrid1.ExportSettings.OpenInNewWindow = True
        RadGrid1.MasterTableView.ExportToExcel()
End Sub

Thanks in advance. :)

Regards,
waiwai

[Pavlina]

Hello Wai Wai Chan,

To achieve the desired functionality you need to set the format on ExcelExportCellFormatting event manually. Please refer to the following help article for more information.
Word/Excel export (HTML-based)

Best wishes,
Pavlina
the Telerik team

[Wai Wai Chan]

Hi Pavlina,

Thanks for your concern about this.

In fact I tried in ExcelExportCellFormatting event before, but it seems like more one setting particular cell's styling (background-color, border style, etc)

But the problem I am facing now is Excel treat my String input ("000579") as an integer (579) to be displayed.

Thanks,
waiwai

[Wai Wai Chan]

Hi Pavlina,

Found the solution from the link given, it does convert number to text.

Protected Sub NominationSummaryRadGrid_ExcelExportCellFormatting(ByVal source As Object, ByVal e As Telerik.Web.UI.ExcelExportCellFormattingEventArgs) Handles NominationSummaryRadGrid.ExcelExportCellFormatting
        If e.FormattedColumn.UniqueName = "UserID" Then
            e.Cell.Style("mso-number-format") = "\@" 'Text
        End If
End Sub

Thanks and good day,
waiwai