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

  • Question

  • User-209135589 posted

    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();
        //        }
        //    }
        //}
       
        
     }

    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


    For this iam created the aspxpage


    <%@ Page Language="C#" AutoEventWireup="true" MasterPageFile="~/DataEntry/UserMaster.master"

        CodeFile="Exportdata.aspx.cs" Inherits="DataEntry_Exportdata" %>


    <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">

    </asp:Content>

    <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>

                    

                </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 + " ";

                    da.Fill(ds);

                    GridView3.DataSource = ds;

                    GridView3.DataBind();




              }

           }

       }

    }

    But iam facing a problem when i want to display the tables i mean gridview 1 in pageload..i write the bindgridview() method in pageload..then the radiobutton cheked the method checked is not firing..plz help me to solve the problem...

    and how to export these data to excel sheet...



    Friday, November 26, 2010 1:28 AM

Answers