.Net webapi MiniExcel生成Excel

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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242

/// <summary>
/// Excel帮助类
/// </summary>
public class ExcelHelper
{
/// <summary>
/// 生成Excel
已知类型
/// </summary>
/// <typeparam name="T">数据库查询实体得类型</typeparam>
/// <param name="sheetName">sheet名</param>
/// <param name="lists">数据源</param>
public static string GenerateExcel<T>(List<T> lists) where T : class
{
//如果当前得泛型集合为0 说明数据库中没有数据 直接返回
//获取随机文件名
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel{fileName}";
//MiniExcel.SaveAs(path, lists);
if (lists.Count == 0)
{
MiniExcel.SaveAs(path, null, true, "data", ExcelType.XLSX);
return path;
}
//判断可以写几个工作表 按一个65536行算
int count = lists.Count;
var page = Math.Ceiling((double)count / 65536);
var sheets = new Dictionary<string, object>();
//生成工作表和数据得字典
for (int i = 0; i < page; i++)
{
var data = lists.GetRange(i * 65535, 65535 - 1 * (i + 1));
sheets.Add($"sheet{i}", data);
}
//循环便利生成字典
var dictionarys = new List<Dictionary<string, object>>();
foreach (var item in lists)
{
Type type = item.GetType();
//获取T得所有字段名
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
//循环 properties 拿出他得字段名和value
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
//根据名字获取到当前循环得item(属性名) 得值
var vao = property.GetValue(item);
dictionary.Add(name, vao);
}
dictionarys.Add(dictionary);
}
//生成EXCEL
var config = new OpenXmlConfiguration()
{
TableStyles = TableStyles.Default
};
MiniExcel.SaveAsAsync(path, sheets, configuration: config);
return path;
}
/// <summary>
/// 生成备份模板 已知类型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static string GenerateExcelTemp<T>() where T : class
{
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel{fileName}";
Type type = typeof(T);
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
var name = property.Name;
dictionary.Add(name, "");
}
var dictionarys = new List<Dictionary<string, object>>();
dictionarys.Add(dictionary);
MiniExcel.SaveAs(path, dictionarys, true, "sheet1", ExcelType.XLSX);
return path;
}
/// <summary>
/// 不落地生成Excel模板
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static MemoryStream GenerateExcelTempStream<T>() where T : class
{
Type type = typeof(T);
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
var name = property.Name;
dictionary.Add(name, "由此往下填充即可");
}
var dictionarys = new List<Dictionary<string, object>>();
dictionarys.Add(dictionary);
var memoryStream = new MemoryStream();
memoryStream.SaveAs(dictionarys, sheetName: "temp");
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
/// <summary>
/// 不落地生成Excel备份文件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static MemoryStream GenerateExcelStream<T>(List<T> lists) where T : class
{
var type = typeof(T);
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var memoryStream = new MemoryStream();
if (lists.Count == 0)
{
var _dictionarys = new List<Dictionary<string, object>>();
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
//根据名字获取到当前循环得item(属性名) 得值
dictionary.Add(name, "无数据");
}
_dictionarys.Add(dictionary);
memoryStream.SaveAs(_dictionarys, sheetName: "data0");
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
//判断可以写几个工作表 按一个65536行算
int count = lists.Count;
var page = Math.Ceiling((double)count / 65536);
var sheets = new Dictionary<string, object>();
//生成工作表和数据得字典
for (int i = 0; i < page; i++)
{
if (count >= 65535 - 1 * (i + 1))
{
var data = lists.GetRange(i * 65535, 65535 - 1 * (i + 1));
sheets.Add($"sheet{i}", data);
}
else
{
var data = lists.GetRange(i * 65535, count );
sheets.Add($"sheet{i}", data);
}
}
//循环便利生成字典
var dictionarys = new List<Dictionary<string, object>>();
foreach (var item in lists)
{
//获取T得所有字段名
var dictionary = new Dictionary<string, object>();
//循环 properties 拿出他得字段名和value
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
//根据名字获取到当前循环得item(属性名) 得值
var vao = property.GetValue(item);
dictionary.Add(name, vao);
}
dictionarys.Add(dictionary);
}
//生成EXCEL
var config = new OpenXmlConfiguration()
{
TableStyles = TableStyles.Default
};
//MiniExcel.SaveAsAsync(path, sheets, configuration: config);
memoryStream.SaveAs(sheets, configuration: config);
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
/// <summary>
/// 根据type 生成Excel 未知类型
/// </summary>
/// <param name="type"></param>
/// <param name="sheetName"></param>
/// <param name="list">必须是集合类型</param>
/// <returns></returns>
public static string GenerateExcel(Type type, string sheetName, List<object> lists)
{
//获取随机文件名
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel{fileName}";
//MiniExcel.SaveAs(path, lists);
if (lists.Count == 0)
{
MiniExcel.SaveAs(path, null, true, sheetName, ExcelType.XLSX);
return path;
}
//循环便利生成字典
var dictionarys = new List<Dictionary<string, object>>();
foreach (var item in lists)
{
//获取T得所有字段名
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
//循环 properties 拿出他得字段名和value
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
var typeValue = (dynamic)item;
// dynamic.InvokeGet(typeValue, name);
var vao = Microsoft.VisualBasic.CompilerServices.Versioned.CallByName(typeValue, name, CallType.Get);
//根据名字获取到当前循环得item(属性名) 得值
dictionary.Add(name, vao);
}
dictionarys.Add(dictionary);
}
//生成EXCEL
MiniExcel.SaveAs(path, dictionarys, true, sheetName, ExcelType.XLSX);
return path;
}
/// <summary>
/// 根据type 生成Excel 未知类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static string GenerateExcelTemp(Type type)
{
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel{fileName}";
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
var name = property.Name;
dictionary.Add(name, "");
}
var dictionarys = new List<Dictionary<string, object>>();
dictionarys.Add(dictionary);
MiniExcel.SaveAs(path, dictionarys, true, "sheet1", ExcelType.XLSX);
return path;
}
}

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

/// <summary>
/// 备份
/// </summary>
/// <param name="backUpEnum">
///
0. UsersInfo,
///
1. TaskScheduled,
///
2. SystemPosition,
///
3. SystemMenu,
///
4. Room,
///
5. PositionSystemMenu,
///
6. OperationAudit,
///
7. LeaveDetails,
///
8. CcDetail,
///
9. AuditFlowDetail,
/// 10. AuditFlow,
/// 11. MapCoordinatesInfo,
/// 12. Community,
/// 13. Building,
/// 14. Resident,
/// 15. NucleicAcidSiteStaff,
/// 16. NucleicAcidSite,
/// 17. NucleicAcid,
/// 18. Infected,
/// </param>
/// <returns></returns>
[HttpGet("UserBackUp")]
public async Task<IActionResult> UserBackUp(BackUpEnum backUpEnum)
{
using (var conn = _connectFactory.GetOpenConn())
{
var typeName = backUpEnum.ToString();
// var type = typeof(typeName);
var type = CreateTypeHelper.typen(typeName);
//数据源
var data = await conn.Connection.SqlQueryable<object>($"select * from {typeName}").ToListAsync();
var result = ExcelHelper.GenerateExcel(type, "data", data);
FileStream fs = new FileStream(result, FileMode.Open);
this.HttpContext.Response.Headers.Add("Content-Length", fs.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(fs, "application/octet-stream;charset=UTF-8", Path.GetFileName(result));
}
}
/// <summary>
/// 下载备份模板
/// </summary>
/// <param name="backUpEnum">
///
0. UsersInfo,
///
1. TaskScheduled,
///
2. SystemPosition,
///
3. SystemMenu,
///
4. Room,
///
5. PositionSystemMenu,
///
6. OperationAudit,
///
7. LeaveDetails,
///
8. CcDetail,
///
9. AuditFlowDetail,
/// 10. AuditFlow,
/// 11. MapCoordinatesInfo,
/// 12. Community,
/// 13. Building,
/// 14. Resident,
/// 15. NucleicAcidSiteStaff,
/// 16. NucleicAcidSite,
/// 17. NucleicAcid,
/// 18. Infected,
/// </param>
/// <returns></returns>
[HttpGet("DownloadTemp")]
public IActionResult DownloadTemp(BackUpEnum backUpEnum)
{
var typeName = backUpEnum.ToString();
// var type = typeof(typeName);
var type = CreateTypeHelper.typen(typeName);
var result = ExcelHelper.GenerateExcelTemp(type);
FileStream fs = new FileStream(result, FileMode.Open);
this.HttpContext.Response.Headers.Add("Content-Length", fs.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(fs, "application/octet-stream;charset=UTF-8", Path.GetFileName(result));
}

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
 /// <summary>
/// 用户在处理完接口之后格式化返回Json格式数据
/// </summary>
public class ReturnResultProcessingFilerAttribute : Attribute, IAsyncResultFilter
{
public async Task OnResultExecutionAsync(ResultExecutingContext context, ResultExecutionDelegate next)
{
//特殊处理：对有ApiIgnoreAttribute标签的，不进行返回结果包装，原样输出
var controllerActionDescriptor = context.ActionDescriptor as ControllerActionDescriptor;
if (controllerActionDescriptor != null)
{
var isDefined = controllerActionDescriptor.EndpointMetadata.Any(a => a.GetType().Equals(typeof(ApiIgnoreAttribute)));
if (isDefined)
{
return;
}
}
// 返回结果为JsonResult的请求进行Result包装
if (context.Result != null)
{
if (context.Result is ObjectResult)
{
var result = context.Result as ObjectResult;
context.Result = new JsonResult(new { code = 200, msg = "success", data = result!.Value });
}
else if (context.Result is EmptyResult)
{
context.Result = new JsonResult(new { code = 200, msg = "success", data = new { } });
}
else if (context.Result is ContentResult)
{
var result = context.Result as ContentResult;
context.Result = new JsonResult(new { code = result!.StatusCode, msg = result.Content });
}
else if (context.Result is JsonResult)
{
var result = context.Result as JsonResult;
context.Result = new JsonResult(result?.Value);
}
else if (context.Result is FileStreamResult)
{
//当返回类型为FileStreamResult直接返回
}
else
{
throw new Exception($"未经处理的Result类型：{context.Result.GetType().Name}");
}
}
await next.Invoke();
//返回完数据把文件干掉 
if (context.Result is FileStreamResult)
{
//获取到文件得绝对路径
var path = ((System.IO.FileStream)((Microsoft.AspNetCore.Mvc.FileStreamResult)context.Result).FileStream).Name;
if (Path.GetExtension(path) == ".xlsx")
{
File.Delete(path);
}
}
}

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

/// <summary>
/// 备份
/// </summary>
/// <returns></returns>
[HttpGet("UserBackUp")]
public async Task<IActionResult> UserBackUp()
{
using (var conn = _connectFactory.GetOpenConn())
{
var data = await conn.Connection.SqlQueryable<UsersInfo>($"select * from UsersInfo").ToListAsync();
var result = ExcelHelper.GenerateExcelStream<UsersInfo>(data);
this.HttpContext.Response.Headers.Add("Content-Length", result.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(result, "application/octet-stream;charset=UTF-8", Path.GetRandomFileName()+".xlsx");
}
}
/// <summary>
/// 下载备份模板
/// </summary>

/// <returns></returns>
[HttpGet("DownloadTemp")]
public IActionResult DownloadTemp()
{
var result = ExcelHelper.GenerateExcelTempStream<UsersInfo>();
//FileStream fs = new FileStream(result, FileMode.Open);
this.HttpContext.Response.Headers.Add("Content-Length", result.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(result, "application/octet-stream;charset=UTF-8", Path.GetFileName(Path.GetRandomFileName()+".xlsx"));
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
	public enum BackUpEnum
{
UsersInfo,
TaskScheduled,
SystemPosition,
SystemMenu,
Room,
PositionSystemMenu,
OperationAudit,
LeaveDetails,
CcDetail,
AuditFlowDetail,
AuditFlow,
MapCoordinatesInfo,
Community,
Building,
Resident,
NucleicAcidSiteStaff,
NucleicAcidSite,
NucleicAcid,
Infected,
}