EPPLus导出excel项目实战，包含合并单元格

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 导出文件

 

posted @   沐乐  阅读(1328)  评论(0)  编辑  收藏  举报
相关博文：
· NGINX配置详解(超全)
· .NET服务端网页截图
· 导出excel,导出文件流并上传系统，导出合并行，合并列列excel
· 使用EPPlus实现Excel导出功能
· core6 EPPlus 导入下载
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！
点击右上角即可分享
微信分享提示