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(); } }