通用的导入Execl工具类_.NET_编程开发_程序员俱乐部

1、创建实体属性标记

 1  public class CellAttribute : Attribute
 2     {
 3         /// <summary>
 4         /// 
 5         /// </summary>
 6         /// <param name="displayName">显示名称</param>
 7         /// <param name="hander"></param>
 8         public CellAttribute(string displayName, Type hander = null)
 9         {
10             DisplayName = displayName;
11 
12             Hander = hander;
13         }
14 
15         /// <summary>
16         /// 显示名称
17         /// </summary>
18         public string DisplayName { get; set; }
19 
20         /// <summary>
21         /// 类型
22         /// </summary>
23         public Type Hander { get; set; }
24     }

2、创建通用处理方法

  1 public class XlsFileHandler<T> where T : new()
  2     {
  3         private readonly string _path;
  4         private readonly Dictionary<string, CellAttribute> _cellAttributes;
  5         readonly Dictionary<string, string> _propDictionary;
  6 
  7         public XlsFileHandler(string path)
  8         {
  9             _path = path;
 10             _cellAttributes = new Dictionary<string, CellAttribute>();
 11             _propDictionary = new Dictionary<string, string>();
 12             CreateMappers();
 13         }
 14 
 15         /// <summary>
 16         /// 创建映射
 17         /// </summary>
 18         private void CreateMappers()
 19         {
 20             foreach (var prop in typeof(T).GetProperties())
 21             {
 22                 foreach (CellAttribute cellMapper in prop.GetCustomAttributes(false).OfType<CellAttribute>())
 23                 {
 24                     _propDictionary.Add(cellMapper.DisplayName, prop.Name);
 25                     _cellAttributes.Add(cellMapper.DisplayName, cellMapper);
 26                 }
 27             }
 28         }
 29 
 30         /// <summary>
 31         /// 获取整个xls文件对应行的T对象
 32         /// </summary>
 33         /// <returns></returns>
 34         public List<T> ToData()
 35         {
 36             List<T> dataList = new List<T>();
 37             using (FileStream stream = GetStream())
 38             {
 39                 IWorkbook workbook = new HSSFWorkbook(stream);
 40                 ISheet sheet = workbook.GetSheetAt(0);
 41                 var rows = sheet.GetRowEnumerator();
 42                 int lastCell = 0;
 43                 int i = 0;
 44                 IRow headRow = null;
 45                 while (rows.MoveNext())
 46                 {
 47                     var row = sheet.GetRow(i);
 48                     if (i == 0)
 49                     {
 50                         headRow = sheet.GetRow(0);
 51                         lastCell = row.LastCellNum;
 52                     }
 53                     else
 54                     {
 55                         T t = GetData(workbook, headRow, row, lastCell);
 56                         dataList.Add(t);
 57                     }
 58                     i++;
 59                 }
 60                 stream.Close();
 61             }
 62             return dataList;
 63         }
 64 
 65         /// <summary>
 66         /// 获取T对象
 67         /// </summary>
 68         /// <param name="workbook"></param>
 69         /// <param name="headRow"></param>
 70         /// <param name="currentRow"></param>
 71         /// <param name="lastCell"></param>
 72         /// <returns></returns>
 73         private T GetData(IWorkbook workbook, IRow headRow, IRow currentRow, int lastCell)
 74         {
 75             T t = new T();
 76             for (int j = 0; j < lastCell; j++)
 77             {
 78                 var displayName = headRow.Cells[j].StringCellValue;
 79                 if (!_cellAttributes.ContainsKey(displayName) || !_propDictionary.ContainsKey(displayName))
 80                 {
 81                     continue;
 82                 }
 83                 var currentAttr = _cellAttributes[displayName];
 84                 var propName = _propDictionary[displayName];
 85 
 86                 ICell currentCell = currentRow.GetCell(j);
 87                 string value = currentCell != null ? GetCellValue(workbook, currentCell) : "";
 88                 if (currentAttr.Hander != null)
 89                 {
 90                     SetValue(ref t, propName, InvokeHandler(currentAttr.Hander, value));
 91                 }
 92                 else
 93                 {
 94                     SetValue(ref t, propName, value);
 95                 }
 96             }
 97             return t;
 98         }
 99 
100         /// <summary>
101         /// 动态执行处理方法
102         /// </summary>
103         /// <param name="type"></param>
104         /// <param name="value"></param>
105         /// <returns></returns>
106         private static object InvokeHandler(Type type, object value)
107         {
108             System.Reflection.ConstructorInfo constructor = type.GetConstructor(Type.EmptyTypes);
109             if (constructor == null) throw new ArgumentNullException("type");
110             object mgConstructor = constructor.Invoke(null);
111             System.Reflection.MethodInfo method = type.GetMethod("GetResults");
112             return method.Invoke(mgConstructor, new[] { value });
113         }
114 
115         /// <summary>
116         /// 获取文件流
117         /// </summary>
118         /// <returns></returns>
119         private FileStream GetStream()
120         {
121             if (!File.Exists(_path)) throw new FileNotFoundException("path");
122             return new FileStream(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
123         }
124 
125         /// <summary>
126         /// 获取xls文件单元格的值
127         /// </summary>
128         /// <param name="workbook"></param>
129         /// <param name="cell"></param>
130         /// <returns></returns>
131         private static string GetCellValue(IWorkbook workbook, ICell cell)
132         {
133             string value;
134             switch (cell.CellType)
135             {
136                 case CellType.FORMULA:
137                     HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
138                     value = evaluator.Evaluate(cell).FormatAsString();
139                     break;
140                 default:
141                     value = cell.ToString();
142                     break;
143             }
144             return value;
145         }
146 
147         /// <summary>
148         /// 设置T属性值
149         /// </summary>
150         /// <param name="t"></param>
151         /// <param name="propName"></param>
152         /// <param name="value"></param>
153         private static void SetValue(ref T t, string propName, object value)
154         {
155             var typeName = t.GetType().GetProperty(propName).PropertyType.Name;
156             var property = t.GetType().GetProperty(propName);
157             switch (typeName)
158             {
159                 case "Int32":
160                     property.SetValue(t, Convert.ToInt32(value), null);
161                     break;
162                 case "DateTime":
163                     property.SetValue(t, Convert.ToDateTime(value), null);
164                     break;
165                 case "Decimal":
166                     property.SetValue(t, Convert.ToDecimal(value), null);
167                     break;
168                 default:
169                     property.SetValue(t, value, null);
170                     break;
171             }
172         }
173     }

3、创建Execl文件映射类

 1 public class ReadMapper
 2     {
 3         [CellAttribute("测试1")]
 4         public decimal Code { get; set; }
 5 
 6         [CellAttribute("测试2")]
 7         public int Name { get; set; }
 8 
 9         [CellAttribute("测试3", typeof(ClassCellHander))]
10         public string Group { get; set; }
11 
12         [CellAttribute("测试4")]
13         public DateTime AddTime { get; set; }
14     }

4、指定Execl文件路径，通过通用处理方法导出映射实体

1   [Test]
2         public void Read1()
3         {
4             const string filePath = @"C:\Users\zk\Desktop\1.xls";
5             XlsFileHandler<ReadMapper> handler = new XlsFileHandler<ReadMapper>(filePath);
6             List<ReadMapper> readMappers = handler.ToData();
7             Assert.AreEqual(readMappers.Count, 3);
8         }