public void ExcelExportMissedFups() { try { Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=FollowupsMissed (" + DateTime.Today.ToString("dd-MM-yyyy") + ").xls"); Response.Charset = ""; Response.ContentType = "application/vnd.xls"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); GridView2MissedFups.AllowPaging = false; MissedFupsExcelExportMessage(); GridView2MissedFups.CaptionAlign = TableCaptionAlign.Top; ExportdataMissedFups(); for (int i = 0; i < GridView2MissedFups.HeaderRow.Cells.Count; i++) { GridView2MissedFups.HeaderRow.Cells[i].Style.Add("background-color", "#e17055"); GridView2MissedFups.HeaderRow.Cells[i].Style.Add("Color", "white"); GridView2MissedFups.HeaderRow.Cells[i].Style.Add("font-size", "15px"); GridView2MissedFups.HeaderRow.Cells[i].Style.Add("height", "30px"); } GridView2MissedFups.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); } catch (Exception ex) { Response.Write("<script type=\"text/javascript\">alert(" + ex.Message + ")</script>"); } }
private void ExportdataMissedFups() { MySqlConnection con = new MySqlConnection(constr); try { con.Open(); MySqlCommand cmd; if (ddMissedFups.SelectedValue == "NB_Missed_Ex_Sunday") { cmd = new MySqlCommand("select a.*,Datediff(str_to_date(end_date, '%d-%m-%Y'),str_to_date(date, '%d-%m-%Y')) as day_diff from view_followups4a as a where a.status='3' and (str_to_date(a.end_date, '%d-%m-%Y') < CURDATE()) and not exists (select z.id from view_followups4a as z where z.status='3' and (str_to_date(z.end_date, '%d-%m-%Y') < CURDATE()) and Datediff(str_to_date(z.end_date, '%d-%m-%Y'),str_to_date(z.date, '%d-%m-%Y')) ='0' and z.Day='Sunday' and z.id=a.id) order by a.study_code,str_to_date(a.date, '%d-%m-%Y')", con); MySqlDataAdapter sda = new MySqlDataAdapter(); { cmd.Connection = con; sda.SelectCommand = cmd; DataTable dt = new DataTable(); { sda.Fill(dt); GridView2MissedFups.DataSource = dt; GridView2MissedFups.DataBind(); con.Close(); } } } else if (ddMissedFups.SelectedValue == "NB_Missed_Inc_Sunday") { cmd = new MySqlCommand("select a.*,Datediff(str_to_date(end_date, '%d-%m-%Y'),str_to_date(date, '%d-%m-%Y')) as day_diff from view_followups4a as a where a.status='3' and (str_to_date(a.end_date, '%d-%m-%Y') < CURDATE()) order by a.study_code,str_to_date(a.date, '%d-%m-%Y')", con); MySqlDataAdapter sda = new MySqlDataAdapter(); { cmd.Connection = con; sda.SelectCommand = cmd; DataTable dt = new DataTable(); { sda.Fill(dt); GridView2MissedFups.DataSource = dt; GridView2MissedFups.DataBind(); con.Close(); } } } else if (ddMissedFups.SelectedValue == "FFQ_Missed") { cmd = new MySqlCommand("select *,Datediff(str_to_date(end_date, '%d-%m-%Y'),str_to_date(date, '%d-%m-%Y')) as day_diff from view_followups5b where status='3' and (str_to_date(end_date, '%d-%m-%Y') < CURDATE()) order by study_code,str_to_date(date, '%d-%m-%Y')", con); MySqlDataAdapter sda = new MySqlDataAdapter(); { cmd.Connection = con; sda.SelectCommand = cmd; DataTable dt = new DataTable(); { sda.Fill(dt); GridView2MissedFups.DataSource = dt; GridView2MissedFups.DataBind(); con.Close(); } } } else if (ddMissedFups.SelectedValue == "Anthro_Missed") { cmd = new MySqlCommand("select *,Datediff(str_to_date(end_date, '%d-%m-%Y'),str_to_date(date, '%d-%m-%Y')) as day_diff from view_followups6 where status='3' and (str_to_date(end_date, '%d-%m-%Y') < CURDATE()) order by study_code,str_to_date(date, '%d-%m-%Y')", con); MySqlDataAdapter sda = new MySqlDataAdapter(); { cmd.Connection = con; sda.SelectCommand = cmd; DataTable dt = new DataTable(); { sda.Fill(dt); GridView2MissedFups.DataSource = dt; GridView2MissedFups.DataBind(); con.Close(); } } } } catch (Exception ex) { Response.Write("<script type=\"text/javascript\">alert('" + ex.Message + "')</script>"); } finally { con.Close(); } }