首页 > 代码库 > C#代码实现 Excel表格与Object互相转换，Excel表格导入数据库（.NET2.0 .NET4.0）
C#代码实现 Excel表格与Object互相转换，Excel表格导入数据库（.NET2.0 .NET4.0）
前些天在工作上遇到这个需求，在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 =http://www.mamicode.com/= 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 try103 {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 #region117 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 #endregion132 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 try149 {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 try169 {170 switch (row.GetCell(index).CellType)171 {172 case CellType.Numeric:173 try174 {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 try203 {204 result = Convert.ToDateTime(str);205 }206 catch (Exception)207 {208 try209 {210 result = Convert.ToDateTime((str + "-01-01").Replace("年", "").Replace("月", ""));211 }212 catch (Exception)213 {214 result = null;215 }216 }217 }218 else219 {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 #region230 231 #endregion232 }233 }234 catch (Exception e)235 {236 Console.WriteLine(e);237 }238 return result;239 }240 }241 242 243 class ExcelExporter244 {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 ExcelHelper293 {294 /// <summary>295 /// import file excel file to a IEnumerable of TModel296 /// </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 file309 /// </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 ExcelUtil323 {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。
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。