How to Export Datalist Data to Excel document in Asp.net | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF snippets and tutorial

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.

Post a Comment

It works well.

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget