private byte[] ReporteObjetivo(List <M_ObjetivoExcel> reporte)
{
try
{
M_Excel excelNew = new M_Excel();
M_Worksheet workSheet = new M_Worksheet
{
Header = "Reporte de Objetivos",
Data = DTHelper.ToDataTable(reporte),
};
workSheet.Columns.Add(new M_Column(0, "Motivo", "Motivo"));
workSheet.Columns.Add(new M_Column(1, "EstadoObjetivo", "Estado"));
workSheet.Columns.Add(new M_Column(4, "Moneda", "Moneda"));
workSheet.Columns.Add(new M_Column(5, "Importe", "Importe"));
workSheet.Columns.Add(new M_Column(6, "FechaVencimiento", "Fecha de Vencimiento", typeof(DateTime)));
workSheet.Columns.Add(new M_Column(7, "Descripcion", "Descripción"));
excelNew.WorksheetList.Add(workSheet);
return(ExcelExportHelper.ExportExcel(excelNew));
}
catch (Exception ex)
{
throw ex;
}
}
private byte[] ReporteRegistro(List <M_RegistroExcel> reporte)
{
try
{
M_Excel excelNew = new M_Excel();
M_Worksheet workSheet = new M_Worksheet
{
Header = "Reporte de Registros",
Data = DTHelper.ToDataTable(reporte),
};
workSheet.Columns.Add(new M_Column(0, "TipoRegistro", "Tipo de Registro"));
workSheet.Columns.Add(new M_Column(1, "Categoria", "Categoría"));
workSheet.Columns.Add(new M_Column(2, "SubCategoria", "Subcategoría"));
workSheet.Columns.Add(new M_Column(3, "Moneda", "Moneda"));
workSheet.Columns.Add(new M_Column(4, "Importe", "Importe"));
workSheet.Columns.Add(new M_Column(5, "Fecha", "Fecha", typeof(DateTime)));
workSheet.Columns.Add(new M_Column(6, "Descripcion", "Descripción"));
excelNew.WorksheetList.Add(workSheet);
return(ExcelExportHelper.ExportExcel(excelNew));
}
catch (Exception ex)
{
throw ex;
}
}
public static byte[] ExportExcel(M_Excel excel, bool formateoCeldas = true)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
foreach (var item in excel.WorksheetList)
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.Format("{0}", !string.IsNullOrEmpty(item.Name) ? item.Name : "datos"));
int startRowFrom = string.IsNullOrEmpty(item.Header) ? 1 : 3;
#region [Región: Replace/Remove Columns & Set Position]
List <string> listaColumnas = new List <string>();
DataTable clonedtable = item.Data.Clone();
foreach (DataColumn col in item.Data.Columns)
{
listaColumnas.Add(col.ColumnName);
}
foreach (string columna in listaColumnas)
{
M_Column column = item.Columns.FirstOrDefault(x => x.OldName == columna);
if (column != null && column.Tipo != null)
{
clonedtable.Columns[columna].DataType = column.Tipo;
}
}
foreach (DataRow dr in item.Data.Rows)
{
clonedtable.Rows.Add(dr.ItemArray);
}
foreach (string columna in listaColumnas)
{
M_Column column = item.Columns.FirstOrDefault(x => x.OldName == columna);
if (column != null)
{
clonedtable.Columns[columna].ColumnName = column.NewName;
}
else
{
clonedtable.Columns.RemoveAt(clonedtable.Columns.IndexOf(columna));
}
}
int positionTemp;
for (int write = 0; write < clonedtable.Columns.Count; write++)
{
for (int sort = 0; sort < clonedtable.Columns.Count - 1; sort++)
{
M_Column column = item.Columns.FirstOrDefault(x => x.NewName == clonedtable.Columns[sort].ToString());
M_Column column2 = item.Columns.FirstOrDefault(x => x.NewName == clonedtable.Columns[sort + 1].ToString());
if (column.Position > column2.Position)
{
positionTemp = sort + 1;
clonedtable.Columns[sort + 1].SetOrdinal(sort);
}
}
}
#endregion
#region [Región: Verificar Filas sin datos]
for (int row = 0; row < clonedtable.Rows.Count; row++)
{
for (int col = 0; col < clonedtable.Rows[row].ItemArray.Count(); col++)
{
if (string.IsNullOrEmpty(clonedtable.Rows[row].ItemArray[col].ToString()) && clonedtable.Columns[col].DataType.Name != "DateTime" && clonedtable.Columns[col].DataType.Name != "Decimal")
{
clonedtable.Rows[row].SetField(col, item.Columns.FirstOrDefault(x => x.Position == col).EmptyValue);
}
if (!string.IsNullOrEmpty(clonedtable.Rows[row].ItemArray[col].ToString()) && clonedtable.Columns[col].DataType.Name == "String")
{
clonedtable.Rows[row].SetField(col, HtmlToPlainText(clonedtable.Rows[row].ItemArray[col].ToString()));
}
}
}
#endregion
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(clonedtable, true);
#region [Región: Format Header]
if (workSheet.Cells[startRowFrom, 1].Value != null)
{
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, clonedtable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#9c26b2"));
}
}
#endregion
#region [Región: Format Cells - Add Borders]
if (workSheet.Cells[startRowFrom + 1, 1].Value != null)
{
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + clonedtable.Rows.Count, clonedtable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
}
#endregion
#region [Región: Header]
if (!string.IsNullOrEmpty(item.Header))
{
using (ExcelRange head = workSheet.Cells[string.Format("A1:{0}", CalcularLargoHeader(clonedtable.Columns.Count))])
{
head.Value = item.Header;
head.Style.Font.Size = 16;
head.Merge = true;
}
}
#endregion
#region [Región: Format Types Cells]
if (formateoCeldas)
{
if (workSheet.Cells[startRowFrom + 1, 1].Value != null)
{
for (int i = 1; i <= clonedtable.Columns.Count; i++)
{
for (int j = 1; j <= clonedtable.Rows.Count; j++)
{
using (ExcelRange r = workSheet.Cells[startRowFrom + j, i, startRowFrom + j, i])
{
if (r.Value != null)
{
var type = r.Value.GetType();
switch (type.Name)
{
case "DateTime":
using (ExcelRange r2 = workSheet.Cells[startRowFrom + 1, i, startRowFrom + j, i])
{
r2.Style.Numberformat.Format = "dd/MM/yyyy";
}
break;
default:
break;
}
}
}
}
}
}
}
else
{
if (workSheet.Cells[startRowFrom + 1, 1].Value != null)
{
for (int i = 1; i <= clonedtable.Columns.Count; i++)
{
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, i, startRowFrom + 1, i])
{
if (r.Value != null)
{
var type = r.Value.GetType();
using (ExcelRange r2 = workSheet.Cells[startRowFrom + 1, i, startRowFrom + clonedtable.Rows.Count, i])
{
switch (type.Name)
{
case "DateTime":
r2.Style.Numberformat.Format = "dd/MM/yyyy";
break;
case "string":
r2.Style.Numberformat.Format = "@";
break;
default:
break;
}
}
}
}
}
}
}
#endregion
// AutoFit
workSheet.Cells.AutoFitColumns();
}
result = package.GetAsByteArray();
}
return(result);
}