Results 1 to 3 of 3

Thread: Export to Excel - Keep leading zeros in numeric fields

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    1

    Export to Excel - Keep leading zeros in numeric fields

    I have been stumped for days. I have googled and googled and googled and read lots of different things, but none seemed to work or apply for me.

    In short, I have Gridviews (asp.net) that users can export to excel on a button click. Problem is, any numeric field that starts with 0 . . the lead 0 gets dropped. I understand that the issue is that Excel is reading this as a numeric and automatically drops the zeros. My export gridview to excel is pretty basic. And I have tried mso-number-format:\@ in many different ways and still did not get the expected results. Perhaps the answer is right there and I am overthinking it.

    My VB.net code for export ( I am actually exporting 3 grid views . . one is a header, one is the actual data and one is a count and cost. . they are being built into the table for export)



    Response.Clear()

    Response.Buffer = True

    Dim sw As New StringWriter()

    Dim hw As New HtmlTextWriter(sw)

    Dim frm As HtmlForm = New HtmlForm()



    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")

    Response.Charset = ""

    Response.ContentType = "application/vnd.ms-excel"


    Dim tb As New Table()

    Dim tr1 As New TableRow()

    Dim cell1 As New TableCell()

    cell1.Controls.Add(GridView6)
    tr1.Attributes.Add("class", "text")
    tr1.Cells.Add(cell1)

    cell1.Attributes.Add("class", "text")

    Dim cell3 As New TableCell()

    cell3.Attributes.Add("class", "text")

    Dim tr2 As New TableRow()

    tr2.Cells.Add(cell2)

    Dim cell4 As New TableCell
    cell4.Attributes.Add("class", "text")
    cell4.Text = " "

    Dim tr4 As New TableRow
    tr4.Cells.Add(cell4)

    Dim tr3 As New TableRow()

    tr3.Cells.Add(cell3)
    Dim tr7 As New TableRow
    tr7.Cells.Add(cell7)

    tb.Attributes.Add("class", "text")

    tb.Rows.Add(tr1)

    tb.Rows.Add(tr2)

    tb.Rows.Add(tr3)

    tb.Rows.Add(tr4)

    tb.Rows.Add(tr7)

    tb.RenderControl(hw)


    Response.Output.Write(sw.ToString())

    Response.Flush()

    Response.End()

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Export to Excel - Keep leading zeros in numeric fields

    Welcome to VBForums

    Thread moved from the 'CodeBank VB.Net' forum (which is for you to post working code examples, not questions) to the 'ASP.Net' forum

  3. #3
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: Export to Excel - Keep leading zeros in numeric fields

    Hello,

    A workaround that we did is to check if the cell Text property value starts with a zero. If true, insert an   before that value.

    - kgc
    Last edited by KGComputers; Jun 27th, 2017 at 10:20 AM.
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width