I am doing a project in MVC and would like to know if there is any way to create dynamic tabs in Excel according to filters and export it later. Example: If the filter only comes with "School" selected, it creates only one tab called School and exports data from schools:
Ifthefiltercomes"School" and "Entity" selected, it creates two tabs called School and Entity and export the data for each.
Yes, there is. I mounted the Helper below that does this. It is based on the EPPlus package. You may have to change this Helper a bit to work correctly for your case.
Note that it uses a lot Reflection :
public static class ExcelHelper
{
public static byte[] ExportarListaParaExcel<T>(IEnumerable<T> lista, String tituloPlanilha = "Exportação para Excel")
where T: class, new()
{
using (var excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "Eu mesmo";
excelPackage.Workbook.Properties.Title = tituloPlanilha;
var sheet = excelPackage.Workbook.Worksheets.Add(tituloPlanilha);
sheet.Name = tituloPlanilha;
var properties = ReflectionUtils.ExtrairPropertiesDeObjeto(new T()).Where(p => !p.Name.EndsWith("Id") && !p.PropertyType.IsGenericType).ToList();
var titulos = properties.Select(p => p.AtributoDisplayName() != null ? p.AtributoDisplayName().DisplayName : p.Name).ToArray();
var i = 1;
foreach (var titulo in titulos)
{
sheet.Cells[1, i++].Value = titulo;
}
var rowIndex = 2;
foreach (var item in lista)
{
var col = 1;
foreach (var prop in properties.Where(p => !p.Name.EndsWith("Id") && !p.PropertyType.IsGenericType))
{
var val = item.GetType().GetProperty(prop.Name).GetValue(item, null);
String str;
if (val == null)
{
str = "";
}
else if (val.GetType().IsPrimitive || val.GetType() == typeof(Decimal) || val.GetType() == typeof(String) || val.GetType() == typeof(DateTime))
{
str = val.ToString();
}
else
{
str = val.GetType().GetProperty("Nome").GetValue(val, null).ToString();
}
sheet.Cells[rowIndex, col++].Value = str ?? "";
}
rowIndex++;
}
return excelPackage.GetAsByteArray();
}
}
public static byte[] ExportarViewModelParaExcel(object viewModel, String tituloPlanilha = "Exportação para Excel")
{
using (var excelPackage = new ExcelPackage())
{
excelPackage.Workbook.Properties.Author = "Eu mesmo";
excelPackage.Workbook.Properties.Title = tituloPlanilha;
foreach (var objeto in ReflectionUtils.ExtrairPropertiesDeObjeto(viewModel).ToList())
{
var sheet = excelPackage.Workbook.Worksheets.Add(objeto.Name);
sheet.Name = objeto.Name;
if (objeto.PropertyType.GetInterfaces().Any(t => t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>)))
{
// É uma lista
var lista = (IEnumerable)objeto.GetValue(viewModel, null);
if (lista == null) continue;
var fields = lista.GetType().GetGenericArguments()[0].GetFields(BindingFlags.Public | BindingFlags.Instance);
var titulos = fields.Select(p => p.AtributoDisplayName() != null ? p.AtributoDisplayName().DisplayName : p.Name).ToArray();
var i = 1;
foreach (var titulo in titulos)
{
sheet.Cells[1, i++].Value = titulo;
}
var rowIndex = 2;
foreach (var item in lista)
{
var col = 1;
foreach (var field in fields)
{
var val = item.GetType().GetField(field.Name).GetValue(item);
String str;
if (val == null)
{
str = "";
}
else if (val.GetType().IsPrimitive || val.GetType() == typeof(Decimal) || val.GetType() == typeof(String) || val.GetType() == typeof(DateTime))
{
str = val.ToString();
}
else
{
str = val.GetType().GetField("Nome").GetValue(val).ToString();
}
sheet.Cells[rowIndex, col++].Value = str ?? "";
}
rowIndex++;
}
}
else
{
// É um objeto
var obj = objeto.GetValue(viewModel, null);
var fields = obj.GetType().GetFields(BindingFlags.Public | BindingFlags.Instance);
var titulos = fields.Select(p => new {
Titulo = p.AtributoDisplayName() != null ? p.AtributoDisplayName().DisplayName : p.Name,
Valor = p.GetValue(obj)
}).ToList();
var i = 1;
foreach (var titulo in titulos)
{
sheet.Cells[i, 1].Value = titulo.Titulo;
sheet.Cells[i++, 2].Value = titulo.Valor;
}
}
}
return excelPackage.GetAsByteArray();
}
}
}
The code that adds a new tab is this:
var sheet = excelPackage.Workbook.Worksheets.Add(objeto.Name);
To send to View :
return File(excelPackage.GetAsByteArray(), System.Net.Mime.MediaTypeNames.Application.Octet, "Exemplo-" + DateTime.Now.ToString() + ".xlsx");
Action can return ActionResult or FileResult .