提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
通过NPOI封装了Excel文件流转List公共函数
提示:以下是本篇文章正文内容,下面案例可供参考
一、NPOI是什么?
自行百度反正是个操作Excel库
二、使用步骤
1.引入库
VsNuGet包管理库打开工具:
![我这里安装的是2.5.2不怕出问题可以安装最新版本](https://img-blog.csdnimg.cn/20210519152538643.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTA0NDEwMQ==,size_16,color_FFFFFF,t_70#pic_center)
2.创建ExcelExpertHelper公共类
代码如下(示例):
/// <summary>
/// 根据对象注解转
/// </summary>
public class ExcelExpertHelper
{
public class ImportExcelUtil<T> where T : new()
{
//合法文件扩展名
private static List<string> extName = new List<string>() { ".xls", ".xlsx" };
/// <summary>
/// 导入Excel内容读取到List<T>中
/// </summary>
/// <param name="file">导入Execl文件</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <returns>List<T></returns>
public static List<T> InputExcel(IFormFile file, string sheetName = null)
{
//获取文件后缀名
string type = Path.GetExtension(file.FileName);
//判断是否导入合法文件
if (!extName.Contains(type))
{
return null;
}
//转成为文件流
MemoryStream ms = new MemoryStream();
file.CopyTo(ms);
ms.Seek(0, SeekOrigin.Begin);
//实例化T数组
List<T> list = new List<T>();
//获取数据
list = InputExcel(ms, sheetName);
return list;
}
/// <summary>
/// 将Excel文件内容读取到List<T>中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名:true=是,false=否</param>
/// <returns>List<T></returns>
public static List<T> InputExcel(string fileName, string sheetName = null)
{
if (!File.Exists(fileName))
{
return null;
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//实例化T数组
List<T> list = new List<T>();
//获取数据
list = InputExcel(fs, sheetName);
return list;
}
/// <summary>
/// 将Excel文件内容读取到List<T>中
/// </summary>
/// <param name="fileStream">文件流</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <returns>List<T></returns>
private static List<T> InputExcel(Stream fileStream, string sheetName = null)
{
//创建Excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名称
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName,则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
//实例化T数组
List<T> list = new List<T>();
if (sheet != null)
{
//一行最后一个cell的编号 即总的列数
IRow cellNum = sheet.GetRow(0);
int num = cellNum.LastCellNum;
//获取泛型对象T的所有注解
PropertyInfo[] peroperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
//每行转换为单个T对象
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
var obj = new T();
for (int j = 0; j < num; j++)
{
//行名称
var colName = cellNum.GetCell(j) + "";
string propName = "";
//去找注解对应的对象字段名
foreach (PropertyInfo property in peroperties)
{
object[] objs = property.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objs.Length > 0 && ((DescriptionAttribute)objs[0]).Description == colName)
{
propName = property.Name;
//没有数据的单元格都默认是null
ICell cell = row.GetCell(j);
if (cell != null)
{
var value = row.GetCell(j).ToString();
//获取对象的属性类型
//string str = property.PropertyType.FullName;
var propType = property.PropertyType;
if (propType == typeof(string))
{
typeof(T).GetProperty(propName).SetValue(obj, value, null);
}
else if (propType==typeof(DateTime)||propType==typeof(DateTime?))
{
//如果等于空
if (string.IsNullOrEmpty(value))
{
typeof(T).GetProperty(propName).SetValue(obj, null, null);
}
else
{
DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
typeof(T).GetProperty(propName).SetValue(obj, pdt, null);
}
}
else if (propType ==typeof(bool))
{
if (string.IsNullOrEmpty(value))
{
typeof(T).GetProperty(propName).SetValue(obj, null, null);
}
else
{
bool pb = Convert.ToBoolean(value);
typeof(T).GetProperty(propName).SetValue(obj, pb, null);
}
}
else if (propType==typeof(Int16)|| propType == typeof(Int16?))
{
if (string.IsNullOrEmpty(value))
{
typeof(T).GetProperty(propName).SetValue(obj, null, null);
}
else
{
short pi16 = Convert.ToInt16(value);
typeof(T).GetProperty(propName).SetValue(obj, pi16, null);
}
}
else if (propType == typeof(Int32)|| propType == typeof(Int32?))
{
if (string.IsNullOrEmpty(value))
{
typeof(T).GetProperty(propName).SetValue(obj, null, null);
}
else
{
int pi32 = Convert.ToInt32(value);
typeof(T).GetProperty(propName).SetValue(obj, pi32, null);
}
}
else if (propType == typeof(Int64) || propType == typeof(Int64?))
{
if (string.IsNullOrEmpty(value))
{
typeof(T).GetProperty(propName).SetValue(obj, null, null);
}
else
{
long pi64 = Convert.ToInt64(value);
typeof(T).GetProperty(propName).SetValue(obj, pi64, null);
}
}
else if (propType == typeof(byte))
{
byte pb = Convert.ToByte(value);
typeof(T).GetProperty(propName).SetValue(obj, pb, null);
}
else
{
typeof(T).GetProperty(propName).SetValue(obj, null, null);
}
}
}
}
}
list.Add(obj);
}
}
return list;
}
}
}
3.调用示例
创建实体:
public class Req_EquipmentImportViewModel
{
/// <summary>
/// 设备ID
/// </summary>
// public string Id { get; set; } = Guid.NewGuid().ToString();
[Description("设备ID")]
public string ImportEqId { get; set; }
[Description("设备身份证编码")]
public string EqIdCard { get; set; }
[Description("设备名称")]
public string EqName { get; set; }
[Description("设备状态")]
public string EqState { get; set; }
//如果属性不需要接受Excel值就不写注解就行,或者Excel中没有这个字段
public string 不要{ get; set; }
}
调用:
//文件流转对应的List
List<Req_EquipmentImportViewModel> equipment = ExcelExpertHelper.ImportExcelUtil<Req_EquipmentImportViewModel>.InputExcel(file);