MVC导出Excel通用型

controller层代码

调用类

封装导出的类

using MaoLiao.Infrastructure.Attributes;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace MaoLiao.Common
{
// Excel相关操作
// 依赖项: NPOI(2.3.0) Nuget 添加命令:Install-Package NPOI -Version 2.3.0
// NPOI(2.3.0)依赖项 SharpZipLib (>= 0.86.0)
public class ExcelHelper<T> where T : new()
{
private static List<ColumnInfo> _columns;
static ExcelHelper()
{
_columns = new List<ColumnInfo>();
foreach (var pro in typeof(T).GetProperties())
{
ExportAttribute attribute = (ExportAttribute)pro.GetCustomAttribute(typeof(ExportAttribute));
if (attribute != null)
{
_columns.Add(new ColumnInfo() { name = attribute.columnName, property = pro });
}
}
}

public static void Export(IEnumerable<T> list, string fileName)
{
HttpContext curContext = HttpContext.Current;
if (curContext != null)
{
if (!fileName.EndsWith(".xls") && !fileName.EndsWith(".xlsx"))
{
fileName += ".xls";
}
using (MemoryStream stream = IEnumerableToStream(list))
{
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "UTF-8";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
curContext.Response.BinaryWrite(stream.ToArray());
curContext.Response.End();
}
}
}

private static MemoryStream IEnumerableToStream(IEnumerable<T> list)
{
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet1 = book.CreateSheet("Sheet1");

//列
IRow titles = sheet1.CreateRow(0);
int col = 0;
foreach (var c in _columns)
{
titles.CreateCell(col++).SetCellValue(c.name);
}

//行
int row = 1;
foreach (var entity in list)
{
col = 0;
IRow _row = sheet1.CreateRow(row++);
foreach (var c in _columns)
{
object value = c.property.GetValue(entity);
string cellValue = value == null ? string.Empty : value.ToString();
_row.CreateCell(col++).SetCellValue(cellValue);
}
}
using (MemoryStream stream = new MemoryStream())
{
book.Write(stream);
return stream;
}
}
}

public class ColumnInfo
{
public string name { get; set; }
public PropertyInfo property { get; set; }
}

}

////导出的模型类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MaoLiao.Infrastructure.Attributes
{
//该特性表示此属性可导出到Excel
//
[AttributeUsage(AttributeTargets.Property)]
public class ExportAttribute : Attribute
{
/// <summary>
/// 导出展示的列名
/// </summary>
/// <param name="columnName"></param>
public ExportAttribute(string columnName)
{
this.columnName = columnName;
}
/// <summary>
/// 导出展示的列名
/// </summary>
public string columnName { get; set; }

}
}

要导出的模型层

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MaoLiao.Infrastructure.Attributes;

namespace MaoLiao.Infrastructure.Model
{
public class AnchorInfo
{
public int id { get; set; }
[Export("IDX")]
public long useridx { get; set; }
[Export("姓名")]
public string myName { get; set; }
[Export("主播类型")]
public int anchorType { get; set; }
public string topic { get; set; }
public int height { get; set; }
public int weight { get; set; }
public string constellation { get; set; }
public string introduce { get; set; }
public string country { get; set; }
public string province { get; set; }
public string city { get; set; }
public string signatures { get; set; }
public DateTime addtime { get; set; }
public int state { get; set; }
public string adminUser { get; set; }
public long phoneNo { get; set; }
public string note { get; set; }
public int sign { get; set; }

[Export("性别")]
public string gender { get; set; }
[Export("时间价格（猫粮）")]
public int videoCallPrice { get; set; }
[Export("钻级")]
public int starLevel { get; set; }
[Export("最后登录时间")]
public DateTime updatetime { get; set; }
public int count { get; set; }
public string labelType { get; set; }

public List<AnchorInfo> List()
{
throw new NotImplementedException();
}
}
}