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

1 Answer 81 Views
Grid
myat
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
        radgrid.MasterTableView.ExportToExcel()
    End Sub

Here's my excel result,

1 Answer, 1 is accepted

Sort by
0
Doncho
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">
    </ExportSettings>
    <MasterTableView CommandItemDisplay="Top">
        <CommandItemSettings ShowExportToExcelButton="true" />
        <Columns>
            <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>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

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
        Next

        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 = "@"
        Next

        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,
Doncho
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 https://learn.telerik.com/.

Tags
Grid
Asked by
myat
Top achievements
Rank 1
Answers by
Doncho
Telerik team
Share this question
or