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 }
