創立execl導入對象類的步調。本站提示廣大學習愛好者:(創立execl導入對象類的步調)文章只能為提供參考,不一定能成為您想要的結果。以下是創立execl導入對象類的步調正文
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:\Users\zk\Desktop\1.xls";
XlsFileHandler<ReadMapper> handler = new XlsFileHandler<ReadMapper>(filePath);
List<ReadMapper> readMappers = handler.ToData();
Assert.AreEqual(readMappers.Count, 3);
}