Pages

Subscribe:

Sunday, 26 January 2014

export excel from sql database

Code :

 protected void Page_Load(object sender, EventArgs e)
    {
        ExportDataToExcel();
    }

 public void ExportDataToExcel()
    {
        // DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        Conn.Open();
        string query = "select row_number() over (order by entry_date desc)as [Sr no.],Name,designation as Designation,company as [Company Name],country as Country,email as Email,CONVERT(varchar(30),DATEADD(MI,30,dateadd(hh,4.30, entry_date))) as 'Add Date'  from tblName order by entry_date desc";
        SqlCommand cmd = new SqlCommand(query, Conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        Conn.Close();
        ExportToExcel(dt);
    }

    public void ExportToExcel(DataTable dt)
    {
        if (dt.Rows.Count > 0)
        {
            string filename = "Report.xls";
            System.IO.StringWriter tw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

            GridView dgGrid = new GridView();
            dgGrid.HeaderStyle.BackColor = System.Drawing.Color.Navy;
            dgGrid.HeaderStyle.ForeColor = System.Drawing.Color.White;
            dgGrid.BackColor = System.Drawing.Color.White;
            dgGrid.RowStyle.Font.Name = "Arial";
            dgGrid.RowStyle.Font.Size = 9;
            dgGrid.AlternatingRowStyle.BackColor = System.Drawing.Color.White;
            dgGrid.DataSource = dt;
            dgGrid.DataBind();

            HttpResponse response = HttpContext.Current.Response;
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            System.Web.HttpContext.Current.Response.Charset = "utf-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(1252);
            response.Clear();
            response.Charset = "";

            dgGrid.RenderControl(hw);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
            this.EnableViewState = false;
            Response.Write(tw.ToString());
            Response.End();
        }
    }

Design :

<div></div>

0 comments:

Post a Comment