前言
我们在日常开发中对Excel的操作可能会比较频繁，好多功能都会涉及到Excel的操作。在.Net Core中大家可能使用Npoi比较多，这款软件功能也十分强大，而且接近原始编程。但是直接使用Npoi大部分时候我们可能都会自己封装一下，毕竟根据二八原则，我们百分之八十的场景可能都是进行简单的导入导出操作，这里就引出我们的主角Npoi。
NPOI简介
NPOI是指构建在POI 3.x版本之上的一个程序，NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。NPOI是一个开源的C#读写Excel、WORD等微软OLE2组件文档的项目。
一、安装相对应的程序包
在 .Net Core 中使用NPOI首先必须先安装NPOI；如下图所示：
1.1、在 “管理NuGet程序包” 中的浏览搜索：“NPOI”
点击安装以上两个即可，安装完成之后最好重新编译一下项目以防出错。
二、新建Excel帮助类
在项目中新建“ExcelHelper”类；此类用于封装导入导出以及其他配置方法。代码如下：
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Collections;
using NPOI.HSSF.Util;
namespace WebApplication1 //命名空间依据自己的项目进行修改
{
/// 对象集合
/// //如果excel某一列列名不存在：以该列的序号作为DataTable的列名，如果DataTable中包含了这个序列为名的列，那么列名为重复列名+序号
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
//excel中的某一列列名不为空，但是重复了：对应的DataTable列名为“重复列名+序号”
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
//正常情况，列名存在且不重复：用excel中的列名作为DataTable中对应的列名
{
string aaa = headerRow.GetCell(i).ToString();
string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key;
DataColumn column = new DataColumn(colName);
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍历
{
try
{
IRow row;
if (sheet.GetRow(i) == null)//如果excel有空行，则添加缺失的行
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍历
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String://字符串
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = default(string);
}
break;
case CellType.Numeric://数字
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//时间戳数字
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula://公式
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
}
}
catch (Exception exception)
{
//loger.Error(exception.ToString());
}
return table;
}
/// prlist = new List();
//获取TResult的类型实例 反射的入口
Type t = typeof(TResult);
//获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表
Array.ForEach(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
//创建返回的集合
List oblist = new List();
foreach (DataRow row in dt.Rows)
{
//创建TResult的实例
TResult ob = new TResult();
//找到对应的数据 并赋值
prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
//放入到返回的集合中.
oblist.Add(ob);
}
return oblist;
}
/// list = new List();
Type type = typeof(T);
//List listColums = new List();
PropertyInfo[] pArray = type.GetProperties(); //集合属性数组
foreach (DataRow row in dt.Rows)
{
T entity = Activator.CreateInstance(); //新建对象实例
foreach (PropertyInfo p in pArray)
{
if (!dt.Columns.Contains(p.Name) || row[p.Name] == null || row[p.Name] == DBNull.Value)
{
continue; //DataTable列中不存在集合属性或者字段内容为空则，跳出循环，进行下个循环
}
if (isStoreDB && p.PropertyType == typeof(DateTime) && Convert.ToDateTime(row[p.Name]) < Convert.ToDateTime("1753-01-01"))
{
continue;
}
try
{
var obj = Convert.ChangeType(row[p.Name], p.PropertyType);//类型强转，将table字段类型转为集合字段类型
p.SetValue(entity, obj, null);
}
catch (Exception)
{
// throw;
}
}
list.Add(entity);
}
return list;
}
/// list = new List(); //实例化一个list
// 在这里写 获取T类型的所有公有属性。 注意这里仅仅是获取T类型的公有属性，不是公有方法，也不是公有字段，当然也不是私有属性
PropertyInfo[] tMembersAll = typeof(T).GetProperties();
for (int i = 0; i < dt.Rows.Count; i++)
{
//创建泛型对象。为什么这里要创建一个泛型对象呢？是因为目前我不确定泛型的类型。
T t = Activator.CreateInstance();
//获取t对象类型的所有公有属性。但是我不建议吧这条语句写在for循环里，因为没循环一次就要获取一次，占用资源，所以建议写在外面
//PropertyInfo[] tMembersAll = t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++)
{
//遍历tMembersAll
foreach (PropertyInfo tMember in tMembersAll)
{
//取dt表中j列的名字，并把名字转换成大写的字母。整条代码的意思是：如果列名和属性名称相同时赋值
if (dt.Columns[j].ColumnName.ToUpper().Equals(tMember.Name.ToUpper()))
{
//dt.Rows[i][j]表示取dt表里的第i行的第j列；DBNull是指数据库中当一个字段没有被设置值的时候的值，相当于数据库中的“空值”。
if (dt.Rows[i][j] != DBNull.Value)
{
//SetValue是指：将指定属性设置为指定值。 tMember是T泛型对象t的一个公有成员，整条代码的意思就是：将dt.Rows[i][j]赋值给t对象的tMember成员,参数详情请参照http://msdn.microsoft.com/zh-cn/library/3z2t396t(v=vs.100).aspx/html
tMember.SetValue(t, Convert.ToString(dt.Rows[i][j]), null);
}
else
{
tMember.SetValue(t, null, null);
}
break;//注意这里的break是写在if语句里面的，意思就是说如果列名和属性名称相同并且已经赋值了，那么我就跳出foreach循环，进行j+1的下次循环
}
}
}
list.Add(t);
}
dt.Dispose();
return list.ToList();
}
/// results = DataTableToList(table);
table.Dispose();
return results;
}
/// results = DataTableToList(table);
table.Dispose();
return results;
}
#endregion
/// 三、调用
3.1、增加一个“keywords”模型类，用作导出
public class keywords
{
[Column("姓名")]
public string IllegalKeywords { get; set; }
}
3.2、添加一个控制器
3.3、编写导入导出的控制器代码
3.3.1、重写“Close”函数
在导出时，为了防止MemoryStream无法关闭从而报错，所以我们继承MemoryStream；代码如下：
namespace WebApplication1 //命名空间依据自己的项目进行修改
{
public class NpoiMemoryStream : MemoryStream
{
public NpoiMemoryStream()
{
AllowClose = true;
}
public bool AllowClose { get; set; }
public override void Close()
{
if (AllowClose)
base.Close();
}
}
}
3.3.2、添加控制器代码
/// keyWordsList = ExcelHelper.ImportExceltoDt(filepath, dir,"Sheet1",0);
#region 将List动态添加至数据库
//……
#endregion
return Json(new { code = 200, msg = "导入成功" });
}
/// keys = new List();
for (int i = 0; i < 6; i++)
{
keywords keyword = new keywords();
keyword.IllegalKeywords = "测试_" + i;
keys.Add(keyword);
}
#endregion
#region 实例化DataTable并进行赋值
DataTable dt = new DataTable();
dt = listToDataTable(keys);//List对象集合转DataTable
#endregion
string filename = DateTime.Now.ToString("在线用户yyyyMMdd") + ".xlsx";
Dictionary<string, string> dir = new Dictionary<string, string>();
dir.Add("IllegalKeywords", "姓名");
XSSFWorkbook book= ExcelHelper.ExportExcel(dt, dir);
dt.Dispose();//释放DataTable所占用的数据资源
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
book.Write(ms, true);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
book.Dispose();//使用由XSSFWorkbook所占用的资源
return File(ms, "application/vnd.ms-excel", Path.GetFileName(filename));//进行浏览器下载
}
3.3.3、Excel导出效果
3.3.4、Excel导入效果
导入后的List再根据需求调用添加方法实现数据的添加
