之前写了一个模型导出Excel通用类，但是在实际应用中，可能不是直接导出模型，而是通过Linq查询后获取到最终结果再导出
通用类：
public enum DataTypeEnum
{
Int = ,
Float = ,
Double = ,
String = ,
DateTime = ,
Date =
}
public class ExportFieldInfo
{
/// <summary>
/// 字段名，用于反射获取值
/// </summary>
public string FieldName { get; set; }
/// <summary>
/// 中文名，用于导出标题
/// </summary>
public string DisplayName { get; set; }
/// <summary>
/// 数据类型，用于强制转换，并进行格式化,其实利用反射也可以获取到数据类型，此处因为要处理Date和Date的显示格式
/// </summary>
public DataTypeEnum DataType { get; set; }
}
public class ExcelHelper
{
/// <summary>
/// 导出到内存流
/// </summary>
/// <param name="data">需要导出的模型列表</param>
/// <param name="fieldInfo">导出的字段列表信息</param>
/// <param name="sheetRows">每个工作表的行数</param>
/// <returns></returns>
public static MemoryStream ToExcel(List<object> data, List<ExportFieldInfo> fieldInfo, int sheetRows = )
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//需要生成工作溥总簿
int sheetCount = data.Count / sheetRows + ;
int rowCount = data.Count;
for (int i = ; i < sheetCount; i++)
{
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet" + Convert.ToString(i));
//给sheet添加第一行的头部标题
NPOI.SS.UserModel.IRow rowTitle = sheet.CreateRow();
for (int k = ; k < fieldInfo.Count; k++)
{
rowTitle.CreateCell(k).SetCellValue(fieldInfo.ElementAt(k).DisplayName);
}
//处理Excel一张工作簿只能放65536行记录的问题
//因为头部占一行，所以要减1
for (int j = ; j < sheetRows - ; j++)
{
//将数据逐步写入sheet各个行
NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(j + );
int dataIndex = i * (sheetRows - ) + j;
for (int k = ; k < fieldInfo.Count; k++)
{
//获取类型
Type type = data[dataIndex].GetType();
//获取指定名称的属性
System.Reflection.PropertyInfo propertyInfo = type.GetProperty(fieldInfo.ElementAt(k).FieldName);
//获取属性值
var value = propertyInfo.GetValue(data[dataIndex], null);
switch (fieldInfo.ElementAt(k).DataType)
{
case DataTypeEnum.Int:
rowtemp.CreateCell(k).SetCellValue(Convert.ToInt32(value));
break;
case DataTypeEnum.Float:
rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value));
break;
case DataTypeEnum.Double:
rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value));
break;
case DataTypeEnum.String:
rowtemp.CreateCell(k).SetCellValue(Convert.ToString(value));
break;
case DataTypeEnum.DateTime:
rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss.SSS"));
break;
case DataTypeEnum.Date:
rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));
break;
default:
break;
}
}
//所有记录循环完成
if (i * (sheetRows - ) + (j + ) == rowCount)
{
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(, SeekOrigin.Begin);
return ms;
}
} }
return null;
}
}
调用：
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="request">请求参数集合</param>
/// <returns></returns>
public FileResult ToExcel(ActiveRecordRequest request)
{
//获取记录信息
Response<object> listInfo = this.CrmService.GetActiveRecordList(request);
//初始化需要导出字段
List<ExportFieldInfo> fieldInfo = new List<ExportFieldInfo>();
fieldInfo.Add(new ExportFieldInfo()
{
FieldName = "ActiveDate",
DisplayName = "活动日期",
DataType = DataTypeEnum.Date
});
fieldInfo.Add(new ExportFieldInfo()
{
FieldName = "ActiveSubject",
DisplayName = "活动主题",
DataType = DataTypeEnum.String
});
fieldInfo.Add(new ExportFieldInfo()
{
FieldName = "Remark",
DisplayName = "备注",
DataType = DataTypeEnum.String
});
return File(ExcelHelper.ToExcel(listInfo.Data, fieldInfo), "application/vnd.ms-excel", "活动信息表.xls");
}
js调用：
//导出到Excel
ToExcel: function () {
location.href = "/" + PageParam.Area + "/" + PageParam.AjaxController + "/ToExcel?IsPaging=false";
}