Export gridview to excel number formatting

Question

User1521061112 posted

I have a problem exporting from a gridview to Excel. Code follows:

Partial Class secure_fertall
    Inherits System.Web.UI.Page


    Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim style As String = "<style> td { mso-number-format:\@; } </style> "
        Response.AddHeader("content-disposition", "attachment;filename=fertilizer.xls")
        Response.Charset = String.Empty
        Response.ContentType = "application/vnd.xls"
        Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
        Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
        GridView1.RenderControl(hw)
        Response.Write(sw.ToString())
        Response.End()
    End Sub


    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

    End Sub
End Class

Gridview code is:

    <asp:GridView ID="GridView1" runat="server" AllowPaging="False" 
        AllowSorting="False" AutoGenerateColumns="False" DataKeyNames="lngLicNum" 
        DataSourceID="SQLDataSource1">
        <Columns>
            <asp:BoundField DataField="lngLicNum" HeaderText="License Number" ReadOnly="True" 
                SortExpression="lngLicNum" />
            <asp:BoundField DataField="txtCoName" HeaderText="Company" 
                SortExpression="txtCoName" />
            <asp:BoundField DataField="txtMailAddress" HeaderText="Address" 
                SortExpression="txtMailAddress" />
            <asp:BoundField DataField="txtMailCity" HeaderText="City" 
                SortExpression="txtMailCity" />
            <asp:BoundField DataField="txtMailState" HeaderText="State" 
                SortExpression="txtMailState" />
            <asp:BoundField DataField="txtMailZip" HeaderText="Zip" 
                SortExpression="txtMailZip" />
            <asp:BoundField DataField="dtExpiration" HeaderText="Expiration Date" 
                SortExpression="dtExpiration" />
            <asp:BoundField DataField="txtBrand" HeaderText="Brand Name"
            SortExpression="txtBrand" />
            <asp:BoundField DataField="NPK" HeaderText="NPK Values"
            SortExpression="NPK" />
            <asp:BoundField DataField="ProdExp" HeaderText="Product Expiration"
            SortExpression="ProdExp" />
        </Columns>
    </asp:GridView>

The specific field I have an issue with on the gridview is the 'NPK Values'. These are 3 numbers separated by a hyphen, like 1-2-3 or perhaps 2.3-5.5-7, or even 8-16-12 (which is intertreted as a date.

To go the Excel sheet after import and format cells is impractical, so is there a way to get the literal values from the gridview and place them into excel without excel changing the format?

Answer 1

User1508394307 posted

There is a trick that might help

GridView1.RenderControl(hw)

Response.Write("<style>.text { mso-number-format:\@; } </style>")

Response.Write(sw.ToString())

http://forums.asp.net/t/876377.aspx

Hope this helps.

Answer 2

User1521061112 posted

Tried this..no difference

Answer 3

User1508394307 posted

Hm, I see now in your original post that you have

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

but it seems you never use it? Is this a problem maybe?

Answer 4

User1521061112 posted

Can you clarify? Not sure how to use it...that's my question..

Answer 5

User1508394307 posted

You have declared 

Dim style As String = ...

but you don't use it anywhere in the code.

Also you said, that you've tried styles. Was it about your code or you did it as per my example with Response.Write?

Answer 6

User1521061112 posted

Tried your code in the Response.Write. Did not work..

I confess to being rather new to this, so how do I utilize the dim statement in my circumstance?

Answer 7

User1521061112 posted

Partial Class secure_fertall
    Inherits System.Web.UI.Page


    Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Response.Clear()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment;filename=fertilizer.xls")
        Response.Charset = String.Empty
        Response.ContentType = "application/vnd.ms-excel"
        Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
        Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
        GridView1.AllowPaging = False
        GridView1.DataBind()
        For i As Integer = 0 To GridView1.Rows.Count - 1
            Dim row As GridViewRow = GridView1.Rows(i)
        Next
        GridView1.RenderControl(hw)
        Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"
        Response.Write(style)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.End()
        'Response.Write("<style>.text { mso-number-format:\@; } </style>")
        'Response.Write(sw.ToString())
        'Response.End()
    End Sub


    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

    End Sub
End Class

Could this be from the database side? The values from the field in question is nvarchar, so I do not immediately see why this could be the culprit.