Export data from SQL Server to Excel in asp.net

This sample introduces how to export data from SQL Server to an Excel spreadsheet and in the next article we shall see how to generate an Excel spreadsheet with data from SQL Server.

Default page retrieves data from SQL Server using a DataTable and then exports the DataTable to an Excel 2003/2007 spreadsheet on server disk.

In the previous post we have seen how to import data from excel to sql server. Please refer the table used in the previous post for this sample. First, create a procedure to select books from the database. Then create a function in the code-behind page to return Books in a DataTable. Use StringWriter and HtmlTextWriter to iterate through the data in the DataTable. Create an object for DataGrid and Bind the DataTable to it.

Stored procedure to get Books

CREATE PROCEDURE [dbo].[GetBooks]
AS
BEGIN
SELECT [ID]
      ,[Name]
      ,[Author]
  FROM [Books]
END
GO

Default.aspx

<div>
<asp:GridView ID="gvBooks" runat="server" AutoGenerateColumns="False"
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID" />
            <asp:BoundField DataField="Name" HeaderText="Book Name" />
           <asp:BoundField DataField="Author" HeaderText="Author" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="btnExport" runat="server" Text="Export"
        onclick="btnExport_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</div>

Default.aspx.cs

using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


// SQL Server Connection String
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        gvBooks.DataSource = GetBooks();
        gvBooks.DataBind();
    }

protected void btnExport_Click(object sender, EventArgs e)
    {
        ExportToExcel(GetBooks(), "DownloadBooksToExcel.xls");
    }

public void ExportToExcel(DataTable dt, string fileName)
    {
        if (dt.Rows.Count > 0)
        {
            StringWriter sw = new StringWriter();
            HtmlTextWriter htmlWriter = new HtmlTextWriter(sw);
            DataGrid dg = new DataGrid();
            dg.DataSource = dt;
            dg.DataBind();

            dg.RenderControl( htmlWriter);
            Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
            this.EnableViewState = false;
            Response.Write(sw.ToString());
            Response.End();
        }
    }
    public DataTable GetBooks()
    {
        DataTable dtBooks = new DataTable();
        SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
        adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            adpBooks.Fill(dtBooks);
            return dtBooks;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }