private void ShowData(string strEmailAddress)
{
dt = new DataTable();
string cnnstr = ConfigurationManager.AppSettings["ConnectionString"];
SqlConnection conn = new SqlConnection(cnnstr);
conn.Open();
string Query = @"SELECT dbo.Employee.EmployeeId, dbo.Employee.Name, dbo.Employee.Surname, dbo.Role.Description,
dbo.Project.ProjectName, dbo.TimeSheet.StartTime, dbo.TimeSheet.EndTime
FROM dbo.TimeSheet INNER JOIN
dbo.Employee ON dbo.TimeSheet.EmployeeId = dbo.Employee.EmployeeId INNER JOIN
dbo.Project ON dbo.TimeSheet.ProjectId = dbo.Project.ProjectId INNER JOIN
dbo.Role ON dbo.Employee.RoleId = dbo.Role.RoleId
WHERE dbo.Employee.EmailAddress = '" + txtEmail.Text + "'";
adapt = new SqlDataAdapter(Query, conn);
adapt.Fill(dt);
if (dt.Rows.Count > 0)
{
GridViewTimeSheet.DataSource = dt;
GridViewTimeSheet.DataBind();
}
else
{
dt.Rows.Add(dt.NewRow());
GridViewTimeSheet.DataSource = dt;
GridViewTimeSheet.DataBind();
int TotalColumns = GridViewTimeSheet.Rows[0].Cells.Count;
GridViewTimeSheet.Rows[0].Cells.Clear();
GridViewTimeSheet.Rows[0].Cells.Add(new TableCell());
GridViewTimeSheet.Rows[0].Cells[0].ColumnSpan = TotalColumns;
GridViewTimeSheet.Rows[0].Cells[0].Text = "No records Found";
}
conn.Close();
}
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
GridViewTimeSheet.AllowPaging = false;
this.ShowData(txtEmail.Text);
GridViewTimeSheet.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in GridViewTimeSheet.HeaderRow.Cells)
{
cell.BackColor = GridViewTimeSheet.HeaderStyle.BackColor;
}
foreach (GridViewRow row in GridViewTimeSheet.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = GridViewTimeSheet.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = GridViewTimeSheet.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
GridViewTimeSheet.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
