基于NOPI的Execl模板轉換類,直接將Execl模板轉換對應的Entity

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