Remove From My Forums

How to export string data with left zeros from gridview to excel -..... Help .... please

Question
0

Sign in to vote

User512979433 posted

Hello and thank you for any idea about subject .

I have the next code to export gridview to excel :

Response.Clear()

Response.AddHeader("Content-Disposition", "attachment;filename=Relacion_Pedidos.xls")

Response.ContentType = "application/vnd.ms-excel"

Dim sw As StringWriter = New StringWriter()

Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)

GridViewExcel.RenderControl(htw)

Response.Write(sw.ToString())

Response.End()

But one field have data like 000041 and in the excel file show this data : 41 wihtout left zeros...

Please help... thank you.

Wednesday, September 26, 2018 8:55 PM

Answers

User839733648 posted

Hi Hector Florez M,

You could keep the zero's by making small modification in the code.

I suggest that you may achieve this by adding style to the column.

I've made a sample on my side, and for more details, you could refer to the code below. (Please pay attention to the code I‘ve marked)

.aspx

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowDataBound="GridView1_RowDataBound">
                <Columns>
                    <asp:BoundField DataField="Eid" HeaderText="Eid" SortExpression="Eid" />
                    <asp:BoundField DataField="Ename" HeaderText="Ename" SortExpression="Ename" />
                    <asp:BoundField DataField="age" HeaderText="age" SortExpression="age" />
                    <asp:BoundField DataField="sex" HeaderText="sex" SortExpression="sex" />
                </Columns>
            </asp:GridView>
            <br />
            <asp:Button ID="Button1" runat="server" Text="Export to Excel" OnClick="Button1_Click" />
        </div>
    </form>
</body>
</html>

code behind.

Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration
Imports System.IO
		Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
			If Not Me.IsPostBack Then
				Bindgrid()
			End If
		End Sub

		Private Sub Bindgrid()
			Dim constr As String = ConfigurationManager.ConnectionStrings("EmployeeManagementConnectionString").ConnectionString
			Using con As New SqlConnection(constr)
				Using cmd As New SqlCommand("SELECT * FROM tb_info", con)
					Using sda As New SqlDataAdapter(cmd)
						cmd.CommandType = CommandType.Text
						Dim dt As New DataTable()
						sda.Fill(dt)
						GridView1.DataSource = dt
						GridView1.DataBind()
					End Using
				End Using
			End Using
		End Sub


		Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
			ExportGridToExcel()
		End Sub

		Private Sub ExportGridToExcel()
			Dim style As String = "<style> .text { mso-number-format:\@; } </script> "
			Response.Clear()
			Response.AddHeader("Content-Disposition", "attachment;filename=Relacion_Pedidos.xls")
			Response.ContentType = "application/vnd.ms-excel"
			Dim sw As New StringWriter()
			Dim htw As New HtmlTextWriter(sw)
			GridView1.RenderControl(htw)
			'Style is added dynamically
			Response.Write(style)
			Response.Write(sw.ToString())
			Response.End()

		End Sub

		Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
			'required to avoid the runtime error "  
			'Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."  
		End Sub

		Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
			If e.Row.RowType = DataControlRowType.DataRow Then
				e.Row.Cells(1).Attributes.Add("class", "text")
			End If
		End Sub

result:

Best Regards,

Jenifer

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Thursday, September 27, 2018 7:08 AM

All replies

0

Sign in to vote

User-1716253493 posted

Add single quote in the front of the number

Thursday, September 27, 2018 12:47 AM