How to Export Data in Csv using StringBuilder in Asp.Net

Description:-

In this Example we will See How to Create Csv File from Gridview or else database. For Export Data in Csv file from Gridview we will use StringBuilder Controls to Save in Csv file we will use Attachment in Code behind. So let’s start to Create Gridview Data from Database and Export in Csv file through Code behind in dot net.

Step 1: Create you Table in Sql table and Name it “Employee”. And Fill Data in Table

CREATE TABLE [dbo].[Employee](

      [Number] [int] IDENTITY(1,1) NOT NULL,

      [Name] [varchar](50) NOT NULL,

      [Gender] [varchar](50) NOT NULL,

      [Email] [varchar](50) NOT NULL,

      [MobileNumber] [bigint] NOT NULL,

      [Bdate] [date] NULL,

 CONSTRAINT [PK_Employeee] PRIMARY KEY CLUSTERED

(

      [Number] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Step 2: Now Design you Webpage like below.

<div>
   <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">
      <AlternatingRowStyle BackColor="White" />
      <Columns>
         <asp:TemplateField HeaderText="Employee Name">
            <ItemTemplate>
               <asp:Label ID="lblstateid" runat="server" Text='<%#Eval("Name") %>'></asp:Label>
            </ItemTemplate>
         </asp:TemplateField>
         <asp:TemplateField HeaderText="Gender">
            <ItemTemplate>
               <asp:Label ID="lblstatename" runat="server" Text='<%#Eval("Gender") %>'></asp:Label>
            </ItemTemplate>
         </asp:TemplateField>
         <asp:TemplateField HeaderText="Email-ID">
            <ItemTemplate>
               <asp:Label ID="lblcountryid" runat="server" Text='<%#Eval("Email") %>'></asp:Label>
            </ItemTemplate>
         </asp:TemplateField>
         <asp:TemplateField HeaderText="MobileNumber">
            <ItemTemplate>
               <asp:Label ID="lblcountryid" runat="server" Text='<%#Eval("MobileNumber") %>'></asp:Label>
            </ItemTemplate>
         </asp:TemplateField>
         <asp:TemplateField HeaderText="Bdate">
            <ItemTemplate>
               <asp:Label ID="lblcountryid" runat="server" Text='<%#Eval("Bdate") %>'></asp:Label>
            </ItemTemplate>
         </asp:TemplateField>
      </Columns>
      <FooterStyle BackColor="#CCCC99" />
      <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
      <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
      <RowStyle BackColor="#F7F7DE" />
      <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
      <SortedAscendingCellStyle BackColor="#FBFBF2" />
      <SortedAscendingHeaderStyle BackColor="#848384" />
      <SortedDescendingCellStyle BackColor="#EAEAD3" />
      <SortedDescendingHeaderStyle BackColor="#575357" />
   </asp:GridView>
</div>
<div>
   <asp:Button ID="btntoCsv" runat="server" Text="EXPORT" OnClick="btntoCsv_Click" Width="248px" />
</div>

Step 3: Now go to Code behind and we will code for Bind Data in Gridview.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        binddata();
    }
}

public void binddata()
{
    SqlConnection con = new SqlConnection(CS);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "Select * from Employeee";
    cmd.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    if (dt.Rows.Count > 0)
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

Now on Button click event Code for Creating Csv File and For selecting Data from Gridview or else Database. 

protected void btntoCsv_Click(object sender, EventArgs e)
{
    // binddata();
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=gvtocsv.csv");
    Response.Charset = "";
    Response.ContentType = "application/text";
    StringBuilder sBuilder = new System.Text.StringBuilder();
    for (int index = 0; index < GridView1.Columns.Count; index++)
    {
        sBuilder.Append(GridView1.Columns[index].HeaderText + ',');
    }
    sBuilder.Append("\r\n");
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
        {
            sBuilder.Append(GridView1.Rows[i].Cells[k].Text.Replace(",", "") + ",");
        }
        sBuilder.Append("\r\n");
    }
    Response.Output.Write(sBuilder.ToString());
    Response.Flush();
    Response.End();
}

Now run your Code in web page and see output.

Author : Umesh Patel

Related Posts