CODEWITHSUNDEEP
asp.netvb.net
Johnny B.
Johnny B.

Reputation: 1

Format a column from a GridView before export in excel

I'm trying to export a GridView to Excel and I have a column with a series of numbers like 1245333325364. When I run the query for the GridView I can see the complete number but when I export to excel all I see is 1.00133E+12 on that column. I know I can have the user change this in excel but not all files are being open after export they just save it straight into a directory. I will really like to change the column's format in the export process rather than having the user do it before they save the file. I'm performing the export in ASP using VB.net any help will be really appreciate.

The code I'm using to export the GridView is like so:

 Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Dim sw As New StringWriter()
    Dim hw As New HtmlTextWriter(sw)
    Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"

    GridView1.AllowPaging = False
    GridView1.DataBind()

    For i As Integer = 0 To GridView1.Rows.Count - 1
        Dim row As GridViewRow = GridView1.Rows(i)

        row.Attributes.Add("class", "textmode")

    Next
    GridView1.RenderControl(hw)

    'style to format numbers to string 

    Response.Write(style)
    Response.Output.Write(sw.ToString())
    Response.Flush()
    Response.End()

Upvotes: 0

Views: 712

Answers (1)

Jamal
Jamal

Reputation: 422

You are applying the style formatting to the row property and that does not work. You need to apply the textmode style to each cell in the row inside the loop.

Update each cell:

For Each row As GridViewRow In GridView1.Rows
        
        For Each cell As TableCell In row.Cells                
            cell.CssClass = "textmode"
        Next
Next

GridView1.RenderControl(hw)

Response.Write(style)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.End()

Note:

You may also need to change the Microsoft specific formatting if the above has any issues; just in case.

Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"

To

Dim style As String = "<style> .textmode { } </style>"

Upvotes: 0

Related Questions