If the goal is to generate an XLS or XLSX, what you are trying does not make sense. The correct one is use a NuGet package that helps you generate an Excel :
PM> Install-Package EPPlus
I've mounted a Helper that does this export:
using MeuProjeto.Common.Ferramentas;
using MeuProjeto.Common.Models;
using MeuProjeto.Models;
using OfficeOpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.Mvc;
namespace MeuProjeto.Helpers
{
public static class ExcelHelper
{
public static byte[] ExportarListaParaExcel<T>(IEnumerable<T> lista, String tituloPlanilha = "Exportação para Excel")
where T: class, IEntidade, 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")).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")))
{
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();
}
}
}
}
You also need to add this static class here somewhere in your project:
public static class ReflectionUtils
{
/// <summary>
///
/// </summary>
/// <param name="objeto"></param>
/// <returns></returns>
public static IEnumerable<PropertyInfo> ExtrairPropertiesDeObjeto(Object objeto)
{
return objeto.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public |
BindingFlags.NonPublic | BindingFlags.FlattenHierarchy);
}
/// <summary>
///
/// </summary>
/// <param name="property"></param>
/// <returns></returns>
public static DisplayNameAttribute ExtrairAtributoDisplayNameDeProperty(PropertyInfo property)
{
var atributo = (DisplayNameAttribute)property.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault();
return atributo;
}
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public static DisplayNameAttribute ExtrairAtributoDisplayNameDeField(FieldInfo field)
{
var atributo = (DisplayNameAttribute)field.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault();
return atributo;
}
/// <summary>
///
/// </summary>
/// <param name="property"></param>
/// <returns></returns>
public static DisplayNameAttribute AtributoDisplayName(this PropertyInfo property)
{
return ExtrairAtributoDisplayNameDeProperty(property);
}
/// <summary>
///
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public static DisplayNameAttribute AtributoDisplayName(this FieldInfo field)
{
return ExtrairAtributoDisplayNameDeField(field);
}
}
Usage:
public ActionResult ExportarParaExcel()
{
var lista = /* Faça a sua seleção aqui */
return File(ExcelHelper.ExportarListaParaExcel<MeuModel>(lista), System.Net.Mime.MediaTypeNames.Application.Octet, "teste.xlsx");
}
