private void ExportToExcel()
{
GridView1.AllowPaging = false;
ShowData();
String animalReport = "Animal Type: " + drpAnimalType.SelectedValue.ToString() + " Report ";
String filename = "Created on: " + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Day.ToString() + "/" + DateTime.Now.Year.ToString();
HttpResponse response = HttpContext.Current.Response;
StringWriter sw3 = new StringWriter();
HtmlTextWriter htW3 = new HtmlTextWriter(sw3);
StringWriter sw = new StringWriter();
HtmlTextWriter htW = new HtmlTextWriter(sw);
StringWriter sw2 = new StringWriter();
HtmlTextWriter htW2 = new HtmlTextWriter(sw2);
response.Clear();
response.Buffer = true;
response.Charset = "";
response.ContentType = "application/vnd.xls";
response.AddHeader("content-disposition", "attachment; filename=\"" + animalReport + filename + "\"" + ".xls");
string headerTable = @"<Table>" + animalReport + " " + filename + "<tr><td></td></tr></Table>";
string headerTable1 = @"<Table>" + drpAnimalType.SelectedValue.ToString() + " Totals Based on Live Programs <tr><td></td></tr></Table>";
string headerTable2 = @"<Table>" + drpAnimalType.SelectedValue.ToString() + " Totals Based on Online Programs <tr><td></td></tr></Table>";
string headerTable3 = @"<Table> Count of " + drpAnimalType.SelectedValue.ToString() + " Total Program Involvement <tr><td></td></tr></Table>";
string blankline = @"<Table><tr><td></td></tr></Table>";
Response.Write(headerTable);
Response.Write(headerTable3);
GridView1.RenderControl(htW3);
Response.Output.Write(sw3.ToString());
Response.Write(blankline);
Response.Write(headerTable1);
AnimalLiveGrid.RenderControl(htW);
Response.Output.Write(sw.ToString());
Response.Write(blankline);
Response.Write(headerTable2);
gridOnlinePrograms.RenderControl(htW2);
Response.Output.Write(sw2.ToString());
Response.Write(blankline);
Response.End();
}
protected void ShowData()
{
System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
//sc.ConnectionString = @"Server=localhost;Database=WildTek;Trusted_Connection=Yes;";
String cs = ConfigurationManager.ConnectionStrings["WildTekConnectionString"].ConnectionString;
sc.ConnectionString = cs;
sc.Open();
System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
insert.Connection = sc;
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "ModalView", "<script>$function(){ $('#myModal').modal('show');});</script>", false);
DataTable dt = new DataTable();
//con = new SqlConnection(sc);
//con.Open();
SqlCommand cmd = new SqlCommand("SELECT Animal.AnimalName, SUM(CASE WHEN Program.onoff = 1 THEN 1 ELSE 0 END) AS TotalOnSitePrograms, " +
"SUM(CASE WHEN Program.onoff = 0 THEN 1 ELSE 0 END) AS TotalOffSitePrograms, Count(ProgramAnimal.ProgramID) AS TotalLivePrograms, SUM(Program.NumberOfChildren) AS NumberOfChildren, " +
"SUM(Program.NumberOfAdults) AS NumberOfAdults FROM Animal, " +
"Program, ProgramAnimal WHERE(Animal.AnimalType = @AnimalType) AND Animal.AnimalID = ProgramAnimal.AnimalID AND ProgramAnimal.ProgramID = Program.ProgramID " +
"GROUP BY Animal.AnimalName, Animal.AnimalType ORDER BY Animal.AnimalName", sc);
cmd.Parameters.AddWithValue("@AnimalType", drpAnimalType.Text.ToString());
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.Fill(dt);
if (dt.Rows.Count > 0)
{
AnimalLiveGrid.DataSource = dt;
AnimalLiveGrid.DataBind();
}
sc.Close();
}
