Thursday, 12 October 2017

make Excel using Database

protected void ConnectionXML()
    {
        string constr = ConfigurationManager.ConnectionStrings["MyconPinUp"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {


            string cmdget = " select  b.recr_id,a.email_id, a.mob1, a.user_id,b.job_id,rtrim((rtrim(a.first_name+' '+isnull(a.last_name,'')))) as name," +
                               " a.pref_locality,c.locality_name+' , '+d.city_name as locality_name," +
                               " b.subject_id,e.subject_name,a.total_exp_years,a.total_exp_months,b.applied_on " +
                               " from user_profile a,job_applied b,locality_master c, city_master d ,profile_subj_master e" +
                               " where " +
                               " a.org_id=b.org_id and a.user_id=b.user_id " +
                               " and a.org_id=c.org_id and a.pref_locality=c.locality_id" +
                               " and c.org_id=d.org_id and c.city_id=d.city_id " +
                               " and b.org_id=e.org_id and b.subject_id=e.subject_id " +
                               " and isnull(b.applied,'N')='Y'" +
                " and b.recr_id='" + hnfRecrId.Value + "'" +
                // " and a.user_id in ('U000040','U000011','U000012','U000008','U000001')";
                               " and a.user_id in (" + hdn_User_Ids.Value + ")";

            using (SqlCommand cmd = new SqlCommand(cmdget))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        using (XLWorkbook wb = new XLWorkbook())
                        {
                            wb.Worksheets.Add(dt, "Condidates");

                            Response.Clear();
                            Response.Buffer = true;
                            Response.Charset = "";
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
                            using (MemoryStream MyMemoryStream = new MemoryStream())
                            {
                                wb.SaveAs(MyMemoryStream);
                                MyMemoryStream.WriteTo(Response.OutputStream);
                                Response.Flush();
                                Response.End();
                            }
                        }
                    }
                }
            }
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.End();
        }
    }


No comments:

Post a Comment

IIS deployment support details

  Node JS - IIS deployment support details node: http://go.microsoft.com/?linkid=9784334 IISNode: https://github.com/azure/iisnode/releases/...