/// <summary>
/// Sets the style.
/// </summary>
/// <param name="Fill">The fill.</param>
/// <param name="styleEntry">The style entry.</param>
public static void SetStyle(this ExcelFill Fill, styleSurfaceColor styleEntry)
{
if (styleEntry != null)
{
Fill.PatternType = (ExcelFillStyle)styleEntry.FillType;
Fill.BackgroundColor.SetColor(styleEntry.Color);
Fill.BackgroundColor.Tint = new decimal(styleEntry.Tint);
}
}
public ExcelFillInstance(ObjectInstance prototype, ExcelFill excelFill)
: this(prototype)
{
if (excelFill == null)
{
throw new ArgumentNullException("excelFill");
}
m_excelFill = excelFill;
}
/// <summary>
/// Get background style of a cell
/// </summary>
/// <returns>Background css style</returns>
public string GetBackgroundStyle()
{
ExcelFill fill = Cell.Style.Fill;
string color = "transparent";
if (fill.BackgroundColor != null && fill.BackgroundColor.Rgb != null)
{
color = Utils.ExcelColorToHex(fill.BackgroundColor);
}
return($"background-color: {color};");
}
internal void ToExcelFill(ExcelFill excelFill)
{
excelFill.PatternType = PatternType;
if (excelFill.PatternType == ExcelFillStyle.None)
{
if (BackgroundColor != Color.Empty || PatternColor != Color.Empty)
{
excelFill.PatternType = ExcelFillStyle.Solid;
}
}
if (BackgroundColor != Color.Empty)
{
excelFill.BackgroundColor.SetColor(BackgroundColor);
}
if (PatternColor != Color.Empty)
{
excelFill.PatternColor.SetColor(PatternColor);
}
if (Gradient != null)
{
excelFill.Gradient.Type = Gradient.Type;
excelFill.Gradient.Bottom = Gradient.Bottom;
excelFill.Gradient.Left = Gradient.Left;
excelFill.Gradient.Right = Gradient.Right;
excelFill.Gradient.Top = Gradient.Top;
excelFill.Gradient.Degree = Gradient.Degree;
if (Gradient.Color1 != Color.Empty)
{
excelFill.Gradient.Color1.SetColor(Gradient.Color1);
}
if (Gradient.Color2 != Color.Empty)
{
excelFill.Gradient.Color2.SetColor(Gradient.Color2);
}
}
}
public static ExcelWorksheet FormatAlternatingColours(this ExcelWorksheet ws)
{
object previousValue = null;
int alternatingCount = 0;
for (int row = 1; row <= ws.Dimension.End.Row; row++)
{
object currentValue = ws.Cells[row, 1].Value;
if (currentValue != previousValue)
{
alternatingCount++;
}
ExcelRow rowRange = ws.Row(row);
ExcelFill RowFill = rowRange.Style.Fill;
RowFill.PatternType = ExcelFillStyle.Solid;
RowFill.BackgroundColor.SetColor(alternatingCount % 2 == 0 ? Color.White : Color.WhiteSmoke);
previousValue = currentValue;
}
return(ws);
}
public Fill(ExcelFill fill)
{
_fill = fill;
string hexARGB = _fill.BackgroundColor.Rgb;
if (string.IsNullOrEmpty(hexARGB))
{
_backgroundColor = (Color)Color.BLACK_COLOR;
}
else
{
string hexA = hexARGB.Substring(0, 2);
string hexR = hexARGB.Substring(2, 2);
string hexG = hexARGB.Substring(4, 2);
string hexB = hexARGB.Substring(6, 2);
int a = 0, r = 0, g = 0, b = 0;
a = int.Parse(hexA, System.Globalization.NumberStyles.HexNumber);
r = int.Parse(hexR, System.Globalization.NumberStyles.HexNumber);
g = int.Parse(hexG, System.Globalization.NumberStyles.HexNumber);
b = int.Parse(hexB, System.Globalization.NumberStyles.HexNumber);
_backgroundColor = new Color(a, r, g, b);
}
_backgroundColor.OnValueChanged += BackgroundColorChanged;
}
private void AssertFill(Color expectedBackgroundColor, Color expectedPatternColor, ExcelFillStyle expectedFillStyle, ExcelFill fill)
{
AssertFill(expectedBackgroundColor, expectedFillStyle, fill);
Assert.AreEqual(expectedPatternColor, fill.PatternColor);
}
public byte[] Generate(List <Cliente> clientes)
{
using (var excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "toomarketing";
excelPackage.Workbook.Properties.Title = "toomarketing-clientes";
var sheet = excelPackage.Workbook.Worksheets.Add("Clientes Excel");
sheet.Name = "Reporte Clientes";
sheet.Column(2).Width = 30; //nombre
sheet.Column(3).Width = 30; //apellido
sheet.Column(4).Width = 30; //dni
sheet.Column(5).Width = 30; //fecha
sheet.Column(6).Width = 50; //dir
sheet.Column(7).Width = 10; //fum
sheet.Column(8).Width = 10; //actRiesgo
sheet.Column(9).Width = 30; //zona
//Header
sheet.Cells[rowIndex, 2, rowIndex, 4].Merge = true;
cell = sheet.Cells[rowIndex, 2];
cell.Value = "Reporte Cliente";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 20;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex = rowIndex + 1;
sheet.Cells[rowIndex, 2, rowIndex, 4].Merge = true;
cell = sheet.Cells[rowIndex, 2];
cell.Value = "Fecha: " + DateTime.Now;
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 15;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex = rowIndex + 2;
//Table Header
cell = sheet.Cells[rowIndex, 2];
cell.Value = "Nombre";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = "Apellido";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = "DNI";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 5];
cell.Value = "Fecha Nacimiento";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 6];
cell.Value = "Dirección";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex = rowIndex + 1;
//table body
if (clientes.Count > 0)
{
foreach (Cliente c in clientes)
{
cell = sheet.Cells[rowIndex, 2];
cell.Value = c.Nombre;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = c.Apellido;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = c.Dni.ToString();
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 5];
cell.Value = c.FechaNacimiento.ToShortDateString();
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 6];
cell.Value = c.Direccion;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex = rowIndex + 1;
}
}
return(excelPackage.GetAsByteArray());
}
}
public static Color GetFillColor(ExcelFill fill)
{
if (fill.PatternType == ExcelFillStyle.None)
{
return(Color.Transparent);
}
if (!Properties.Settings.Default.ShowFormat && fill.PatternType != ExcelMergeManager.Instance.DiffStyle)
{
return(Color.Transparent);
}
if (fill.PatternType == ExcelFillStyle.Solid || fill.PatternType == ExcelMergeManager.Instance.DiffStyle)
{
if (string.IsNullOrEmpty(fill.BackgroundColor.Rgb))
{
if (!string.IsNullOrEmpty(fill.BackgroundColor.Theme))
{
int theme = int.Parse(fill.BackgroundColor.Theme);
return(GetTintColor(Themes[theme], (double)fill.BackgroundColor.Tint));
}
if (fill.BackgroundColor.Indexed > 0 && fill.BackgroundColor.Indexed < 64)
{
var col = System.Windows.Media.Imaging.BitmapPalettes.Halftone64.Colors[fill.BackgroundColor.Indexed];
return(Color.FromArgb(col.A, col.R, col.G, col.B));
}
if (string.IsNullOrEmpty(fill.PatternColor.Rgb))
{
if (fill.PatternColor.Indexed > 0 && fill.PatternColor.Indexed < 64)
{
var col = System.Windows.Media.Imaging.BitmapPalettes.Halftone64.Colors[fill.PatternColor.Indexed];
return(Color.FromArgb(col.A, col.R, col.G, col.B));
}
return(Color.Transparent);
}
return(ColorTranslator.FromHtml("#" + fill.PatternColor.Rgb));
}
return(ColorTranslator.FromHtml("#" + fill.BackgroundColor.Rgb));
}
switch (fill.PatternType)
{
case ExcelFillStyle.Gray125: return(Color.Gray);
case ExcelFillStyle.DarkDown: return(Color.DarkGreen);
case ExcelFillStyle.DarkGray: return(Color.DarkGray);
case ExcelFillStyle.MediumGray: return(Color.MediumTurquoise);
case ExcelFillStyle.LightGray: return(Color.LightGray);
case ExcelFillStyle.Gray0625: return(Color.DarkGray);
case ExcelFillStyle.DarkVertical: return(Color.DarkGray);
case ExcelFillStyle.DarkHorizontal: return(Color.DarkGray);
case ExcelFillStyle.DarkUp: return(Color.DarkGray);
case ExcelFillStyle.DarkGrid: return(Color.DarkGray);
case ExcelFillStyle.DarkTrellis: return(Color.DarkGray);
case ExcelFillStyle.LightVertical: return(Color.LightGray);
case ExcelFillStyle.LightHorizontal: return(Color.LightGray);
case ExcelFillStyle.LightDown: return(Color.LightGray);
case ExcelFillStyle.LightUp: return(Color.LightGray);
case ExcelFillStyle.LightGrid: return(Color.LightGray);
case ExcelFillStyle.LightTrellis: return(Color.LightGray);
}
return(Color.Transparent);
}
public Byte[] GenerateExcel(List <DoctorViewModel> docs)
{
using (var excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = " Ismail Epione";
excelPackage.Workbook.Properties.Title = "List Of doctors";
var sheet = excelPackage.Workbook.Worksheets.Add("Mandate Excel");
sheet.Name = " Doctors In Patient medical Path";
sheet.Column(2).Width = 10;
sheet.Column(3).Width = 30;
sheet.Column(4).Width = 30;
sheet.Column(5).Width = 30;
sheet.Column(6).Width = 30;
sheet.Column(7).Width = 30;
#region Report Header
sheet.Cells[rowIndex, 2, rowIndex, 4].Merge = true;
cell = sheet.Cells[rowIndex, 2];
cell.Value = "DeepOr Epione";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 20;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex = rowIndex + 1;
sheet.Cells[rowIndex, 2, rowIndex, 4].Merge = true;
cell = sheet.Cells[rowIndex, 2];
cell.Value = "List Doctors";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 15;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex = rowIndex + 2;
#endregion
#region Table header
cell = sheet.Cells[rowIndex, 2];
cell.Value = "#";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = "Name & Surname";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = "Email";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 5];
cell.Value = "Phone Number";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 6];
cell.Value = "speciality";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 7];
cell.Value = "tarif";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex = rowIndex + 1;
#endregion
#region Table body
int serialNumber = 1;
if (docs.Count > 0)
{
foreach (DoctorViewModel doc in docs)
{
cell = sheet.Cells[rowIndex, 2];
cell.Value = serialNumber++.ToString();
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = doc.firstName + " " + doc.lastName;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = doc.email;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 5];
cell.Value = doc.phoneNumber;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 6];
cell.Value = doc.speciality;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 7];
cell.Value = doc.tariff;;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex = rowIndex + 1;
}
}
#endregion
return(excelPackage.GetAsByteArray());
}
}
public Fill(ExcelFill fill)
{
_fill = fill;
}
public byte[] GenerateExcel(List <DetalleInv> detalleInvs, string InventName)
{
using (var excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "Francisco F";
excelPackage.Workbook.Properties.Title = "Informe Inventario " + InventName;
var sheet = excelPackage.Workbook.Worksheets.Add("Detalle");
sheet.Name = "Detalle " + InventName;
// sheet.Column(2).Width = 10;
sheet.Column(1).Width = 25;
sheet.Column(2).Width = 60;
sheet.Column(3).Width = 10;
sheet.Column(4).Width = 8;
sheet.Column(5).Width = 25;
sheet.Column(6).Width = 30;
sheet.Column(7).Width = 18;
#region Report Header
#endregion
#region TableHeader
cell = sheet.Cells[rowIndex, 1];
cell.Value = "Código";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 2];
cell.Value = "Descripción";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = "Cantidad";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = "Unidad";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 5];
cell.Value = "Operario";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 6];
cell.Value = "Bodega";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 7];
cell.Value = "Fecha de Ingreso";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightBlue);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
#endregion
rowIndex = rowIndex + 1;
#region TableBody
if (detalleInvs.Count > 0)
{
foreach (DetalleInv item in detalleInvs)
{
cell = sheet.Cells[rowIndex, 1];
cell.Value = item._KOPR;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 2];
cell.Value = item._RNDDescripcion;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = item._Cant;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = item._Unidad;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 5];
cell.Value = item._Operario;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 6];
cell.Value = item._CodBodega + " - " + item._Bodega;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 7];
cell.Value = Convert.ToDateTime(item._Fecha);
cell.Style.Numberformat.Format = "dd-mm-yyyy";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex = rowIndex + 1;
}
}
#endregion
return(excelPackage.GetAsByteArray());
}
}
public EPPlusFill(ExcelFill fill)
{
_fill = fill;
}
public byte[] GenerateExcel(List <TitleAndQuestions> list, int type)
{
if (File.Exists("D:\\FeedBackSystem\\FeedBackSystem\\Excel\\Reports.xlsx") && type == 1)
{
File.Delete("D:\\FeedBackSystem\\FeedBackSystem\\Excel\\Reports.xlsx");
}
var FileName = "D:\\FeedBackSystem\\FeedBackSystem\\Excel\\Reports.xlsx";
var file = new FileInfo(FileName);
using (var excelpckg = new ExcelPackage(file))
{
if (type == 1)
{
ExcelWorksheet sheet = excelpckg.Workbook.Worksheets.Add("UserReports 1");
sheet.Name = "Forms Submition Report";
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 50;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
#region ReportHeader
sheet.Cells[rowIndex, 1, rowIndex, 4].Merge = true;
cell = sheet.Cells[rowIndex, 1];
cell.Value = "Forms Submition Report";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 20;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex += 1;
#endregion
#region TableHeader
cell = sheet.Cells[rowIndex, 1];
cell.Value = "UserName";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 2];
cell.Value = "Question";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = "Comment";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = "Rating";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex += 1;
#endregion
#region tableBody
if (list.Count > 0)
{
foreach (var reports in list)
{
cell = sheet.Cells[rowIndex, 1];
cell.Value = reports.UserName;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 2];
cell.Value = reports.Question;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = reports.Comment;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 4];
cell.Value = reports.Rating;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex += 1;
}
}
#endregion
excelpckg.Save();
}
else if (type == 2)
{
ExcelWorksheet sheet = excelpckg.Workbook.Worksheets.Add("UserReports 2");
sheet.Name = "un submitted report";
sheet.Column(2).Width = 50;
#region ReportHeader
sheet.Cells[rowIndex, 1, rowIndex, 4].Merge = true;
cell = sheet.Cells[rowIndex, 1];
cell.Value = "un submitted report";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 20;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex += 1;
#endregion
#region TableHeader
cell = sheet.Cells[rowIndex, 2];
cell.Value = "UserName";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex += 1;
#endregion
#region tableBody
if (list.Count > 0)
{
foreach (TitleAndQuestions reports in list)
{
cell = sheet.Cells[rowIndex, 2];
cell.Value = reports.UserName;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex += 1;
}
}
#endregion
excelpckg.Save();
}
else if (type == 3)
{
ExcelWorksheet sheet = excelpckg.Workbook.Worksheets.Add("UserReports 3");
sheet.Name = "Average rating for each Question ";
sheet.Column(2).Width = 50;
sheet.Column(3).Width = 20;
#region ReportHeader
sheet.Cells[rowIndex, 1, rowIndex, 4].Merge = true;
cell = sheet.Cells[rowIndex, 1];
cell.Value = "Average rating for each Question ";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 20;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rowIndex += 1;
#endregion
#region table header
cell = sheet.Cells[rowIndex, 2];
cell.Value = "Question";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = " avg Rating";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.LightGray);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex += 1;
#endregion
#region tableBody
if (list.Count > 0)
{
foreach (TitleAndQuestions reports in list)
{
cell = sheet.Cells[rowIndex, 2];
cell.Value = reports.Question;
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
cell = sheet.Cells[rowIndex, 3];
cell.Value = Convert.ToInt32(reports.AvgRating) + "%";
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.White);
border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
rowIndex += 1;
}
}
#endregion
excelpckg.Save();
}
}
return(new byte[10]);
}
/// <summary>
/// 设置表头行的单元格样式
/// </summary>
protected virtual ExcelFill SetColumnHeadFillStyle(ExcelFill fill)
{
//fill.PatternType = ExcelFillStyle.Solid;
return(fill);
}