创建execl导入工具类的步骤
创建execl导入工具类的步骤
发布时间:2017-01-07 来源:查字典编辑
摘要:1、创建实体属性标记复制代码代码如下:publicclassCellAttribute:Attribute{////////////显示名称...

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文件路径,通过通用处理方法导出映射实体

创建execl导入工具类的步骤1

复制代码 代码如下:

[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);

}

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新C#教程学习
热门C#教程学习
编程开发子分类