1、创建实体属性标记
复制代码 代码如下:
public class CellAttribute : Attribute
{
/// <summary>
///
/// </summary>
/// <param name="displayName">显示名称</param>
/// <param name="hander"></param>
public CellAttribute(string displayName, Type hander = null)
{
DisplayName = displayName;
Hander = hander;
}
/// <summary>
/// 显示名称
/// </summary>
public string DisplayName { get; set; }
/// <summary>
/// 类型
/// </summary>
public Type Hander { get; set; }
}
2、创建通用处理方法
复制代码 代码如下:
public class XlsFileHandler<T> where T : new()
{
private readonly string _path;
private readonly Dictionary<string, CellAttribute> _cellAttributes;
readonly Dictionary<string, string> _propDictionary;
public XlsFileHandler(string path)
{
_path = path;
_cellAttributes = new Dictionary<string, CellAttribute>();
_propDictionary = new Dictionary<string, string>();
CreateMappers();
}
/// <summary>
/// 创建映射
/// </summary>
private void CreateMappers()
{
foreach (var prop in typeof(T).GetProperties())
{
foreach (CellAttribute cellMapper in prop.GetCustomAttributes(false).OfType<CellAttribute>())
{
_propDictionary.Add(cellMapper.DisplayName, prop.Name);
_cellAttributes.Add(cellMapper.DisplayName, cellMapper);
}
}
}
/// <summary>
/// 获取整个xls文件对应行的T对象
/// </summary>
/// <returns></returns>
public List<T> ToData()
{
List<T> dataList = new List<T>();
using (FileStream stream = GetStream())
{
IWorkbook workbook = new HSSFWorkbook(stream);
ISheet sheet = workbook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
int lastCell = 0;
int i = 0;
IRow headRow = null;
while (rows.MoveNext())
{
var row = sheet.GetRow(i);
if (i == 0)
{
headRow = sheet.GetRow(0);
lastCell = row.LastCellNum;
}
else
{
T t = GetData(workbook, headRow, row, lastCell);
dataList.Add(t);
}
i++;
}
stream.Close();
}
return dataList;
}
/// <summary>
/// 获取T对象
/// </summary>
/// <param name="workbook"></param>
/// <param name="headRow"></param>
/// <param name="currentRow"></param>
/// <param name="lastCell"></param>
/// <returns></returns>
private T GetData(IWorkbook workbook, IRow headRow, IRow currentRow, int lastCell)
{
T t = new T();
for (int j = 0; j < lastCell; j++)
{
var displayName = headRow.Cells[j].StringCellValue;
if (!_cellAttributes.ContainsKey(displayName) || !_propDictionary.ContainsKey(displayName))
{
continue;
}
var currentAttr = _cellAttributes[displayName];
var propName = _propDictionary[displayName];
ICell currentCell = currentRow.GetCell(j);
string value = currentCell != null ? GetCellValue(workbook, currentCell) : "";
if (currentAttr.Hander != null)
{
SetValue(ref t, propName, InvokeHandler(currentAttr.Hander, value));
}
else
{
SetValue(ref t, propName, value);
}
}
return t;
}
/// <summary>
/// 动态执行处理方法
/// </summary>
/// <param name="type"></param>
/// <param name="value"></param>
/// <returns></returns>
private static object InvokeHandler(Type type, object value)
{
System.Reflection.ConstructorInfo constructor = type.GetConstructor(Type.EmptyTypes);
if (constructor == null) throw new ArgumentNullException("type");
object mgConstructor = constructor.Invoke(null);
System.Reflection.MethodInfo method = type.GetMethod("GetResults");
return method.Invoke(mgConstructor, new[] { value });
}
/// <summary>
/// 获取文件流
/// </summary>
/// <returns></returns>
private FileStream GetStream()
{
if (!File.Exists(_path)) throw new FileNotFoundException("path");
return new FileStream(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
}
/// <summary>
/// 获取xls文件单元格的值
/// </summary>
/// <param name="workbook"></param>
/// <param name="cell"></param>
/// <returns></returns>
private static string GetCellValue(IWorkbook workbook, ICell cell)
{
string value;
switch (cell.CellType)
{
case CellType.FORMULA:
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
value = evaluator.Evaluate(cell).FormatAsString();
break;
default:
value = cell.ToString();
break;
}
return value;
}
/// <summary>
/// 设置T属性值
/// </summary>
/// <param name="t"></param>
/// <param name="propName"></param>
/// <param name="value"></param>
private static void SetValue(ref T t, string propName, object value)
{
var typeName = t.GetType().GetProperty(propName).PropertyType.Name;
var property = t.GetType().GetProperty(propName);
switch (typeName)
{
case "Int32":
property.SetValue(t, Convert.ToInt32(value), null);
break;
case "DateTime":
property.SetValue(t, Convert.ToDateTime(value), null);
break;
case "Decimal":
property.SetValue(t, Convert.ToDecimal(value), null);
break;
default:
property.SetValue(t, value, null);
break;
}
}
}
3、创建Execl文件映射类
复制代码 代码如下:
public class ReadMapper
{
[CellAttribute("测试1")]
public decimal Code { get; set; }
[CellAttribute("测试2")]
public int Name { get; set; }
[CellAttribute("测试3", typeof(ClassCellHander))]
public string Group { get; set; }
[CellAttribute("测试4")]
public DateTime AddTime { get; set; }
}
4、指定Execl文件路径，通过通用处理方法导出映射实体
复制代码 代码如下:
[Test]
public void Read1()
{
const string filePath = @"C:UserszkDesktop1.xls";
XlsFileHandler<ReadMapper> handler = new XlsFileHandler<ReadMapper>(filePath);
List<ReadMapper> readMappers = handler.ToData();
Assert.AreEqual(readMappers.Count, 3);
}