通用类 对象Excel互转

  1 public class ExcelHelper
  2     {
  3     
  4         public void Demo(string filePath)
  5         {
  6             if (File.Exists(filePath)) File.Delete(filePath);
  7          var   RootElement = new XLWorkbook();
  8             var   workSheet = RootElement.AddWorksheet("Demo");
  9             RootElement.SaveAs(filePath);
 10         }
 11         public void CreatNewFile(string filePath,string workSheetName)
 12         {
 13             if (File.Exists(filePath)) File.Delete(filePath);
 14             var RootElement = new XLWorkbook();
 15             var workSheet = RootElement.AddWorksheet(workSheetName);
 16             RootElement.SaveAs(filePath); 
 17         }
 18 
 19         public List<T> ReadeExcel<T>(string filePath) where T:new()
 20         {
 21             if (!File.Exists(filePath)) throw new Exception("目标文件不存在");
 22             bool result = true;
 23             int index = 1;
 24             string Identity = "";
 25             XLWorkbook RootElement = new XLWorkbook(filePath);
 26             List<T> list = new List<T>();
 27             IXLWorksheet workSheet = RootElement.Worksheet(1);
 28             Dictionary<int, string> keys = new Dictionary<int, string>();
 29             while (result)
 30             {
 31                 Identity = (workSheet.Cell(1, index).Value + "").Trim();
 32                 if (string.IsNullOrEmpty(Identity))result = false;
 33                 else keys.Add(index, Identity);index++;
 34             }
 35             result = true;
 36             index = 2;
 37             while (result)
 38             {
 39                 Identity = workSheet.Cell(index, 1).Value + "";
 40                 if (string.IsNullOrEmpty(Identity))
 41                 {
 42                     result = false;
 43                 }
 44                 else
 45                 {
 46                     var model = new T();
 47                     var t = model.GetType();
 48                     foreach (var item in t.GetProperties())
 49                     {
 50                         var proper = keys.FirstOrDefault(p => p.Value == item.Name);
 51                         if (proper.Value == null)
 52                         {
 53                             item.SetValue(model, "", null);
 54                         }
 55                         else
 56                         {
 57                             item.SetValue(model, workSheet.Cell(index, proper.Key).Value + "", null);
 58                         }
 59                     }
 60                     list.Add(model);
 61                     index++;
 62                 }
 63 
 64             }
 65 
 66 
 67             return list;
 68         }
 69 
 70         public bool WriteExcel<T>(List<T> source,string filePath)
 71         {
 72             var type = typeof(T);
 73             CreatNewFile(filePath, type.Name);
 74             XLWorkbook RootElement = new XLWorkbook(filePath);
 75             List<T> list = new List<T>();
 76             IXLWorksheet workSheet = RootElement.Worksheet(type.Name);
 77             int i = 1;
 78 
 79             Dictionary<int, string> keys = new Dictionary<int, string>();
 80             foreach (var item in type.GetProperties())
 81                 keys.Add(i++, item.Name);
 82 
 83 
 84             i = 1;
 85             foreach (var key in keys)
 86                 workSheet.Cell(i, key.Key).Value = key.Value;
 87 
 88             foreach (var item in source)
 89             {
 90                 i++;
 91                 type = item.GetType();
 92                 foreach (var key in keys)
 93                 {
 94                     var vaue = type.GetProperty(key.Value);
 95                     if (vaue != null) 
 96                         workSheet.Cell(i, key.Key).Value = vaue.GetValue(item); 
 97                     else 
 98                         workSheet.Cell(i, key.Key).Value = ""; 
 99                 } 
100             } 
101             RootElement.Save();
102 
103             return true;
104         }
105 
106 
107     }

 

posted @ 2018-09-03 17:42  个人天使  阅读(282)  评论(0编辑  收藏  举报