之前一直在使用Excel导入的方式进行数据的批量录入,之前是将数据转化为DatTable,然后通过遍历Rows获取数据,有时候为了能拿到指定的集合类型,还要新建集合数据存储数据,而且不通用。每次有新的文件导入的时候就要单独处理,代码复用性低,重复开发。
这篇文章是直接通过泛型数据,通过导入特性映射直接将excel文件数据转化出来,读到就是所要的集合数据,很是方便,而且通用性很强,安利安利!!!
上代码
/// <summary>
/// excel读取
/// </summary>
/// <param name="path">excel文件路径</param>
/// <param name="isNPOI">是否为NPOI方式</param>
/// <param name="startRow">从第几行开始读取</param>
/// <param name="loadSheetIndex">所加载的sheet序号</param>
/// <returns></returns>
public static List<T> ReadExcel<T>(string path, bool isNPOI = true, int startRow = 1,int loadSheetIndex = 0) where T : class, new()
{
var t = new T(); //实例化泛型类
var list = new List<T>(); //返回列表接受读取数据;
ISheet sheet;
var attrs = AttributeExtension.GetExcelImporterAttribute(t).Where(s => s.Ignore == false || s.IsSummaryTitle == false); //根据类获取特性集合
var attrFields = attrs.Select(s => s.Field).ToArray();
var summarytitleList = AttributeExtension.GetExcelImporterAttribute(t).Where(_ => _.IsSummaryTitle).ToList(); // 标题字段集合
var extension = Path.GetExtension(path);
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
stream.Position = 0;
if (isNPOI)
{
#region NPOI读取Excel
IWorkbook workbook = null;
if (extension == ".xls")
workbook = new HSSFWorkbook(stream);
else
workbook = new XSSFWorkbook(stream);
sheet = workbook.GetSheetAt(loadSheetIndex);
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
T obj = new T();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
var field = attrFields[j];
var cellValue = row.GetCell(j);
if (cellValue != null)
{
SetValue(field, cellValue, obj);
}
}
list.Add(obj);
}
if (workbook != null)
{
workbook.Close();
workbook = null;
}
#endregion
}
else
{
#region Aspose 读取Excel
Workbook workbookAspose = new Workbook(path);
Cells cells = workbookAspose.Worksheets[loadSheetIndex].Cells;
//从1开始,第一行为标题行
try
{
for (int i = startRow; i < cells.MaxDataRow + 1; i++)
{
var row = cells.GetRow(i);
if (row == null) continue;
T obj = new T();
for (int j = 0; j < cells.MaxDataColumn + 1; j++)
{
var field = attrFields[j];
var cellValue = row[j].StringValue?.Trim();
if (cellValue != null)
{
SetValue(field, cellValue.Replace("\n", " "), obj);
}
}
// 给标题行字段赋值
foreach (var item in summarytitleList)
{
var innerRow = cells.GetRow(item.InRow);
if (innerRow == null) continue;
var cellValue = innerRow[item.InCells].StringValue.Trim();
if (cellValue == null) continue;
SetValue(item.Field, cellValue, obj);
}
list.Add(obj);
}
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
if (stream != null)
{
stream.Close();
stream.Dispose();
}
}
return list;
}
这是上面读取excel方法中需要使用的数据赋值的方法
/// <summary>
/// 类型转换
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="field">列名</param>
/// <param name="cellValue">读取到的单元格内容</param>
/// <param name="t"></param>
public static void SetValue<T>(string field, object cellValue, T t)
{
var prop = t.GetType().GetProperty(field).PropertyType;
var typeName = prop.Name;
switch (typeName)
{
case nameof(System.String): //string
var stringValue = ConvertExtension.ToString(cellValue).Trim(new char[2] { '\'', '‘' });
t.GetType().GetProperty(field)?.SetValue(t, stringValue);
break;
case "Nullable`1": //int
SetNullableValue(prop.GenericTypeArguments[0].Name, field, cellValue, t);
break;
case nameof(System.Guid): //int
var gValue = ConvertExtension.ToGuid(cellValue);
t.GetType().GetProperty(field)?.SetValue(t, gValue);
break;
case nameof(System.Int32): //int
var intValue = ConvertExtension.ToInt(cellValue);
t.GetType().GetProperty(field)?.SetValue(t, intValue);
break;
case nameof(System.Decimal): //int
var decimalValue = ConvertExtension.ToDecimal(cellValue);
t.GetType().GetProperty(field)?.SetValue(t, decimalValue);
break;
case nameof(System.DateTime): //int
var datetimelValue = ConvertExtension.ToDateTime(cellValue);
t.GetType().GetProperty(field)?.SetValue(t, datetimelValue);
break;
default:
break;
}
}
ToolEquExcelModel是作为导入的一个演示的实体类,这个类里面需要使用 [ExcelProperity]特性,关于这个特性的定义后面会有代码
public class ToolEquExcelModel
{
/// <summary>
/// 描 述:资产编号
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public string tool_no { get; set; }
/// <summary>
/// 描 述:工装类型
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public ToolTypeEnum tool_type { get; set; }
/// <summary>
/// 描 述:物料号
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public string item_code { get; set; }
/// <summary>
/// 描 述:描述
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public string item_desc { get; set; }
/// <summary>
/// 描 述:工装状态
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public ToolStatusEnum status { get; set; }
/// <summary>
/// 描 述:涉及产品
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public string production { get; set; }
/// <summary>
/// 描 述:存储地点
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public string storage { get; set; }
/// <summary>
/// 描 述:货架位置
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public string shelf { get; set; }
/// <summary>
/// 描 述:入库时间
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public DateTime? in_date { get; set; }
/// <summary>
/// 描 述:工装所属公司
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public string belong { get; set; }
/// <summary>
/// 描 述:是否启用领用校验
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public bool is_use_check { get; set; }
/// <summary>
/// 描 述:是否启用归还校验
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public bool is_back_check { get; set; }
/// <summary>
/// 描 述:是否删除
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public bool is_delete { get; set; }
/// <summary>
/// 描 述:是否生效
/// 默 认 值:
/// 是否空值:True
/// </summary>
[ExcelProperity]
public bool? is_valid { get; set; }
}
这是实体类需要使用的特性定义
[AttributeUsage(AttributeTargets.Property)]
public class ExcelProperityAttribute : Attribute
{
/// <summary>
/// 数据行,非Excelindex
/// </summary>
public int InRow { get; set; }
/// <summary>
/// 数据列,非Excelindex
/// </summary>
public int InCells { get; set; }
/// <summary>
/// 是否为主标题数据
/// </summary>
public bool IsSummaryTitle { get; set; }
/// <summary>
/// 是否忽略
/// </summary>
public bool Ignore { get; set; } = false;
/// <summary>
/// 字段
/// </summary>
public string Field { get; set; }
/// <summary>
/// Excel 表头名
/// </summary>
public string Title { get; set; }
}
最后就是方法的调用了,这里是使用了上面定义的ToolEquExcelModel实体作为验证,最后返回的就是这个类的集合,很是方便
这里直接通过泛型调用,得到的即为所需要的实体,实体的顺序和表格中列的书序请保持一直
调用
var dt = ReadExcel<ToolEquExcelModel>(path, false, 1);