How to Export Datalist Data to Excel document in Asp.net

Introduction: In this post I will explain how we can Export the Datalist Data to Excel Document in Asp.net.

Description:

In the last post I have explained How to Export DatalistData to PDF file in Asp.net, How to Bind Datalist.
 Here I bind the Datalist control using Sqldatasource. See the .aspx desgin below:

<form id="form1" runat="server">

    <div>

    <asp:DataList ID="dlstudent" runat="server" DataKeyField="STUDENT_ID"

            DataSourceID="SqlDataSource1">

            <HeaderStyle Font-Bold="True" BorderColor="Black" />

            <HeaderTemplate>

             <table border="1"><tr style="background-color:Blue;color:White;">

             <td><b>Student Name</b> </td>

             <td>Student Address</td>

             <td>Student Class</td>

             </tr>

            </HeaderTemplate>

            <ItemTemplate>

            <tr style="font-style:italic;">

              <td align="center"><asp:Label ID="lblname" runat="server"

                    Text='<%# Eval("STUDENT_NAME") %>'></asp:Label></td>

                <td align="center"><asp:Label ID="lbladdress" runat="server"

                    Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label></td>

               <td align="center"><asp:Label ID="lblclass" runat="server"

                    Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label></td>                  

            </ItemTemplate>          

                       </asp:DataList>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"

            ConnectionString="<%$ ConnectionStrings:TestBlogConnectionString %>"

            SelectCommand="SELECT * FROM [STUDENT_DETAIL]"></asp:SqlDataSource>

        <br />

        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Export To Excel" />

    </div>

    </form>

Now go to .aspx.cs page and write below given code:

protected void Button1_Click(object sender, EventArgs e)

    {

        try

        {

            Response.Clear();

            Response.Buffer = true;

            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "StudentDetail.xls"));

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

            Response.Charset = "";

            this.EnableViewState = false;

            System.IO.StringWriter writer = new System.IO.StringWriter();

            System.Web.UI.HtmlTextWriter html = new System.Web.UI.HtmlTextWriter(writer);

            dlstudent.DataBind();

            dlstudent.RenderControl(html);

            Response.Write(writer.ToString());

            Response.Flush();

            Response.End();

        }

        catch (Exception ex)

        {

        }

    }

In VB (.aspx.vb)

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Try

            Response.Clear()

            Response.Buffer = True

            Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "StudentDetail.xls"))

            Response.ContentType = "application/ms-excel"

            Response.Charset = ""

            Me.EnableViewState = False

            Dim writer As New System.IO.StringWriter()

            Dim html As New System.Web.UI.HtmlTextWriter(writer)

            dlstudent.DataBind()

            dlstudent.RenderControl(html)

            Response.Write(writer.ToString())

            Response.Flush()

            Response.[End]()

        Catch ex As Exception

        End Try

    End Sub

Now build the project and check the result.

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.