This is a migrated thread and some comments may be shown as answers.

Export to Excel: Force Text Cell Format

3 Answers 430 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Wai Wai Chan
Top achievements
Rank 1
Wai Wai Chan asked on 02 Sep 2010, 05:12 AM
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 StringByVal DataField As StringByVal 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

3 Answers, 1 is accepted

Sort by
0
Accepted
Pavlina
Telerik team
answered on 02 Sep 2010, 08:27 AM
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
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
0
Wai Wai Chan
Top achievements
Rank 1
answered on 02 Sep 2010, 08:37 AM
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
0
Wai Wai Chan
Top achievements
Rank 1
answered on 02 Sep 2010, 08:51 AM
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
Tags
Grid
Asked by
Wai Wai Chan
Top achievements
Rank 1
Answers by
Pavlina
Telerik team
Wai Wai Chan
Top achievements
Rank 1
Share this question
or