记录使用NPOI生成Excel的通用方法
NPOI安装，使用Nuget：
Install-Package NPOI
使用泛型方法，反射两种方式实现生成Excel
第一种方法
有指定Header类型的方式：
/// <summary>
/// 指定表头,根据List生成Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filePath"></param>
/// <param name="excelHeaders"></param>
/// <param name="data"></param>
/// <returns></returns>
public static bool CreateExcel<T>(string filePath, List<ExcelHeader> excelHeaders, List<T> data) where T : class
{
try
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//新增一个Row
IRow headerRow = sheet1.CreateRow(0);
for (int i = 0; i < excelHeaders.Count; i++)
{
//新增单元格
headerRow.CreateCell(i).SetCellValue(excelHeaders[i].HeaderName);
}
int count = 1;
foreach (T item in data)
{
//循环新增
IRow row = sheet1.CreateRow(count);
for (int i = 0; i < excelHeaders.Count; i++)
{
//反射处理
Type t = item.GetType();
PropertyInfo propertyInfo = t.GetProperties().FirstOrDefault(w => w.Name == excelHeaders[i].ColumnName);
if (propertyInfo != null)
{
row.CreateCell(i).SetCellValue(propertyInfo.GetValue(item, null).ToString());
}
}
count++;
}
//保存文件
using (var f = File.Create(filePath))
{
workbook.Write(f);
}
}
catch (Exception ex)
{
throw ex;
}
return true;
}
ExcelHeader类：
public class ExcelHeader
{
public string HeaderName { get; set; }
public string ColumnName { get; set; }
}
第二种方法
这个减少去手动指定需要生成Excel内容的字段信息。会根据List<T>全部生成Excel，逻辑都是一样的：
/// <summary>
/// T 实体类必须指定DisplayName，根据DisplayName 生成列名
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filePath"></param>
/// <param name="data"></param>
/// <returns></returns>
public static bool CreateExcel<T>(string filePath, List<T> data) where T:class
{
try
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
IRow headerRow = sheet1.CreateRow(0);
//列名
PropertyInfo[] columnsPropertyInfo = data.FirstOrDefault().GetType().GetProperties();
int cellCount = 0;
foreach (var column in columnsPropertyInfo)
{
//根据实体类中DisplayName 生成列名
headerRow.CreateCell(cellCount).SetCellValue(column.GetCustomAttribute<DisplayNameAttribute>().DisplayName);
cellCount++;
}
//item
int rowCount = 1;
foreach (T item in data)
{
IRow row = sheet1.CreateRow(rowCount);
int count = 0;
foreach (PropertyInfo propertyInfo in item.GetType().GetProperties())
{
row.CreateCell(count).SetCellValue(propertyInfo.GetValue(item, null).ToString());
count++;
}
rowCount++;
}
using (var f = File.Create(filePath))
{
workbook.Write(f);
}
}
catch (Exception ex)
{
throw ex;
}
return true;
}
这里展示在MVC项目中的使用方法：
public ActionResult DownLoad()
{
//获取需要查询的数据集
var data = GetAllData();
//根据查询出数据实体类，指定对应需要生成Excel类容的字段名信息
List<ExcelHeader> headers = new List<ExcelHeader>();
headers.Add(new ExcelHeader() { ColumnName = "AcquittanceId", HeaderName = "收据号" });
headers.Add(new ExcelHeader() { ColumnName = "Company", HeaderName = "公司" });
headers.Add(new ExcelHeader() { ColumnName = "TaxId", HeaderName = "税号" });
headers.Add(new ExcelHeader() { ColumnName = "BackName", HeaderName = "开户行" });
headers.Add(new ExcelHeader() { ColumnName = "BankAccount", HeaderName = "银行账号" });
headers.Add(new ExcelHeader() { ColumnName = "UserName", HeaderName = "姓名" });
headers.Add(new ExcelHeader() { ColumnName = "PhoneNo", HeaderName = "联系方式" });
headers.Add(new ExcelHeader() { ColumnName = "Email", HeaderName = "地址" });
headers.Add(new ExcelHeader() { ColumnName = "CreateTime", HeaderName = "提交时间" });
//生成物理路径
string fileName = Server.MapPath(string.Format("~/Download/{0}.xlsx",DateTime.Now.ToString("yyyyMMddhhmmss")));
//调用生成 第一种方式
//if (ExcelHelper.CreateExcel<SurverTemp>(fileName, headers, data))
//{
//下载
// DownloadHelper.Download(fileName);
//}
//调用生成 第二种方式
if (ExcelHelper.CreateExcel<SurverTemp>(fileName,data))
{
//下载
DownloadHelper.Download(fileName);
}
return null;
}
