Create tabs dynamically in excel

Question

I am doing an MVC project and I 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 a tab called School and exports school data:

If the filter comes "School" and "Entity" selected, it creates two tabs called School and Entity and export the data to each one.

Answer 1

Yes, there is. I assembled the Helper below that does it. It is based on the package Epplus. Possibly you will have to change this Helper a little to work properly for your case.

Note that he 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");

To Action can return ActionResult or FileResult.