Monday, 17 March 2014

Bind and Export GridView data to CSV file in asp.net(C#, VB.Net)

Bind and Export GridView data to CSV file in asp.net(C#, VB.Net)

Introduction: In previous articles i explained How to bind and Export GridView data to Ms Word file and How to bind and Export GridView data to Ms Excel file and Bind and Export GridView data to PDF file in asp.net and Fill CheckBoxList based on DropDownList selection  and How to show Validation guidelines in web forms using JavaScript and Example to implement Jquery form validations and jQuery to Validate email address using RegularExpression and Convert Rupees,Currency or Numbers to Words.
 In this article I will explain how easily you can bind GridView with data from database and Export that Gridview data to CSV (Comma Separated Value) file using asp.net with C# and VB.Net.
 
bind and export gridview data to CSV file sing asp.net
Click on image to enlarge
    bind and export gridview data to CSV file sing asp.net
    click on image to enlarge
  • Create a Database e.g. "MyDataBase" and a table under that DataBase in Sql Server and name it "EMPLOYEE" as shown in figure:
Note: EMP_ID column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you  want to show in the Gridview.
  • Now in web.config file add the connection string under <configuration> tag 


<connectionStrings>
    <add name="conStr" connectionString="Data Source=LocalServer;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings> 

Note: Replace the Data Source and Initial Catalog as per your application.
Implementation: Let’s create an asp.net web application to understand the concept.
  • In the design page (.aspx) drag and place a GridView control from visual studio toolbox to bind data and a Button control to Export the GridView data to CSV file.
Source Code:

<fieldset style="width:360px;">
            <legend>Bind and Export GridView data to CSV in asp.net</legend>
            <table>
                <tr>
                    <td>
                        <asp:GridView ID="grEmp" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                   GridLines="None" Width="100%" CellPadding="4" ForeColor="#333333">
                   
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />                   
                    <Columns>
                        <asp:BoundField DataField="EMP_NAME" HeaderText="Emp Name"  />
                        <asp:BoundField DataField="DEPT" HeaderText="Department"  />
                        <asp:BoundField DataField="SALARY" HeaderText="salary"  />
                        <asp:BoundField DataField="EMAIL_ID" HeaderText="Email Id" />
                    </Columns>                  
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>
                    </td>               
                </tr>
                <tr>
                    <td>
<asp:Button ID="btnExportToCSV" runat="server" Text="Export To CSV File" OnClick="btnExportToCSV_Click" /> 
                    </td>
                </tr>
            </table>
        </fieldset>
C#.Net Code to Bind and Export GridView data to CSV file
First include the following namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Net;
using System.Net.Mail;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Text;
Then write the code as:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindEmpGrid();
        }
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
    }
    protected void BindEmpGrid()
    {
        SqlCommand cmd = new SqlCommand("select * from EMPLOYEE", con);
        DataTable dt = new DataTable();
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(dt);
        grEmp.DataSource = dt;
        grEmp.DataBind();
    }
protected void btnExportToCSV_Click(object sender, EventArgs e)
    {
        try
        {
            Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment;filename=MyCsvFile.csv");
            Response.ContentType = "application/text";
            StringBuilder strBr = new StringBuilder();
            for (int i = 0; i < grEmp.Columns.Count; i++)
            {
                strBr.Append(grEmp.Columns[i].HeaderText + ',');
            }
            strBr.Append("\n");
            for (int j = 0; j < grEmp.Rows.Count; j++)
            {
                for (int k = 0; k < grEmp.Columns.Count; k++)
                {
                    strBr.Append(grEmp.Rows[j].Cells[k].Text + ',');
                }
                strBr.Append("\n");
            }
            Response.Write(strBr.ToString());
            Response.Flush();
            Response.End();
        }
        catch (Exception ex) { }
    }
VB.Net Code to Bind and Export GridView data to CSV file
First include the following namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Net
Imports System.Net.Mail
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Text
Then write the code as:

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindEmpGrid()
        End If
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        'It solves the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."
    End Sub
    Protected Sub BindEmpGrid()
        Dim cmd As New SqlCommand("select * from EMPLOYEE", con)
        Dim dt As New DataTable()
        Dim adp As New SqlDataAdapter(cmd)
        adp.Fill(dt)
        grEmp.DataSource = dt
        grEmp.DataBind()
    End Sub
Protected Sub btnExportToCSV_Click(sender As Object, e As EventArgs)
        Try
            Response.ClearContent()
            Response.AddHeader("content-disposition", "attachment;filename=MyCsvFile.csv")
            Response.ContentType = "application/text"
            Dim strBr As New StringBuilder()
            For i As Integer = 0 To grEmp.Columns.Count - 1
                strBr.Append(grEmp.Columns(i).HeaderText + ","c)
            Next
            strBr.Append(vbLf)
            For j As Integer = 0 To grEmp.Rows.Count - 1
                For k As Integer = 0 To grEmp.Columns.Count - 1
                    strBr.Append(grEmp.Rows(j).Cells(k).Text + ","c)
                Next
                strBr.Append(vbLf)
            Next
            Response.Write(strBr.ToString())
            Response.Flush()
            Response.[End]()
        Catch ex As Exception
        End Try
    End Sub
  • Notice that I have added an overriding function VerifyRenderingInServerForm in the code behind. This is to resolve the error “Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server” that may occur on exporting GridView data to MS Excel file or MS Word or PDF(Portable Document Format) or CSV (Comma Separated Value) file.
Note: To view complete article on why this error occur and how to resolve that error, read my article “How to Solve Error Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server

Exported gridview data to CSV file will look like as shown in figure:
bind and export gridview data to CSV file sing asp.net
click on image to enlarge
Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

No comments:

Post a Comment