ASP.NET - Rendering Gridview to HtmlTextWriter - XLS - Column Format

Question

I'm using the following code to render a gridview to an XLS file on an ASPX page:

Private Sub ExportGridviewToExcel()
    Dim attachment As String = "attachment; filename=ItemSizeExport.xls"
    Response.ClearContent()
    Response.AddHeader("content-disposition", attachment)
    Response.ContentType = "application/ms-excel"
    Dim sw As New StringWriter()
    Dim htw As New HtmlTextWriter(sw)
    GV_Item_Data.RenderControl(htw)
    Response.Write(sw.ToString())
    Response.End()
End Sub

One of the columns is usually null, but sometimes contains a rather long integer such as 2342515123332222 .. it's a barcode type number... so no math ever has to be done on it.

Long story short, XLS makes this into a floating point and that's junk for the end-user. (~23432E18)

Is there some way for me to force this column in the XLS to be a string?

I'm not willing to generate an XLS via any other method... my "fall back" would be to write a '#' character before each non-blank blank entry in that column hoping that that forces the column to be interpreted as text rather than a number. This is not an ideal solution though, unfortunately.

Answer 1

Instead of a # character before each non-blank entry in that column, you could use an apostrophe. That will convert it to text. Another approach that might be possible is to attach a macro with column format info, but I'm not sure whether that's possible.