Please help : How to export excel leading the zero value column from grid data

1 Answer 81 Views
Top achievements
Rank 1
myat asked on 10 Feb 2022, 10:26 AM

My grid have one column it's start with 0 (As example:001120435)

Here's my grid data

Here's my excel export code,

 Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        radgrid.ExportSettings.FileName = "ListofApplicant"
        radgrid.ExportSettings.IgnorePaging = True
        radgrid.ExportSettings.ExportOnlyData = True
        radgrid.ExportSettings.OpenInNewWindow = True
        radgrid.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx
    End Sub

Here's my excel result,

1 Answer, 1 is accepted

Sort by
Telerik team
answered on 14 Feb 2022, 12:43 PM

Hi Myat,

You can use the OnInfrastructureExporting event exposed by the RadGrid and set the desired Excel format to the cells of a particular column. The approach is described and demonstrated in the following article - Set Excel export cell format.

For your convenience, I have slightly modified the sample so it fits better to the current scenario. Here is a piece of code you can test with:

<telerik:RadGrid runat="server" ID="RadGrid1" OnInfrastructureExporting="RadGrid1_InfrastructureExporting" OnNeedDataSource="RadGrid1_NeedDataSource" AutoGenerateColumns="false">
    <ExportSettings Excel-Format="Xlsx" IgnorePaging="true" ExportOnlyData="true" OpenInNewWindow="true">
    <MasterTableView CommandItemDisplay="Top">
        <CommandItemSettings ShowExportToExcelButton="true" />
            <telerik:GridBoundColumn DataField="numbers" UniqueName="numbers" HeaderText="large numbers" DataType="System.String"></telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="someField" UniqueName="someColumn" HeaderText="another column" DataType="System.Decimal"></telerik:GridBoundColumn>
            <telerik:GridDateTimeColumn DataField="someDate" UniqueName="dateColumn" HeaderText="the date"></telerik:GridDateTimeColumn>

VB code

    Protected Sub RadGrid1_InfrastructureExporting(ByVal sender As Object, ByVal e As GridInfrastructureExportingEventArgs)
        Dim colIndex As Integer = -1
        Dim cells As Telerik.Web.UI.ExportInfrastructure.CellCollection = e.ExportStructure.Tables(0).Rows(1).Cells

        For Each item As Telerik.Web.UI.ExportInfrastructure.Cell In cells

            If item.Text = "large numbers" Then
                colIndex = item.ColIndex
            End If

        If colIndex < 0 Then Return
        Dim col As Telerik.Web.UI.ExportInfrastructure.Column = e.ExportStructure.Tables(0).Columns(colIndex)

        For Each cell As Telerik.Web.UI.ExportInfrastructure.Cell In col.Cells
            cell.Format = "@"

        col.Width = 200
    End Sub

    Protected Sub RadGrid1_NeedDataSource(ByVal sender As Object, ByVal e As GridNeedDataSourceEventArgs)
        RadGrid1.DataSource = GetDummyData()
    End Sub

    Protected Function GetDummyData() As DataTable
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("numbers", GetType(String))
        dt.Columns.Add("someField", GetType(Integer))
        dt.Columns.Add("someDate", GetType(DateTime))
        dt.Rows.Add("000000000001", 2, New DateTime(2011, 6, 12))
        dt.Rows.Add("0000032", 3, New DateTime(2011, 12, 12))
        dt.Rows.Add("0000000003", 6, New DateTime(2012, 6, 17))
        dt.Rows.Add("00000004", 4, New DateTime(2012, 9, 18))
        dt.Rows.Add("00000000000005", 7, New DateTime(2013, 3, 18))
        Return dt
    End Function
I hope you will find this helpful.

Please let me know if any questions come up.

Kind regards,
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at

Asked by
Top achievements
Rank 1
Answers by
Telerik team
Share this question