示例#1
0
        public string ExcelGeneradoSueldos(string tipo, int dia, int mes, int anio, bool incluir_dependencias, int id_area)
        {
            try
            {
                DateTime           fecha = new DateTime(anio, mes, dia);
                RepositorioDeAreas repositorio_de_areas = RepositorioDeAreas.NuevoRepositorioDeAreas(this.conexion_bd);
                Grafico            graficoExcel         = GetReporteSueldosPorArea(tipo, fecha, id_area, incluir_dependencias);

                DataTable table_resumen = new DataTable();
                table_resumen.TableName = "Detalle";

                DataTable table_detalle = new DataTable();
                table_detalle.TableName = "Sueldos";

                Area area = repositorio_de_areas.GetAreaPorId(id_area);

                table_detalle.Columns.Add("Informacion");
                table_detalle.Columns.Add("Cantidad");
                table_detalle.Columns.Add("Porcentaje (%)");
                table_detalle.Columns.Add("SumatoriaSueldo");
                table_detalle.Columns.Add("PromedioSueldo");
                table_detalle.Columns.Add("MedianaSueldo");
                table_detalle.Columns.Add("SumatoriaExtras");
                table_detalle.Columns.Add("PromedioExtras");
                table_detalle.Columns.Add("MedianaExtras");

                foreach (var item in graficoExcel.tabla_resumen)
                {
                    table_detalle.Rows.Add(item.Id,
                                           item.Cantidad,
                                           Math.Truncate(item.Porcentaje * 100) / 100,
                                           item.SumatoriaSueldo,
                                           item.PrimedioSueldo,
                                           item.MedianaSueldo,
                                           item.SumatoriaExtras,
                                           item.PrimedioExtras,
                                           item.MedianaExtras);
                }

                table_resumen.Columns.Add("Area");
                //   table_resumen.Columns.Add("Documento");
                table_resumen.Columns.Add("CUIL");
                table_resumen.Columns.Add("Apellido");
                table_resumen.Columns.Add("Nombre");
                table_resumen.Columns.Add("FechaIngreso");
                table_resumen.Columns.Add("SueldoBruto");
                table_resumen.Columns.Add("SueldoNeto");
                table_resumen.Columns.Add("ExtrasBruto");
                table_resumen.Columns.Add("ExtrasNeto");
                table_resumen.Columns.Add("HsSimples");
                table_resumen.Columns.Add("Hs50%");
                table_resumen.Columns.Add("Hs100%");
                table_resumen.Columns.Add("Comidas");
                table_resumen.Columns.Add("UR");
                foreach (var item in graficoExcel.tabla_detalle)
                {
                    object valor_extra_bruto = null;
                    if (item.ExtrasBruto != 0)
                    {
                        valor_extra_bruto = item.ExtrasBruto;
                    }
                    object valor_extra_neto = null;
                    if (item.ExtrasNeto != 0)
                    {
                        valor_extra_neto = item.ExtrasNeto;
                    }

                    object valor_horas_simples = null;
                    if (item.HsSimples != 0)
                    {
                        valor_horas_simples = item.HsSimples;
                    }

                    object valor_horas_50 = null;
                    if (item.Hs50 != 0)
                    {
                        valor_horas_50 = item.Hs50;
                    }
                    object valor_horas_100 = null;
                    if (item.Hs100 != 0)
                    {
                        valor_horas_100 = item.Hs100;
                    }
                    object valor_comidas = null;
                    if (item.Hs100 != 0)
                    {
                        valor_comidas = item.Comidas;
                    }
                    object valor_UR = null;
                    if (item.UnidadRetributiva != 0)
                    {
                        valor_UR = item.UnidadRetributiva;
                    }
                    table_resumen.Rows.Add(item.Area, item.CUIL, item.Apellido, item.Nombre, item.FechaIngreso.ToShortDateString(), item.SueldoBruto, item.SueldoNeto, valor_extra_bruto, valor_extra_neto, valor_horas_simples, valor_horas_50, valor_horas_100, valor_comidas, valor_UR);
                }

                var workbook = new XLWorkbook();
                //var dataTable_consulta_parametros = table;
                var dataTable_resumen = table_resumen;
                var dataTable_detalle = table_detalle;
                var ws = workbook.Worksheets.Add("Resumen");

                ws.Style.Font.FontSize = 11;
                ws.Style.Font.FontName = "Verdana";

                ws.Column("A").Width = 15;
                ws.Column("B").Width = 15;
                ws.Column("C").Width = 25;
                ws.Column("D").Width = 25;
                ws.Column("E").Width = 18;
                ws.Column("F").Width = 18;
                ws.Column("G").Width = 18;
                ws.Column("H").Width = 18;
                ws.Column("I").Width = 18;
                ws.Column("J").Width = 18;
                ws.Column("K").Width = 18;
                ws.Column("L").Width = 18;
                ws.Column("M").Width = 18;

                ws.Cell(1, 1).Value = "FECHA:";
                ws.Cell(2, 1).Value = "AREA:";

                ws.Cell(1, 1).Style.Font.Bold = true;
                ws.Cell(2, 1).Style.Font.Bold = true;

                ws.Cell(1, 2).Value = fecha.ToShortDateString();
                ws.Cell(2, 2).Value = area.Nombre.ToUpper();

                ws.Range(4, 1, 4, 9).Style.Fill.BackgroundColor = XLColor.FromArgb(79, 129, 189);
                ws.Range(4, 1, 4, 9).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                ws.Range(4, 1, 4, 9).Style.Font.FontColor = XLColor.White;

                ws.Cell(4, 1).Value = "Informacion";
                ws.Cell(4, 2).Value = "Cantidad";
                ws.Cell(4, 3).Value = "Porcentaje %";
                ws.Cell(4, 4).Value = "SumatoriaSueldo";
                ws.Cell(4, 5).Value = "PromedioSueldo";
                ws.Cell(4, 6).Value = "MedianaSueldo";
                ws.Cell(4, 7).Value = "SumatoriaExtras";
                ws.Cell(4, 8).Value = "PromedioExtras";
                ws.Cell(4, 9).Value = "MedianaExtras";

                var rangeWithData = ws.Cell(5, 1).InsertData(dataTable_detalle.AsEnumerable());

                var lastCell = ws.LastCellUsed();

                ws.Range(4, 1, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).Style.Border.InsideBorder  = XLBorderStyleValues.Thin;
                ws.Range(4, 1, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).Style.Border.OutsideBorder = XLBorderStyleValues.Medium;

                ws.Range(5, 3, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).DataType = XLCellValues.Number;

                workbook.Worksheets.Add(dataTable_resumen);

                var lastCell2 = workbook.Worksheet(2).LastCellUsed();
                //  workbook.Worksheet(2).Range(2, 2, lastCell2.Address.RowNumber, 2).DataType = XLCellValues.Number;
                //  workbook.Worksheet(2).Range(2, 5, lastCell2.Address.RowNumber, 13).DataType = XLCellValues.Number;

                workbook.Worksheet(2).Range(2, 2, lastCell2.Address.RowNumber, 2).DataType  = XLCellValues.Number;
                workbook.Worksheet(2).Range(2, 6, lastCell2.Address.RowNumber, 14).DataType = XLCellValues.Number;

                workbook.Worksheet(2).Column("A").Width = 25;
                workbook.Worksheet(2).Column("B").Width = 14;
                workbook.Worksheet(2).Column("C").Width = 25;
                workbook.Worksheet(2).Column("D").Width = 25;
                workbook.Worksheet(2).Column("E").Width = 18;
                workbook.Worksheet(2).Column("F").Width = 18;
                workbook.Worksheet(2).Column("G").Width = 18;
                workbook.Worksheet(2).Column("H").Width = 18;
                workbook.Worksheet(2).Column("I").Width = 18;
                workbook.Worksheet(2).Column("J").Width = 18;
                workbook.Worksheet(2).Column("K").Width = 18;
                workbook.Worksheet(2).Column("L").Width = 18;
                workbook.Worksheet(2).Column("M").Width = 18;

                using (var ms = new MemoryStream())
                { workbook.SaveAs(ms);
                  return(Convert.ToBase64String(ms.ToArray())); }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
示例#2
0
        public string ExcelGeneradoContratos(string tipo, int dia, int mes, int anio, bool incluir_dependencias, int id_area)
        {
            try
            {
                DateTime           fecha = new DateTime(anio, mes, dia);
                RepositorioDeAreas repositorio_de_areas = RepositorioDeAreas.NuevoRepositorioDeAreas(this.conexion_bd);
                GraficoContratos   graficoExcel         = GetGraficoContratados(tipo, fecha, id_area, incluir_dependencias);

                DataTable table_resumen = new DataTable();
                table_resumen.TableName = "Resumen";

                DataTable table_detalle = new DataTable();
                table_detalle.TableName = "Detalle";

                Area area = repositorio_de_areas.GetAreaPorId(id_area);

                table_resumen.Columns.Add("Informacion");
                table_resumen.Columns.Add("Cantidad");
                table_resumen.Columns.Add("Porcentaje(%)");

                table_detalle.Columns.Add("Area");
                table_detalle.Columns.Add("NroDocumento");
                table_detalle.Columns.Add("Apellido_Nombre");
                table_detalle.Columns.Add("Detalle");
                table_detalle.Columns.Add("Informe");


                foreach (var item in graficoExcel.tabla_resumen)
                {
                    table_resumen.Rows.Add(item.DescripcionGrafico,
                                           item.Cantidad,
                                           Math.Truncate(item.Porcentaje * 100) / 100
                                           );
                }

                foreach (var item in graficoExcel.tabla_detalle_contratos)
                {
                    table_detalle.Rows.Add(item.Area,
                                           item.NroDocumento,
                                           item.Apellido + " " + item.Nombre,
                                           item.Estado,
                                           item.Informe
                                           );
                }


                var workbook = new XLWorkbook();
                //var dataTable_consulta_parametros = table;
                var dataTable_resumen = table_resumen;
                var dataTable_detalle = table_detalle;
                var ws = workbook.Worksheets.Add("Resumen");

                ws.Style.Font.FontSize = 11;
                ws.Style.Font.FontName = "Verdana";

                ws.Column("A").Width = 15;
                ws.Column("B").Width = 15;
                ws.Column("C").Width = 25;
                ws.Column("D").Width = 25;
                ws.Column("E").Width = 25;

                // ws.Cell(1, 1).Value = "FECHA:";
                //  ws.Cell(2, 1).Value = "AREA:";

                ws.Cell(1, 1).Style.Font.Bold = true;
                ws.Cell(2, 1).Style.Font.Bold = true;

                //  ws.Cell(1, 2).Value = fecha.ToShortDateString();
                //   ws.Cell(2, 2).Value = area.Nombre.ToUpper();

                ws.Range(4, 1, 4, 3).Style.Fill.BackgroundColor = XLColor.FromArgb(79, 129, 189);
                ws.Range(4, 1, 4, 3).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                ws.Range(4, 1, 4, 3).Style.Font.FontColor = XLColor.White;

                ws.Cell(4, 1).Value = "Informacion";
                ws.Cell(4, 2).Value = "Cantidad";
                ws.Cell(4, 3).Value = "Porcentaje %";
                //   ws.Cell(4, 4).Value = "Porcentaje Hombres";
                //   ws.Cell(4, 5).Value = "Porcentaje Mujeres";


                var rangeWithData = ws.Cell(5, 1).InsertData(dataTable_resumen.AsEnumerable());

                var lastCell = ws.LastCellUsed();

                ws.Range(4, 1, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).Style.Border.InsideBorder  = XLBorderStyleValues.Thin;
                ws.Range(4, 1, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).Style.Border.OutsideBorder = XLBorderStyleValues.Medium;

                ws.Range(5, 2, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).DataType = XLCellValues.Number;

                workbook.Worksheets.Add(dataTable_detalle);

                var lastCell2 = workbook.Worksheet(2).LastCellUsed();
                //workbook.Worksheet(2).Range(2, 2, lastCell2.Address.RowNumber, 2).DataType = XLCellValues.Number;


                //workbook.Worksheet(2).Range(2, 4, lastCell2.Address.RowNumber, 4).DataType = XLCellValues.Number;
                //workbook.Worksheet(2).Range(2, 8, lastCell2.Address.RowNumber, 8).DataType = XLCellValues.Number;

                workbook.Worksheet(2).Column("A").Width = 25;
                workbook.Worksheet(2).Column("B").Width = 15;
                workbook.Worksheet(2).Column("C").Width = 25;
                workbook.Worksheet(2).Column("D").Width = 8;
                workbook.Worksheet(2).Column("E").Width = 10;
                workbook.Worksheet(2).Column("F").Width = 18;
                workbook.Worksheet(2).Column("G").Width = 18;
                workbook.Worksheet(2).Column("H").Width = 18;
                workbook.Worksheet(2).Column("I").Width = 18;
                workbook.Worksheet(2).Column("J").Width = 18;


                using (var ms = new MemoryStream())
                {
                    workbook.SaveAs(ms);
                    return(Convert.ToBase64String(ms.ToArray()));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
示例#3
0
        public string ExcelGenerado(string tipo, int dia, int mes, int anio, bool incluir_dependencias, DateTime fecha, int id_area)
        {
            try
            {
                Grafico grafico = GetGraficoDotacion(tipo, fecha, id_area, incluir_dependencias);

                DataTable table_resumen = new DataTable();
                table_resumen.TableName = "Resumen";

                RepositorioDeAreas repositorio_de_areas = RepositorioDeAreas.NuevoRepositorioDeAreas(this.conexion_bd);
                Area area = repositorio_de_areas.GetAreaPorId(id_area);

                table_resumen.Columns.Add("Informacion");
                table_resumen.Columns.Add("Cantidad");
                table_resumen.Columns.Add("Porcentaje");

                foreach (var item in grafico.tabla_resumen)
                {
                    table_resumen.Rows.Add(item.Id.Replace("|", " "), item.Cantidad, item.Porcentaje);
                }

                DataTable table_detalle = new DataTable();
                table_detalle.TableName = "Detalle";

                // table_detalle.Columns.Add("NroDocumento");
                table_detalle.Columns.Add("CUIL");
                table_detalle.Columns.Add("Apellido");
                table_detalle.Columns.Add("Nombre");
                table_detalle.Columns.Add("Sexo");
                table_detalle.Columns.Add("FechaNacimiento");
                table_detalle.Columns.Add("FechaIngreso");
                table_detalle.Columns.Add("Nivel");
                table_detalle.Columns.Add("Grado");
                table_detalle.Columns.Add("Planta");
                table_detalle.Columns.Add("NivelEstudio");
                table_detalle.Columns.Add("Titulo");
                table_detalle.Columns.Add("Area");
                table_detalle.Columns.Add("Area Descrip Media");

                foreach (var item in grafico.tabla_detalle)
                {
                    table_detalle.Rows.Add(item.CUIL, item.Apellido, item.Nombre, item.Sexo, item.FechaNacimiento.ToShortDateString(), item.FechaIngreso.ToShortDateString(), item.Nivel, item.Grado, item.Planta, item.NivelEstudio, item.Titulo, item.Area, item.AreaDescripMedia);
                }

                //CREACIÓN DE LAS COLUMNAS
                //      table.Columns.Add("Categoria", typeof(string));
                //       table.Columns.Add("% Participación VM", typeof(double));

                var workbook = new XLWorkbook();

                //   var dataTable_consulta_parametros = table;
                var dataTable_resumen = table_resumen;
                var dataTable_detalle = table_detalle;
                var ws = workbook.Worksheets.Add("Resumen");

                ws.Style.Font.FontSize = 11;
                ws.Style.Font.FontName = "Verdana";

                ws.Column("A").Width = 15;
                ws.Column("B").Width = 15;
                ws.Column("C").Width = 15;

                //  ws.Row(1).Height = 25;
                //  ws.Row(1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);

                ws.Cell(1, 1).Value = "FECHA:";
                ws.Cell(2, 1).Value = "AREA:";

                ws.Cell(1, 1).Style.Font.Bold = true;
                ws.Cell(2, 1).Style.Font.Bold = true;

                ws.Cell(1, 2).Value = fecha.ToShortDateString();
                ws.Cell(2, 2).Value = area.Nombre.ToUpper();

                ws.Range(4, 1, 4, 3).Style.Fill.BackgroundColor = XLColor.FromArgb(79, 129, 189);
                ws.Range(4, 1, 4, 3).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                ws.Range(4, 1, 4, 3).Style.Font.FontColor = XLColor.White;

                ws.Cell(4, 1).Value = "Informacion";
                ws.Cell(4, 2).Value = "Cantidad";
                ws.Cell(4, 3).Value = "Porcentaje %";

                var rangeWithData = ws.Cell(5, 1).InsertData(dataTable_resumen.AsEnumerable());

                var lastCell = ws.LastCellUsed();

                ws.Range(4, 1, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).Style.Border.InsideBorder  = XLBorderStyleValues.Thin;
                ws.Range(4, 1, lastCell.Address.RowNumber, lastCell.Address.ColumnNumber).Style.Border.OutsideBorder = XLBorderStyleValues.Medium;

                workbook.Worksheets.Add(dataTable_detalle);

                //  string rut = HttpContext.Current.Request.PhysicalApplicationPath + "/Excel.xlsx";

                using (var ms = new MemoryStream())
                {
                    workbook.SaveAs(ms);

                    // return ms.ToArray();

                    //return File(ms.ToArray(), MediaTypeNames.Application.Octet, "excel2"+ ".xlsx");
                    return(Convert.ToBase64String(ms.ToArray()));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }