本文实例为大家分享了C#使用NPOI将excel导入到list的具体代码，供大家参考，具体内容如下
这个是确定是实体类接收
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
/// <summary>/// 将excel导入到list/// </summary>/// <typeparam name="T"></typeparam>/// <param name="fs">Stream 文件流</param>/// <param name="list">转换的Dictionary：例如 ("昵称","nickname")</param>/// <returns></returns>public static List<T> ExcelToList<T>(this Stream fs, Dictionary<string, string> list) where T : class, new() { List<T> ts = new List<T>(); IWorkbook workbook = null; ISheet sheet = null; T t = new T(); List<string> listName = new List<string>(); try { // 获得此模型的公共属性 var propertys = t.GetType().GetProperties().ToList(); workbook = new HSSFWorkbook(fs); if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (rowCount > 0) { IRow firstRow = sheet.GetRow(0);//第一行 int cellCount = firstRow.LastCellNum;//列数 //循环列数 for (int i = 0; i < cellCount; i++) { //循环需要转换的值 foreach (var item in list) { if (item.Key.Equals(firstRow.GetCell(i).StringCellValue)) { //替换表头 firstRow.GetCell(i).SetCellValue(item.Value); } } //获取已经替换的表头 var s = firstRow.GetCell(i).StringCellValue; //添加到listname listName.Add(s); } for (int i = 1; i <= rowCount; i++) { t = new T(); IRow currRow = sheet.GetRow(i);//第i行 for (int k = 0; k < cellCount; k++) { //取值 object value = null; if (currRow.GetCell(k) != null) { firstRow.GetCell(0).SetCellType(CellType.String); currRow.GetCell(k).SetCellType(CellType.String); value = currRow.GetCell(k).StringCellValue; } else { continue; } var Name = string.Empty; //获取第表头的值 Name = listName[k]; //循环属性 foreach (var pi in propertys) { if (pi.Name.Equals(Name)) { //获取属性类型名称 var s = pi.PropertyType.Name; //如果非空，则赋给对象的属性 if (value != DBNull.Value) { //判断属性的类型(可以自行添加) switch (s) { case "Guid": pi.SetValue(t, new Guid(value.ToString()), null); break; case "Int32": pi.SetValue(t, value.ToString() == "" ? 0 : Convert.ToInt32(value.ToString()), null); break; case "Decimal": pi.SetValue(t, value.ToString() == "" ? 0 : Convert.ToDecimal(value.ToString()), null); break; case "DateTime": pi.SetValue(t, Convert.ToDateTime(value.ToString()), null); break; case "Double": pi.SetValue(t, value.ToString() == "" ? 0 : Convert.ToDouble(value.ToString()), null); break; case "String": pi.SetValue(t, value, null); break; default: break; } } } } } //对象添加到泛型集合中 ts.Add(t); } } } } return ts; } catch (Exception ex) { if (fs != null) { fs.Close(); } return null; } } |
调用
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
var list = new Dictionary<string, string>(); list.Add("ID", "TradeAccountId"); list.Add("简介", "Intro"); list.Add("昵称1", "Nickname"); list.Add("限制人数", "SubscribeLimit"); list.Add("模式", "SubscribeMode"); list.Add("收益率", "ProfitRate"); list.Add("收益", "ProfitLossAmount"); list.Add("头像", "Img"); list.Add("平台名称", "Name"); list.Add("用户昵称", "UserNickname"); FileStream fs = new FileStream(@"C:\Users\Administrator\Desktop\Test\Test\bin\Debug\Export\2021-04-27-14-46-36.xls", FileMode.Open); var list3 = fs.ExcelToList<Res_Signal>(list); |
实体类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
[Serializable]public class Res_Signal { /// <summary> /// 交易账号ID /// </summary> public Guid TradeAccountId { get; set; } /// <summary> /// 交易账号简介 /// </summary> public String Intro { get; set; } /// <summary> /// 交易账号昵称 /// </summary> public String Nickname { get; set; } /// <summary> /// 订阅限制值 /// </summary> public Int32 SubscribeLimit { get; set; } /// <summary> /// 订阅模式 目前都是免费 0免费 1收费 /// </summary> public Int32 SubscribeMode { get; set; } /// <summary> /// 订阅模式名称 /// </summary> public String SubscribeMode_Des { get; set; } /// <summary> /// 平台名称 /// </summary> public String Name { get; set; } /// <summary> /// 头像 /// </summary> public String HeadImg { get; set; } /// <summary> /// 用户昵称 /// </summary> public String UserNickname { get; set; } /// <summary> /// 订阅人数 /// </summary> public Int32 SubscribeCount { get; set; } /// <summary> /// 平台图片 /// </summary> public String Img { get; set; } /// <summary> /// 收益率 /// </summary> public decimal ProfitRate { get; set; } /// <summary> /// 总收益 /// </summary> public decimal ProfitLossAmount { get; set; }} |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/qq_41078088/article/details/116239479
