void CargarDetalleCanil() { DataTable Data = AtencionPeluqueriaBuss.BuscarCanil(0, txtfechaIni.Text, txtFechaFinal.Text, cboRecurso.SelectedValue, cboEstado.SelectedValue); grvresultado.DataSource = Data; grvresultado.DataBind(); }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { try { Int32 ColumnaDetalle = 1; Int32 Contador = 0; DataTable Data = AtencionPeluqueriaBuss.BuscarCanil(0, Request.QueryString["txtfechaIni"], Request.QueryString["txtFechaFinal"], Request.QueryString["cboRecurso"], Request.QueryString["cboEstado"]); DataTable Data2 = AtencionPeluqueriaBuss.BuscarSector(0, Request.QueryString["txtfechaIni"], Request.QueryString["txtFechaFinal"], Request.QueryString["cboRecurso"], Request.QueryString["cboEstado"]); string Name = Convert.ToString(DateTime.Now.Ticks); using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet WorkSheet = package.Workbook.Worksheets.Add("BASE"); Int32 Fila = 2; if (Data.Rows.Count > 0 && (Request.QueryString["cboRecurso"] == "0" || Request.QueryString["cboRecurso"] == "" || Request.QueryString["cboRecurso"] == "1")) { ExcelRange range = WorkSheet.Cells[2, 1, 2, 6]; range.Merge = true; WorkSheet.Cells[Fila, 1].Style.Font.SetFromFont(new Font("Calibri", 16, FontStyle.Bold)); WorkSheet.Cells[Fila, 1].Style.Font.Color.SetColor(Color.Black); WorkSheet.Cells[Fila, 1].Value = "Listado de Canil"; range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; Fila = Fila + 2; WorkSheet.Cells[Fila, 1].Value = "CODIGO"; WorkSheet.Cells[Fila, 2].Value = "NOMBRE"; WorkSheet.Cells[Fila, 3].Value = "TAMAÑO"; WorkSheet.Cells[Fila, 4].Value = "ESTADO"; WorkSheet.Cells[Fila, 5].Value = "ESPECIE"; WorkSheet.Cells[Fila, 6].Value = "OBSERVACIONES"; ExcelRange RangoDeCeldasNameT2 = WorkSheet.Cells[Fila, 1, Fila, 6]; RangoDeCeldasNameT2.Style.Border.Top.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Border.Left.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Border.Right.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Font.Color.SetColor(Color.Black); RangoDeCeldasNameT2.Style.Fill.PatternType = ExcelFillStyle.Solid; RangoDeCeldasNameT2.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FACA2C")); RangoDeCeldasNameT2.Style.Font.Bold = true; RangoDeCeldasNameT2.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; RangoDeCeldasNameT2.Style.WrapText = true; ColumnaDetalle = 1; Contador = 0; foreach (DataRow dr2 in Data.Rows) { Fila = Fila + 1; ColumnaDetalle = 1; foreach (DataColumn dr in Data.Columns) { if (ColumnaDetalle <= 6) { WorkSheet.Cells[Fila, ColumnaDetalle].Value = dr2[dr.ColumnName]; } ColumnaDetalle = ColumnaDetalle + 1; Contador = Contador + 1; } ExcelRange rangeCab6 = WorkSheet.Cells[Fila, 1, Fila, 6]; rangeCab6.Style.Border.Top.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.Left.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.Right.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.BorderAround(ExcelBorderStyle.Thin); rangeCab6.Style.Font.Color.SetColor(Color.Black); rangeCab6.Style.Font.Bold = false; rangeCab6.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; rangeCab6.Style.WrapText = true; } Fila = Fila + 2; } if (Data2.Rows.Count > 0 && (Request.QueryString["cboRecurso"] == "0" || Request.QueryString["cboRecurso"] == "" || Request.QueryString["cboRecurso"] == "2")) { ExcelRange range5 = WorkSheet.Cells[Fila, 1, Fila, 6]; range5.Merge = true; WorkSheet.Cells[Fila, 1].Style.Font.SetFromFont(new Font("Calibri", 16, FontStyle.Bold)); WorkSheet.Cells[Fila, 1].Style.Font.Color.SetColor(Color.Black); WorkSheet.Cells[Fila, 1].Value = "Listado de Sectores"; range5.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; Fila = Fila + 2; WorkSheet.Cells[Fila, 1].Value = "CODIGO"; WorkSheet.Cells[Fila, 2].Value = "NOMBRE"; WorkSheet.Cells[Fila, 3].Value = "SERVICIO"; WorkSheet.Cells[Fila, 4].Value = "ESTADO"; WorkSheet.Cells[Fila, 5].Value = "OBSERVACIONES"; ExcelRange RangoDeCeldasName5 = WorkSheet.Cells[Fila, 5, Fila, 6]; RangoDeCeldasName5.Merge = true; ExcelRange RangoDeCeldasNameT4 = WorkSheet.Cells[Fila, 1, Fila, 6]; RangoDeCeldasNameT4.Style.Border.Top.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Border.Left.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Border.Right.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Font.Color.SetColor(Color.Black); RangoDeCeldasNameT4.Style.Fill.PatternType = ExcelFillStyle.Solid; RangoDeCeldasNameT4.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FACA2C")); RangoDeCeldasNameT4.Style.Font.Bold = true; RangoDeCeldasNameT4.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; RangoDeCeldasNameT4.Style.WrapText = true; ColumnaDetalle = 1; Contador = 0; foreach (DataRow dr3 in Data2.Rows) { Fila = Fila + 1; ColumnaDetalle = 1; foreach (DataColumn dr4 in Data2.Columns) { if (ColumnaDetalle <= 5) { WorkSheet.Cells[Fila, ColumnaDetalle].Value = dr3[dr4.ColumnName]; if (ColumnaDetalle == 5) { ExcelRange RangoDeCeldasNamecol5 = WorkSheet.Cells[Fila, 5, Fila, 6]; RangoDeCeldasNamecol5.Merge = true; } } ColumnaDetalle = ColumnaDetalle + 1; Contador = Contador + 1; } ExcelRange RangeCab8 = WorkSheet.Cells[Fila, 1, Fila, 6]; RangeCab8.Style.Border.Top.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.Left.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.Right.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.BorderAround(ExcelBorderStyle.Thin); RangeCab8.Style.Font.Color.SetColor(Color.Black); RangeCab8.Style.Font.Bold = false; RangeCab8.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; RangeCab8.Style.WrapText = true; } } ExcelColumn Col1 = WorkSheet.Column(1); ExcelColumn Col2 = WorkSheet.Column(2); ExcelColumn Col3 = WorkSheet.Column(3); ExcelColumn Col4 = WorkSheet.Column(4); ExcelColumn Col5 = WorkSheet.Column(5); ExcelColumn Col6 = WorkSheet.Column(6); Col1.Width = 20; Col2.Width = 20; Col3.Width = 20; Col4.Width = 20; Col5.Width = 20; Col6.Width = 40; Response.Clear(); Response.ContentType = "application/xlsx"; Response.AddHeader("content-disposition", "attachment; filename=Reporte_Canil_Sectores.xlsx"); Response.BinaryWrite(package.GetAsByteArray()); Response.End(); } } catch (Exception ex) { lblmsg.Text = "Error interno en el sistema contacte a su administrador " + " Detalle:" + ex.Message; msgError.Clear(); msgError.AppendLine("Fecha:" + DateTime.Now.ToString()); msgError.AppendLine("Descripción:" + ex.Message); msgError.AppendLine("Detalle:" + ex.StackTrace); log.Error(msgError.ToString()); } } }