C#代码实现 Excel表格与Object互相转换，Excel表格导入数据库（.NET2.0 .NET4.0）
浏览数：94 /
时间：2015年06月12日
前些天在工作上遇到这个需求，在GitHub找到一个开源代码可以用，Fork了一个版本，整理一下发出来。
①.Net项目中使用Nuget安装一个 NPOI 包 https://github.com/tonyqus/npoi
②再Nuget安装 Chsword.Excel2Object 包 https://github.com/chsword/Excel2Object
也可以直接使用命令行（“Install-Package Chsword.Excel2Object”）
注：上述程序包是作者的源代码，如果项目的.NET版本太低（2.0神马的），比如我们╮(╯▽╰)╭ ，可以跳过此步骤直接复制下面的代码。
我把.NET4.0的语法都替换掉了，用到的类都揉到一个页面了，没有作者那么条理清晰，引用方便一些。
1 using System; 2 using System.Collections; 3 using System.Collections.Generic; 4 using System.IO; 5 using System.Reflection; 6 using NPOI.HSSF.UserModel; 7 using NPOI.SS.UserModel; 8 9 10 namespace Bu.Function 11 { 12 13 /// <summary> 14 /// excel转object 15 /// </summary> 16 17 public class ExcelAttribute : Attribute 18 { 19 public ExcelAttribute(string name) 20 { 21 Title = name; 22 } 23 24 public int Order { get; set; } 25 public string Title { get; set; } 26 } 27 28 29 public class ExcelImporter 30 { 31 public IEnumerable<TModel> ExcelToObject<TModel>(string path, int? type = null) where TModel : class, new() 32 { 33 var result = GetDataRows(path); 34 var dict = ExcelUtil.GetExportAttrDict<TModel>(); 35 var dictColumns = new Dictionary<int, KeyValuePair<PropertyInfo, ExcelAttribute>>(); 36 37 IEnumerator rows = result; 38 39 var titleRow = (IRow)rows.Current; 40 if (titleRow != null) 41 foreach (var cell in titleRow.Cells) 42 { 43 var prop = new KeyValuePair<PropertyInfo, ExcelAttribute>(); 44 foreach (var item in dict) 45 { 46 if (cell.StringCellValue == item.Value.Title) 47 { 48 prop = item; 49 } 50 } 51 52 if (prop.Key != null && !dictColumns.ContainsKey(cell.ColumnIndex)) 53 { 54 dictColumns.Add(cell.ColumnIndex, prop); 55 } 56 } 57 while (rows.MoveNext()) 58 { 59 var row = (IRow)rows.Current; 60 if (row != null) 61 { 62 var firstCell = row.GetCell(0); 63 if (firstCell == null || firstCell.CellType == CellType.Blank || 64 string.IsNullOrEmpty(firstCell.ToString())) 65 continue; 66 } 67 68 var model = new TModel(); 69 70 foreach (var pair in dictColumns) 71 { 72 var propType = pair.Value.Key.PropertyType; 73 if (propType == typeof(DateTime?) || 74 propType == typeof(DateTime)) 75 { 76 pair.Value.Key.SetValue(model, GetCellDateTime(row, pair.Key), null); 77 } 78 else 79 { 80 81 try 82 { 83 var val= Convert.ChangeType(GetCellValue(row, pair.Key), propType); 84 pair.Value.Key.SetValue(model, val, null); 85 } 86 catch (Exception ex) 87 { 88 break; 89 } 90 91 92 } 93 } 94 yield return model; 95 } 96 97 } 98 99 string GetCellValue(IRow row, int index) 100 { 101 var result = string.Empty; 102 try 103 { 104 switch (row.GetCell(index).CellType) 105 { 106 case CellType.Numeric: 107 result = row.GetCell(index).NumericCellValue.ToString(); 108 break; 109 case CellType.String: 110 result = row.GetCell(index).StringCellValue; 111 break; 112 case CellType.Blank: 113 result = string.Empty; 114 break; 115 116 #region 117 118 //case CellType.Formula: 119 // result = row.GetCell(index).CellFormula; 120 // break; 121 //case CellType.Boolean: 122 // result = row.GetCell(index).NumericCellValue.ToString(); 123 // break; 124 //case CellType.Error: 125 // result = row.GetCell(index).NumericCellValue.ToString(); 126 // break; 127 //case CellType.Unknown: 128 // result = row.GetCell(index).NumericCellValue.ToString(); 129 // break; 130 131 #endregion 132 default: 133 result = row.GetCell(index).ToString(); 134 break; 135 } 136 } 137 catch (Exception e) 138 { 139 Console.WriteLine(e); 140 } 141 return (result ?? "").Trim(); 142 } 143 IEnumerator GetDataRows(string path) 144 { 145 if (string.IsNullOrEmpty(path)) 146 return null; 147 HSSFWorkbook hssfworkbook; 148 try 149 { 150 using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) 151 { 152 hssfworkbook = new HSSFWorkbook(file); 153 } 154 } 155 catch (Exception) 156 { 157 return null; 158 } 159 ISheet sheet = hssfworkbook.GetSheetAt(0); 160 IEnumerator rows = sheet.GetRowEnumerator(); 161 rows.MoveNext(); 162 return rows; 163 } 164 165 DateTime? GetCellDateTime(IRow row, int index) 166 { 167 DateTime? result = null; 168 try 169 { 170 switch (row.GetCell(index).CellType) 171 { 172 case CellType.Numeric: 173 try 174 { 175 result = row.GetCell(index).DateCellValue; 176 } 177 catch (Exception e) 178 { 179 Console.WriteLine(e); 180 } 181 break; 182 case CellType.String: 183 var str = row.GetCell(index).StringCellValue; 184 if (str.EndsWith("年")) 185 { 186 DateTime dt; 187 if (DateTime.TryParse((str + "-01-01").Replace("年", ""), out dt)) 188 { 189 result = dt; 190 } 191 } 192 else if (str.EndsWith("月")) 193 { 194 DateTime dt; 195 if (DateTime.TryParse((str + "-01").Replace("年", "").Replace("月", ""), out dt)) 196 { 197 result = dt; 198 } 199 } 200 else if (!str.Contains("年") && !str.Contains("月") && !str.Contains("日")) 201 { 202 try 203 { 204 result = Convert.ToDateTime(str); 205 } 206 catch (Exception) 207 { 208 try 209 { 210 result = Convert.ToDateTime((str + "-01-01").Replace("年", "").Replace("月", "")); 211 } 212 catch (Exception) 213 { 214 result = null; 215 } 216 } 217 } 218 else 219 { 220 DateTime dt; 221 if (DateTime.TryParse(str.Replace("年", "").Replace("月", ""), out dt)) 222 { 223 result = dt; 224 } 225 } 226 break; 227 case CellType.Blank: 228 break; 229 #region 230 231 #endregion 232 } 233 } 234 catch (Exception e) 235 { 236 Console.WriteLine(e); 237 } 238 return result; 239 } 240 } 241 242 243 class ExcelExporter 244 { 245 public byte[] ObjectToExcelBytes<TModel>(IEnumerable<TModel> data) 246 { 247 var workbook = new HSSFWorkbook(); 248 var sheet = workbook.CreateSheet(); 249 var attrDict = ExcelUtil.GetExportAttrDict<TModel>(); 250 var attrArray = new KeyValuePair<PropertyInfo, ExcelAttribute>[] { }; 251 int aNum = 0; 252 foreach (var item in attrDict) 253 { 254 attrArray[aNum] = item; 255 aNum++; 256 257 } 258 259 for (int i = 0; i < attrArray.Length; i++) 260 { 261 sheet.SetColumnWidth(i, 50 * 256); 262 } 263 var headerRow = sheet.CreateRow(0); 264 265 for (int i = 0; i < attrArray.Length; i++) 266 { 267 headerRow.CreateCell(i).SetCellValue(attrArray[i].Value.Title); 268 } 269 int rowNumber = 1; 270 foreach (var item in data) 271 { 272 var row = sheet.CreateRow(rowNumber++); 273 for (int i = 0; i < attrArray.Length; i++) 274 { 275 row.CreateCell(i).SetCellValue((attrArray[i].Key.GetValue(item, null) ?? "").ToString()); 276 } 277 } 278 using (var output = new MemoryStream()) 279 { 280 workbook.Write(output); 281 var bytes = output.ToArray(); 282 return bytes; 283 } 284 } 285 286 287 } 288 289 290 291 292 public class ExcelHelper 293 { 294 /// <summary> 295 /// import file excel file to a IEnumerable of TModel 296 /// </summary> 297 /// <typeparam name="TModel"></typeparam> 298 /// <param name="path">excel full path</param> 299 /// <returns></returns> 300 public static IEnumerable<TModel> ExcelToObject<TModel>(string path) where TModel : class, new() 301 { 302 var importer = new ExcelImporter(); 303 return importer.ExcelToObject<TModel>(path); 304 305 } 306 307 /// <summary> 308 /// Export object to excel file 309 /// </summary> 310 /// <typeparam name="TModel"></typeparam> 311 /// <param name="data">a IEnumerable of TModel</param> 312 /// <param name="path">excel full path</param> 313 public static void ObjectToExcel<TModel>(IEnumerable<TModel> data, string path) where TModel : class, new() 314 { 315 var importer = new ExcelExporter(); 316 var bytes = importer.ObjectToExcelBytes(data); 317 File.WriteAllBytes(path, bytes); 318 } 319 } 320 321 322 internal class ExcelUtil 323 { 324 public static Dictionary<PropertyInfo, ExcelAttribute> GetExportAttrDict<T>() 325 { 326 var dict = new Dictionary<PropertyInfo, ExcelAttribute>(); 327 foreach (var propertyInfo in typeof(T).GetProperties()) 328 { 329 var attr = new object(); 330 var ppi = propertyInfo.GetCustomAttributes(true); 331 for (int i = 0; i < ppi.Length; i++) 332 { 333 if (ppi[i] is ExcelAttribute) 334 { 335 attr = ppi[i]; 336 break; 337 } 338 } 339 340 if (attr != null) 341 { 342 343 dict.Add(propertyInfo, attr as ExcelAttribute); 344 345 } 346 } 347 return dict; 348 } 349 } 350 351 352 353 354 }
③准备一段 Demo Code
public class ReportModel { [Excel("标题",Order=1)] public string Title { get; set; } [Excel("用户",Order=2)] public string Name { get; set; } }
准备一个List
var models = new List<ReportModel> { new ReportModel{Name="a",Title="b"}, new ReportModel{Name="c",Title="d"}, new ReportModel{Name="f",Title="e"} };
④由Object转为Excel
var exporter = new ExcelExporter(); var bytes = exporter.ObjectToExcelBytes(models); File.WriteAllBytes("C:\\demo.xls", bytes);
由Excel转为Object
var importer = new ExcelImporter(); IEnumerable<ReportModel> result = importer.ExcelToObject<ReportModel>("c:\\demo.xls");
转成Object再验证，存入数据库神马的 是不是就so easy啦~
与ASP.NET MVC结合使用
由于ASP.NET MVC中Model上会使用DisplayAttribute所以Excel2Object除了支持ExcelAttribute外，也支持DisplayAttribute。
C#代码实现 Excel表格与Object互相转换，Excel表格导入数据库（.NET2.0 .NET4.0）,古老的榕树,5-wow.com
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
