1.项目引入EPPLus依赖包
![]()
2.定义excel导出属性公共类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
namespace FtFactory.Dto
{
/// <summary>
/// excel 公共属性类
/// </summary>
public class ExcelCommonDto
{
public class ExcelMemberInfos
{
/// <summary>
/// 属性信息
/// </summary>
public PropertyInfo PropertyInfo { get; set; }
/// <summary>
/// 列名
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// 排序
/// </summary>
public int Order { get; set; }
/// <summary>
/// 是否合并行
/// </summary>
public bool IsMergeRow { get; set; }
}
public class ExcelAttribute : Attribute
{
/// <summary>
/// 列名
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// 是否忽略此属性
/// </summary>
public bool Ignore { get; set; }
/// <summary>
/// 列的顺序
/// </summary>
public int ColumnOrder { get; set; }
/// <summary>
/// 是否合并行
/// </summary>
public bool IsMergeRow { get; set; }
}
}
}
3. 导出excel公共方法
1 using FtFactory.ExcelModel;
2 using OfficeOpenXml;
3 using OfficeOpenXml.Style;
4 using System;
5 using System.Collections;
6 using System.Collections.Generic;
7 using System.Drawing;
8 using System.IO;
9 using System.Linq;
10 using System.Reflection;
11 using static FtFactory.Dto.ExcelCommonDto;
12
13 namespace FtFactory.Common
14 {
15 /// <summary>
16 /// excel 操作公共类
17 /// </summary>
18 public static class ExcelCommon
19 {
20 /// <summary>
21 /// 初始化表头
22 /// </summary>
23 /// <typeparam name="T"></typeparam>
24 public class InitExcelHeader<T> where T : class
25 {
26 /// <summary>
27 /// 需要输出的实体对象的成员集合
28 /// </summary>
29 public List<ExcelMemberInfos> IncludeMembers { get; set; }
30
31 public InitExcelHeader(List<T> list)
32 {
33 IncludeMembers = new List<ExcelMemberInfos>();
34 InitModel(list);
35 }
36
37 /// <summary>
38 /// 初始化表头
39 /// </summary>
40 /// <param name="list"></param>
41 private void InitModel(List<T> list)
42 {
43 var props = typeof(T).GetProperties();
44 InitPropertys(props, IncludeMembers);
45 IncludeMembers = IncludeMembers.OrderBy(x => x.Order).ToList();
46 }
47
48 public void InitPropertys(PropertyInfo[] propertyInfos, List<ExcelMemberInfos> IncludeMembers)
49 {
50 if (propertyInfos != null)
51 {
52 foreach (var prop in propertyInfos)
53 {
54 if (prop.Name.ToUpper() == "TYPEID")
55 {
56 continue;
57 }
58 var info = new ExcelMemberInfos
59 {
60 PropertyInfo = prop,
61 Order = 0,
62 ColumnName = prop.Name
63 };
64 if (prop.PropertyType.Name.Contains("List"))
65 {
66 var childProps = Type.GetType("FtFactory.Models" + "." + prop.Name).GetProperties();
67 InitPropertys(childProps, IncludeMembers);
68 }
69 var attrs = prop.GetCustomAttributes(typeof(ExcelAttribute), true);
70 if (attrs.Length <= 0)
71 {
72 continue;
73 }
74 var attr = (ExcelAttribute)attrs.FirstOrDefault();
75 if (attr == null)
76 {
77 continue;
78 }
79 if (attr.Ignore)
80 {
81 continue;
82 }
83 if (!string.IsNullOrEmpty(attr.ColumnName))
84 {
85 info.ColumnName = attr.ColumnName;
86 }
87 if (attr.IsMergeRow)
88 {
89 info.IsMergeRow = attr.IsMergeRow;
90 }
91 info.Order = attr.ColumnOrder;
92 IncludeMembers.Add(info);
93 }
94 }
95 }
96 }
97
98 /// <summary>
99 /// 导出excel
100 /// </summary>
101 /// <typeparam name="T"></typeparam>
102 /// <param name="excelList">数据及</param>
103 /// <returns></returns>
104 public static byte[] ExportToExcel<T>(this List<T> excelList) where T : class
105 {
106 try
107 {
108 ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
109 using var excelPack = new ExcelPackage();
110 var worksheet = excelPack.Workbook.Worksheets.Add("sheet1");
111 var epcExcelInfo = new InitExcelHeader<T>(excelList); // 初始化表头信息
112 for (var i = 0; i < epcExcelInfo.IncludeMembers.Count(); i++) //填充excel数据
113 {
114 int startWriteRow = 2; // 默认从第二行写数据
115 int endMergeRow = 0; // 合并结束行
116 int mergeRow = 1; // 合并行数
117 int lastMergeRow = 0;
118 var memberInfos = epcExcelInfo.IncludeMembers[i]; // 导出配置属性
119 worksheet.Cells[1, i + 1].Value = memberInfos?.ColumnName; // 表头赋值，默认为第一行
120 var propName = memberInfos.PropertyInfo.Name; // 获取表头字段名
121 for (var j = 0; j < excelList.Count; j++)
122 {
123 var childitem = excelList[j].GetType().GetProperties(); // 获取输入数据属性匹配配置属性
124 for (var n = 0; n < childitem.Length; n++)
125 {
126 if (propName == childitem[n].Name)
127 {
128 worksheet.Cells[startWriteRow + endMergeRow, i + 1].Value = childitem[n].GetValue(excelList[j], null)?.ToString();
129 if (!memberInfos.IsMergeRow)
130 {
131 startWriteRow++;
132 }
133 }
134 if (childitem[n].PropertyType.Name.Contains("List"))
135 {
136 var itemvalue = (IList)childitem[n].GetValue(excelList[j]);
137 mergeRow = itemvalue.Count;
138 endMergeRow += itemvalue.Count;
139 foreach (var o in itemvalue)
140 {
141 var childitem2 = o.GetType().GetProperties();
142 for (var m = 0; m < childitem2.Length; m++)
143 {
144 if (propName == childitem2[m].Name)
145 {
146 worksheet.Cells[startWriteRow, i + 1].Value = childitem2[m].GetValue(o, null)?.ToString();
147 startWriteRow++;
148 }
149 }
150 }
151 }
152 }
153 if (memberInfos.IsMergeRow && endMergeRow > 1)
154 {
155 if (mergeRow > 1)
156 {
157 if (j == 0)
158 {
159 worksheet.Cells[2, i + 1, endMergeRow + 1, i + 1].Merge = true; // 第一条数据默认从第二行开始合并
160 }
161 else
162 {
163 worksheet.Cells[lastMergeRow + 2, i + 1, endMergeRow + 1, i + 1].Merge = true;
164 }
165 }
166 lastMergeRow = endMergeRow; // 下次合并开始行为上次合并末行
167 }
168 }
169 }
170 worksheet.Cells.AutoFitColumns();
171 worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
172 worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
173 worksheet.Row(1).Style.Font.Bold = true; // 标题加粗
174 var stream = new MemoryStream();
175 excelPack.SaveAs(stream);
176 byte[] bytes = stream.ToArray();
177 stream.Close();
178 return bytes;
179 }
180 catch (Exception)
181 {
182 return null;
183 }
184 }
185 }
186 }
4.定义导出类的实例
4.1 统计excel一级类
using FtFactory.Models;
using System.Collections.Generic;
using static FtFactory.Dto.ExcelCommonDto;
namespace FtFactory.ExcelModel
{
public class StopLineExcelModel
{
/// <summary>
/// 停线类型
/// </summary>
[Excel(ColumnName = "停线类型", ColumnOrder = 1, IsMergeRow = true)]
public string typeKey { get; set; }
/// <summary>
/// 停线次数
/// </summary>
[Excel(ColumnName = "总停线次数", ColumnOrder = 2, IsMergeRow = true)]
public int stopLineCount { get; set; }
/// <summary>
/// 停线时长
/// </summary>
[Excel(ColumnName = "总停线时长", ColumnOrder = 3, IsMergeRow = true)]
public decimal stopLineTimeTotal { get; set; }
/// <summary>
/// 详情数据
/// </summary>
[Excel(Ignore = true)]
public List<FtStopLineBu> FtStopLineBu { get; set; }
}
}
4.2 统计excel二级类
using static FtFactory.Dto.ExcelCommonDto;
namespace FtFactory.Models
{
public partial class FtStopLineBu
{
/// <summary>
/// 主键GUID
/// </summary>
[Excel(Ignore = true)]
public string Guid { get; set; }
/// <summary>
/// 序列号
/// </summary>
[Excel(Ignore = true)]
public string Sn { get; set; }
/// <summary>
/// 任务ID
/// </summary>
[Excel(Ignore = true)]
public string TaskId { get; set; }
/// <summary>
/// 停线位置
/// </summary>
[Excel(Ignore = true)]
public string StopPosition { get; set; }
/// <summary>
/// 停线类型
/// </summary>
[Excel(Ignore = true)]
public string StopType { get; set; }
/// <summary>
/// 原因
/// </summary>
[Excel(ColumnName = "停线原因", ColumnOrder = 11)]
public string StopReason { get; set; }
/// <summary>
/// 开始时间
/// </summary>
[Excel(ColumnName = "开始时间", ColumnOrder = 5)]
public string StartTime { get; set; }
/// <summary>
/// 图片地址
/// </summary>
[Excel(Ignore = true)]
public string Images { get; set; }
/// <summary>
/// 责任部门
/// </summary>
[Excel(ColumnName = "责任科室", ColumnOrder = 4)]
public string ResponseDepart { get; set; }
/// <summary>
/// 创建人
/// </summary>
[Excel(Ignore = true)]
public string CreatePerson { get; set; }
/// <summary>
/// 处理人工号
/// </summary>
[Excel(Ignore = true)]
public string Operator { get; set; }
/// <summary>
/// 处理措施
/// </summary>
[Excel(ColumnName = "处理措施", ColumnOrder = 12)]
public string Operation { get; set; }
/// <summary>
/// 结束时间/恢复时间
/// </summary>
[Excel(ColumnName = "结束时间", ColumnOrder = 6)]
public string EndTime { get; set; }
/// <summary>
/// 判责人工号
/// </summary>
[Excel(Ignore = true)]
public string DecisionPerson { get; set; }
/// <summary>
/// 最终责任部门
/// </summary>
[Excel(Ignore = true)]
public string FinalResponseDepart { get; set; }
/// <summary>
/// 删除标识
/// </summary>
[Excel(Ignore = true)]
public string DeleteFlag { get; set; }
/// <summary>
/// 创建时间
/// </summary>
[Excel(Ignore = true)]
public string CreateTime { get; set; }
/// <summary>
/// 更新时间
/// </summary>
[Excel(Ignore = true)]
public string UpdateTime { get; set; }
/// <summary>
/// 节点ID
/// </summary>
[Excel(Ignore = true)]
public string Processor { get; set; }
/// <summary>
/// 下一节点
/// </summary>
[Excel(Ignore = true)]
public string NextProcessor { get; set; }
/// <summary>
/// 状态
/// </summary>
[Excel(Ignore = true)]
public string Status { get; set; }
/// <summary>
/// 责任人
/// </summary>
[Excel(ColumnName = "责任人", ColumnOrder = 10)]
public string ResponsiblePerson { get; set; }
/// <summary>
/// 处理人
/// </summary>
[Excel(ColumnName = "处理人", ColumnOrder = 8)]
public string Remark1 { get; set; }
/// <summary>
/// 判责人
/// </summary>
[Excel(ColumnName = "判责人", ColumnOrder = 9)]
public string Remark2 { get; set; }
/// <summary>
/// 处理人工号
/// </summary>
[Excel(Ignore = true)]
public string Remark3 { get; set; }
/// <summary>
/// 节点标识
/// </summary>
[Excel(Ignore = true)]
public string Remark4 { get; set; }
/// <summary>
/// 停线时长
/// </summary>
[Excel(ColumnName = "停线时长", ColumnOrder = 7)]
public string Remark5 { get; set; }
/// <summary>
/// 设备名称
/// </summary>
[Excel(ColumnName = "设备名称", ColumnOrder = 13)]
public string EquipmentName { get; set; }
/// <summary>
/// 设备编号
/// </summary>
[Excel(ColumnName = "设备编号", ColumnOrder = 14)]
public string EquipmentNo { get; set; }
/// <summary>
/// 工厂代码
/// </summary>
[Excel(ColumnName = "工厂代码", ColumnOrder = 15)]
public string FactoryCode { get; set; }
}
}
5. 调用生成excel公共方法
/// <summary>
/// 停线通报统计导出
/// </summary>
/// <param name="reportModel"></param>
/// <returns></returns>
[HttpPost]
public async Task<IActionResult> ExportStopLineReport([FromBody] StopLineReportModel reportModel)
{
if (string.IsNullOrEmpty(reportModel.ReportKey))
{
return BadRequest("统计类型不能为空！");
}
StopLineService stopLineService = new(_context, Configuration);
var groupStoplineData = stopLineService.GetStopLineReport(reportModel);
var streamBytes = ExcelCommon.ExportToExcel(groupStoplineData);
return File(streamBytes, "application/octet-stream", "停线通报导出.xlsx");
}
6.前端调用后端生成excel方法
6.1 前端下载公共方法
1 // 导出文件流为excel（xlsx类型）
2 export function downloadBlob(data, fileName, fileType) {
3 if (!data) {
4 return;
5 }
6 const content = data;
7 const blob = new Blob([content], { type: fileType });
8 if ('download' in document.createElement('a')) {
9 // 非IE下载
10 const elink = document.createElement('a');
11 elink.download = fileName || '';
12 elink.style.display = 'none';
13 elink.href = URL.createObjectURL(blob);
14 document.body.appendChild(elink);
15 elink.click();
16 URL.revokeObjectURL(elink.href); // 释放URL 对象
17 document.body.removeChild(elink);
18 } else {
19 // IE10+下载
20 navigator.msSaveBlob(blob, fileName);
21 }
22 }
6.2 导出事件
1 exportMonPlan(){
2 const OPTIONS = {
3 url: exportMonPlan,
4 responseType: 'blob',
5 data: this.searchform,
6 method: 'POST'
7 };
8 axiosCommon(OPTIONS).then((result)=>{
9 downloadBlob(result.data, '文件名.xlsx', 'application/vnd.ms-excel');
10 })
11 }
7 导出文件
![]()
浙公网安备 33010602011771号