locked
Export gridview to excel number formatting RRS feed

  • 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?

    Thursday, March 7, 2013 12:37 PM

All replies

  • 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.

    Thursday, March 7, 2013 3:15 PM
  • User1521061112 posted

    Tried this..no difference

    Thursday, March 7, 2013 3:43 PM
  • 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?

    Thursday, March 7, 2013 3:50 PM
  • User1521061112 posted

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

    Thursday, March 7, 2013 3:52 PM
  • 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?

    Thursday, March 7, 2013 5:09 PM
  • 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?

    Friday, March 8, 2013 8:43 AM
  • 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.

    Friday, March 8, 2013 9:01 AM