public ActionResult ReporteAvance() { using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnSimple"].ConnectionString)) { var db = new edayRoomEntities(); var metrics = db.GetGeneralMetricsForReport().First(); var package = new ExcelPackage(); package.Workbook.Worksheets.Add("E-day"); package.Workbook.Worksheets.Add("Cruce"); package.Workbook.Worksheets.Add("QC"); package.Workbook.Worksheets.Add("Manuelita"); var edayWorksheet = package.Workbook.Worksheets[1]; edayWorksheet.Column(1).Width = 20; edayWorksheet.Column(2).Width = 43; edayWorksheet.Column(3).Width = 10; edayWorksheet.Column(4).Width = 5; edayWorksheet.Column(5).Width = 43; edayWorksheet.Column(6).Width = 10; edayWorksheet.Cells["B3"].Value = "Reporte por Hora"; edayWorksheet.Cells["B3"].Style.Font.Bold = true; edayWorksheet.Cells["B3"].Style.Font.Size = 18; edayWorksheet.Cells["A1"].Value = DateTime.Now.ToString("dd/MM/yyyy hh:mm tt"); edayWorksheet.Cells["B4"].Value = "Sala Situacional Integral 14-A"; edayWorksheet.Cells["B4"].Style.Font.Bold = true; edayWorksheet.Cells["B4"].Style.Font.Size = 18; edayWorksheet.Cells["B6"].Value = "Indicadores generales"; edayWorksheet.Cells["B6"].Style.Font.Bold = true; edayWorksheet.Cells["B6"].Style.Font.Size = 14; edayWorksheet.Cells["B8"].Value = "Porcentaje de contactabilidad"; edayWorksheet.Cells["C8"].Value = Math.Round((decimal)metrics.mesasContactadas2hr, 2) + "%"; edayWorksheet.Cells["B9"].Value = "Variación vs reporte previo"; edayWorksheet.Cells["C9"].Value = "xx%"; edayWorksheet.Cells["B11"].Value = "Mesas abiertas (sobre muestra)"; edayWorksheet.Cells["C11"].Value = Math.Round((decimal)metrics.mesasAbiertas,2)+"%"; edayWorksheet.Cells["B13"].Value = "Votantes contabilizados"; edayWorksheet.Cells["C13"].Value = metrics.votantesContabilizados+""; edayWorksheet.Cells["B14"].Value = "Variación vs reporte previo"; edayWorksheet.Cells["C14"].Value = "xx%"; edayWorksheet.Cells["B16"].Value = "Proyección de votantes nacional"; edayWorksheet.Cells["C16"].Value = metrics.proyeccionNacional+""; edayWorksheet.Cells["C16"].Style.Numberformat.Format = "#,#0.0"; edayWorksheet.Cells["B17"].Value = "Variación vs reporte previo"; edayWorksheet.Cells["C17"].Value = "xx%"; edayWorksheet.Cells["B19"].Value = "Pareto de participación por tendencia"; edayWorksheet.Cells["B20"].Value = "Centros opositores"; edayWorksheet.Cells["C20"].Value = metrics.capriles+"%"; edayWorksheet.Cells["B21"].Value = "Centros chavistas"; edayWorksheet.Cells["C21"].Value = metrics.chavista + "%"; edayWorksheet.Cells["B22"].Value = "Centros ni-ni"; edayWorksheet.Cells["C22"].Value = metrics.nini + "%"; edayWorksheet.Cells["B26"].Value = "Participación y Proyecciones (por estado)"; edayWorksheet.Cells["B26"].Style.Font.Bold = true; edayWorksheet.Cells["B26"].Style.Font.Size = 14; //Inicio del calculo de Participacion y proyecciones var ugs = db.Centroes.Select(c => c.unidadGeografica1).Distinct(); bool even = false; string colA, colB; int row = 28; foreach (var u in ugs) { if(even){ colA = "E"; colB = "F"; }else{ colA = "B"; colB = "C"; } even = !even; var dataUG = db.GetMetricsReportForUG(u).First(); edayWorksheet.Cells[colA+row].Value = u; edayWorksheet.Cells[colA+row].Style.Font.Bold = true; edayWorksheet.Cells[colA+row].Style.Font.Size = 12; edayWorksheet.Cells[colA+(row+2)].Value = "Mesas a contactar"; edayWorksheet.Cells[colB+(row+2)].Value = dataUG.mesas; edayWorksheet.Cells[colA+(row+3)].Value = "Mesas contactadas"; edayWorksheet.Cells[colB+(row+3)].Value = dataUG.mesasContactadas; edayWorksheet.Cells[colA+(row+5)].Value = "Votantes cuantificados"; edayWorksheet.Cells[colB+(row+5)].Value = dataUG.votantesCuantificados; edayWorksheet.Cells[colA+(row+6)].Value = "Proyección de votantes regional"; edayWorksheet.Cells[colB+(row+6)].Value = dataUG.proyeccionRegional; edayWorksheet.Cells[colA+(row+7)].Value = "Crecimiento vs reporte previo"; edayWorksheet.Cells[colB+(row+7)].Value = "xx%"; edayWorksheet.Cells[colA+(row+9)].Value = "Pareto de participación por tendencia"; edayWorksheet.Cells[colA+(row+10)].Value = "Centros opositores"; edayWorksheet.Cells[colB+(row+10)].Value = dataUG.capriles; edayWorksheet.Cells[colA+(row+11)].Value = "Centros chavistas"; edayWorksheet.Cells[colB+(row+11)].Value = dataUG.chavista; edayWorksheet.Cells[colA+(row+12)].Value = "Centros ni-ni"; edayWorksheet.Cells[colB+(row+12)].Value = dataUG.nini; edayWorksheet.Cells[colA+(row+14)].Value = "Proyección de participación al cierre"; edayWorksheet.Cells[colB+(row+14)].Value = dataUG.proyeccionFin; edayWorksheet.Cells[colA+(row+15)].Value = "Referencia 7O"; edayWorksheet.Cells[colB+(row+15)].Value = "xx%"; edayWorksheet.Cells[colA+(row+16)].Value = "Referencia 2007-2010"; edayWorksheet.Cells[colB+(row+16)].Value = "xx%"; edayWorksheet.Cells[colA+(row+17)].Value = "Participación óptima"; edayWorksheet.Cells[colB+(row+17)].Value = "xx%"; if(!even){ row += 19; } } var stream = new MemoryStream(); package.SaveAs(stream); string fileName = "reporte" + DateTime.Now.ToString("yyyy.MM.dd.HH.mm.ss") + ".xlsx"; const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; stream.Position = 0; return File(stream, contentType, fileName); } }