forked from szgit/Helper.Core.Library
-
Notifications
You must be signed in to change notification settings - Fork 0
/
ExcelExtendHelper.cs
216 lines (197 loc) · 7.85 KB
/
ExcelExtendHelper.cs
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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
/*
* 作用:利用 NPOI 读取/写入 Excel 文档,支持多表单文档的读取/写入。
* 联系:QQ 100101392
* 来源:https://github.com/snipen/Helper.Core.Library
* */
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Reflection;
namespace Helper.Core.Library
{
public class ExcelExtendHelper
{
#region 私有属性常量
private const string ExcelFormatErrorException = "Excel 文件后缀不正确!";
#endregion
#region 对外公开方法
/// <summary>
/// 返回多 Sheet 表单数据
/// </summary>
/// <param name="excelPath">Excel 路径</param>
/// <param name="sheetDataList">单元格数据,类型:ReadMultiSheet</param>
/// <param name="reflectionType">反射类型</param>
/// <returns></returns>
public static Dictionary<string, object> ToEntityList(string excelPath, List<object> sheetDataList, ReflectionTypeEnum reflectionType = ReflectionTypeEnum.Expression)
{
Dictionary<string, object> dataDict = new Dictionary<string, object>();
ExcelHelper.ExecuteIWorkbookRead(excelPath, (IWorkbook workbook) =>
{
MethodInfo method = typeof(ExcelHelper).GetMethod("SheetEntityList", BindingFlags.Static | BindingFlags.NonPublic);
foreach (object sheetData in sheetDataList)
{
IReadMultiSheet excelMultiSheet = sheetData as IReadMultiSheet;
if (excelMultiSheet != null)
{
Type sheetType = ReflectionHelper.GetGenericType(sheetData.GetType());
MethodInfo generic = method.MakeGenericMethod(sheetType);
object objectData = generic.Invoke(ExcelHelper.Instance, new object[] {
workbook,
excelMultiSheet.SheetName,
excelMultiSheet.PropertyMatchList,
excelMultiSheet.HeaderIndex,
excelMultiSheet.DataIndex,
excelMultiSheet.PrimaryKey,
reflectionType
});
dataDict.Add(excelMultiSheet.SheetName, objectData);
}
}
});
return dataDict;
}
/// <summary>
/// 创建多 Sheet Excel 文档
/// </summary>
/// <param name="excelPath">Excel 路径</param>
/// <param name="sheetDataList">单元格数据,类型:WriteMultiSheet</param>
/// <param name="reflectionType">反射类型</param>
/// <returns></returns>
public static bool ToExcel(string excelPath, List<object> sheetDataList, ReflectionTypeEnum reflectionType = ReflectionTypeEnum.Expression)
{
bool result = ExcelHelper.ExecuteIWorkbookWrite(excelPath, (IWorkbook workbook) =>
{
MethodInfo method = typeof(ExcelHelper).GetMethod("ToSheet", BindingFlags.Static | BindingFlags.NonPublic);
foreach (object sheetData in sheetDataList)
{
IWriteMultiSheet excelMultiSheet = sheetData as IWriteMultiSheet;
if (excelMultiSheet != null)
{
Type sheetType = ReflectionHelper.GetGenericType(sheetData.GetType());
MethodInfo generic = method.MakeGenericMethod(sheetType);
generic.Invoke(ExcelHelper.Instance, new object[] {
workbook,
ReflectionHelper.GetPropertyValue(sheetData, sheetData.GetType().GetProperty("DataList")),
excelMultiSheet.SheetName,
excelMultiSheet.CellCallback,
excelMultiSheet.SheetCallback,
excelMultiSheet.IsHeader,
excelMultiSheet.PropertyList,
excelMultiSheet.PropertyContain,
excelMultiSheet.PropertyMatchList,
excelMultiSheet.ColumnValueFormat,
reflectionType
});
}
}
});
return result;
}
#endregion
}
#region 逻辑处理接口对象
internal interface IWriteMultiSheet
{
string SheetName { get; set; }
bool IsHeader { get; set; }
string[] PropertyList { get; set; }
bool PropertyContain { get; set; }
object PropertyMatchList { get; set; }
object ColumnValueFormat { get; set; }
Action<ICell, object> CellCallback { get; set; }
Action<ISheet, List<string>> SheetCallback { get; set; }
}
internal interface IReadMultiSheet
{
string SheetName { get; set; }
int HeaderIndex { get; set; }
int DataIndex { get; set; }
string PrimaryKey { get; set; }
object PropertyMatchList { get; set; }
}
#endregion
#region 逻辑处理辅助类
public class ReadMultiSheet<T> : IReadMultiSheet where T : class
{
private int _headerIndex = 0;
private int _dataIndex = 1;
/// <summary>
/// Sheet 名称
/// </summary>
public string SheetName { get; set; }
/// <summary>
/// 表头起始索引,默认值:0,表示第一行是表头数据,与 dataIndex 相同时,表示 Excel 无表头
/// </summary>
public int HeaderIndex
{
get { return this._headerIndex; }
set { this._headerIndex = value; }
}
/// <summary>
/// 数据行起始索引,默认值:1,表示数据从第二行开始
/// </summary>
public int DataIndex
{
get { return this._dataIndex; }
set { this._dataIndex = value; }
}
/// <summary>
/// 主键标识,如果未指定,则表示第一列是主键
/// </summary>
public string PrimaryKey { get; set; }
/// <summary>
/// 属性匹配,Dictionary<string, object> 或 new {}
/// </summary>
public object PropertyMatchList { get; set; }
}
public class WriteMultiSheet<T> : IWriteMultiSheet where T : class
{
private bool _isHeader = true;
private bool _propertyContain = true;
/// <summary>
/// Sheet 表单名称
/// </summary>
public string SheetName { get; set; }
/// <summary>
/// 实体数据列表
/// </summary>
public List<T> DataList { get; set; }
/// <summary>
/// 是否创建表头
/// </summary>
public bool IsHeader
{
get { return this._isHeader; }
set { this._isHeader = value; }
}
/// <summary>
/// 属性筛选列表
/// </summary>
public string[] PropertyList { get; set; }
/// <summary>
/// 是否包含
/// </summary>
public bool PropertyContain
{
get { return this._propertyContain; }
set { this._propertyContain = value; }
}
/// <summary>
/// 属性匹配,Dictionary<string, object> 或 new {}
/// </summary>
public object PropertyMatchList { get; set; }
/// <summary>
/// 日期格式化
/// </summary>
public object ColumnValueFormat { get; set; }
/// <summary>
/// 单元格写入之后调用
/// </summary>
public Action<ICell, object> CellCallback { get; set; }
/// <summary>
/// 表单数据写入之后调用
/// </summary>
public Action<ISheet, List<string>> SheetCallback { get; set; }
}
#endregion
}