Export SQL Data to Excel Using ASP.Net

Introduction

 

This article explains how to export SQL Data to Excel using ASP.NET.

 

This article will help you to show the SQL data to the user and if he wishes then he can save that data in Excel Format.

 

Let's see the procedure required to create this type of application.

 

Step 1

 

First of all, I have worked on SQL where I have created a Stored Procedure named "same_city". This SP processes a table where records of various Branch Students exist. It will fetch the students of all branches in the same city.

 

Then I create a new project in ASP.NET and provide the connection of the Students Database to this application. This can be done by right-clicking on "Add Connection" in the Server Explorer and choose "Add New Connection".

 

 

Step 2

 

After creating the connection with the Database I worked on the design page of the application and there used two buttons and a Grid View. The code of this page is as follows:

<form id="form1" runat="server">
    <div>
        <asp:Button runat="server" Text="Fetch Data" OnClick="Unnamed1_Click" />
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Get Data" />
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
            <AlternatingRowStyle BackColor="#DCDCDC" />
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#0000A9" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#000065" />
        </asp:GridView>
    </div>
</form>

The first button named "Fetch data" will be used to show the data in a Grid View and the second button will be used to download the shown data in Excel.

 

Step 3

 

Then I worked on the click of the first button, it's code is as follows:

SqlConnection x;
SqlCommand y1;
DataTable dt;
protected void Unnamed1_Click(object sender, EventArgs e) {
    x = new SqlConnection(@ "Data Source=.;Initial Catalog=test;User ID=sa;Password=password@123");
    x.Open();
    y1 = new SqlCommand();
    y1.Connection = x;
    y1.CommandText = "same_city";

    y1.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter y = new SqlDataAdapter(y1);

    dt = new DataTable();
    y.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
    x.Close();
}

Here first I created the connection with the database using SqlConnection, in the CommandText, I passed the name of Stored Procedure that is "same_city", I also provide the command type as StoredProcedure that will help to find what type of command is to be used.

 

Then the data is shown in the Grid.

 

Now if you run the application then you will be able to see the data from the SP but will not be able to download it in Excel format.

 

sql2----

 

Step 4

 

Now our next task is to allow the user to save this data in Excel format; that is the main purpose of this application.

 

So, we will work on the click of the second button. Write this code with the click of the second button:

if (GridView1.Visible) {
    Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");
    Response.ContentType = "application/excel";
    StringWriter sWriter = new StringWriter();
    HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);
    GridView1.RenderControl(hTextWriter);
    Response.Write(sWriter.ToString());
    Response.End();
}

This code will first check whether some data is available in the Grid, if it's available then it will store that data with the name "GridViewToExcel".

 

But if you run your application you will get an error like:

 

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server. 

 

This error will be removed by writing the following simple line of code:

public override void VerifyRenderingInServerForm(Control control)
{
}

Now your application is ready to be executed so you can debug it.

 

The complete code of my application is as follows:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace WebApplication44 {
    public partial class WebForm1: System.Web.UI.Page {
        protected void Page_Load(object sender, EventArgs e) {

        }
        SqlConnection x;
        SqlCommand y1;
        DataTable dt;
        protected void Unnamed1_Click(object sender, EventArgs e) {
            x = new SqlConnection(@ "Data Source=.;Initial Catalog=test;User ID=sa;Password=password@123");
            x.Open();
            y1 = new SqlCommand();
            y1.Connection = x;
            y1.CommandText = "same_city";

            y1.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter y = new SqlDataAdapter(y1);

            dt = new DataTable();
            y.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
            x.Close();
        }

        protected void Button1_Click(object sender, EventArgs e) {
            if (GridView1.Visible) {
                Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");
                Response.ContentType = "application/excel";
                StringWriter sWriter = new StringWriter();
                HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);
                GridView1.RenderControl(hTextWriter);
                Response.Write(sWriter.ToString());
                Response.End();
            }
        }
        public override void VerifyRenderingInServerForm(Control control) {}
    }
}

Output

 

On running the application you will get output like this:

 

 

When you click on "Fetch Data" then you will see the data in Grid Format.

 

 

Now if you click on the "Get Data" you will see that the data is stored in Excel format.