How to Export Datalist Data to Excel document in Asp.net - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework 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.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+

1 comments:

It works well.


EmoticonEmoticon