Format a column from a GridView before export in excel

Question

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()

Start using a specialized library for creating Excel files, like [EPPlus](https://github.com/JanKallman/EPPlus). [Example here](https://stackoverflow.com/a/47293207/5836671) and [here](https://stackoverflow.com/questions/52002573/send-excel-email-attachment-c-sharp). All you are doing now is creating a HTML page with an .xls extension. — VDWWD, Aug 22 '20 at 18:18
The code working and export completed as mentioned above but the issue with formatting only, the links you provided is C# and i am working in VB.NET — Johnny B., Aug 22 '20 at 18:35
The code is not working in the sense that when using this method you lose the formatting of dates, numbers etc. As you found out... Also users get a warning message when opening these type of file. VB or C# does not really matter, but if you cannot convert yourself, there are tools https://codeconverter.icsharpcode.net/ — VDWWD, Aug 22 '20 at 19:31

Jamal · Answer 1 · 2020-08-22T20:19:01.100

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>"

Format a column from a GridView before export in excel

1 Answers1