Export SQL Data to Microsoft Excel (using Visual Studio 2008, c#) using asp.net

See more:

ASP.NET

I have list of tables in my database.

how to retrieve list of tables including data into databound control and

how to export the tables data to excelsheet individually..

a)Gets the list of tables within the database
b)Gets data
c)Exports the data to excel worksheets (there are 2 methods defined (1) range method (2) cell by cell
d.) Saves the excel sheet

<asp:Content ID="Content3" ContentPlaceHolderID="MainContentPanel" runat="Server">
<script language="javascript" type="text/javascript">
function selectOne(rdoId,gridName)
{
var rdo = document.getElementById(rdoId);
var all = document.getElementsByTagName("input");
for(i=0;i< all.length;i++)
{
if(all[i].type=="radio" && all[i].id != rdo.id)
{
var count=all[i].id.indexOf(gridName);
if(count!=-1)
{
all[i].checked=false;
}
}
}
rdo.checked=true;
}
</script>
    <asp:Panel ID="panelPostListings" runat="server" class="MainUserContentPanel">
    <div>
        <fieldset style="background-color: #FEF0C9">
            <legend>
                    <span class="h_txt16_2"><font color="red">Export Data To Excel Sheet</font></span>
                     </legend>
                      <div style="width: 300px; float: left; padding-right: 20px;">
                    <table>
                        <tr>
                            <td>
                                <asp:Label ID="lbldatabase" runat="server" Text="DatabaseName"></asp:Label>
                            </td>
                        </tr>
                        <tr>
                            <td>
                                <asp:TextBox ID="txtdbname" runat="server" Width="239px"></asp:TextBox>
                                Enter DataBase Name
                            </td>
                        </tr>
                        <tr>
                            <td>
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                &nbsp;<asp:Button ID="btnsend" runat="server" Text="submit" CausesValidation="false"
                                    Width="73px" onclick="btnsend_Click" />
                            </td>
                        </tr>
                    </table>
                </div>
               <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" PageSize="10" OnPageIndexChanging="GridView1_PageIndexChanging">
               <Columns>
               <asp:TemplateField>
               <ItemTemplate>
               <asp:RadioButton ID="chktable" runat="server" OnCheckedChanged="Changed"  OnClick="javascript:selectOne(this.id,'GridView1');" GroupName="radio" AutoPostBack="true" />
               </ItemTemplate>
               </asp:TemplateField>
              <asp:BoundField HeaderText="TableName" DataField="TableName" />
               </Columns>
               </asp:GridView>
            <asp:GridView ID="GridView2" runat="server" >
            <Columns>
            <asp:TemplateField>
            <ItemTemplate>
            <%--<asp:CheckBox runat="server" ID="chkcolumn" OnCheckedChanged="chkChanged" AutoPostBack="true"/>--%>
            </ItemTemplate>
            </asp:TemplateField>
            </Columns>
            </asp:GridView>
                <asp:GridView ID="GridView3" runat="server" >
                </asp:GridView>
                </fieldset>
                </div>

    </asp:Panel>
   </asp:Content>

aspx.cs:--
-----------------
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
public partial class DataEntry_Exportdata : System.Web.UI.Page
{
string userid;
DataSet ds = new DataSet();
string selectedtable;
string sqlconn = ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserID"] != null && Session["UserID"] != "")
{
userid = Convert.ToString(Session["UserID"]);

}
else
{
Response.Redirect("~/Login.aspx?ReturnUrl=" + Request.Url.PathAndQuery.Replace("&", "^"));
}
//bindgridview();
}
protected void btnsend_Click(object sender, EventArgs e)
{

bindgridview();


}
public void bindgridview()
{
SqlConnection cn = new SqlConnection(sqlconn);
//string dbname="stanzoo";
// string s = " SELECT SysObjects.[Name] as TableName,SysColumns.[Name] as ColumnName,SysTypes.[Name] As DataType,SysColumns.[Length] As Length FROM SysObjects INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id] INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype] WHERE SysObjects.[type] = 'U' ORDER BY SysObjects.[Name] ";
string s = " SELECT SysObjects.[Name] as TableName FROM SysObjects WHERE SysObjects.[type] = 'U' ORDER BY SysObjects.[Name] ";
SqlDataAdapter da = new SqlDataAdapter(s, cn);
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
bindgridview();
}
protected void Changed(object sender, EventArgs e)
{

for (int i = 0; i < GridView1.Rows.Count; i++)
{

RadioButton chkb = (RadioButton)GridView1.Rows[i].Cells[0].FindControl("chktable");

if (chkb.Checked)
{


selectedtable = GridView1.Rows[i].Cells[1].Text.ToString();
//Application["a"] = selectedtable;
SqlConnection cn = new SqlConnection(sqlconn);
//string dbname="stanzoo";
string s = "select * from " + selectedtable + " ";
//string s = " SELECT SysColumns.[Name] as ColumnName FROM SysObjects INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id] WHERE SysObjects.[type] = 'U' and SysObjects.[Name]='" + selectedtable + "'";
//string s = " SELECT SysObjects.[Name] as TableName FROM SysObjects WHERE SysObjects.[type] = 'U' ORDER BY SysObjects.[Name] ";
SqlDataAdapter da = new SqlDataAdapter(s, cn);
da.Fill(ds);
GridView2.DataSource = ds;
GridView2.DataBind();



}
}
}
protected void chkChanged(object sender, EventArgs e)
{

for (int i = 0; i < GridView2.Rows.Count; i++)
{

CheckBox chkb = (CheckBox)GridView2.Rows[i].Cells[0].FindControl("chkcolumn");

if (chkb.Checked)
{


string selectedcolumn = GridView2.Rows[i].Cells[1].Text.ToString();
string selectedtable1 = Application["a"].ToString();

SqlConnection cn = new SqlConnection(sqlconn);
//string dbname="stanzoo";
// string s = " SELECT SysColumns.[Name] as ColumnName FROM SysObjects INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id] WHERE SysObjects.[type] = 'U' and SysObjects.[Name]='" + selectedtable + "'";
//string s = " SELECT SysObjects.[Name] as TableName FROM SysObjects WHERE SysObjects.[type] = 'U' ORDER BY SysObjects.[Name] ";
string s = "select " + selectedcolumn + " from " + selectedtable1 + " ";
SqlDataAdapter da = new SqlDataAdapter(s, cn);
da.Fill(ds);
GridView3.DataSource = ds;
GridView3.DataBind();



}
}
}


}




using the above code iam getting tables and data..but how cant tehse dat convetrt into excel sheet..plz give asuggetstion